Solved

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

Posted on 2014-03-10
12
382 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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 to return specific rows and columns, with various degrees of sorting and limits in place.

752 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