Source: http://www.study-code.com/dotnet/aspnet/67500.htm
Problem
Q: How to get the ID of an auto-increment column (ID column) and write it to another table.
Relationship Diagram
Implementation points
1. How to obtain the ID generated by the new insert record: SQL Server and Access (when using Jet ADO, of course, including ADO. NET, during connection) supports @ Identity global variables and returns the last Identifier value generated in all tables of the current session.
2. When writing data to multiple tables at the same time, transactions must be displayed.
MS Access Version
<% @ Page Language = "C #" %>
<% @ Import Namespace = "System. Data" %>
<% @ Import Namespace = "System. Data. OleDb" %>
<! DOCTYPE html PUBLIC "-// W3C // dtd xhtml 1.0 Transitional // EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Script runat = "server">
String connStr;
Protected void Page_Load (object sender, EventArgs e)
{
ConnStr = "provider = Microsoft. Jet. OleDb.4.0; data source =" + Server. MapPath ("~ /App_Data/demomanytow.mdb ");
}
Protected void btnLogin_Click (object sender, EventArgs e)
{
If (chkRoles. SelectedIndex =-1) throw new Exception ("select at least one role. ");
String sqlInserUser = "insert into [User] ([UserName], [Password]) VALUES (?, ?) ";
String sqlInserUserRoel = "insert into UserRole ([UserId], [RoleId]) VALUES (?, ?) ";
String sqlSelectNewUserId = "SELECT @ Identity"; // OR "select max ([UserId]) FROM [User]";
Using (OleDbConnection conn = new OleDbConnection (connStr )){
Conn. Open ();
// Display start transaction
OleDbTransaction trans = conn. BeginTransaction ();
OleDbCommand cmd = conn. CreateCommand ();
// Associate transaction
Cmd. Transaction = trans;
Try {
Cmd. CommandText = sqlInserUser;
Cmd. Parameters. Add ("UserName", txtUserName. Text );
Cmd. Parameters. Add ("Password", txtPassword. Text );
// Insert User
Cmd. ExecuteNonQuery ();
Cmd. CommandText = sqlSelectNewUserId;
// Read the newly inserted UserId
Int newUserId = (int) cmd. ExecuteScalar ();
// Test only
If (chkGeneratError. Checked) throw new Exception ("an error occurred when creating a user. ");
Cmd. CommandText = sqlInserUserRoel;
Cmd. Parameters. Clear ();
Cmd. Parameters. Add ("UserId", OleDbType. Integer );
Cmd. Parameters. Add ("RoleId", OleDbType. Integer );
Cmd. Parameters [0]. Value = newUserId;
// Traverse the list of optional roles
Foreach (ListItem item in chkRoles. Items ){
If (item. Selected ){
Cmd. Parameters [1]. Value = item. Value;
// Write the intermediate relationship table UserRole
Cmd. ExecuteNonQuery ();
}
}
// Submit the transaction
Trans. Commit ();
LblMsg. Text = String. Format ("User '{0}' is created successfully. \ N transaction submitted. ", TxtUserName. Text );
}
Catch (Exception inner ){
// Roll back the transaction if an error occurs
If (trans! = Null) trans. Rollback ();
LblMsg. Text = String. Format ("User '{0}' failed to be created. \ N the transaction has been rolled back. \ N details: {1} ", txtUserName. Text, inner. Message );
// Throw new Exception ("User Creation failed. The transaction has been rolled back. ", Inner );
}
}
// Reload User data
GrdvUsers. DataBind ();
}
Protected void grdvUsers_RowDataBound (object sender, GridViewRowEventArgs e)
{
// Load the Roles corresponding to each User
DataList dlstRolesOfUser = e. Row. FindControl ("dlstRolesOfUser") as DataList;
If (dlstRolesOfUser = null) return;
Int userId = (int) grdvUsers. DataKeys [e. Row. RowIndex]. Value;
String sqlSelectRoleOfUser =
"SELECT Role. RoleName FROM (Role inner join UserRole ON Role. RoleId = UserRole. RoleId) WHERE UserRole. UserId =? ";
OleDbDataAdapter da = new OleDbDataAdapter (sqlSelectRoleOfUser, connStr );
Da. SelectCommand. Parameters. Add ("UserId", userId );
DataTable dtRolesOfUser = new DataTable ();
Da. Fill (dtRolesOfUser );
DlstRolesOfUser. DataSource = dtRolesOfUser;
DlstRolesOfUser. DataBind ();
}
</Script>
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head runat = "server">
<Title> multi-to-Multi-write instance-Access version </title>
</Head>
<Body>
<Form id = "form1" runat = "server">
<Div>
<H1> multi-to-Multi-write instance -- Access version <H3> create a user <Table border = "1">
<Tr>
<Td>
User name:
</Td>
<Td>
<Asp: TextBox ID = "txtUserName" runat = "server"> </asp: TextBox>
<Asp: RequiredFieldValidator ID = "RequiredFieldValidator1" runat = "server" ControlToValidate = "txtUserName"
Display = "Dynamic" ErrorMessage = "Required"> </asp: RequiredFieldValidator> </td>
</Tr>
<Tr>
<Td>
Password:
</Td>
<Td>
<Asp: TextBox ID = "txtPassword" runat = "server"> </asp: TextBox> </td>
</Tr>
<Tr>
<Td>
Role:
</Td>
<Td>
<Asp: CheckBoxList ID = "chkRoles" runat = "server" performanceid = "AccessDataSource1" DataTextField = "RoleName" DataValueField = "RoleId" RepeatDirection = "Horizontal"> </asp: checkBoxList> <asp: AccessDataSource ID = "accessperformance1" runat = "server" DataFile = "~ /App_Data/demomanytow.mdb"
SelectCommand = "SELECT [RoleId], [RoleName] FROM [Role]"> </asp: AccessDataSource>
</Td>
</Tr>
<Tr>
<Td>
Force Error:
</Td>
<Td>
<Asp: CheckBox ID = "chkGeneratError" runat = "server"/> </td>
</Tr>
<Tr>
<Td>
</Td>
<Td>
<Asp: Button ID = "btnLogin" runat = "server" Text = "OK" OnClick = "btnLogin_Click"/> </td>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
