Updating Access DB with changes from a DataSet results in error.

[Bryan Masephol]

Hi All

I have a OleDbConnection as the "connection" below. I'm retriving a dataset from an access 2002 db and displaying it in a DataGrid. I'm making the connection to my access db file with this connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename

and everything is working fine with that. My table in the database is simple. 10 columns of text or numbers. No joining or anything else. It has a autonumber as the primary key. Simple enough.

// Update Code
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this.connection);

OleDbCommandBuilder ocb = new OleDbCommandBuilder(adapter);

adapter.Update(ds, QueryConstants.RACETABLENAME);
// End update Code

When I try to call the update above to save the changes I have made via the DataGrid, I get this error.

Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

From what I can find about this error it may have something to do with the way I'm connection to the data source or the way the datasource is setup (primary keys....) but as much as I know and have tried I cannot resolve this problem.

I also have this application working from a sql server connection with the same table and data setup as the access file. And when i use the SqlConnection instead of the OleDbConnection everything works fine.

I know this may seem jumbled but if anyone had this problem and got it solved please let me know. I also read something about if the db driver you are using dosen't support returning schema information about the table then the command builder can't do its thing. I just don't see why microsofts own product wouldn't support that.

I have updated to the lastest Jet Driver (SP7) and still no fix. Please help
Thanks
Bryan

[Ron Allen]

Bryan,
Check to make sure that none of your field names are reserved words.
You can work around this by setting QuotePrefix and QuoteSuffix on the
CommandBuilder (usually to '[' and ']' for access).
If this doesn't work you check the rest of your logic. I guess that you
have left some things out as I don't see a Fill statement for your table or
other means of associating the DataAdapter with a Table.

You may get more responses for this type of question in
microsoft.public.dotnet.framework.adonet as well.
Ron Allen
"Bryan Masephol" <ma******@uwec.edu> wrote in message
news:eb**************@TK2MSFTNGP12.phx.gbl...
Hi All

I have a OleDbConnection as the "connection" below. I'm retriving a dataset
from an access 2002 db and displaying it in a DataGrid. I'm making the
connection to my access db file with this connection string

"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename

and everything is working fine with that. My table in the database is
simple. 10 columns of text or numbers. No joining or anything else. It
has a autonumber as the primary key. Simple enough.

// Update Code
OleDbDataAdapter adapter = new OleDbDataAdapter(sql, this.connection);

OleDbCommandBuilder ocb = new OleDbCommandBuilder(adapter);

adapter.Update(ds, QueryConstants.RACETABLENAME);
// End update Code

When I try to call the update above to save the changes I have made via the
DataGrid, I get this error.

Dynamic SQL generation is not supported against a SelectCommand that does
not return any base table information.

From what I can find about this error it may have something to do with the
way I'm connection to the data source or the way the datasource is setup
(primary keys....) but as much as I know and have tried I cannot resolve
this problem.

I also have this application working from a sql server connection with the
same table and data setup as the access file. And when i use the
SqlConnection instead of the OleDbConnection everything works fine.

I know this may seem jumbled but if anyone had this problem and got it
solved please let me know. I also read something about if the db driver you
are using dosen't support returning schema information about the table then
the command builder can't do its thing. I just don't see why microsofts own
product wouldn't support that.

I have updated to the lastest Jet Driver (SP7) and still no fix. Please
help
Thanks
Bryan