Solved

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

Posted on 2014-03-10
12
376 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Optimization 14 44
i have to take the screenshot of command prompt? how to do this? 1 37
Clear Filter 8 40
SQL server vNext 18 29
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

777 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