Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

AFTER A INSERT WITH SQL IN A DATABASE WITH A IDENTITY KEY,    FINDING THE GENERATED VALUE FOR THIS KEY

Posted on 2011-03-21
13
Medium Priority
?
478 Views
Last Modified: 2012-06-27
I a loop I insert new records with a identity
like
 strSQL= " INSERT INTO WEBSHOP VALUES(0, 'JACK 110316', '', '0' ,'JACK', '05.0425', '110316',  0) "
 dbconn.Execute (strSQL)
I have a need to know after this call what the value is in the indentity key after each call
how to retrieve this?
0
Comment
Question by:BIAPRO
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 35186665
which database system are you using?  (version and edition)

for sql server (from sql 2000)

then
strSQL= " INSERT INTO WEBSHOP VALUES(0, 'JACK 110316', '', '0' ,'JACK', '05.0425', '110316',  0) ;select scope_identity() as idValue;"
 dbconn.Execute (strSQL)

although ideally you should use a stored procedure to do the insert(s) and have that return the identity values...
0
 

Author Comment

by:BIAPRO
ID: 35186714
for testing on laptop I use Pervasive 2000I  at end user location Pervasive V10
0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 35186816
Immediately after the INSERY statement, issue the statement "SELECT @@IDENTITY;"

This system variable holds (and allows you to retrieve) the value of the last inserted Identity value.
It may or may not work in PSQL2000, but will definitely work in PSQLv10.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:BIAPRO
ID: 35186818
oK, found out that 2000I has no support for this , it does work (no error returned,) on V10,
what would be the syntax to get the value XXXX out off the call?
0
 

Author Comment

by:BIAPRO
ID: 35187115
I 'stuck for now,  using this call I  can 't figure out how to get the returned value for the identity in some variable?
how to do this?
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 35188243
Hmmmm... Been a while since playing with pervasive...

What about creating a stored procedure and executing that ??

CREATE PROCEDURE spPutMyData(IN :p1 int, IN :p2 char(20), IN :p3 char(20))         --or whatever it really is for those columns. could also create "out" param
RETURNS(:NewIDValue Int);
Begin 
INSERT INTO WEBSHOP VALUES(:p1,:p2,:p3) ;
select :NewIDValue = @@identity;
End;

Open in new window

0
 

Author Comment

by:BIAPRO
ID: 35188272
what would be the syntax to get the indentity value back in VB6 language?
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 400 total points
ID: 35188740
VB6 ? You are simply calling a stored procedure. Plenty of examples around for example : http://www.vb6.us/tutorials/using-ado-and-stored-procedures-vb6 and scroll down to look at the @@identity example. Pretty much what you want to do, just slight change in syntax (on SQL side) for pervasive. For that reason also better to have :NewIDValue as an "out" param rather than "returns"

0
 
LVL 28

Expert Comment

by:Bill Bach
ID: 35189293
I assumed that you were using SQL for other purposes.  This is a simple SELECT statement that looks like all of your others -- just open the record set and read the value out.

     sSQL = "Select @@IDENTITY as NewValue"
    adoRecordset.Open sSQL, adoConnection, adOpenForwardOnly
    MyNewValue = adoRecordset!NewValue
    adoRecordset.Close
    Set adoRecordset = Nothing
0
 

Author Comment

by:BIAPRO
ID: 35189505
what I do is inserting a new identity record, then i need to know right after that call the "created'  value , due that this need to be added to a grd
it looks like
INSERT INTO WEBSHOP VALUES(0, 'JACK        110324 ', '         ', '1' , 'JACK        ', '17.0170       ', '110324',  11);SELECT @@IDENTITY AS 'Identity'
Set RST = Cn.Execute(strSQL)
After this caal i need this value
0
 
LVL 28

Accepted Solution

by:
Bill Bach earned 1600 total points
ID: 35189670
Try this:  (Note that I'm not a VB developer, so I cannot test this.)
    sSQL = "INSERT INTO WEBSHOP VALUES(0, 'JACK        110324 ', '         ', '1' , 'JACK        ', '17.0170       ', '110324',  11);"
    adoConnection.Execute sSQL
REM Note that we use this syntax when no data is coming back
    sSQL = "Select @@IDENTITY as NewValue"
    adoRecordset=adoConnection.Execute(sSQL)
REM Note that we use this syntax when some data is coming back
    MyNewValue = adoRecordset!NewValue
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 35191136
As a passing observation, it has been shown that, under certain circumstances, @@IDENTITY may result in an invalid ID being returned.  For instance, if there is a trigger that also inserts into another table (e.g. a history table), then the @@IDENTITY value may well be the value of the identity column of the table in the trigger's activity.
0
 

Author Comment

by:BIAPRO
ID: 35193517
true , I have read that on some sites, for this application I don 't worry, due to normaly I only use vaccess (btrieve) direct statements.
I did run in to this now (SQL)  because I write a demo in VB6 for an web programmer, who has to "translate" whats happening into a web interface connected to the Pervasive server , and only Sql calls are then possible. So I give him SQL calls in the  VB6 demo.
Thus in the aplication are orders lines inserted , and then the identity values "given"line by line to the backoffice program during insert, so one table is active for inserting only
thanks

 
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 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