How to return the value of a computed column after inserting a record

Greetings Experts!

I have an input form that captures information about pallets received at our loading dock. The user enters some information in the form, then clicks a button which writes the user-supplied info back to a SQL table.  This table includes an auto-numbering IDENTITY column (called dbID) and a computed column (called PalletID) that uses the value of the identity column to create a unique pallet ID number, which is also stored in the table.

After the record is inserted and PalletID has been computed, I need to retrieve the value of PalletID and display it on my form so it can be printed on an ID tag.  I tried to open a  recordset using SELECT... WHERE but I don't know the value of either IDENTITY column or the PalletID column and those are the only possibilities for unique identifiers for my record.

How can I return the value of either the dbID or PalletID columns after the INSERT completes?

Here's my code:
Function SavePrintTagInfo()

dim cn
dim strConnString
dim strSQLInstanceName
dim strDatabaseName
dim strSQL


set cn = createObject("ADODB.Connection")

strSQLInstanceName="WIN-FVVKGITEQC8\SQLEXPRESS"

strDatabaseName=SystemVariables.CodeObject.CompanyDatabaseName

strConnString = "Provider=SQLOLEDB;Data Source=" & strSQLInstanceName & "; _
                       Persist Security Info=False; _
                       Initial Catalog=" & strDatabaseName & ";Integrated Security=SSPI"

cn.Open strConnString

' Write form values to table 
' dbID is auto-sequence IDENTITY folumn. PalletID is computed as Current Year 
' plus dbID value
strSQL = "INSERT INTO dbo.T1 (ManID, RecDate, SCode) VALUES _ 
('" & Form.CodeObject.ManID & "','" & _ 
Form.CodeObject.RecDate & "','" & _ 
Form.CodeObject.StockCode & "')"

cn.Execute strSQL

cn.close
set cn = nothing


' Call function that creates text file output for barcoded tag
WriteFilePalletTag()


End Function

Open in new window


Many thanks, in advance, for your help!
monkeybiz12345Asked:
Who is Participating?
 
Dale BurrellDirectorCommented:
If you use the following SQL then you will get the PalletId back:

declare @NewId int; INSERT INTO dbo.T1 (ManID, RecDate, SCode) VALUES (x, y, z); set @NewId = scope_identity(); select PalletId from T1 there Id = @NewId

Open in new window


You need to use ExecuteScalar (http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx) instead of Execute.

As an aside I would strongly recommend the use of SqlParamaters instead of building a dynamic string. Much safer and the SqlParameter does much of the work for you. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx
0
 
mankowitzCommented:
to get the most recently inserted record, you can use

SELECT @@IDENTITY;

In your case, you want the PalletID, so you would use

SELECT [PalletID] WHERE dbID=@@IDENTITY;
0
 
Dale BurrellDirectorCommented:
ps - I would recommend never using @@identity, if for example your insert causes a trigger to insert a different record, @@identity returns the id of the wrong table. scope_identity doesn't.

http://technet.microsoft.com/en-us/library/ms187342.aspx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Anthony PerkinsCommented:
All of these solutions may work, but require an extra round trip to the server to query the database.    I would suggest you  use the OUTPUT clause to retrieve the value.
0
 
Dale BurrellDirectorCommented:
Don't see how my solution involves an extra round trip? Its only one - can you do it in none? :)
0
 
monkeybiz12345Author Commented:
@Dale Burrell,

I was able to get your suggestion to work from within the SQL Query Analyzer.  

Please can you clarify your statement that I need to use ExecuteScalar? It looks like, in order to do that, I'd need to create a function called ExecuteScalar and call it instead of using the command:

cn.Execute strSQL

Is that what you meant?
0
 
monkeybiz12345Author Commented:
@Anthony Perkins,

Re: the OUTPUT clause... This is beyond my current knowledge of SQL but I'm intrigued.   I found this:

http://technet.microsoft.com/en-us/library/ms177564.aspx

Is this what you're referring to?
0
 
Dale BurrellDirectorCommented:
@monkeybiz12345 - sorry I assumed you were using VB.NET.

You would need to capture the returned recordset and read the first value from it.
0
 
Anthony PerkinsCommented:
Is this what you're referring to?
Yes, that is correct.  It avoids the necessity of requerying the database with all the performance and risks involved doing that.
0
 
mankowitzCommented:
Just to clarify, you are NOT saving a server roundtrip because you still have to get the pallet information, so even if you have an output variable, you still have to use another select. Further, if you really think that an additional query through an open connection is expensive, just send both lines together as a multi or make a stored procedure.

INSERT INTO tbl (a,b,c) VALUES ('d', 'e', 'f');
SELECT [PalletID] WHERE dbID=@@IDENTITY;

remains optimal.
0
 
Anthony PerkinsCommented:
Just to clarify, you are NOT saving a server roundtrip because you still have to get the pallet information, so even if you have an output variable,
I beg to differ with you.

if you really think that an additional query through an open connection is expensive
Why would you make that assumption?  In a true client/server such as using ASP, each call is a separate connection (albeit pooled if this was .NET).
0
 
mankowitzCommented:
My point is that if you explicitly open and close each connection, the connection should stay open between queries. Otherwise, what does cn.Open actually do?

But the truth is that is irrelevant. You could send both lines as a multiple statement command or simply fold them into a stored procedure. You don't need to access the variables until you have the pallet ID.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.