Click here to Skip to main content
11,702,396 members (74,357 online)
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 1:29am
Edited 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 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
Comments
Member 11213666 at 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
0 Maciej Los 455
1 Sergey Alexandrovich Kryukov 310
2 OriginalGriff 265
3 CHill60 145
4 Andy Lanng 99
0 OriginalGriff 8,369
1 Sergey Alexandrovich Kryukov 7,547
2 CPallini 4,922
3 Maciej Los 4,411
4 Mika Wendelius 3,556


Advertise | Privacy | Mobile
Web02 | 2.8.150819.1 | Last Updated 5 Feb 2013
Copyright © CodeProject, 1999-2015
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