How do I grab MS SQL Server autoincrement value after INSERT?

[nlights]

I need to grab the auto-generated PK value from my SQL table as soon as an INSERT fires.

The darnest thing is that while I can do this in the Xbasic Interactive window, running the exact same sequence in Xbasic code will not return the PK value, no matter what I try. Here's the code that works interactively but not as running code:

				qry = "INSERT INTO ITEM (ITRACKINGNUMBER) VALUES ('ABC')"
				if conn.Execute(qry)
					'Get the new IITEMID
					qry="select id = Scope_Identity()"
					if conn.Execute(qry)
						cItemID = conn.resultset.data("id")
					else
					end if
				else
				end if

Interactive execution returns the autoincremented ID (IITEMID), running code not so much. Any ideas?

[glenschild]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

I need to grab the auto-generated PK value from my SQL table as soon as an INSERT fires.

The darnest thing is that while I can do this in the Xbasic Interactive window, running the exact same sequence in Xbasic code will not return the PK value, no matter what I try. Here's the code that works interactively but not as running code:

                qry = "INSERT INTO ITEM (ITRACKINGNUMBER) VALUES ('ABC')"
                if conn.Execute(qry)
                    'Get the new IITEMID
                    qry="select id = Scope_Identity()"
                    if conn.Execute(qry)
                        cItemID = conn.resultset.data("id")
                    else
                    end if
                else
                end if

Interactive execution returns the autoincremented ID (IITEMID), running code not so much. Any ideas?

No need to use another query as there is an xbasic function LastInsertedIdentity so use

cItemID = conn.LastInsertedIdentity()

[nlights]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

