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

[Gancy]

Hi,
I have data access tier designed in such a way, just by changnging
application settings, same lines of code is made to work with both MS
SQL Server or MS Access. Code works fine with MS SQL Server. however
while executing following code lines on MS Acess

ds = new DataSet();
oAdapter.SelectCommand.CommandText = this.SelectCommand;
oAdapter.SelectCommand.Connection = oConn;

if (idbType == (int) ApplicationConstants.DBType.SQLServer)
{
oAdapter.DeleteCommand = sqlCommandBuilder.GetDeleteCommand();
oAdapter.InsertCommand =
sqlCommandBuilder.GetInsertCommand();
oAdapter.UpdateCommand =
sqlCommandBuilder.GetUpdateCommand();
}
else if (idbType == (int) ApplicationConstants.DBType.Access)
{
oAdapter.DeleteCommand =
oleCommandBuilder.GetDeleteCommand();
oAdapter.InsertCommand =
oleCommandBuilder.GetInsertCommand();
oAdapter.UpdateCommand =
oleCommandBuilder.GetUpdateCommand();
}

return oAdapter.Fill(ds);


It throws out the following error

System.InvalidOperationException was unhandled
Message="Dynamic SQL generation is not supported against a
SelectCommand that does not return any base table information."

But i have defined primary keys on all tables in Access databasel, just
like what i have in MS Sql database. Just to avoid this error i can
not use explict Insert, Delete or Update command for Adapter as the
same lines of code (above) has to work with 15 diff tables.

Any suggestions to this?

Thanks & Regards
Ganesh

 

[William \(Bill\) Vaughn]

Setting the SelectCommand is not enough. ADO.NET has to execute a query on
the server to return a set of resultsets that are used to generate the CB
UpdateCommand etc. These are not generated immediately--only when
referenced. You do not have to use the GetInsertCommand or any of those
methods to use the generated code. When you execute Update (for the first
time) the generated code is built and used INSTEAD OF the DataAdapter
UpdateCommand etc. See my article on the CommandBuilder and why it should be
avoided.
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cor Ligthert [MVP]]

Gancy,

Have a look at this sample on our website.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor