Click here to Skip to main content
12,406,592 members (48,194 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: VB SQL-Server SQL-Server-2008 , +
Here is my table schema
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
Updated 12-Jan-13 1:34am
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

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:
INSERT INTO MyTable (column1, column2) VALUES (...)
OUTPUT inserted.CustomersID INTO @customerid
Just remember to define a SqlParameter for the returning value.
  Permalink  
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
Mika Wendelius 22-Jan-13 13:38pm
   
Have you tried running the statement in SSMS?
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

SELECT TOP 1 * FROM table_Name ORDER BY unique_column DESC

Try this.
  Permalink  
Comments
KrishanuDey 12-Jan-13 7:42am
   
is there any way to use the output clause in vb.net
onurag19 12-Jan-13 7:44am
   
how do you want to use this in gridview or what ?
Member 10711615 16-Sep-15 2:20am
   
What will be the result--last inserted one or first one ?
i need last inserted CustomerID
thanks
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

SELECT TOP 1 * FROM Customer ORDER BY CustomerID DESC

and bind data to gridview or repeater.
  Permalink  
Comments
Member 10711615 16-Sep-15 2:19am
   
What will be the answer --last inserted one or first one ?
i need last inserted CustomerID
thanks
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

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.
  Permalink  
v2
Comments
Member 11213666 23-Jun-15 2:23am
   
Solution 1 is working fine

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160730.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100