Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2004-09-27
5
Medium Priority
?
961 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 1500 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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.
Suggested Courses

571 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