<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Dim conn, cmd, rs, command, param, companyType, item, htmlOut

' Did we POST the form?
if LCase(Request.ServerVariables("HTTP_METHOD")) = "post" then
    companyType = Request.Form("companyType")
else 
    companyType = "construction"
end if

' a string with which we will create the HTML for a table to hold the results
htmlOut = ""

' Note the use of @companyType, that's a text parameter
command = "SELECT id as ID, company as Company, address as Address, city as City, state as ST, postalCode as ZipCode,"
command = command & " phoneNumber as Phone FROM companys WHERE ([type] = @companyType);"

Set cmd = Server.CreateObject("ADODB.Command")
Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

conn.open Application("examples")
Set cmd.ActiveConnection = conn

' This is where we tell ADODB what our parameter is, (an Integer), it's maximum allowed length
' and it's value, from out variable companyID
'
' Doing this means our value is only used as variable to compare against the ID column
' and can not be executed as in-line SQL
Set param = cmd.CreateParameter("@companyType",adVarChar,adParamInput,CLng(Len(companyType)),companyType)

' We must add our parameter to the command objects Parameters collection
cmd.Parameters.Append param

' We also must assign our SQL command to the ADODB command object.
cmd.CommandText = command

' Now we open a record set using the command object we prepared.
rs.Open cmd,,adOpenStatic

' From here down it is similar as any other data access method.
if NOT (rs.eof AND rs.eof) then
    htmlOut = "<table><thead><tr><td colspan="""& rs.fields.count &""">"
    htmlOut = htmlOut & "Number of Records:&nbsp;"& rs.recordcount 
    htmlOut = htmlOut & "</td></tr></thead><tbody><tr>"

    for each item in rs.fields
        htmlOut = htmlOut & "<td>"& item.name &"</td>"
    next
    htmlOut = htmlOut & "</tr><tr>"
    for each item in rs.fields
        htmlOut = htmlOut & "<td>"& item &"</td>"
    next
    htmlOut = htmlOut & "</tr></tbody></table>" 
end if

' clean up
rs.close
Set rs=nothing
conn.close
Set conn=nothing
Set param = nothing
Set cmd = nothing