I am currently delving into parameterized queries with classic ASP and a MySQL database. I have an old page where visitors can search the database using a variety of hierarchical dropdowns that are populated from the database (e.g., Country, State, County). So the content of each dropdown will be filtered based on the selections made with the other dropdowns (7 total). I am trying to convert this page so that it uses parameterized queries.
In searching around the web, I think I have the steps necessary to create the queries correctly, but I am getting the following error.
Microsoft VBScript runtime error '800a01b6' Object doesn't support this property or method: 'Parameters.Append'
My code for the County dropdown is below.
Dim strConnString
strConnString = "Driver={MySQL ODBC 5.2w Driver}; Server=**servername**; Option=16834; User Id=**username**; Password=**pw**; Database=**db**;"
Dim objConn7, objRS7, cmd7
Set objConn7 = Server.CreateObject("ADODB.Connection")
objConn7.Open strConnString
Set cmd7 = Server.CreateObject("ADODB.Command")
cmd7.ActiveConnection = objConn7
cmd7.Prepared = true
cmd7.CommandType = adCmdText
If strSt <> "" Then
If strOrd <> "" Then
If strFam <> "" Then
If strGen <> "" Then
If strSp <> "" Then
cmd7.CommandText = "SELECT DISTINCT(County) FROM specimens WHERE taxOrder= ? AND taxFamily= ? AND Genus= ? AND Species= ? AND Country= ? AND State= ? ORDER BY County ASC;"
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxOrder", adVarChar, adParamInput, 35, strOrd)
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxFamily", adVarChar, adParamInput, 35, strFam)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Genus", adVarChar, adParamInput, 25, strGen)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Species", adVarChar, adParamInput, 60, strSp)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Country", adVarChar, adParamInput, 30, strCn)
Set cmd7.parameters.Append = cmd7.CreateParameter("@State", adVarChar, adParamInput, 30, strSt)
Else
cmd7.CommandText = "SELECT DISTINCT(County) FROM specimens WHERE TaxOrder= ? AND TaxFamily= ? AND Genus= ? AND Country= ? AND State= ? ORDER BY County ASC;"
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxOrder", adVarChar, adParamInput, 35, strOrd)
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxFamily", adVarChar, adParamInput, 35, strFam)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Genus", adVarChar, adParamInput, 25, strGen)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Country", adVarChar, adParamInput, 30, strCn)
Set cmd7.parameters.Append = cmd7.CreateParameter("@State", adVarChar, adParamInput, 30, strSt)
End If
Else
cmd7.CommandText = "SELECT DISTINCT(County) FROM specimens WHERE TaxOrder= ? AND TaxFamily= ? AND Country= ? AND State= ? ORDER BY County ASC;"
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxOrder", adVarChar, adParamInput, 35, strOrd)
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxFamily", adVarChar, adParamInput, 35, strFam)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Country", adVarChar, adParamInput, 30, strCn)
Set cmd7.parameters.Append = cmd7.CreateParameter("@State", adVarChar, adParamInput, 30, strSt)
End If
Else
cmd7.CommandText = "SELECT DISTINCT(County) FROM specimens WHERE TaxOrder= ? AND Country= ? AND State= ? ORDER BY County ASC;"
Set cmd7.parameters.Append = cmd7.CreateParameter("@taxOrder", adVarChar, adParamInput, 35, strOrd)
Set cmd7.parameters.Append = cmd7.CreateParameter("@Country", adVarChar, adParamInput, 30, strCn)
Set cmd7.parameters.Append = cmd7.CreateParameter("@State", adVarChar, adParamInput, 30, strSt)
End If
Else
cmd7.CommandText = "SELECT DISTINCT(County) FROM specimens WHERE Country= ? AND State= ? ORDER BY County ASC;"
Set cmd7.Parameters.Append = cmd7.CreateParameter("@Country", adVarChar, adParamInput, 30, strCn) <====== Error thrown here!
Set cmd7.Parameters.Append = cmd7.CreateParameter("@State", adVarChar, adParamInput, 30, strSt)
End If
Set objRS7 = cmd7.Execute
%>
<!-- START County dropdown -->
<div id="f-county" class="form-inline"><label for="county"><strong>County: </strong></label>
<select id="county" name="county" tabindex="7" onchange="window.location=document.NewRecord.county.options[document.NewRecord.county.selectedIndex].value">
<option value="dbSearch.asp?uid=<%=strUName%>&q=&o=<%=strOrd%>&f=<%=strFam%>&g=<%=strGen%>&sp=<%=strSp%>&cn=<%=strCn%>&st=<%=strSt%>&co=&recs=<%=strRecs%>&page=<%=strPage%>" <%If strCo = "" Then Response.Write " selected" End If%>>Select</option>
<% If NOT objRS7.EOF Then
WHILE NOT objRS7.EOF
If objRS7("County") <> "" Then %>
<option value="dbSearch.asp?uid=<%=strUName%>&q=&o=<%=strOrd%>&f=<%=strFam%>&g=<%=strGen%>&sp=<%=strSp%>&cn=<%=strCn%>&st=<%=strSt%>&co=<%=objRS7("County")%>&recs=<%=strRecs%>&page=<%=strPage%>" <%If strCo = objRS7("County") Then Response.Write " selected" End If%>><%=objRS7("County")%></option>
<% End If
objRS7.MoveNext
WEND
End If
objRS7.Close
Set objRS7 = Nothing
objConn7.Close
Set objConn7 = Nothing
Set cmd7 = Nothing
%>
</select>
</div> <!-- END County dropdown -->
<% End If %>
So, when the country and state dropdowns are selected and the county dropdown is supposed to appear I get the error listed above. The line causing the error is indicated above (<====). NOTE, I have not added the parameterized query commands to the other dropdowns yet, so that is why they are working and the county one is not.
My question is, can anyone see in the code what may be causing this error. By the text of the error, it seems like the cmd7 object may not be created or recognized correctly (?), but I have followed the statements I have seen on sites such as Stack Overflow. Any help would be greatly appreciated.
Also, am I doing the filtering of the dropdowns correctly, or is there a better way of doing this with parameters?
As @Flakes and @user692942 point out above, the append method does not take an assignment (=) so the correct solution to fixing the original error should be to write the append statement as follows:
cmd7.Parameters.Append cmd7.CreateParameter("@Country", adVarChar, adParamInput, 30, strCn)