Saturday, July 18, 2015

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
  }



No comments:

Post a Comment