·
Visual Studio .NET 2003
·
SQL Server 2000
Listing 1 – Simple WebForm used for the Test
<form id="Form1" method="post" runat="server">
<p>First Name<asp:TextBox id="FirstName" runat="server"></asp:TextBox></p>
<p>Last Name<asp:TextBox id="LastName" runat="server"></asp:TextBox></p>
<p><asp:Button id="btnAddRecord" runat="server" Text="Add"></asp:Button></p>
<p><asp:Label id="lblLastRecordAdded" runat="server"></asp:Label></p>
</form>
We will create a database named DummyDB
and a table with the name Employees with Employeeid, FirstName and LastName as column names under the database. The required
SQL Script is given below for reference
Listing 2 - Table Creation Script
CREATE TABLE Employees (Employeeid int IDENTITY (1, 1) NOT NULL, FirstName nvarchar (50), LastName nvarchar (50))
Now that we have the table in place, we will see how to get
the Id of newly added record through the code. We will use the SCOPE_IDENTITY() function for our requirement. After
inserting the new record we simply return the value, as shown below.
Listing 3 – Use SCOPE_IDENTITY()
-- INSERT the new record
INSERT INTO Employees(FirstName, LastName)
VALUES(@FirstName, @LastName)
-- Now return the EmployeeId of the newly inserted record
SELECT SCOPE_IDENTITY()
The ExecuteScalar() method can be used in ASP.NET since it
returns the first column of the first row in the result set returned by the
query.
Listing 4 – Use ExecuteScalar()
Below is the code in VB.NET and C#.