CODEWITHSUNDEEP
c#ms-access
Mangrio
Mangrio

Reputation: 1020

Inserting multiple rows in MS Access at once in c#

I am trying to insert multiple records in MS Access at once, but it says

Characters found after end of SQL statement.

How do I insert all records at once, here is my code.

Sample records which are in DataTable;

DataTable dt = new DataTable();
            dt.Clear();
            dt.Columns.Add("STORE_NAM1");
            dt.Columns.Add("STORE_ADD1");
            dt.Columns.Add("STORE_ADD2");
            dt.Columns.Add("PHONE");
            dt.Columns.Add("FAX");
            dt.Columns.Add("ABN_ACN_NO");
            dt.Columns.Add("EMAIL");
            dt.Columns.Add("WEB");
            object[] o = { "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer" };
            dt.Rows.Add(o);
            object[] o2 = { "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer" };
            dt.Rows.Add(o2);
            object[] o3 = { "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer", "Qadeer" };
            dt.Rows.Add(o3);

Here is insertion..

    using (OleDbConnection con = new OleDbConnection(connectionAccess))
    {


        List<string> Rows = new List<string>();
        string sCommand = "";
        foreach (DataRow row in dt.Rows)
        {
            sCommand += "insert into store (STORE_NAM1, STORE_ADD1, STORE_ADD2, PHONE, FAX, ABN_ACN_NO, EMAIL, WEB) values ('"
                + row["STORE_NAM1"].ToString() + "', '" 
                + row["STORE_ADD1"].ToString() + "', '" 
                + row["STORE_ADD2"].ToString() + "', '" 
                + row["PHONE"].ToString() + "', '" 
                + row["FAX"].ToString() + "', '" 
                + row["ABN_ACN_NO"].ToString() + "', '" 
                + row["EMAIL"].ToString() + "', '" 
                + row["WEB"].ToString() + "');";

//if I hard code values, this works fine with one insert
//sCommand = "insert into store(STORE_NAM1, STORE_ADD1, STORE_ADD2, PHONE, FAX, ABN_ACN_NO, EMAIL, WEB) values ('Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer');";

//this does not work with multiple records
//sCommand = "insert into store(STORE_NAM1, STORE_ADD1, STORE_ADD2, PHONE, FAX, ABN_ACN_NO, EMAIL, WEB) values ('Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer'); insert into store(STORE_NAM1, STORE_ADD1, STORE_ADD2, PHONE, FAX, ABN_ACN_NO, EMAIL, WEB) values ('Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer'); insert into store(STORE_NAM1, STORE_ADD1, STORE_ADD2, PHONE, FAX, ABN_ACN_NO, EMAIL, WEB) values ('Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer', 'Qadeer');";


      try
        {
            using (OleDbCommand myCmd = new OleDbCommand(sCommand, con))
            {
                con.Open();
                myCmd.CommandType = CommandType.Text;
                int result = myCmd.ExecuteNonQuery();
                Console.WriteLine(result +" records inserted");
            }
        }
        catch (Exception ex)
        {

            Console.WriteLine(ex.Message);

        }

    }

I have also tried it with parameterized way.

Upvotes: 2

Views: 1258

Answers (1)

Mary
Mary

Reputation: 15091

Change

sCommand += "insert...

To

sCommand = "insert...

Your first time through the loop the Insert string is fine. After that, it just gets longer and longer. Access wants just one statement per query.

Upvotes: 1

Related Questions