Solved

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

Posted on 2014-03-10
12
370 Views
Last Modified: 2014-03-22
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!
0
Comment
Question by:monkeybiz12345
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 21

Accepted Solution

by:
Dale Burrell earned 500 total points
ID: 39919388
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 39919394
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39919406
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39919419
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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39919488
Don't see how my solution involves an extra round trip? Its only one - can you do it in none? :)
0
 

Author Comment

by:monkeybiz12345
ID: 39919542
@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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:monkeybiz12345
ID: 39919547
@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
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 39919552
@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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39922311
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 39934249
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39935751
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
 
LVL 24

Expert Comment

by:mankowitz
ID: 39938709
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

Featured Post

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!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Bulk load data error 5 34
ssms - object execution statistics 12 37
MS SQL Backup 24 72
How to find duplicates in SQL Server 3 23
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

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

12 Experts available now in Live!

Get 1:1 Help Now