OdbcCommandBuilder doesn't generates a valid InsertCommand

[Elnath]

im connecting to a DataBase tru ODBC, i need to insert new records (rows) therefore i want the InsertCommand to be generated by my CommbadBuilder. This solution however is aint working, the SelectCommand generate simply ignore all but one of the fields in the source table as a result the InsertCommand generated will only insert the selected field.

Used both "Select *" and the extended select listing all of the 94 fields of the table, both cases return the same SelectCommand that is selecting only the keyfield of the table. I think that if i have my CommandBuilder interprets corretly the query passed it will be able to generate a proper InsertCommand query.

and here is some code:

OdbcDataAdapter adp = new OdbcDataAdapter("select * from tabTest", ms_cnn);
OdbcCommandBuilder cb = new OdbcCommandBuilder(adp);
adp.Fill(local_ds, "tabTest");
 

ms_cnn hold a valid and open connection so that my connection will not be closed upon the update, as i need to insert many rows. If i browse the DataTable it is correctly filled with values but not after the update (looking from the DB) either im not getting any exception this mean the fields are correctly filled but due to the InsertCommand only the one field selected by the SelectCommand will be inserted into my table. As u see the select statement ask for all the fields on the table and as said listing all of the fields has no result.

Is there any know issue related to the use of CommandBuilder in this case? Ive used for works past and it was correctly selecting the whole records as asked due to the query used.


thanks all,

[Plater]

The description on MSDN does not seem like it it ment to do that?

The OdbcDataAdapter does not automatically generate the SQL statements required to reconcile changes made to a DataSet associated with the data source. However, you can create an OdbcCommandBuilder object that generates SQL statements for single-table updates by setting the SelectCommand property of the OdbcDataAdapter. The OdbcCommandBuilder then generates any additional SQL statements that you do not set.

The relationship between an OdbcDataAdapter and its corresponding OdbcCommandBuilder is always one-to-one. To create this correspondence, you set the OdbcDataAdapter property of the OdbcCommandBuilder object. This causes the OdbcCommandBuilder to register itself as a listener, which produces the output of RowUpdating events that affect the DataSet.

To generate INSERT, UPDATE, or DELETE statements, the OdbcCommandBuilder uses the SelectCommand property to retrieve a required set of metadata. If you change the value of SelectCommand after the metadata has been retrieved, such as after the first update, you should then call the RefreshSchema method to update the metadata.

Note
If the SELECT statement assigned to the SelectCommand property uses aliased column names, the resulting INSERT, UPDATE, and DELETE statements may be inaccurate or fail. If the underlying ODBC driver cannot provide the appropriate base column name for the alias column name (using the SQL_DESC_BASE_COLUMN_NAME value of SQLColAttribute), the alias name could be used in the generated INSERT, UPDATE, and DELETE statements. For example, the Microsoft ODBC Driver for Oracle returns the alias name as the base column name. Therefore, the generated INSERT, UPDATE, and DELETE statements would cause errors.

[Elnath]

The description on MSDN does not seem like it it ment to do that?

from my local MSDN and from how i used it before:

Automatically generates single-table commands that are used to reconcile changes made to a DataSet with the associated data source.

it should generate the InsertCommand query, but if this is not enought why after i pass a selection query listing all the fields (both with * or every single field) it show me a select with only the key field?

this mean he actually read correctly the DataTable of my DB (with * ive not specified a field name) and will lookup that field, so why it will not consider other fields aswell?

due to my line 2 im using a CommandBuilder to generate the SQL statement, im not expecting the adapter to do that for me:

OdbcDataAdapter adp = new OdbcDataAdapter("select * from tabTest", ms_cnn);
OdbcCommandBuilder cb = new OdbcCommandBuilder(adp);
adp.Fill(local_ds, "tabTest");