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

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?
BIAPROAsked:
Who is Participating?
 
Bill BachConnect With a Mentor PresidentCommented:
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
 
LowfatspreadCommented:
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
 
BIAPROAuthor Commented:
for testing on laptop I use Pervasive 2000I  at end user location Pervasive V10
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Bill BachPresidentCommented:
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
 
BIAPROAuthor Commented:
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
 
BIAPROAuthor Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
BIAPROAuthor Commented:
what would be the syntax to get the indentity value back in VB6 language?
0
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
Bill BachPresidentCommented:
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
 
BIAPROAuthor Commented:
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
 
8080_DiverCommented:
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
 
BIAPROAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.