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
Solved

Retrieving record id from identity - Stored Procedure and VB.Net

Posted on 2004-09-27
5
938 Views
Last Modified: 2012-05-05
I have looked through the examples and found what I thought was the answer. The problem is how to get the @@identity from the stored procedure into my VB.Net application. Any help would be greatly appreciated as I will be using this in several stored procedures/vb code.

Stored Procedure:
CREATE PROCEDURE [dbo].[sp_jrn_InsertNewRecordToCounter] (@TableName VarChar(25))
AS
SET NOCOUNT ON
EXEC('INSERT INTO ' +  @TableName + ' ([Date])  VALUES(GetDate())')

SELECT @@identity
RETURN @@identity
SET NOCOUNT OFF
GO


VB.Net code:
'insert a new record into the project table
                    'declare a new sqlcommand
                    Dim SQLCommand As SqlClient.SqlCommand

                    'declare a sql transaction name
                    Dim myTrans As SqlClient.SqlTransaction

                    'insert a new record in to the appropriate table (strTable)
                    'define the sql command to be run
                    SQLCommand = New SqlClient.SqlCommand("sp_jrn_InsertNewRecordToCounter", conn)
                    SQLCommand.CommandType = CommandType.StoredProcedure
                    SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@TableName", SqlDbType.VarChar, 25))
                    SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@@identity", SqlDbType.Int))
                    SQLCommand.Parameters("@TableName").Value = CStr(strTable)

                    'make sure connection to sql server is open
                    conn.Open()

                    'set the sql command's connection to conn
                    SQLCommand.Connection = conn

                    ' Begin the transaction.
                    myTrans = conn.BeginTransaction

                    ' Assign transaction object for a pending local transaction
                    SQLCommand.Transaction = myTrans

                    'run the update query
                    SQLCommand.ExecuteNonQuery()

                    'commit the trans
                    myTrans.Commit()

                    'close the connection to the sql server
                    conn.Close()
                    Dim intID As Integer = SQLCommand.Parameters.Item("@@identity").Value()
                    MsgBox(intID)
                    Exit Sub

Thanks in advance, MT
0
Comment
Question by:MajikTara
  • 3
  • 2
5 Comments
 
LVL 7

Accepted Solution

by:
FDzjuba earned 500 total points
ID: 12162483
1st metod, but not the best:

CREATE PROCEDURE [dbo].[sp_jrn_InsertNewRecordToCounter]
@TableName VarChar(25)
@IdentityID int OUTPUT

AS
SET NOCOUNT ON
EXEC('INSERT INTO ' +  @TableName + ' ([Date])  VALUES(GetDate())')
   SET @IdentityID = @@identity ;
SET NOCOUNT OFF
GO


                    'declare a new sqlcommand
                    Dim SQLCommand As SqlClient.SqlCommand

                    'declare a sql transaction name
                    Dim myTrans As SqlClient.SqlTransaction

                    'insert a new record in to the appropriate table (strTable)
                    'define the sql command to be run
                    SQLCommand = New SqlClient.SqlCommand("sp_jrn_InsertNewRecordToCounter", conn)
                    SQLCommand.CommandType = CommandType.StoredProcedure
                    SQLCommand.Parameters.Add(New SqlClient.SqlParameter("TableName", SqlDbType.VarChar, 25))
                    conn.Open()
                    SQLCommand.Execute

                    Dim nIdentity as integer
                    nIdentity  = SQLCommand.Parameters("IdentityID").Value

that should work, maybe you need to work a little more on the vb code, cos i am not sure that its syntax correct
0
 

Author Comment

by:MajikTara
ID: 12164540
Hi FDzjuba,
When I try as you indicated I receive the following error:
Procedure 'sp_jrn_InsertNewRecordToCounter' expects parameter '@IdentityID', which was not supplied.

I then added the following to the vb.net code after "SQLCommand.Parameters.Add(New SQLClient.SQLParameter("......  :
SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@IdentityID", SQLDbType.Int, ParameterDirection.OutPut)

I received the same message. It appears that I have some problem with my code extracting the results. Any other items I may have missed?

(Note SQLCommand.Execute does not exist, it's SQLCommand.ExecuteNonQuery and "TableName" needs to be "@TableName")

MT
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12165364
i don't have VB to my disposal, so kind of shot in the dark, try this

   
'  Build Command
   Dim cmd As ADODB.Command
   Set cmd = New ADODB.Command
   With cmd
      .ActiveConnection = conn
      .CommandType = adCmdStoredProc
      .CommandText = "[sp_jrn_InsertNewRecordToCounter]"
   End With
     
'  Build Parameters
   Dim par As ADODB.Parameter
   With cmd
      Set par = .CreateParameter("IdentityID", adInt, adParamOutput)
      .Parameters.Append par
      Set par = .CreateParameter("TableName", adVarChar, adParamInput)
      .Parameters.Append par
   End With
     
'  Execute Query
   With cmd
      .Parameters("TableName").Value = "mytable"
      .Execute
      MsgBox .Parameters("IdentityID").Value
   End With
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12165372
aha, just realized you VB.NET that is why you've used class SqlClient, just to let you know its kind a messy, the above VB6 code should work with VB.NET anyway
0
 

Author Comment

by:MajikTara
ID: 12171447
FDzjuba,

Your first version of the code was correct, but one thing was missing. Please note the lines preceeded with *. I don't understand why it requires the line for parameter direction rather than the overloaded property. Thanks for the help and I have awarded the points to you.

CREATE PROCEDURE [dbo].[sp_jrn_InsertNewRecordToCounter]
@TableName VarChar(25)
@IdentityID int OUTPUT

AS
SET NOCOUNT ON
EXEC('INSERT INTO ' +  @TableName + ' ([Date])  VALUES(GetDate())') ;  <--- the semicolon moved here from next line
   SET @IdentityID = @@identity
SET NOCOUNT OFF
GO


                    'declare a new sqlcommand
                    Dim SQLCommand As SqlClient.SqlCommand

                    'declare a sql transaction name
                    Dim myTrans As SqlClient.SqlTransaction

                    'insert a new record in to the appropriate table (strTable)
                    'define the sql command to be run
                    SQLCommand = New SqlClient.SqlCommand("sp_jrn_InsertNewRecordToCounter", conn)
                    SQLCommand.CommandType = CommandType.StoredProcedure
                    SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@TableName", SqlDbType.VarChar, 25))
*****          SQLCommand.Parameters.Add(New SqlClient.SqlParameter("@IdentityID", SqlDbType.Int))
*****          SQLCommand.Parameters("@ID").Direction = ParameterDirection.Output
                    conn.Open()
*****          SQLCommand.ExecuteNonQuery

                    Dim nIdentity as integer
                    nIdentity  = SQLCommand.Parameters("IdentityID").Value

Thanks, MT
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

807 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