Solved

Inserted Records

Posted on 1998-07-06
3
430 Views
Last Modified: 2008-03-10
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?
0
Comment
Question by:Coates
  • 2
3 Comments
 

Author Comment

by:Coates
ID: 1098154
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
 

Author Comment

by:Coates
ID: 1098155
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
 
LVL 7

Accepted Solution

by:
spiridonov earned 50 total points
ID: 1098156
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many Password Managers (PM) out there to choose from. PM's can help with your password habits and routines, but they should not be a crutch you rely on too heavily. I also have an article for company/enterprise PM's.
Scenario: Your operations manager has discovered an anomaly in your security system. The business will start to suffer within 15 minutes if it is a major IT incident. What should she do? We have 6 recommendations for managing major incidents (https:…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now