none
OleDbDataAdapter does not insert Datatable in access table RRS feed

  • Question

  • I have  record in sql server table . I am filling all record in DataTable from sql server and after i want to insert all record in access table. But no data inserting and no error occured on inserting time. Please provide need full help.

       public bool SalesGenExistingCustomer_Insert_InAccessDB(DataTable DT)
            {
              
               bool flag = true;
              int ii = DT.Rows.Count;
                try
                {
                    string SQL = "SELECT top 1 * FROM GenExistingcustomers";
                    string INSERT = "INSERT INTO GenExistingcustomers([CustomerId],[CompanyName],[BillingAddress],[City],[State],[Zip],[Phone],[Email])" +
                                    " VALUES (@CustomerId,@CompanyName,@BillingAddress,@City,@State,@Zip,@Phone,@Email)";

                    OleDbConnection OleConn = new OleDbConnection(ConnString);
                    OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
                    OleAdp.InsertCommand = new OleDbCommand(INSERT);
                    OleAdp.InsertCommand.Parameters.Add("@CustomerId", OleDbType.LongVarChar, 1008, "CustomerId");
                    OleAdp.InsertCommand.Parameters.Add("@CompanyName", OleDbType.LongVarChar, 1008, "CompanyName");
                    OleAdp.InsertCommand.Parameters.Add("@BillingAddress", OleDbType.LongVarChar, 1008, "BillingAddress");
                    OleAdp.InsertCommand.Parameters.Add("@City", OleDbType.LongVarChar, 1008, "City");
                    OleAdp.InsertCommand.Parameters.Add("@State", OleDbType.LongVarChar, 1008, "States");
                    OleAdp.InsertCommand.Parameters.Add("@Zip", OleDbType.LongVarChar, 4000, "Zip");
                    OleAdp.InsertCommand.Parameters.Add("@Phone", OleDbType.LongVarChar, 1008, "Phone");
                    OleAdp.InsertCommand.Parameters.Add("@Email", OleDbType.LongVarChar, 1008, "Email");

                    OleAdp.InsertCommand.Connection = OleConn;
                    OleAdp.InsertCommand.Connection.Open();
                    int i = OleAdp.Update(DT);
                    OleAdp.InsertCommand.Connection.Close();

                }
                catch (Exception ex)
                {
                    LogFile(ex.ToString());
                    flag = false;
                }
                string ssss = DT.Rows[1][4].ToString();
                return flag;

            }
    if you add one row like as -
               DataRow dr;
               dr = DT.NewRow();
               dr[0] = "1";
               dr[1] = "AAAA";
               dr[2] = "3";
               dr[3] = "4";
               dr[4] = "5";
               dr[5] = "6";
               dr[6] = "7";
               dr[7] = "8";
               dr[8] = "9";
               DT.Rows.Add(dr);
    then it's  inserting one record but leaving another record.




    Monday, February 1, 2016 8:38 AM

