Search code examples
mysqlasp-classicprepared-statement

Object doesn't support this property or method: 'Parameters.Append'


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?


Solution

  • 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)