Home > Enterprise >  Inserting Bulk data into Access Database using DAO using C#
Inserting Bulk data into Access Database using DAO using C#

Time:07-18

I am trying to insert almost 100000 rows of data into access. I was using ADO.net to insert the data, but it is taking too much time to do the insert so I decided to use DAO. I followed an example to insert the data using DAO. Below is my code:

 public void testDAO()
        {
            try
            {
                List<DB.Rec> recList = new List<DB.Rec>();
                recList = getData();
                DBEngine dbEngine = new DBEngine();

                Database db = dbEngine.OpenDatabase(@"C:\Recs2001.mdb");


                Recordset rs= db.OpenRecordset("RecsCD");
                Field[] recFields = new Field[7];

               
               for(int i=0; i<=recList.Count;i  )
                {
                    rs.AddNew();
                    for (int k = 0; k <= 6; k  )
                    {
                        recFields[k].Value = recList[k].Test1;
                        recFields[k].Value = recList[k].Test2;
                        recFields[k].Value = recList[k].Test3;
                        recFields[k].Value = recList[k].Test4;
                        recFields[k].Value = recList[k].Test5;
                        recFields[k].Value = recList[k].Test6;
                        recFields[k].Value = recList[k].Test7;

                    }
                    rs.Update();
                }
                
                rs.Close();
                db.Close();
            }
            catch (Exception ex)
            {

            }
         }

when the debugger goes to this line:

recFields[k].Value = recList[k].Test1;

I get an error saying:

System.NullReferenceException: 'Object reference not set to an instance of an object.'

In immediate window, I can see the value of recList[k].Test1 so I am not sure why it is complaining about 'Object reference not set to an instance of an object.'.

I must be missing some declartion, but I cannot see what am I doing wrong.

any help will be highly appreciated.

CodePudding user response:

I expect the error is due to not referencing the recordset object.

Not sure the recFields array is needed. If the recordset pulls fields in the order you want to enter data, just reference the fields by index.
rs(k) = recList[k].Test1; or rs.Fields(k) = recList[k].Test1;

Or if recFields array actually has field names and you want to reference fields explicitly:
rs(recFields[k]) = recList[k].Test1;

  • Related