OleDbDataAdapter.UpdateCommand without CommandBuilder

Hello,
I just tried to create a UpdateCommand for a table that has no primary key by myself.
This is not as easy as it sounds, because I only know the name of one column that is used to identify the rows that have to be updated. All other columns (the columns that might have been changed) can be very different in name and number from case to case.
Here my unfinished code:

dataAdapter.UpdateCommand = new OleDbCommand(
"UPDATE tMyTable SET ", connection);
foreach (DataColumn column in m_tMyTable.Columns)
{
// dont update Nr-column because its used to identify the rows
if( column.ColumnName == "Nr" )
continue;
dataAdapter.UpdateCommand.CommandText += column.ColumnName + "= ";
dataAdapter.UpdateCommand.Parameter.Add(
column.ColumnName, // ParameterName
, // ..type no idea how to convert from System.Type in column
column.MaxLength, // size
column.ColumnName); // source column
if (column != m_tListenListe.Columns[m_tListenListe.Columns.Count - 1])
dataAdapter.InsertCommand.CommandText += ", ";
}
dataAdapter.UpdateCommand.CommandText += ") WHERE Nr= ";

Im not sure if the way Im going is correct. The mainproblem right now is that I have no idea how to convert from System.Type in DataColumn to OleDbType or System.Data.DBType in Parameter.
Can anyone help me or suggest another solution

.NET Development17

you are making things complicated :-)

you should create say the UpdateCommand manually. I guess you are trying to make an on the fly statement, I guess you could do this but should you really need to do this Or are you trying to some how make it generic so pretty much any set of data can be updated without you knowing what it is

you need to find out the type of the column from the datatable then do various if statement to pick your OleDbType. One way you could try is to just give it the parameter name and the field value (2nd overload of the OleDbParameter)

ahmedilyas: "Or are you trying to some how make it generic so pretty much any set of data can be updated without you knowing what it is "

Yes, thats exactly what Im trying! ;-)

"find out the type of the column from the datatable then do various if statement to pick your OleDbType"

I hoped that this would not be necessary.
How does the CommandBuilder handle this internally

because the CommandBuilder is smart ;-) It actually uses the SelectCommand to generate the Command appropriately.

Only thing I can suggest is from your example, perhaps do this but it may not work all the time.

dataAdapter.UpdateCommand = new OleDbCommand("UPDATE tMyTable SET ", connection);

foreach (DataColumn column in m_tMyTable.Columns)
{
if( column.ColumnName == "Nr" )
continue;
dataAdapter.UpdateCommand.CommandText += column.ColumnName + "= ";

if (column != m_tListenListe.Columns[m_tListenListe.Columns.Count - 1])
dataAdapter.InsertCommand.CommandText += ", ";
}
}

dataAdapter.UpdateCommand.CommandText += ") WHERE Nr= ";

this will add the value of the row you specify of the current column to the parameter collection, so hopefully this may be ok to use. What happens

This wont work, because there is no current row. I have to prepare the updatecommand and then call OleDbDataAdapter.UpdateCommand. Nevertheless I tried it, because I hoped that setting the parameter to a value woulb maybe also set the DBType, but it didnt work.

It really seems that I will have to set the DBType by hand for every System.Type. This su***.

Perhaps I should have a try not using the oledbadapter, but executing all commands for database changes that were made by hand. This sounds like a lot of work, but at least it will hopefully work.

Argh

It works using the datatype OleDbType.Variant:

dataAdapter.UpdateCommand.Parameter.Add(
// ParameterName
OleDbType.Variant, // works with every type
column.MaxLength, // size
column.ColumnName); // source column