Answers

  • Hi AnkitKumar2016,

    From your code snippet, I create a demo on my side, it seems that has some spelling errors. You refer to the code snippet as below and modify your code check if it works for you.

    static void Main(string[] args)
    
            {
    
                string sqlString = "SELECT top 1 * FROM GenExistingcustomers";
    
                DataTable dt = new DataTable();
    
                using (OleDbConnection connection = new OleDbConnection(connString))
    
                {
    
                    try
    
                    {
    
                        connection.Open();
    
                        OleDbDataAdapter command = new OleDbDataAdapter(sqlString, connection);
    
                        command.Fill(dt);
    
                    }
    
                    catch (Exception ex)
    
                    {
    
                        Console.WriteLine(ex.Message);
    
                    }
    
                }
    
                dt.Clear();
    
                DataRow dr;
    
                dr = dt.NewRow();
    
                dr[0] = "1";
    
                dr[1] = "AAAA";
    
                dr[2] = "3";
    
                dr[3] = "4";
    
                dr[4] = "5";
    
                dr[5] = "6";
    
                dr[6] = "7";
    
                dr[7] = "8";
    
                //dr[8] = "9"; //your table only 8 fields.
    
                dt.Rows.Add(dr);
    
                SalesGenExistingCustomer_Insert_InAccessDB(dt);
    
                Console.WriteLine("OK");
    
                Console.ReadKey();
    
            }
    
    
            public static bool SalesGenExistingCustomer_Insert_InAccessDB(DataTable DT)
    
            {
    
                
    
                bool flag = true;
    
                int ii = DT.Rows.Count;
    
                try
    
                {
    
                    string SQL = "SELECT top 1 * FROM GenExistingcustomers";
    
                    string INSERT = "INSERT INTO GenExistingcustomers([CustomerId],[CompanyName],[BillingAddress],[City],[State],[Zip],[Phone],[Email])" +
    
                                    " VALUES (@CustomerId,@CompanyName,@BillingAddress,@City,@State,@Zip,@Phone,@Email)";
    
                    OleDbConnection OleConn = new OleDbConnection(connString);
    
                    OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
    
                    OleAdp.InsertCommand = new OleDbCommand(INSERT);
    
                    OleAdp.InsertCommand.Parameters.Add("@CustomerId", OleDbType.LongVarChar, 1008, "CustomerId");
    
                    OleAdp.InsertCommand.Parameters.Add("@CompanyName", OleDbType.LongVarChar, 1008, "CompanyName");
    
                    OleAdp.InsertCommand.Parameters.Add("@BillingAddress", OleDbType.LongVarChar, 1008, "BillingAddress");
    
                    OleAdp.InsertCommand.Parameters.Add("@City", OleDbType.LongVarChar, 1008, "City");
    
                    OleAdp.InsertCommand.Parameters.Add("@State", OleDbType.LongVarChar, 1008, "State"); //a spelling error
    
                    OleAdp.InsertCommand.Parameters.Add("@Zip", OleDbType.LongVarChar, 4000, "Zip");
    
                    OleAdp.InsertCommand.Parameters.Add("@Phone", OleDbType.LongVarChar, 1008, "Phone");
    
                    OleAdp.InsertCommand.Parameters.Add("@Email", OleDbType.LongVarChar, 1008, "Email");
    
                    OleAdp.InsertCommand.Connection = OleConn;
                    OleAdp.InsertCommand.Connection.Open();
                    int i = OleAdp.Update(DT);
                    OleAdp.InsertCommand.Connection.Close();
                }
                catch (Exception ex)
                {
                    //LogFile(ex.ToString());
                    Console.WriteLine(ex.Message);
                    flag = false;
                }
                //string ssss = DT.Rows[1][4].ToString();
                return flag;
            }

    Best regards,

    Cole Wu


    Tuesday, February 2, 2016 2:12 AM
    Moderator

