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
....
...
....

Hi Deepa,

You are missing the FROM clause in the SELECT statement.

Check online documentation for syntax. It should be in this orderCheers!

_________________________
- Vijay G
Strive for Perfection

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 ?

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.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

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
%>

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.    Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection