This sample deals with the retrieval of the value of the Autonumber field for a data row inserted in MS Access 2000. SQL Server provides access to new Identity values through SCOPE_IDENTITY, IDENT_CURRENT and @@IDENTITY based on the scope and session boundaries. In Jet 4, Microsoft added support for ANSI-92 SQL syntax, including support for @@IDENTITY. This feature can be very useful in the Internet mode. Typically, you will be able to identify and access rows inserted from Web pages and manipulate the newly added rows.
Let’s consider a Student information system. The backed database used is Access 2000. The table tblStudent contains Student records and the tblScore table contains the score. The structure of the two tables is shown below. The StudentID field is Primary Key in the tblStudent table and foreign key in the tblScore table.
TblStudent
Field Name Data Type StudentID Autonumber NameText AddressText
TblScore
Field Name Data Type ScoreID Autonumber StudentIDNumber (Long Integer) ScoreNumber (Long Integer)
The Web Form allows user to enter the Student Name and Address and the Score. When the user clicks the Add button, the Student record is added in the tblStudent table with the Name and Address information. We query for the Identity value using the SQL statement “Select @@Identity” to get the value of the StudentId in the inserted record and use this value to create and populate the Score row.
This example is simplified for the sake of demonstration. In a real-life scenario, you would more likely have a one-to-many relationship between the tblStudent and tblScore tables. The same principle can be used in that scenario, to add multiple detail rows with the foreign key field filled in with the autonumber value for the master table. You must take care of concurrency situations and use Transactions to ensure the integrity of the data.
In a live situation, you should use a DataAdapter and handle the RowUpdated event raised for the adapter to ensure that the new row is correctly added in the master table. This example does not include any validations. In a live situation, you must add validations and error checks.
Figure: Sample Screenshot
Complete Code Listing: Save as IdentAccess.aspx
<%@ Page Language="c#"%>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<html>
<head>
<script runat="server">
private void Button1_Click(object sender, System.EventArgs e)
{
string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:db.mdb";
string strSQL = "INSERT INTO tblStudent (Name,Address) VALUES(?,?)";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd = new OleDbCommand(strSQL,conn );
cmd.Parameters.Add("@Name", OleDbType.Char, 50).Value = txtName.Text;
cmd.Parameters.Add("@Address", OleDbType.Char, 50).Value = txtAddress.Text;
cmd.ExecuteNonQuery();
cmd = new OleDbCommand("SELECT @@IDENTITY", conn);
int nId = (int)cmd.ExecuteScalar();
strSQL = "INSERT INTO tblScore (StudentId, Score) VALUES (?,?)";
cmd.CommandText = strSQL;
cmd.Parameters.Add("@StudentId", OleDbType.Integer).Value = nId;
cmd.Parameters.Add("@Score", OleDbType.Integer).Value = Int32.Parse(txtScore.Text);
cmd.ExecuteNonQuery();
lblStatus.Text = "The Student Information has been entered in the system.";
}
</script>
</head>
<body>
<H1>Student Entry Form</H1>
<form id="Form1" method="post" runat="server">
<asp:Label id="Label1" runat="server" Width="100px">Name</asp:Label>
<asp:TextBox id="txtName" runat="server" Width="329px"></asp:TextBox><BR/>
<asp:Label id="Label2" runat="server" Width="100px">Address</asp:Label>
<asp:TextBox id="txtAddress" runat="server" Width="329px"></asp:TextBox><BR/>
<asp:Label id="Label3" runat="server" Width="100px">Score</asp:Label>
<asp:TextBox id="txtScore" runat="server" Width="329px"></asp:TextBox><BR/>
<asp:Button id="Button1" runat="server" Text="Add" OnClick="Button1_Click"></asp:Button>
<asp:Label id="lblStatus" runat="server">
/asp:Label>
</form>
</body>
</script>
Conclusion
In this example we saw how to access the identity values from a newly inserted row in an Access Database from an ASP.Net web form.
Click here to sign up for FREE tech newsletters from Murdok!
Dipal Choksi is a Bachelor of Engineering (Computer Science). She has industry experience in team-effort projects and also as an individual contributor. She has worked on Visual Basic, Visual C++, Java, Directory Services, ASP projects
