 |
 |
 |
|
| SecurityProNews > Articles > Application Development > Retrieve The Autonumber Value In Access Using C# |
[ articles_application_development ]Retrieve The Autonumber Value In Access Using C#Dipal Choksi Contributing Writer 2004-01-16  SecurityProNews RSS Feed
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
TblScore
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 iEntry! View All Articles by Dipal Choksi About the Author: 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 More articles_application_development Articles SecurityProNews RSS Feed
|
|
 |
iEntry Featured Services: Jayde Member Services | Forums | Freeware | Advertise with Us |
 |
| Contact
Us | Advertise
| Newsletter
Archive | Sitemap
| Submit an
Article | News
Feeds SecurityProNews is an iEntry, Inc.® publication - 1998-2008 All Rights Reserved | Privacy Policy and Legal |
Join the WebProWorld Forums: Click Here |
|
|||||||||||||||||||||||||
