|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Dynamic SQL generation is not supported against a SelectCommand that does not return any base tableI 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 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. -- Show quote____________________________________ 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. __________________________________ "Gancy" <ganesh_tiw***@hotmail.com> wrote in message news:1141475222.461553.267530@i40g2000cwc.googlegroups.com... > 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 > 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 Show quote "Gancy" <ganesh_tiw***@hotmail.com> schreef in bericht news:1141475222.461553.267530@i40g2000cwc.googlegroups.com... > 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 >  |
|||||||||||||||||||||||
Other interesting topics