TblStudent
| Field Name |
Data Type |
| StudentID |
Autonumber |
| Name |
Text |
| Address | Text |
TblScore
| Field Name | Data Type | | ScoreID | Autonumber | | StudentID | Number (Long Integer) | | Score | Number (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.
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
Read this Newsletter at: http://www.cprogrammingtrends.com/2004/0121.html |
|
| From the Forum: |
| Access database |
... Is there a way to implement a query or report that will search the master table, that has all basic employee information, to see if the month of their hire date matchs the current month, and if so to include them in the report I described above?
...
|
|
By
Dipal Choksi