Visual Basic Code Snippet - Get Identity Column Value from SQL Server

Visual Basic Code Snippet - Get Identity Column Value from SQL Server

This .Net Visual Basic code snippet connects to SQL server and executes a SQL statement and return the latest identity used within the scope of that user statement. To use this function simply provide open database connection and SQL statement. This function uses SqlClient name space to get the @@IDENTITY using ExecuteScalar method. Modify the exception handling section for your project requirements. Identity related data manipulation can be done more efficiently using stored procedure, this is an example showing how to read the identity value.

Identity Column
An Identity column is a column ( also known as a field ) in a database table that uniquely identifies every row in the table, and is made up of values generated by the database. An identity column has a name, initial seed and step. When a row is inserted into a table the column will take the value of the curent seed incremented by the step.
Note: An identity column is not guaranteed to be unique nor consecutive. You should always place a unique index on an identity column if your system requires uniqueness.

Public Function ExeSQLReturnLong(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Long
    Dim _ReturnID As Long = 0
    Dim _ReturnObject As Object = Nothing

    Try
        ' Execute Query, and return last insert ID
        _SQL &= ";SELECT @@IDENTITY AS 'Identity'"
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)

        _ReturnObject = _SqlCommand.ExecuteScalar()

        _SqlCommand.Dispose()
        _SqlCommand = Nothing
    Catch _Exception As System.Data.SqlClient.SqlException
        ' Error occurred while trying to execute reader
        ' send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message)

        Return 0
    End Try

    ' convert return object to long
    Try
        _ReturnID = Long.Parse(_ReturnObject.ToString())
    Catch _Exception As Exception
        ' Error occurred while trying to convert return id to long
        ' send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message)

        Return 0
    End Try

    Return _ReturnID
End Function

Here is a simple example showing how to use above function (ExeSQLReturnLong) to login to SQL server and inserts a row into a table with an identity column (productid) and uses @@IDENTITY to display the identity value used in the new row. This function (ExeSQLReturnLong) retrieves the identity value as an object and converts it into a long and return the value.

' set temporary variable for database connection
Dim _SqlConnection As New System.Data.SqlClient.SqlConnection()

' assign database connection string
_SqlConnection.ConnectionString = "Server=SERVERADDRESS;Database=DATABASENAME;Uid=USERID;Pwd=PASSWORD;"

' Connect to database
Try
    _SqlConnection.Open()
Catch _Exception As Exception
    ' Error occurred while trying to connect to database
    Console.WriteLine(_Exception.Message)
End Try

' Check for valid open database connection before query database
If _SqlConnection IsNot Nothing AndAlso _SqlConnection.State = ConnectionState.Open Then
    Dim _NewProductID As Long = 0

    ' add new record to database table and get the autoincrement product id
                        ' Pass open database connection to function
                        ' Pass SQL statement to insert new record
    _NewProductID = ExeSQLReturnLong(_SqlConnection, "INSERT INTO sampletable (name, price) VALUES ('sample product name', 22.75)")

    If _NewProductID > 0 Then
        ' Record successfully insert into database
        Console.WriteLine("Record added to database table - New Record ID : " & _NewProductID.ToString())
    Else
        ' Record failed to insert into database
        Console.WriteLine("Failed to add new record to database table")
    End If

    ' close database connection
    _SqlConnection.Close();
End If

Here is the same function which modified (ExeSQLReturnLong => ExeSQLReturnObject) to return the identity as an object which gives the opinion to convert the identity value to any data type.

Public Function ExeSQLReturnObject(ByRef _SqlConnection As System.Data.SqlClient.SqlConnection, ByVal _SQL As String) As Object
    Dim _ReturnObject As Object = Nothing

    Try
        ' Execute Query, and return last insert ID
        _SQL &= ";SELECT @@IDENTITY AS 'Identity'"
        Dim _SqlCommand As New System.Data.SqlClient.SqlCommand(_SQL, _SqlConnection)

        _ReturnObject = _SqlCommand.ExecuteScalar()

        _SqlCommand.Dispose()
        _SqlCommand = Nothing
    Catch _Exception As System.Data.SqlClient.SqlException
        ' Error occurred while trying to execute reader
        ' send error message to console (change below line to customize error handling)
        Console.WriteLine(_Exception.Message)

        Return Nothing
    End Try

    Return _ReturnObject
End Function

VB Keywords Used:

• long
• Parse
• ExecuteNonQuery
• SqlConnection
• ConnectionString
• SqlCommand
• Exception

Code Snippet Information:

• Applies To: .Net, Visual Basic, VB, CLI, SQL, ExecuteScalar, Identity Column, SQL Server, SQL Client, Connection String, Database Connection
• Programming Language : Visual Basic (VB)

External Resources:

• @@IDENTITY (Transact-SQL)
• SqlCommand.ExecuteScalar Method
• SqlConnection Class
• SqlConnection.ConnectionString Property
• SqlCommand Class
• Exception Class

Leave a comment