Excel OleDb

[Techno]

in C# (.NET 2.0) if we use a dataAdapter to fill an excel sheet into a dataset, how can we then update it, once changes have been made in the dataset?

I cant use the CommandBuilder().GetUpdateCommand() as it complains it needs a primary key to do the update, which is true, but there is a primary key in the excel file, so how can I tell it to use that as the primary key?

[mendhak]

You could use ExecuteNonQuery()

[jmcilhinney]

You don't have to ever use a CommandBuilder and I recommend that you don't. Just write the non-query SQL statements for the DataAdapter yourself. If you wrote the SELECT statement you can just as easily write the others.

[Techno]

well not quite.
I guess OleDbCommandBuilder is handy for "newbies" - I was just trying to demonstrate something.
Thing is, how can I then create the Update command? I obviously need to give it values for any rows which have been modified in the DataSet - how would I achieve this correctly?

[jmcilhinney]

You add a parameter for each column you're updating. That's the only way to update using a DataAdapter and its exactly what a CommandBuilder does behind the scenes. Each parameter is mapped to a column in the DataTable using its SourceColumn property.

[Techno]

I agree. Yes shouldve remembered that, this is what happens with 2 hours sleep!
So if you can bare with me....how would I go about doing this again? What do I do?

I create the oledbparameters (one parameter per column) and set the sourcecolumn
add the parameters into the UpdateCommand parameter collection
then what? What would the CommandText be?

[jmcilhinney]

E.g.

myDataAdapter.UpdateCommand = New OleDbCommand("UPDATE MyTable SET Column1 = ?, Column2 = ? WHERE ID = ?", myConnection)
myDataAdapter.UpdateCommand.Parameters.Add("Column1", OleDbType.VarChar, 50, "Column1")
myDataAdapter.UpdateCommand.Parameters.Add("Column2", OleDbType.Integer, 0, "Column2")
myDataAdapter.UpdateCommand.Parameters.Add("ID", OleDbType.Integer, 50, "ID")
 
myDataAdapter.UpdateCommand(myDataTable)

Note that the first argument of the Add method is the name of the parameter. It is convention to use column name but it could be anything. The last parameter is the column name and must match the name of the DataColumn in the DataTable from which the data will be retrieved for that parameter. If you use the '?' place-holders your parameters must be added in the same order as they are used in the SQL code. Alternatively you can use named parameters, in which case the order doesn't matter:

myDataAdapter.UpdateCommand = New OleDbCommand("UPDATE MyTable SET Column1 = @Column1, Column2 = @Column2 WHERE ID = @ID", myConnection)
myDataAdapter.UpdateCommand.Parameters.Add("@Column1", OleDbType.VarChar, 50, "Column1")
myDataAdapter.UpdateCommand.Parameters.Add("@Column2", OleDbType.Integer, 0, "Column2")
myDataAdapter.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 50, "ID")
 
myDataAdapter.UpdateCommand(myDataTable)

Note now that the first parameter to each call to the Add method matches the name of the parameter used in the SQL code. The Jet OLEDB provider accepts either format, but that is not the case for all providers.

[Techno]

yup - sorry, after i posted this I went out and just walking to the bus stop i remembered how to do this, once again many thanks and i will post back if i have any more queries

[mendhak]

Did you come running back from the bus stop?

[Techno]

no no lol

[jmcilhinney]

Own up. You weren't walking to the bus stop. You were in "the smallest room in the house" weren't you? That's where all great thinking is done.

[Techno]

LOL
Thanks - everything seems to work great ;-)