Since I have never used an Access database before, when you want to insert multiple data records in the database, we should first follow the sqlserver practice: insertintotablename (column1, column2) values (, b), (c, d), (e, f ). So according to this idea, my first plan came out. Example 1: TheDemo: StringBuild
Since I have never used an Access database before, when you want to insert multiple data records in the database, follow the SQL server practice first: insert into tablename (column1, column2) values (a, B), (c, d), (e, f ). So according to this idea, my first plan came out. Try 1: The Demo: StringBuild
Since I have never used an Access database before, when you want to insert multiple data records in the database, we should first follow the SQL server's practice: "insert into tablename (column1, column2) values (a, B), (c, d), (e, f )". So according to this idea, my first plan came out.
Try one:
The Demo:
StringBuilder BuiList = new StringBuilder (string. format ("({0}, 0)", UserID); foreach (RepeaterItem item in Rpt_AdminRole.Items) {if (item. itemType = ListItemType. item | item. itemType = ListItemType. alternatingItem) {HtmlInputCheckBox cbRole = item. findControl ("cb_Role") as HtmlInputCheckBox; if (cbRole. checked) {BuiList. append (", ("); BuiList. append (UserID); BuiList. append (","); BuiList. append (cbRole. value); BuiList. append (")");}}}
The Dal:
/// /// Add a Role relationship ////// Role Relationship eg )"/// Public static int InsertRoleContact (string roleContact) {string SQL = "insert into Sky_Admin_Role (AdminID, RoleID) values" + roleContact; return Common. oleDbHelper. executeNonQuery (CommandType. text, SQL, null );}
The following error occurs in exec: the end position of the SQL statement is missing the semicolon (;).
Access's support for SQL is indeed much simplified, and this attempt fails !, Soon I had another idea. SQL server multi-table query supports select table! You can directly upload a able to an SQL statement!
Test 2
The Demo: Get DataTable
Public DataTable GetInsertSQL (Repeater rep, string controlID) {DataTable data = new DataTable (); data. columns. add ("AdminID"); data. columns. add ("RoleID"); foreach (RepeaterItem item in rep. items) {if (item. itemType = ListItemType. item | item. itemType = ListItemType. alternatingItem) {HtmlInputCheckBox cb = item. findControl (controlID) as HtmlInputCheckBox; if (cb. checked) {DataRow row = data. newRow (); row. itemArray = new object [] {UserID, cb. value}; data. rows. add (row) ;}} return data ;}
The Dal:
Public static int InsertRoleContact (DataTable dt) {string SQL = "insert into Sky_Admin_Role (AdminID, RoleID) select * from @ Data "; oleDbParameter [] param = new OleDbParameter [] {new OleDbParameter ("@ Data ",?) {Value = dt }}; return Common. OleDbHelper. ExecuteNonQuery (CommandType. Text, SQL, param );}
When the code reaches here, I will know that this method does not work, because OledbType does not have the corresponding table type. If SQL server supports xml, it can be set to SqlDbType. the Xml type is used to transmit DataTable data. Because SQL supports xml, SQL queries based on xml can be used. This article mainly discusses Access, which is not discussed here! Attempt 2: An error occurred while announcing the announcement !. Next, I thought about the zero-time table. Does Access support querying the zero-time table?
Try 3
The Demo: retrieve The desired data format (1, 2, 3)
Public string GetInserCollection (Repeater rep, string controlID) {StringBuilder buiCollecton = new StringBuilder (""); buiCollecton. append ("(0"); foreach (RepeaterItem item in rep. items) {if (item. itemType = ListItemType. item | item. itemType = ListItemType. alternatingItem) {HtmlInputCheckBox cb = item. findControl (controlID) as HtmlInputCheckBox; if (cb. checked) {buiCollecton. append (string. format (", {0}", cb. value) ;}} buiCollecton. append (")"); return buiCollecton. toString ();}
The Dal:
Public static int InsertRoleContact (int UserID, string RoleCollection)
{
StringBuilder BuiSQL = new StringBuilder ("");
BuiSQL. Append ("declare @ SkyContact table (userID int, roleID int );");
BuiSQL. Append ("insert into @ SkyContact values select" + UserID + ", R_ID from Sky_Role ;");
BuiSQL. Append (string. Format ("insert into Sky_Admin_Role values (select * from @ SkyContact where roleID in {0})", RoleCollection ));
Return Common. OleDbHelper. ExecuteNonQuery (CommandType. Text, BuiSQL. ToString (), null );
}
Here, I want to simulate a zero-time table defined in SQL Server, insert as much data as possible to it, and then query the desired data based on the zero-time table and put it into the desired data for execution! An error occurred while listing the exec results! The following error is thrown: Invalid SQL statement; expect 'delete', 'insert', 'Procedure ', 'select', or 'update '. In fact, errors may occur. After all, Access statements such as insert into table values () and () are not supported. In this case, you have to declare failure even if you try three times! After trying so much, I had to execute multiple insert statements using the method I had already prepared.
Try 4
The Demo: first obtain The desired data format: 1, 2, 3 here. Check SQL:
Public static int InsertRoleContact2 (int UserID, string RoleCollection) {string [] arr = RoleCollection. split (','); StringBuilder BuilSQL = new StringBuilder (""); foreach (string item in arr) {BuilSQL. append (string. format ("insert into Sky_Admin_Role (AdminID, RoleID) values ({0 },{ 1});", UserID, Convert. toInt32 (item);} return Common. oleDbHelper. executeNonQuery (CommandType. text, BuilSQL. toString (), null );}