Now what did it insert in that Autonumber field?

Results 1 to 5 of 5

Thread: Now what did it insert in that Autonumber field?

  1. #1
    Stuart Grimshaw Guest

    Default Now what did it insert in that Autonumber field?

    I have a query that inserts data into a table,<BR><BR>INSERT INTO items (Item_Text)<BR><BR>The only two fields in the DB are item_ID and Item_Text, where item_ID is an MSAccess Autonumber field, and Item_Text is , obviously, a text field.<BR><BR>I want to be able to tell what the item_ID field contains after executing the SQL.<BR><BR>I can&#039t run another SQL, someone else may have inserted data into the table by the time that&#039s executed, plus I don&#039t want to slow the whole thing down by doing that. Will the @@identity variable work? and if so how the hell would I use it in an ASP file? (using VBScript)<BR><BR>Might I be better off trying to use GUIDs to get a unique reference number?

  2. #2
    Jason Buck Guest

    Default RE: Now what did it insert in that Autonumber field?

    Is your text going to be unique? (Is it like a userid or something where you will not allow duplicates)<BR><BR>If this is the case then Just run a select statement that retrieves the value you just posted.<BR><BR>strITEMTEXT = request.form("ITEM_TEXT")<BR><BR>mysql = SELECT ITEM_ID, ITEM_TEXT FROM ITEM WHERE (((ITEM_TEXT))=&#039" & strITEMTEXT & "&#039);"<BR><BR>If you are doing it the way above then I recommend that you do it this way.<BR><BR>Add a new field to you database. Call the field ITEM_SUBMITTED.<BR><BR><BR>strSUBMITTED = &#060;%NOW()%&#062;<BR>strstrITEMTEXT = request.form("ITEM_TEXT")<BR><BR><BR><BR>Post the above to fields to the database and then query the database for the following:<BR><BR>mysql = SELECT ITEM_ID, ITEM_TEXT FROM ITEM WHERE (((ITEM_SUBMITTED))=&#039" & strSUBMITTED & "&#039);"<BR><BR>strID = rstemp("ITEM_ID")<BR><BR>Then <BR>response.write = strITEMTEXT & " was submitted. Your ID Number is " & strID<BR><BR>I am assuming that you know how to open and close your dataconnections. <BR><BR>If I didn&#039t explain this clearly enough or you have some other questions, please feel free to contact me.<BR><BR>Jason<BR>buckja@wnj.com<BR>

  3. #3
    Stuart Grimshaw Guest

    Default RE: Now what did it insert in that Autonumber field?

    I really wanted to avoid using a 2nd SQL to get the info out if I could, from a speed point of view really. Some of the remoter users connections are painfully slow.<BR><BR>I&#039m looking at using recordset.ADDNEW instead of an SQL, this appears to give me the ability to do what I want, if I could get it to work...<BR><BR>I create the recordset :<BR><BR>Set Rs = Server.CreateObject("ADODB.Recordset")<BR>Rs.Activ eConnection = DBConn<BR>Rs.CursorType = adOpenKeyset<BR><BR>after creating the connection (dbCONN) obviously, then open the table :<BR><BR>Rs.Source = "UserItems"<BR>Rs.Open<BR><BR>when I actually try to call the "AddNew" method, I get the error :<BR><BR>"The operation requested by the appliation is not supported by the provider."<BR><BR>Any ideas?

  4. #4
    Stephen Fisher Guest

    Default RE: Now what did it insert in that Autonumber field?

    Try setting the CursorLocation to adUseServer or adUse Client, I can&#039t remember which.<BR><BR>I will post it in a few minutes, I have to get back to my desk to check.


  5. #5
    Stephen Fisher Guest

    Default RE: Now what did it insert in that Autonumber field?

    strSQL = "Select * From UserItems where item_ID &#060; 0"<BR><BR>Set Rs = Server.CreateObject("ADODB.Recordset")<BR>Rs.Activ eConnection = DBConn<BR>&#039rs.maxrecords=1<BR>rs.CursorLocatio n = adUseServer<BR>rs.CursorType = adOpenStatic<BR>rs.LockType = adLockOptimistic<BR>rs.Open strSQL, cnGWERR<BR><BR>The SQL will return an empty recordset with the correct columns.<BR>If you use the rs.AddNew it adds a new record and autoincrements.<BR><BR>then<BR>rs("Item_Text") = strItemText &#039item text variable<BR><BR>rs.Update<BR><BR>&#039This may need to go before or after the update, I&#039m not sure.<BR>intID = rs("Item_ID") &#039intID should be set to the new ID<BR><BR><BR>This will definately work.<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
  •