OleDbCommandBuilder doesn't generate anything...

OleDbCommandBuilder doesn't generate anything...

• Gajanan Didore

  Hi, David,

  I ran across your posting reply in a search, and your response to the question partially answers what I was running into. So yes, I can see the auto-generated UpdateCommand now by calling GetUpdateCommand().CommandText.

  However, what happens if you need to pass in a custom update command I set an UpdateCommand to my DataAdapter object with my proprietary update SQL, and with parameters. However, after I set that, if I make a call to the CommandBuilder to GetUpdateCommand().CommandText, it is still the auto-generated update sql, not the one that I passed in!

  Also, say if I do want to use the auto-generated update sql, how can I get a meaningful where clause to generate Because the auto-generated sql right now only generates the "Update [table] set [field 1] = , [field 2] = , .....where ([field 1] = and [field 2] = ....)". Basically all fields in the select columns, they are all in the where clause. I only want say the primary key (Unique ID).

  I am making a call to FillSchema() on that DataAdapter object for the table, and this is what I set:

  m_dataAdapterPractice.FillSchema(m_dataSetPUPP.Tables["Practice"] ,SchemaType.Source);

  I also tried SchemaType.Mapping, the default one, and that didn't work either.

  Anyways, I really appreciate some pointers here, because I just can't get the update to work!

  Thanks,

  Ann

  
  
  

• tequilatamm

  Ann,

  The CommandBuilder is designed to generate updating logic for you.

  Supplying your own updating logic gives you more control and better performance than relying on a CommandBuilder, since the CommandBuilder has to query the database in order to generate the updating logic. If you supply your own updating logic, set the InsertCommand, UpdateCommand and DeleteCommand properties on your DataAdapter to the Commands you've created and don't use a CommandBuilder at all. The DataAdapter will then use the logic you've supplied.

  I hope this information proves helpful.

  
  
  

• Alon *

  
  Store the CommandBuilder somewhere:
  
  

  OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

  
  
  The CommandBuilder will generate the updating logic when you call DataAdapter.Update.  The logic will be maintained within the CommandBuilder.  It will not set the InsertCommand, UpdateCommand or DeleteCommand properties of the DataAdapter.  If you want to force the CommandBuilder to generate that logic and examine it, call GetInsertCommand, GetUpdateCommand or GetDeleteCommand.
  
  I hope this information proves helpful.
  
  David Sceppa
  ADO.NET Program Manager
  Microsoft
  
  
  

• Josh Pendergrass

  Ahhhhh ok ! Thanks for your help and your precisions 
  
  Ludovic B.
  

• robinjam

  Thank for your answer, but I can't get it to work (and yes, I read the other posts on the same subjet, but it didn't help me...).
  
  So I wrote this simple code :
  
  

  OleDbConnection conn = DBConnection.GetConnection();
  

  OleDbDataAdapter clientsAdapter = new OleDbDataAdapter("SELECT * FROM Customers", conn);
  
  

  new OleDbCommandBuilder(clientsAdapter);
  
  without success. I am doing something wrong, but what I tried this code on a small table with simple column name (id - name), I tried to rename my columns(cust_id, cust_name).
  
  GetConnection() return a new valid connection on my access database (I can manually INSERT / UPDATE rows).
  
  I tried conn.Open() before creating the adapter, before the command builder, but same result

  
  

• Paulustrious

  You need to create the CommandBuilder before you call adapter.Fill.
  
  Make sure your Customers table has a primary key.
  
  If the column names contain special characters (spaces, periods (.), etc), you need to set QuotePrefix and QuoteSuffix to '[' and ']' respectively.
  
  
• OleDbCommandBuilder doesn't generate anything...