Inserted Records

I am inserting a record into a table that has a trigger that generates a new key field value for the inserted record.
I want to use sql to retrieve the ID field of the latest record inserted immedialtely after it has been added to the DB
I am using the following code and am having trouble doing so:
strSQL = "insert into users (site_id, title, lastname, firstname, country, city, phone, fax, userid, op_current_user) values ('" & txtOrg.Text & "', '" & cTitle.Text & "', '" & txtLastName.Text & "', '" & txtFirstname.Text & "', '" & CStr(cCode) & "', '" & txtCity.Text & "', '" & txtPhone.Text & "', '" & txtFax.Text & "', 0, '2')"
Set rsUser = cn.OpenResultset(strSQL)/*This Works OK*/
Set rsUser = cn.OpenResultset("Select * from users,inserted where users.userid = inserted.userid")/*This generates an error*/
txtUID.Text = rsUser!userid
Could anyone provide me with the proper syntax for what i am trying to achieve?
CoatesAsked:
Who is Participating?
 
Victor SpiridonovConnect With a Mentor Commented:
INSERTED table is only available in trigger, you can not see it from anywhere else. If your user_id is autogenerated (IDENTITY), you can use @@IDENTITY global variable, that contains last inserted by current connection identity value .
0
 
CoatesAuthor Commented:
I get The Following Error Message using this code:
"Sybase does not allow more than one active statementwhen retrieving results without a cursor".
Is there an alternative method to retrieve the latest inserted key field value
0
 
CoatesAuthor Commented:
I changed the code format to the following:
strSQL = "insert into users (site_id, title, lastname, firstname, country, city, phone, fax, userid, op_current_user) values ('" & txtOrg.Text & "', '" & cTitle.Text & "', '" & txtLastName.Text & "', '" & txtFirstname.Text & "', '" & CStr(cCode) & "', '" & txtCity.Text & "', '" & txtPhone.Text & "', '" & txtFax.Text & "', 0, '2')"
    strSQL = strSQL & " Select userid from users,inserted"
    Set rsUser = cn.OpenResultset(strSQL)
    txtUID = rsUser!userid
But i got another error:
"Inserted not found.Specify owner.objectname or use sp_help to check whether object exists"
I thought that the inserted table holds information about any changesmade to the table
The sybase version im using is11.0.2.2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.