A problem I ran into recently involved an ADO Command object. I was trying to use a stored procedure with SQL Server. After setting everything, including the parameters, I was getting no results. Of course, the error messages didn't make any sense, either. After reviewing the code, I realized I hadn't set the CommandType property of the Command object. Once I set that, things worked properly. Here's the complete code:
Set cmdQuery = Server.CreateObject("ADODB.Command")
With cmdQuery
Set .ActiveConnection = dcnDB
.CommandType = adCmdStoredProc
.CommandText = "sp_KeywordSearch"
Set parItem = .CreateParameter("Keyword", adVarChar, _
adParamInput, 255, Request("txtKeywords"))
.Parameters.Append parItem
End With
Set rsData = Server.CreateObject("ADODB.Recordset")
rsData.Open cmdQuery, , adOpenStatic
In this case, the stored procedure is named sp_KeywordSearch and takes a parameter called Keyword. The parameter is created and then appended to the Command object. When you open the recordset object, instead of specifying a SQL string, you supply a Command object.