The DataAdapter.SelectCommand property needs to be initialized

I recently had to work with ADO.NET once again after working with Entity Framework exclusively for the last several years.

Therefore, unsurprisingly, I had to go back and recall all of the ADO.NET code samples/examples that I learned many, many years ago.

Well, I was attempting to copy some data from a DataTable to a Microsoft Access database table, when I suddenly encountered the following error message:

"The DataAdapter.SelectCommand property needs to be initialized"

Unfortunately, this error message is not very useful in troubleshooting the root cause of the problem.  In fact, I discovered that it was not even close to the actual problem in the code base!

As it turns out, I was using an OleDbCommandBuilder in my code, and this object needed to be passed the DataAdapter class instance as part of the constructor.  Once I did that, my code worked perfectly!

This was my resultant code:

 

public void CopyToAccessDB(DataTable table)

  {

      using (OleDbConnection conn = new OleDbConnection(_connectionString))

      {

          //Open the database connection

          conn.Open();

          string strSQL = string.Format("SELECT * FROM {0};", table.TableName);

          OleDbDataAdapter dataAdapter = new OleDbDataAdapter(strSQL, conn);

          OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(dataAdapter);

          dataAdapter.InsertCommand = cmdBuilder.GetInsertCommand();

          //Just to make sure, set the RowState to added to make sure an Insert is performed

          foreach (DataRow row in table.Rows)

          {

              row.SetAdded();

          }//foreach

          dataAdapter.Update(table);

      }//using

  }