|
Subject:
|
Command text was not set for the command object.
|
|
Posted By:
|
deepa12
|
Post Date:
|
10/29/2004 3:11:54 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><FONT SIZE=""-1"">No bids placed</FONT></TD>"
Else
....
...
....
|
|
Reply By:
|
happygv
|
Reply Date:
|
10/30/2004 1:46:05 AM
|
Hi Deepa,
You are missing the FROM clause in the SELECT statement.
Check online documentation for syntax. It should be in this orderSelect COLUMNLIST
From TABLENAME
where CRITERIA Cheers!
_________________________
- Vijay G
Strive for Perfection
|
|
Reply By:
|
deepa12
|
Reply Date:
|
10/31/2004 7:29:03 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 ?
|
|
Reply By:
|
happygv
|
Reply Date:
|
11/1/2004 2:59:37 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.Response.write "SELECT Max(bid.BidAmount) as MaxBidAmount, " & _
"Max(bid.Timestamp) as LastBidTime from bid " & _
" WHERE ItemID = ' " & rsItems ("ItemID") &" ' "
Response.End
Set rsBid = objCmd.ExecuteLooks 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
|
|
Reply By:
|
deepa12
|
Reply Date:
|
11/2/2004 9:51:39 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><FONT SIZE=""-1"">Click to Bid</FONT>"
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><FONT SIZE=""-1"">No bids placed</FONT></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><FONT size=""-1"" COLOR=""Red"">" & _
"You are the current high bidder</FONT></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
%>
|
|
Reply By:
|
happygv
|
Reply Date:
|
11/2/2004 4:37:38 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. Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
strSQL="SELECT Max(BidAmount) as MaxBidAmount, " & _
"Max(Timestamp) as LastBidTime from bid"
objCmd.CommandType = adCmdTextHope that helps.
Cheers!
_________________________
- Vijay G
Strive for Perfection
|
