Command text was not set for the command object.

deepa12 · October 29th, 2004, 03:11 PM

Hi all,
        I have problem with the following code.. It gives error at command.execute statement, but I guess it's the SQL statement.I could not think what is wrong with this statement...Please help anyone..

Dim objCmd, rsBid
    Set objCmd = Server.CreateObject("ADODB.Command")
    Set objCmd.ActiveConnection = objConn
    objCmd.CommandType = adCmdText
....
....
objCmd.CommandText = "SELECT Max(bid.BidAmount) as MaxBidAmount, " & _
                    "Max(bid.Timestamp) as LastBidTime from bid " & _
                    " WHERE ItemID = ' " & rsItems ("ItemID") &" ' "

      Set rsBid = objCmd.Execute <-- Error Line If IsNull( rsBid("MaxBidAmount") ) Then
        Response.Write _
          "<TD COLSPAN=2>No bids placed</TD>"
      Else
....
...
....

happygv · October 30th, 2004, 01:46 AM

Hi Deepa,

You are missing the FROM clause in the SELECT statement.

Check online documentation for syntax. It should be in this order

Code:

Select COLUMNLIST
From TABLENAME
where CRITERIA

Cheers!

_________________________
- Vijay G
Strive for Perfection

deepa12 · October 31st, 2004, 08:29 AM

Hi,
Thanks for your response.But if you look my SQL statement is in the order you said it should be. Do you think as I used Max function ,I should use group by also ?

happygv · November 1st, 2004, 03:59 PM

I didnot notice that it has the FROM class, sorry about that.

Can you post on what exactly the ERROR that you see there. And no mention about what database you use(Access/SQL server???). May be you can try executing the output of the following code directly on to your database.

Code:

Response.write "SELECT Max(bid.BidAmount) as MaxBidAmount, " & _
                    "Max(bid.Timestamp) as LastBidTime from bid " & _
                    " WHERE ItemID = ' " & rsItems ("ItemID") &" ' "
Response.End
Set rsBid = objCmd.Execute

Looks like the SPACE before and after the rs(Items("ItemID") or the single quotes itself could be the cause for that. What is the datatype of ItemId on your BID table?

Cheers!

_________________________
- Vijay G
Strive for Perfection

deepa12 · November 2nd, 2004, 10:51 AM

Hi,
    I get the following Error.I am using Access 2000 and ItemId datatype is Number

Error Type:
Microsoft JET Database Engine (0x80040E0C)
Command text was not set for the command object.
/KhandkeOnline/Images/classified/BrowseListings.asp, line 86

Actually I changed the code little bit. but it's still giving the same error.Here is the new Code

<%
  Dim rsItems
  strSQL = "SELECT * FROM Item " & _
           "WHERE ExpirationDate > #" & FormatDateTime(Now,2) & "# " & _
           "AND ItemStatus = 'Active';"

  Set rsItems = Server.CreateObject("ADODB.Recordset")
  rsItems.Open strSQL, objConn

  If Not rsItems.EOF Then
    Response.Write _
      "<TABLE BORDER=""1"" CELLSPACING=""3"" CELLPADDING=""3"" FONT SIZE=""2"">" & _
      " <TR>" & _
      " <TH>Item ID"
               If Session("PersonID") <> "" Then
                 Response.Write "<BR>Click to Bid"
               End If
           Response.Write "</TH>" & _
      " <TH>Name</TH>" & _
      " <TH>Asking Price</TH>" & _
      " <TH>Listing Date</TH>" & _
      " <TH>Current Bid</TH>" & _
      " <TH>Bid Time</TH>" & _
      " </TR>"
    Dim objCmd, rsBid
    Set objCmd = Server.CreateObject("ADODB.Command")
    Set objCmd.ActiveConnection = objConn
    strSQL="SELECT Max(BidAmount) as MaxBidAmount, " & _
                            "Max(Timestamp) as LastBidTime from bid"
    objCmd.CommandType = adCmdText

    Do While Not rsItems.EOF
      Response.Write "<TR ALIGN=CENTER>"
      If Session("PersonID") <> "" Then
        Response.Write _
          "<TD><A HREF=""Bid.asp?Item=" & rsItems("ItemID") & """>" & _
          rsItems("ItemID") & "</A></TD>"
      Else
        Response.Write "<TD>" & rsItems("ItemID") & "</TD>"
      End If

      Response.Write _
        "<TD>" & rsItems("ItemName") & "</TD>" & _
        "<TD>" & FormatCurrency(rsItems("AskingPrice")) & "</TD>" & _
        "<TD>" & FormatDateTime(rsItems("ListingDate"),2) & "</TD>" & _

        objCmd.CommandText = strSQL & " where ItemID = " & rsItems("ItemID") & ";"
        Set rsBid = objCmd.Execute
      If IsNull( rsBid("MaxBidAmount") ) Then
        Response.Write _
          "<TD COLSPAN=2>No bids placed</TD>"
      Else
        Response.Write _
          "<TD>" & FormatCurrency(rsBid("MaxBidAmount")) & "</TD>" & _
          "<TD>" & rsBid("LastBidTime") & "</TD>"
        Dim strSQL2, rsHighBidder
        strSQL2 = "SELECT BidderID FROM bid " & _
                  "WHERE ItemID = " & rsItems("ItemID") &" ORDER BY Timestamp DESC"
        objCmd.CommandType = adCmdText
        objCmd.CommandText = strSQL2
        Set rsHighBidder = objCmd.Execute
        If rsHighBidder("BidderID") = Session("PersonID") Then
          Response.Write "<TD>" & _
                         "You are the current high bidder</TD>"
        End If
        rsHighBidder.Close
        Set rsHighBidder = Nothing
      End If
      rsBid.Close
      Set rsBid = Nothing
      Response.Write "</TR>"
      rsItems.MoveNext
    Loop
    Response.Write "</TABLE>"
    rsItems.close
    Set rsItems = Nothing
  Else
    Response.Write "<CENTER><H2>No items currently for sale</H2></CENTER>"
  End If
%>

happygv · November 2nd, 2004, 05:37 PM

Hi Deepa,

As the error is straight forward, did you forget to set the strSQL to the command object? As per you code, you have set the command's active connection, commandtype etc, but you haven't assigned the strSQL's value to its commandtext as you did in your first post of this thread.

Code:

    Set objCmd = Server.CreateObject("ADODB.Command")
    Set objCmd.ActiveConnection = objConn
    strSQL="SELECT Max(BidAmount) as MaxBidAmount, " & _
                            "Max(Timestamp) as LastBidTime from bid"
    objCmd.CommandType = adCmdText

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection