why the syntax error..HELP!!!

Results 1 to 2 of 2

Thread: why the syntax error..HELP!!!

  1. #1
    Join Date
    Dec 1969

    Default why the syntax error..HELP!!!

    rsCustomerID = conn.Execute("Select customer_ID,created_date from Customers Group BY created_date HAVING MAX(created_date)")<BR> <BR><BR>I get the following error...<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80040e14&#039; <BR><BR>[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near &#039;)&#039;. <BR><BR>I am trying to get the customer_ID that was inserted recently.<BR>Thanks!!<BR><BR><BR>

  2. #2
    peterjl@austec.net.au Guest

    Default RE: why the syntax error..HELP!!!

    BTW: The result of the execute method is a recordset, which is an object. So you need to use the SET command.<BR>I am not sure where the error is but I think this would be simpler:<BR><BR>Set rsCustomerID = conn.Execute("Select top 1 customer_ID from Customers Order by created_date desc")<BR><BR>This will sort the Customers by the date in reverse order (ie most recent first), but it will also only return the most recent row by using the Top modifier. I also returns only one field, so the CustomerID = rsCustomerID(0).<BR><BR>Just one more thing. This method of getting the most recent ID is okay on low traffic sites, but on a busy site another record could be added by another user between the first visitor&#039;s insert and retrieve. That is:<BR><BR>user A and B submit teir forma=s at the same time.<BR>A inserts<BR>B inserts<BR>A retrieves (and gets b&#039;s ID)<BR><BR>It is better to use the AddNew method on a recordset which has a Keyset cursor, because after you do the update, you still have access to the same recordset object pointing to the new row:<BR><BR>set rs=server.cresteobject("adodb.recordset")<BR>rs.op en "Select * from Customers", objConnection, adOpenKeyset, adLockOptimistic <BR>rs.addnew<BR>rs("field1") = f1value<BR>rs.update<BR>custid = rs("Custid")<BR><BR>See the faq&#039;s for more details<BR><BR>http://www.aspfaqs.com/aspfaqs/<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts