Insert multiple data entries in the Access Database

Source: Internet
Author: User
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 );}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.