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
467 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
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
Comment Utility
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
Comment Utility
for testing on laptop I use Pervasive 2000I  at end user location Pervasive V10
0
 
LVL 28

Expert Comment

by:Bill Bach
Comment Utility
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
 

Author Comment

by:BIAPRO
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:BIAPRO
Comment Utility
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 100 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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 400 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

16 Experts available now in Live!

Get 1:1 Help Now