CodeSnip: How to Get Id of the Record Using ASP.NET and SQL Server 2000
page 3 of 5
by Sushila Bowalekar Patel
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 40576/ 52

Code

VB.NET

Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim strconnection, strsqlinsert As String
Dim Employeeid As String
Private Sub btnAddRecord_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles btnAddRecord.Click
  strconnection = "server=localhost;uid=sa;password=;database=DummyDB"
  strsqlinsert = "Insert into Employees ( "
  strsqlinsert + = "FirstName ,LastName"
  strsqlinsert + = ")"
  strsqlinsert + = " values ("
  strsqlinsert + = "@FirstName,@LastName"
  strsqlinsert + = ")"
  strsqlinsert + = "; SELECT SCOPE_IDENTITY() ; "
  conn = New SqlConnection(strconnection)
  cmd = New SqlCommand(strsqlinsert, conn)
  cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstName.Text
  cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastName.Text
  cmd.Connection.Open()
  Employeeid = cmd.ExecuteScalar
  cmd.Connection.Close()
  lblLastRecordAdded.Text = Employeeid
End Sub

C#

string strconnection, strsqlinsert;
SqlConnection conn;
SqlCommand cmd;
string Employeeid;
private void btnAddRecord_Click(object sender, System.EventArgs e)
{
  strconnection = "server=localhost;uid=sa;password=;database=DummyDB";
  strsqlinsert = "Insert into Employees ( ";
  strsqlinsert += "FirstName ,LastName";
  strsqlinsert += ")";
  strsqlinsert += " values (";
  strsqlinsert += "@FirstName,@LastName";
  strsqlinsert += ")";
  strsqlinsert += "; SELECT SCOPE_IDENTITY() ; ";
  conn = new SqlConnection(strconnection);
  cmd = new SqlCommand(strsqlinsert, conn);
  cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FirstName.Text;
  cmd.Parameters.Add("@LastName", SqlDbType.NVarChar).Value = LastName.Text;
  cmd.Connection.Open();
  Employeeid = cmd.ExecuteScalar().ToString();
  cmd.Connection.Close();
  lblLastRecordAdded.Text = Employeeid;
}

Figure 1

NOTE

Although SCOPE_IDENTITY and @@IDENTITY return the last identity value generated for any table in the current session, SCOPE_IDENTITY returns value only in current scope whereas @@IDENTITY returns value across all scopes.

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.


View Entire Article

User Comments

Title: thanks   
Name: vibhu singh
Date: 2007-05-01 4:20:09 AM
Comment:
thanks. i am a student. i was searching code for insert data in sql table. your site provide perfect solution for my search. thanks for this help.
Title: help   
Name: amit07
Date: 2007-03-30 9:58:25 AM
Comment:
hi,
can u plz clear the diff b/w scope_identity() and @@identity
more.
Title: Excellent   
Name: From Iran
Date: 2007-03-28 5:36:09 AM
Comment:
This Tutorial was one of the rare clear ones on the NET excellent job
Title: Thanks   
Name: Hooman
Date: 2007-03-28 5:13:04 AM
Comment:
Thank you, You Made my Day
Title: Thanks Thanks Thanks Thanks Thanks   
Name: Desperate
Date: 2007-02-22 10:28:39 PM
Comment:
Man, you have no idea, you saved my job!
Title: Thanks   
Name: Swinkster
Date: 2007-02-13 9:17:43 AM
Comment:
Thank you! Well written example.
Title: a very good one...   
Name: Genie
Date: 2006-12-26 9:58:10 PM
Comment:
Thanks a lot...
i've been searching for this the whole day, but now i got it :)
Title: Very Good Code   
Name: M.Anwar Iqbal Khan
Date: 2006-12-03 7:13:54 AM
Comment:
That kind of code i am looking for in internet, very good code. Thanks.
Title: Muchas Gracias   
Name: Peter
Date: 2006-11-28 3:29:33 AM
Comment:
Thanks, just what i needed!
Title: great article   
Name: cake
Date: 2006-09-27 10:14:38 AM
Comment:
thanks alot!
Title: thanks   
Name: surya bhan
Date: 2006-09-27 4:03:50 AM
Comment:
very good
Title: Thanks   
Name: Dave London
Date: 2006-08-21 12:45:40 PM
Comment:
Good article. Until recently I wasn't aware of the difference myself. Your solution is the one I would recommend as @@indentity is not thread safe and it is better practice to use SCOPE_IDENTITY() just in case triggers are added to the table at a later stage.
Title: Good snippit   
Name: toffer
Date: 2006-08-10 5:34:26 AM
Comment:
great article... exactly what i need ... :)
Title: Thanks   
Name: TonyM
Date: 2006-08-08 4:50:49 PM
Comment:
Excellent Example. Thanks for the snippets, explanations and example. I searched high and low for a good example, glad I found this one!
Title: Thank you!   
Name: terryh
Date: 2006-07-28 9:25:26 PM
Comment:
Finally, exactly what I needed. Why is this stuff so hard to find?...oh well...






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2022 ASPAlliance.com  |  Page Processed at 2022-09-24 4:19:57 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search