All replies

  • Hi AnkitKumar2016,

    From your code snippet, I create a demo on my side, it seems that has some spelling errors. You refer to the code snippet as below and modify your code check if it works for you.

    static void Main(string[] args)
    
            {
    
                string sqlString = "SELECT top 1 * FROM GenExistingcustomers";
    
                DataTable dt = new DataTable();
    
                using (OleDbConnection connection = new OleDbConnection(connString))
    
                {
    
                    try
    
                    {
    
                        connection.Open();
    
                        OleDbDataAdapter command = new OleDbDataAdapter(sqlString, connection);
    
                        command.Fill(dt);
    
                    }
    
                    catch (Exception ex)
    
                    {
    
                        Console.WriteLine(ex.Message);
    
                    }
    
                }
    
                dt.Clear();
    
                DataRow dr;
    
                dr = dt.NewRow();
    
                dr[0] = "1";
    
                dr[1] = "AAAA";
    
                dr[2] = "3";
    
                dr[3] = "4";
    
                dr[4] = "5";
    
                dr[5] = "6";
    
                dr[6] = "7";
    
                dr[7] = "8";
    
                //dr[8] = "9"; //your table only 8 fields.
    
                dt.Rows.Add(dr);
    
                SalesGenExistingCustomer_Insert_InAccessDB(dt);
    
                Console.WriteLine("OK");
    
                Console.ReadKey();
    
            }
    
    
            public static bool SalesGenExistingCustomer_Insert_InAccessDB(DataTable DT)
    
            {
    
                
    
                bool flag = true;
    
                int ii = DT.Rows.Count;
    
                try
    
                {
    
                    string SQL = "SELECT top 1 * FROM GenExistingcustomers";
    
                    string INSERT = "INSERT INTO GenExistingcustomers([CustomerId],[CompanyName],[BillingAddress],[City],[State],[Zip],[Phone],[Email])" +
    
                                    " VALUES (@CustomerId,@CompanyName,@BillingAddress,@City,@State,@Zip,@Phone,@Email)";
    
                    OleDbConnection OleConn = new OleDbConnection(connString);
    
                    OleDbDataAdapter OleAdp = new OleDbDataAdapter(SQL, OleConn);
    
                    OleAdp.InsertCommand = new OleDbCommand(INSERT);
    
                    OleAdp.InsertCommand.Parameters.Add("@CustomerId", OleDbType.LongVarChar, 1008, "CustomerId");
    
                    OleAdp.InsertCommand.Parameters.Add("@CompanyName", OleDbType.LongVarChar, 1008, "CompanyName");
    
                    OleAdp.InsertCommand.Parameters.Add("@BillingAddress", OleDbType.LongVarChar, 1008, "BillingAddress");
    
                    OleAdp.InsertCommand.Parameters.Add("@City", OleDbType.LongVarChar, 1008, "City");
    
                    OleAdp.InsertCommand.Parameters.Add("@State", OleDbType.LongVarChar, 1008, "State"); //a spelling error
    
                    OleAdp.InsertCommand.Parameters.Add("@Zip", OleDbType.LongVarChar, 4000, "Zip");
    
                    OleAdp.InsertCommand.Parameters.Add("@Phone", OleDbType.LongVarChar, 1008, "Phone");
    
                    OleAdp.InsertCommand.Parameters.Add("@Email", OleDbType.LongVarChar, 1008, "Email");
    
                    OleAdp.InsertCommand.Connection = OleConn;
                    OleAdp.InsertCommand.Connection.Open();
                    int i = OleAdp.Update(DT);
                    OleAdp.InsertCommand.Connection.Close();
                }
                catch (Exception ex)
                {
                    //LogFile(ex.ToString());
                    Console.WriteLine(ex.Message);
                    flag = false;
                }
                //string ssss = DT.Rows[1][4].ToString();
                return flag;
            }

    Best regards,

    Cole Wu


    Tuesday, February 2, 2016 2:12 AM
    Moderator
  • Are you sure it's actually inserting any records at all into the Access database? You said it would insert one record, but I believe that you need to use "?" for your parameters with Access. Your Insert command should look like this:

    string INSERT = "INSERT INTO GenExistingcustomers([CustomerId],[CompanyName],[BillingAddress],[City],[State],[Zip],[Phone],[Email])" +
                                    " VALUES (?,?,?,?,?,?,?,?)";

    Using "@CustomerId", etc. for the Parameters.Add(...) is fine (it's more for you to know you're adding the right parameters). But, the actual INSERT command just uses the "?" as a placeholder, because it uses positional parameters instead of named parameters. So, the Parameters.Add(...) needs to be done in the correct order (which you have done, so that part is OK).


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, February 2, 2016 2:13 AM
  • thanks of reply. I think error occurred by my existing data which i am loading from sql server table. my code is tested. I want to insert bulk record in access db. I check datatype is correct of DataTable and access Table. Can you check my code by filling data from sql server with same structure or different structure and  provide correct way. Same method i am using by odata services.I am filling record in list using odata services and then filling all data in DataTable from linq and then inserting succesfully. please help.   
    Tuesday, February 2, 2016 8:17 AM
  • I am not inserting a record on DataTable. I have 1000 records in sql server table whose data i am filling in DataTable.I do not need to insert row by DataRow.This is a testing process for finding error.Please suggest.
    Tuesday, February 2, 2016 1:27 PM
  • You did not say in your initial question that you needed to do a bulk insert. Unfortunately, Access does not support bulk inserts. I found this excellent thread on StackOverflow that might be useful to you:

    http://stackoverflow.com/questions/7070011/writing-large-number-of-records-bulk-insert-to-access-in-net-c


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, February 2, 2016 3:37 PM
  • I am inserting data in bulk its clear in  my program.
     OleAdp.Update(DT);
    Here DT is the DataTable and its working fine. But when we fetching data from sql server table into Datatable and then inserting this DataTable then no data is inserting in access table and no error occurred.

    Wednesday, February 3, 2016 6:24 AM
  • That is not really a bulk insert ... the DataAdapter still does the Insert one row at a time when you call it's .Update() method.

    Also, I think you do need to change your Insert to use the "?" for the parameters in your Insert string, as I have shown. You didn't say whether or not you tried that.

    And, one last thing, if the process of inserting 1000 rows is too slow, then you might also want to consider some of the options in the StackOverflow link that I provided.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Thursday, February 4, 2016 5:54 AM
  • I think you did not  understand clearly my task. Please read care fully my all question with reply. This is method of bulk inserting which 100% tested. Ok you can try. You add record more than one into DataTable and create table in access with same structure and try to insert. Okey. Problem is sql server table which  is not inserting and no any error occured. And Access support bulk insert.


    Friday, February 5, 2016 5:51 AM
  • You're right ... I did not, and probably still do not, understand.

    Problem is sql server table which  is not inserting and no any error occured. And Access support bulk insert.

    I thought you originally said that you were selecting data from SQL Server and trying to insert them into Access. So do you mean that the problem is that it (SQL select) is not inserting data into a DataTable? It probably is, but each row probably does not have a RowState of Inserted, so the Adapter.Update() doesn't know that there is anything to update to your Access table.

    If this is the case, you have two options: 1) Mark all the rows in your DataTable as Added and then do the .Update() or 2) Do the insert manually using a .ExecuteNonQuery()

    Option 1:

    foreach (DataRow row in DT.Rows)
        row.SetAdded();
    
    // now, this will work
    int i = OleAdp.Update(DT);
    

    Option 2:

    //This is if you don't set the rows as Added
    string INSERT = "INSERT INTO GenExistingcustomers([CustomerId],[CompanyName],[BillingAddress],[City],[State],[Zip],[Phone],[Email])" +
                                    " VALUES (?,?,?,?,?,?,?,?)";
    OleDbConnection OleConn = new OleDbConnection(ConnString);
    OleDbCommand cmd = new OleDbCommand(INSERT, OleConn);                               
    foreach (DataRow row in DT.Rows)
    {
        cmd.Parameters.Clear();
        cmd.Parameters.AddWithValue("@CustomerId", row["CustomerId"]);
        cmd.Parameters.AddWithValue("@CompanyName", row["CompanyName"]);
        cmd.Parameters.AddWithValue("@BillingAddress", row["BillingAddress"]);
        // ... continue to add the rest of the parameters
        cmd.ExecuteNonQuery();
    }


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, February 5, 2016 4:07 PM
  • ok Great!
    Monday, February 8, 2016 8:00 AM
  • OK ... does that mean you've got it working now?

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, February 8, 2016 2:33 PM
  • No, I have no final solution....if you have then please provide code without for/foreach loop because its take more extra time in execution.
    Friday, February 12, 2016 12:18 PM
  • There is no way to do it without a foreach loop, because Access does not support a "real" bulk insert, at least not through OleDbDataAdapters.

    With the 2 examples I gave, Option 2 is the better one, because then you're only looping once.
    In Option 1, there are two loops:
    1) You do looping in code to set the RowState to Add.
    2) The Adapter.Update(), while it is only one line of code, behind the scenes it's doing the same thing, looping through the rows and updating them one by one.

    However, I found something interesting here:
    https://social.msdn.microsoft.com/Forums/en-US/b32b343b-f7a8-412e-a73c-012e2ccc9621/c-select-csv-file-read-into-ms-access-database?forum=csharpgeneral

    The last reply, from @Paul, might be something you could try ... although I don't know if it will be any faster. Apparently, Access supports inserting directly into a table from a csv file. I am not an Access programmer, so this is only stuff I found by Googling.

    My suggestion then is to write a csv file from the data you retreived from SQL and then read that csv file directly into your Access table (as shown in @Paul's reply that I linked to above).

    Here's a link for creating a csv file from a DataTable: http://stackoverflow.com/questions/4959722/c-sharp-datatable-to-csv?lq=1


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, February 12, 2016 3:38 PM