Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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 2:29am
Edited 12-Jan-13 2: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 at 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 at 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 at 12-Jan-13 7:42am
   
is there any way to use the output clause in vb.net
onurag19 at 12-Jan-13 7:44am
   
how do you want to use this in gridview or what ?
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  
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

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

  Print Answers RSS
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2014
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