General information
Title:
Problem updating an Excel ODBC data source
Environment versions
Network:
Windows 2003 Server
VS2005 - C# - ADO.NET
I am unable to update an Excel worksheet that is contained in an Excel ODBC data source. The table is named "footanal".
I use the factory returned by DbProviderFactories.GetFactory(System.Data.Odbc) to create OdbcDataAdapter, OdbcConnection, and OdbcCommand object.
DbAdapter.SelectCommandText = "SELECT * FROM FootAnal";
DbAdapter.Fill(this.dataSetPatientsSessions.footanal);
I then create a new footanalrow object and assign values to its members.
Then, this.dataSetPatientsSessions.footanal.AddRow(mynewrow)
I use an OdbcCommandBuilder object to create an INSERT command and assign that to DbAdapter.InsertCommand;
Then, call DbAdapter.Update(this.dataSetPatientsSessions)
This is all within a try-catch block and there are no exceptions thrown.
When I open the Excel spreadsheet I observe
1. A new record has been added at the end of the sheet.
2. Most of the column headers (in row 1) have been replaced with #VALUE!
3. The values in most (but not all) of the columns have been replaced with #VALUE!
To try to debug this: before the call to Update, I asked the OdbcCommandBuilder object to create an UPDATE command. This did throw an exception which said:
"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information"
I would appreciate help getting around this problem.
Thanks in advance for your help.
Jim
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only