Command text was not set for the command object.

[sharonc]

Hi,
I am getting this error:

Command text was not set for the command object.
/projects/Maintenance/search_results.asp, line 42

<b>Can someone tell what is wrong</b>

<b>Here is my code:</b>

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../Connections/rs.asp" -->
<%
Dim rs__SEARCH
rs__SEARCH = "b"
If (REQUEST.FORM("txtSearch") <> "") Then
rs__SEARCH = REQUEST.FORM("txtSearch")
End If
%>
<%
Dim rs, strUnit, strType, strSearchCode
Dim rs_numRows

strUnit = Request.Form("txtUnit")
strType = Request.Form("txtType")
strSearchCode = strUnit + strType
response.write strSearchCode
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_rs_STRING

Select Case strSearchCode
Case "11"
rs.Source = "SELECT * FROM dbo.vw_Lubrications WHERE Equipment LIKE '%" + Replace(rs__SEARCH, "'", "''") + "%' ORDER BY Equipment ASC"
Case "12"
rs__SEARCH = Right(REQUEST.FORM("txtSearch"),5)
rs.Source = "SELECT * FROM dbo.vw_Lubrications WHERE EquipmentNbr LIKE '%" + Replace(rs__SEARCH, "'", "") + "%' ORDER BY EquipmentNbr ASC"
Case "13"
rs.Source = "SELECT * FROM dbo.vw_Lubrications WHERE Lubricants LIKE '%" + Replace(rs__SEARCH, "'", "''") + "%' ORDER BY Lubricants ASC"
Case "14"
rs.Source = "SELECT * FROM dbo.vw_Lubrications WHERE IdentifierColor LIKE '%" + Replace(rs__SEARCH, "'", "''") + "%' ORDER BY IdentifierColor ASC"
Case "15"
rs.Source = "SELECT * FROM dbo.vw_Lubrications WHERE CtrlNbr LIKE '%" + Replace(rs__SEARCH, "'", "''") + "%' ORDER BY CtrlNbr ASC"
Case "16"
rs.Source = "SELECT * FROM dbo.vw_Lubrications WHERE IdentifierName LIKE '%" + Replace(rs__SEARCH, "'", "''") + "%' ORDER BY IdentifierName ASC"
Case Else
Response.write "You must select your search parameters."
End Select

rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

<b>Here is the connection string:</b>

Dim MM_rs_STRING
MM_rs_STRING = "driver=SQL Server;SERVER=SEIMS;DATABASE=FORMS;UID=forms;PWD=forms;

 

[Sheco]

What happens if you do a Response.Write to show the value of rs.Source ... before you execute it.

 

[sharonc]

Hi Sheco,
When I put the Response.Write in, it gives me the error: Command text was not set for the command object for the rs.Open() line. Thank you for replying so quickly.

 

[Sheco]

So it gives you an error when you try to write out the value of rs.Source ?

Perhaps I don't understand.

 

[sharonc]

Yes, it gives me an error when I try to write out the value of rs.Source.

 

[Sheco]

woah.

OK then how about a temp variable to hold your SQL statement like this:

Dim sTmpSQL 
Select Case strSearchCode
    Case "11"
        sTmpSQL = "SELECT *  FROM dbo.vw_Lubrications  WHERE Equipment LIKE '%" + Replace(rs__SEARCH, "'", "''") + "%'  ORDER BY Equipment ASC"
    Case "12"
 
    ....

And then after the End Select do a Response.Write sTmpSQL before you do anything else, just to make sure

 

[Sheco]

I mean stick the response.write in before you open the recordset.

Then open it like this:
rs.Open sTmpSQL

 

[steven290]

i think the problem is here

strUnit = Request.Form("txtUnit")
strType = Request.Form("txtType")
strSearchCode = [red]strUnit + strType[/red]
response.write strSearchCode    
Set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_rs_STRING

Select Case strSearchCode
...
Case "11"

it prob should be

strUnit = [red]clng(Request.Form("txtUnit"))[/red]
strType = [red]clng(Request.Form("txtType"))[/red]
strSearchCode = strUnit + strType
...

 

[steven290]

and make the "case" in select case like this

Case 11

 

[Sheco]

Ah good catch, are they numbers are arn't they??

 

[sharonc]

I found out what the problem was. I was trying to you a variable in my select statment that didn't exist.

Thank you for your help.