Yeah, I noticed that after posting my question. I tried it, and still keep getting the same result. I even tried it with the optional <tableinfo> parameter, but no go. Trying to think of other approaches here... GUID? :-(

[GGandhi]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

you need to re-query the server
qry="select Scope_Identity()"
result = cn.execute(qry)
if result
dim rs as sql::resultset
rs = cn.resultset
dim id as n
id = rs.data(1)
.
.
.
end if

in these lines
I have posted similar code in another post in this area

http://msgboard.alphasoftware.com/al...226#post672226 post #8

ps: normally lastInsertedId works, I do not know why it did not in your case.

[nlights]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

Thanks, Gandhi --

That's exactly what I did (re-query.) My code is slightly different than yours, but the principal is the same.

I'm thinking this has probably something to do with my DB back-end (full MS SQL Server 2008.) Like I said, the code works just fine when entered interactively, but fails (returns blank) when running through the code. I re-engineered my data model, re-wrote a bunch of code, and regenerated my tables in order to use GUIDs (luckily I use a DB CASE tool which makes the data part of the changes very easy/) GUIDs are more work, but at least I know what the primary key value is going to be before and after the row is inserted. This is critical in my case, because I need to make link table entries after the two many-to-many tables are updated, and the link tables need to know what the PKs are on both sides.

If AA supported data binding on unrelated tables, another workaround would be to look at e.newdata (or whatever it is) after the insert. I think it would give me the new ID, no problem. But since I can't do unrelated table data binding in AA, I'm running into dead-ends all over the place trying to get this pretty simple job done. And it is rather aggravating.

Furthermore, the documentation is hideous enough to drive me looking for answers through Google search. The videos are great, but good luck trying to find an answer to a specific question in the 1000+ video library...

[GGandhi]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

take a look at this screencast:

http://screencast.com/t/KLLXtVC76tdD

I am able to get it in grid and dialog.
see if this helps you

ps: I added one more record before taping so the number is 8 rather than 7.

[Steve Wood]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

In case someone comes looking for same using MySQL, here is syntax for that database, minus error checking:

dim cn as sql::Connection
cn.open("::name::conn")
vsql = "INSERT INTO table_a (field1) VALUES 'ABC' "

cn.execute(vsql)
vsql = "SELECT LAST_INSERT_ID() as mypk" ' this gets the auto-increment value
cn.execute(vsql)
mypk = cn.resultset.data("mypk")

...go on to use mypk as needed.

[nlights]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

Gandhi --

Thank you for the screencast! Unfortunately, however, I have to roll my own data management in Xbasic because of the inherent limitations in AA Data Binding capabilities (no unrelated data, no one-to-many without repeating sections in UX dialog.) So, I have to write the code that AA really should take care of, and executing that code I get absolutely nowhere with the lastInsertedId() function. Again, works when I type it in the interactive window, but if I copy the exact same lines that worked into the code window and run the code, I can see through the debugger that lastInsertedID() (or any other workarounds) simply do not work. Period. Maybe it's because I'm running full SQL Server 2008 rather than the express version. Who knows? But it is not working here (when the code runs.)

[nlights]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

Steve --

Thanks for the response. Looks like you are using MySQL. SQL Server uses the SELECT SCOPE_IDENTITY() -command, whereas MySQL users SELECT LAST_INSERT_ID(). SQL Server 2008 does not work with this part of AA.

[Garry Flanigan]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

I agree with Glenn,

cItemID = conn.LastInsertedIdentity()

has always worked for me with SQL Server 2008.
I do not like the second query as how can you be sure that another user has not inserted in the meantime? Be careful when looking at such things in the debugger.
Immediately after conn.Execute("insert statement here"), conn.LastInsertedIdentity holds the correct value in my experience but doing something else with conn then later querying for value of conn.LastInsertedIdentity will not necessarily hold the value you expect. In short, I recommend you look very closely at your code.
If others have different experience I would like to know as this is an important area for me in a number of areas where I do further processing when a record is inserted or updated.

[compuaid]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

You could always use a stored procedure to do the insert. That would also require that your insert be parameterized which is good practice to avoid SQL injection. The stored query would return the Identity of the insert. Here's an example using the Northwind database that allows you to add a new category and return the Identity assigned:

CREATE PROCEDURE [dbo].[AddCategory] 
    @CatName            NVARCHAR(15),
    @new_identity       INT    OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO CATEGORIES (CategoryName) VALUES (@CatName)

    SELECT @new_identity = SCOPE_IDENTITY()

    RETURN

END

You would need to call this with an input parameter for the new category name and an output parameter that will contain the Identity value after the insert.

The Xbasic would look something like:

dim cn as sql::Connection
cn.open("::name::Nwind")

dim argz as sql::Arguments
dim result as n
dim catname as c

catname = "New Category"

argz.set("NewCat",catname)
argz.set("result",result,SQL::ArgumentUsage::InputOutputArgument) 

vcall="{Call AddCategory(:NewCat, :result)} "


OK = cn.execute(vCall,argz)
if ok
  ' Get the IDENTITY value
   result=argz.find("result").data
   msgbox("Result","The identity is "+Str(result))
else
   msgbox("Warning:","Insert failed")
end if

[nlights]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

Hi Garry --

It is strange that this seems to work for some people, not so much for others. I noticed that others have run into this same problem (e.g., http://msgboard.alphasoftware.com/al...sertedIdentity) If it didn't work in interactive mode, I would question my code. But since the EXACT same code works interactively and not in the code, I suspected that it had to do with timing issues. So I paused the debugger for a while before executing LastInsertedIdentity and examining the result, but still nothing.

Here's the entire Xbasic function where this happens (or, rather, does not happen.) Maybe I'm missing something...

function SaveScanData as c (e as p)

DIM vItemTable AS P
DIM vActionTable AS P
DIM conn AS SQL::Connection
DIM rs AS P
DIM c AS P
DIM cScanList AS C
dim ti as SQL::TableInfo

DEBUG(1)

cScanList = comma_to_crlf(e._var1)


if conn.open("::Name::PSShip")
	'Iterate tracking numbers: if found in ITEMS table already, keep going.  If not, insert.
	for each cTrackingNumber in cScanList    	
		'1: Add a row to Item -table if necessary (do not duplicate tracking numbers in the ITEM table)

			qry = "SELECT ITRACKINGNUMBER FROM ITEM WHERE ITRACKINGNUMBER = '"+ALLTRIM(cTrackingNumber.Value)+"'"
			if conn.Execute(qry)
				if EVAL_VALID("conn.Resultset.data(\"ITRACKINGNUMBER\")")      'This is one way to tell if query returned anything
					'found, keep going
				else
					'insert new row to ITEM
					rs = conn.ResultSet 
					qry = "INSERT INTO ITEM (ITRACKINGNUMBER) VALUES ('"+ALLTRIM(cTrackingNumber.Value)+"')"
					if conn.Execute(qry)
						'Get the new IITEMID
						cItemID = conn.LastInsertedIdentity()  
                                                
                                                'NOTE: cItemID comes up blank here

					else
						'error inserting tracking number to ITEM table
					end if
				end if
			else
			end if
		
		'2. Add row to action table
                        'TODO
	next 
end if

SaveScanData = "//alert('Return from SaveScanData');"

[nlights]

Re: How do I grab MS SQL Server autoincrement value after INSERT?

Hi Brad --

Excellent, thank you for the "lightning tutorial" on stored procedures and Xbasic . This really is a great approach, because it lets the database do the work, returns the generated key reliably, AND protects against SQL injection.


Pertti

You could always use a stored procedure to do the insert. That would also require that your insert be parameterized which is good practice to avoid SQL injection. The stored query would return the Identity of the insert. Here's an example using the Northwind database that allows you to add a new category and return the Identity assigned:

CREATE PROCEDURE [dbo].[AddCategory] 
    @CatName            NVARCHAR(15),
    @new_identity       INT    OUTPUT
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO CATEGORIES (CategoryName) VALUES (@CatName)

    SELECT @new_identity = SCOPE_IDENTITY()

    RETURN

END

You would need to call this with an input parameter for the new category name and an output parameter that will contain the Identity value after the insert.

The Xbasic would look something like:

dim cn as sql::Connection
cn.open("::name::Nwind")

dim argz as sql::Arguments
dim result as n
dim catname as c

catname = "New Category"

argz.set("NewCat",catname)
argz.set("result",result,SQL::ArgumentUsage::InputOutputArgument) 

vcall="{Call AddCategory(:NewCat, :result)} "


OK = cn.execute(vCall,argz)
if ok
  ' Get the IDENTITY value
   result=argz.find("result").data
   msgbox("Result","The identity is "+Str(result))
else
   msgbox("Warning:","Insert failed")
end if