Select Last Inserted Record in SQL Server 2008 using VB.net

Question

0.00/5 (No votes)

See more:

VB

SQL-Server

SQL-Server-2008

, +

Here is my table schema

C++

CustomersID         uniqueidentifier    (newid())
CompanyName         nvarchar(50)
RegistrantName  nvarchar(50)
AddressLine1    nvarchar(50)
AddressLine2    nvarchar(50)
City            nvarchar(50)
State           nvarchar(50)
Country         nvarchar(50)
Zip                 nvarchar(20)
PhoneNo         nvarchar(50)
MobileNo            nvarchar(20)
FaxNo           nvarchar(20)
Website         nvarchar(250)
Email           nvarchar(250)

As I'm not using any identity field in my table I cannot use the SCOPE_IDENTITY() to get the last inserted record id. I need to get the CustomersID after inserting the record. How is it possible? I'm using sqlcommand class object to insert the record.

Posted 12-Jan-13 1:29am

KrishanuDey

Updated 12-Jan-13 1:34am

v2

Add a Solution

4 solutions

Solution 2

In my opinion the best way would be to use the OUTPUT[^] clause. So you could modify your insert statement and add it to the SQL. Something like:

SQL

INSERT INTO MyTable (column1, column2) VALUES (...)
OUTPUT inserted.CustomersID INTO @customerid

Just remember to define a SqlParameter for the returning value.

Posted 12-Jan-13 4:44am

Wendelius

Comments

KrishanuDey 14-Jan-13 22:43pm

Well, I tried your solution. But I got an error saying "Incorrect syntax near 'OUTPUT'." Here is my code..

Dim conn As New SqlConnection(WebConfigurationManager.ConnectionStrings("connectionstring").ConnectionString)
Dim sqlcommand As New SqlCommand
sqlcommand.CommandType = Data.CommandType.Text
sqlcommand.CommandText = "insert into [Customers] (CompanyName,RegistrantName,AddressLine1,AddressLine2,City,State,Country,Zip,PhoneNo,MobileNo,FaxNo,Website,Email) values(@CompanyName,@RegistrantName,@AddressLine1,@AddressLine2,@City,@State,@Country,@Zip,@PhoneNo,@MobileNo,@FaxNo,@Website,@Email) OUTPUT inserted.CustomersID INTO @CustomersID"
sqlcommand.Parameters.AddWithValue("@CompanyName", _Company)
sqlcommand.Parameters.AddWithValue("@RegistrantName", _Name)
sqlcommand.Parameters.AddWithValue("@AddressLine1", _Address1)
sqlcommand.Parameters.AddWithValue("@AddressLine2", _Address2)
sqlcommand.Parameters.AddWithValue("@City", _City)
sqlcommand.Parameters.AddWithValue("@State", _State)
sqlcommand.Parameters.AddWithValue("@Country", _Country)
sqlcommand.Parameters.AddWithValue("@Zip", _Zip)
sqlcommand.Parameters.AddWithValue("@PhoneNo", _PhoneNo)
sqlcommand.Parameters.AddWithValue("@MobileNo", _Mobile)
sqlcommand.Parameters.AddWithValue("@FaxNo", _FaxNo)
sqlcommand.Parameters.AddWithValue("@Website", _Website)
sqlcommand.Parameters.AddWithValue("@Email", _Registration_Email)
Dim paramCustomersID As New SqlParameter("@CustomersID", Data.SqlDbType.UniqueIdentifier)
sqlcommand.Parameters.Add(paramCustomersID)
sqlcommand.Connection = conn
If conn.State = Data.ConnectionState.Closed Then conn.Open()
If sqlcommand.ExecuteNonQuery() > 0 Then
Save = True
_CustomerID = paramCustomersID.Value.ToString()
End If

Wendelius 22-Jan-13 13:38pm

Have you tried running the statement in SSMS?

Solution 1

SQL

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC

Try this.

Posted 12-Jan-13 1:36am

Anurag Sarkar

Comments

KrishanuDey 12-Jan-13 7:42am

is there any way to use the output clause in vb.net

[no name] 12-Jan-13 7:44am

how do you want to use this in gridview or what ?

tiFoysal 16-Sep-15 2:20am

What will be the result--last inserted one or first one ?
i need last inserted CustomerID
thanks

Solution 3

SQL

SELECT TOP 1 * FROM Customer ORDER BY CustomerID DESC

and bind data to gridview or repeater.

Posted 12-Jan-13 7:48am

Surendra0x2

Comments

tiFoysal 16-Sep-15 2:19am

What will be the answer --last inserted one or first one ?
i need last inserted CustomerID
thanks

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Accepted Answer · 2013-02-05T11:41:00

An introduction to TSQL brute force. When all else achieves FAIL state:

USE [cpqaAnswers]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cpqa].[tblKD]') AND type in (N'U'))
DROP TABLE [cpqa].[tblKD]

CREATE TABLE [cpqaAnswers].[cpqa].[tblKD](
    CustomersID	    uniqueidentifier,
    CompanyName	    nvarchar(50),
    RegistrantName  nvarchar(50),
    AddressLine1   nvarchar(50),
    AddressLine2   nvarchar(50),
    City	   nvarchar(50),
    [State]	   nvarchar(50),
    Country	   nvarchar(50),
    Zip	           nvarchar(20),
    PhoneNo	   nvarchar(50),
    MobileNo	   nvarchar(20),
    FaxNo	   nvarchar(20),
    Website	   nvarchar(250),
    Email	   nvarchar(250)
    )

Put some data in a .txt file ... call it data.dat ... use a format that seperates values with a single {TAB}

BULK INSERT [cpqaAnswers].[cpqa].[tblKD] FROM 'C:\cpqaAnswers\KD\data.dat'


			
CREATE TABLE [cpqaAnswers].[cpqa].[tblKDIdx](
    Idx            [int]IDENTITY(1,1),
    CustomersID	    nvarchar(555),
    CompanyName	    nvarchar(50),
    RegistrantName nvarchar(50),
    AddressLine1   nvarchar(50),
    AddressLine2   nvarchar(50),
    City	   nvarchar(50),
    [State]	   nvarchar(50),
    Country	   nvarchar(50),
    Zip	            nvarchar(20),
    PhoneNo	    nvarchar(50),
    MobileNo	    nvarchar(20),
    FaxNo	    nvarchar(20),
    Website	    nvarchar(250),
    Email	    nvarchar(250)
    )			
    
INSERT INTO [cpqaAnswers].[cpqa].[tblKDIdx]
	SELECT * FROM [cpqaAnswers].[cpqa].[tblKD] 
	
SELECT * FROM  [cpqaAnswers].[cpqa].[tblKDIdx] ORDER BY [Idx] DESC

Now leverage the TOP 1 factor ... or use a WHERE clause and the result of:

(SELECT COUNT(*) FROM [cpqaAnswers].[cpqa].[tblKDIdx])

as the [Idx] equality to return the last record.