|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
updating database views via tableadapter?Hi there,
I am developing a database application in c#.net 2005 and I've came across a bit of a problem. I am using a dataset to hold the application data, but one of my tables has been built from a database view. All the other tables update fine using the update method of a tableadapter - however there seems to be a problem updating a view via tableadapters. Does anyone know of a workaround for this? Thanks Darren Sim What is the problem?
-- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Darren Sim" <darr***@sirius.co.uk> wrote in message news:u2F3na1THHA.5068@TK2MSFTNGP03.phx.gbl... > Hi there, > > I am developing a database application in c#.net 2005 and I've came across > a bit of a problem. I am using a dataset to hold the application data, > but one of my tables has been built from a database view. All the other > tables update fine using the update method of a tableadapter - however > there seems to be a problem updating a view via tableadapters. Does > anyone know of a workaround for this? > > Thanks > Darren Sim > Sorry, I suppose more detail would help.
The problem I am experiencing is that when I run the update message I get an error stating that "Dynamic SQL generation is not supported against multiple base tables" The code I am using for this is below, it works fine against a table taken directly from the source database, but encounters the above problem when trying to update a view. public void saveReview(ref DataSet review) { string select = "SELECT s.done_id, a.areaname, a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, q.need,s.score,s.evidence, d.catcutoff"; select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion as q, tblsesscore as s, tbldonecut as d"; select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = cat.cat_id and s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = cat.cat_id"; OleDbDataAdapter da = new OleDbDataAdapter(select, conn.ConnectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.Update(review, "reviewquestions"); } Show quote "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... > What is the problem? > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > Forget OleDbCommandBuilder and rather create insert/delete/update statements
manually. BTW, your problem is not with views but the fact you are joining tables in select and OleDbCommandBuilder doesn't know how to crate I/D/U statements. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Darren Sim" <darr***@sirius.co.uk> wrote in message news:uQ4RlW2THHA.3500@TK2MSFTNGP05.phx.gbl... > Sorry, I suppose more detail would help. > > The problem I am experiencing is that when I run the update message I get > an error stating that > > "Dynamic SQL generation is not supported against multiple base tables" > > The code I am using for this is below, it works fine against a table > taken directly from the source database, but encounters the above problem > when trying to update a view. > > > > public void saveReview(ref DataSet review) > > { > > string select = "SELECT s.done_id, a.areaname, > a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, > q.need,s.score,s.evidence, d.catcutoff"; > > select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion as > q, tblsesscore as s, tbldonecut as d"; > > select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = cat.cat_id > and s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = > cat.cat_id"; > > > > > OleDbDataAdapter da = new OleDbDataAdapter(select, conn.ConnectionString); > > OleDbCommandBuilder cb = new OleDbCommandBuilder(da); > > da.Update(review, "reviewquestions"); > > > > } > > > > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >> What is the problem? >> >> -- >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> RightHand .NET consulting & development www.rthand.com >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> > > Or create a view in the database and use that created view as a table.
-- Show quoteRegards, Garik Melkonyan MCP, MCAD, MCSD .NET "Darren Sim" wrote: > Sorry, I suppose more detail would help. > > The problem I am experiencing is that when I run the update message I get an > error stating that > > "Dynamic SQL generation is not supported against multiple base tables" > > The code I am using for this is below, it works fine against a table taken > directly from the source database, but encounters the above problem when > trying to update a view. > > > > public void saveReview(ref DataSet review) > > { > > string select = "SELECT s.done_id, a.areaname, > a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, > q.need,s.score,s.evidence, d.catcutoff"; > > select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion as q, > tblsesscore as s, tbldonecut as d"; > > select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = cat.cat_id and > s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = > cat.cat_id"; > > > > > OleDbDataAdapter da = new OleDbDataAdapter(select, conn.ConnectionString); > > OleDbCommandBuilder cb = new OleDbCommandBuilder(da); > > da.Update(review, "reviewquestions"); > > > > } > > > > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... > > What is the problem? > > > > -- > > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > > RightHand .NET consulting & development www.rthand.com > > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > > > > There is a view on the database created using the sql in the select
statement on the code snippet. I was having problems using the table that so I defined it in the select statement. da.Update(review, "reviewquestions"); reviewquestions is the view on the database. if I use the code public void saveReview(ref DataSet review) { string select = "SELECT * from reviewquestions"; OleDbDataAdapter da = new OleDbDataAdapter(select, conn.ConnectionString); OleDbCommandBuilder cb = new OleDbCommandBuilder(da); da.Update(review, "reviewquestions"); } I get the following error. "Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information." Show quote "Garik" <garik_ma@do not spam.yahoo.com> wrote in message news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... > Or create a view in the database and use that created view as a table. > > -- > Regards, > Garik Melkonyan > MCP, MCAD, MCSD .NET > > > "Darren Sim" wrote: > >> Sorry, I suppose more detail would help. >> >> The problem I am experiencing is that when I run the update message I get >> an >> error stating that >> >> "Dynamic SQL generation is not supported against multiple base tables" >> >> The code I am using for this is below, it works fine against a table >> taken >> directly from the source database, but encounters the above problem when >> trying to update a view. >> >> >> >> public void saveReview(ref DataSet review) >> >> { >> >> string select = "SELECT s.done_id, a.areaname, >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >> q.need,s.score,s.evidence, d.catcutoff"; >> >> select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion as >> q, >> tblsesscore as s, tbldonecut as d"; >> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = cat.cat_id >> and >> s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = >> cat.cat_id"; >> >> >> >> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >> conn.ConnectionString); >> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >> >> da.Update(review, "reviewquestions"); >> >> >> >> } >> >> >> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >> > What is the problem? >> > >> > -- >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> > RightHand .NET consulting & development www.rthand.com >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> > >> >> >> Is the view defined as read-only inn the database?
Robin S. ------------------------------ Show quote "Darren Sim" <darr***@sirius.co.uk> wrote in message news:%23k%23qzd3THHA.5108@TK2MSFTNGP06.phx.gbl... > There is a view on the database created using the sql in the select > statement on the code snippet. I was having problems using the table > that so I defined it in the select statement. > > da.Update(review, "reviewquestions"); > > reviewquestions is the view on the database. > > > if I use the code > public void saveReview(ref DataSet review) > > { > > string select = "SELECT * from reviewquestions"; > > > > > OleDbDataAdapter da = new OleDbDataAdapter(select, > conn.ConnectionString); > > OleDbCommandBuilder cb = new OleDbCommandBuilder(da); > > da.Update(review, "reviewquestions"); > > > > } > > > I get the following error. > "Dynamic SQL generation is not supported against a SelectCommand that > does not return any base table information." > > > "Garik" <garik_ma@do not spam.yahoo.com> wrote in message > news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >> Or create a view in the database and use that created view as a table. >> >> -- >> Regards, >> Garik Melkonyan >> MCP, MCAD, MCSD .NET >> >> >> "Darren Sim" wrote: >> >>> Sorry, I suppose more detail would help. >>> >>> The problem I am experiencing is that when I run the update message I >>> get an >>> error stating that >>> >>> "Dynamic SQL generation is not supported against multiple base tables" >>> >>> The code I am using for this is below, it works fine against a table >>> taken >>> directly from the source database, but encounters the above problem >>> when >>> trying to update a view. >>> >>> >>> >>> public void saveReview(ref DataSet review) >>> >>> { >>> >>> string select = "SELECT s.done_id, a.areaname, >>> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>> q.need,s.score,s.evidence, d.catcutoff"; >>> >>> select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion >>> as q, >>> tblsesscore as s, tbldonecut as d"; >>> >>> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>> cat.cat_id and >>> s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = >>> cat.cat_id"; >>> >>> >>> >>> >>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>> conn.ConnectionString); >>> >>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>> >>> da.Update(review, "reviewquestions"); >>> >>> >>> >>> } >>> >>> >>> >>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>> > What is the problem? >>> > >>> > -- >>> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>> > RightHand .NET consulting & development www.rthand.com >>> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>> > >>> >>> >>> > > Probably, your view is not updatable.
So, you must write Update/Delete/Insert commands manually, as mentioned Miha. Garik Show quote "Darren Sim" wrote: > There is a view on the database created using the sql in the select > statement on the code snippet. I was having problems using the table that > so I defined it in the select statement. > > da.Update(review, "reviewquestions"); > > reviewquestions is the view on the database. > > > if I use the code > public void saveReview(ref DataSet review) > > { > > string select = "SELECT * from reviewquestions"; > > > > > OleDbDataAdapter da = new OleDbDataAdapter(select, conn.ConnectionString); > > OleDbCommandBuilder cb = new OleDbCommandBuilder(da); > > da.Update(review, "reviewquestions"); > > > > } > > > I get the following error. > "Dynamic SQL generation is not supported against a SelectCommand that does > not return any base table information." > > > "Garik" <garik_ma@do not spam.yahoo.com> wrote in message > news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... > > Or create a view in the database and use that created view as a table. > > > > -- > > Regards, > > Garik Melkonyan > > MCP, MCAD, MCSD .NET > > > > > > "Darren Sim" wrote: > > > >> Sorry, I suppose more detail would help. > >> > >> The problem I am experiencing is that when I run the update message I get > >> an > >> error stating that > >> > >> "Dynamic SQL generation is not supported against multiple base tables" > >> > >> The code I am using for this is below, it works fine against a table > >> taken > >> directly from the source database, but encounters the above problem when > >> trying to update a view. > >> > >> > >> > >> public void saveReview(ref DataSet review) > >> > >> { > >> > >> string select = "SELECT s.done_id, a.areaname, > >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, > >> q.need,s.score,s.evidence, d.catcutoff"; > >> > >> select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion as > >> q, > >> tblsesscore as s, tbldonecut as d"; > >> > >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = cat.cat_id > >> and > >> s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = > >> cat.cat_id"; > >> > >> > >> > >> > >> OleDbDataAdapter da = new OleDbDataAdapter(select, > >> conn.ConnectionString); > >> > >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); > >> > >> da.Update(review, "reviewquestions"); > >> > >> > >> > >> } > >> > >> > >> > >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... > >> > What is the problem? > >> > > >> > -- > >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > >> > RightHand .NET consulting & development www.rthand.com > >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > >> > > >> > >> > >> > > > Even if the view was updateable I would recommend writing sql statements :-)
-- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Garik" <garik_ma@do not spam.yahoo.com> wrote in message news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... > Probably, your view is not updatable. > > So, you must write Update/Delete/Insert commands manually, as mentioned > Miha. > > Garik > > "Darren Sim" wrote: > >> There is a view on the database created using the sql in the select >> statement on the code snippet. I was having problems using the table >> that >> so I defined it in the select statement. >> >> da.Update(review, "reviewquestions"); >> >> reviewquestions is the view on the database. >> >> >> if I use the code >> public void saveReview(ref DataSet review) >> >> { >> >> string select = "SELECT * from reviewquestions"; >> >> >> >> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >> conn.ConnectionString); >> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >> >> da.Update(review, "reviewquestions"); >> >> >> >> } >> >> >> I get the following error. >> "Dynamic SQL generation is not supported against a SelectCommand that >> does >> not return any base table information." >> >> >> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >> > Or create a view in the database and use that created view as a table. >> > >> > -- >> > Regards, >> > Garik Melkonyan >> > MCP, MCAD, MCSD .NET >> > >> > >> > "Darren Sim" wrote: >> > >> >> Sorry, I suppose more detail would help. >> >> >> >> The problem I am experiencing is that when I run the update message I >> >> get >> >> an >> >> error stating that >> >> >> >> "Dynamic SQL generation is not supported against multiple base tables" >> >> >> >> The code I am using for this is below, it works fine against a table >> >> taken >> >> directly from the source database, but encounters the above problem >> >> when >> >> trying to update a view. >> >> >> >> >> >> >> >> public void saveReview(ref DataSet review) >> >> >> >> { >> >> >> >> string select = "SELECT s.done_id, a.areaname, >> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >> >> q.need,s.score,s.evidence, d.catcutoff"; >> >> >> >> select = select + " FROM tblcategory AS cat, tblarea AS a, tblquestion >> >> as >> >> q, >> >> tblsesscore as s, tbldonecut as d"; >> >> >> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >> >> cat.cat_id >> >> and >> >> s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id = >> >> cat.cat_id"; >> >> >> >> >> >> >> >> >> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >> >> conn.ConnectionString); >> >> >> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >> >> >> >> da.Update(review, "reviewquestions"); >> >> >> >> >> >> >> >> } >> >> >> >> >> >> >> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >> >> > What is the problem? >> >> > >> >> > -- >> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> >> > RightHand .NET consulting & development www.rthand.com >> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> > >> >> >> >> >> >> >> >> >> Thanks for your help :)
One final question - if writing my own update etc commands do I need to pull the respective tables key values through in order to do this? Thanks again. Darren Sim Show quote "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:49BCAF9A-765A-471C-9621-D866FA7A9A54@microsoft.com... > Even if the view was updateable I would recommend writing sql statements > :-) > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "Garik" <garik_ma@do not spam.yahoo.com> wrote in message > news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... >> Probably, your view is not updatable. >> >> So, you must write Update/Delete/Insert commands manually, as mentioned >> Miha. >> >> Garik >> >> "Darren Sim" wrote: >> >>> There is a view on the database created using the sql in the select >>> statement on the code snippet. I was having problems using the table >>> that >>> so I defined it in the select statement. >>> >>> da.Update(review, "reviewquestions"); >>> >>> reviewquestions is the view on the database. >>> >>> >>> if I use the code >>> public void saveReview(ref DataSet review) >>> >>> { >>> >>> string select = "SELECT * from reviewquestions"; >>> >>> >>> >>> >>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>> conn.ConnectionString); >>> >>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>> >>> da.Update(review, "reviewquestions"); >>> >>> >>> >>> } >>> >>> >>> I get the following error. >>> "Dynamic SQL generation is not supported against a SelectCommand that >>> does >>> not return any base table information." >>> >>> >>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >>> > Or create a view in the database and use that created view as a table. >>> > >>> > -- >>> > Regards, >>> > Garik Melkonyan >>> > MCP, MCAD, MCSD .NET >>> > >>> > >>> > "Darren Sim" wrote: >>> > >>> >> Sorry, I suppose more detail would help. >>> >> >>> >> The problem I am experiencing is that when I run the update message I >>> >> get >>> >> an >>> >> error stating that >>> >> >>> >> "Dynamic SQL generation is not supported against multiple base >>> >> tables" >>> >> >>> >> The code I am using for this is below, it works fine against a table >>> >> taken >>> >> directly from the source database, but encounters the above problem >>> >> when >>> >> trying to update a view. >>> >> >>> >> >>> >> >>> >> public void saveReview(ref DataSet review) >>> >> >>> >> { >>> >> >>> >> string select = "SELECT s.done_id, a.areaname, >>> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>> >> q.need,s.score,s.evidence, d.catcutoff"; >>> >> >>> >> select = select + " FROM tblcategory AS cat, tblarea AS a, >>> >> tblquestion as >>> >> q, >>> >> tblsesscore as s, tbldonecut as d"; >>> >> >>> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>> >> cat.cat_id >>> >> and >>> >> s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id >>> >> = >>> >> cat.cat_id"; >>> >> >>> >> >>> >> >>> >> >>> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >>> >> conn.ConnectionString); >>> >> >>> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>> >> >>> >> da.Update(review, "reviewquestions"); >>> >> >>> >> >>> >> >>> >> } >>> >> >>> >> >>> >> >>> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>> >> > What is the problem? >>> >> > >>> >> > -- >>> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>> >> > RightHand .NET consulting & development www.rthand.com >>> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>> >> > >>> >> >>> >> >>> >> >>> >>> >>> > Yes, you need to pull whatever data you need. So, if you are going to do
updates or deletes you would need a key for each table affected. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Darren Sim" <darr***@sirius.co.uk> wrote in message news:%23EFXMUCUHHA.3996@TK2MSFTNGP04.phx.gbl... > Thanks for your help :) > > One final question - if writing my own update etc commands do I need to > pull the respective tables key values through in order to do this? > > Thanks again. > Darren Sim > > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > news:49BCAF9A-765A-471C-9621-D866FA7A9A54@microsoft.com... >> Even if the view was updateable I would recommend writing sql statements >> :-) >> >> -- >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> RightHand .NET consulting & development www.rthand.com >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >> news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... >>> Probably, your view is not updatable. >>> >>> So, you must write Update/Delete/Insert commands manually, as mentioned >>> Miha. >>> >>> Garik >>> >>> "Darren Sim" wrote: >>> >>>> There is a view on the database created using the sql in the select >>>> statement on the code snippet. I was having problems using the table >>>> that >>>> so I defined it in the select statement. >>>> >>>> da.Update(review, "reviewquestions"); >>>> >>>> reviewquestions is the view on the database. >>>> >>>> >>>> if I use the code >>>> public void saveReview(ref DataSet review) >>>> >>>> { >>>> >>>> string select = "SELECT * from reviewquestions"; >>>> >>>> >>>> >>>> >>>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>> conn.ConnectionString); >>>> >>>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>> >>>> da.Update(review, "reviewquestions"); >>>> >>>> >>>> >>>> } >>>> >>>> >>>> I get the following error. >>>> "Dynamic SQL generation is not supported against a SelectCommand that >>>> does >>>> not return any base table information." >>>> >>>> >>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >>>> > Or create a view in the database and use that created view as a >>>> > table. >>>> > >>>> > -- >>>> > Regards, >>>> > Garik Melkonyan >>>> > MCP, MCAD, MCSD .NET >>>> > >>>> > >>>> > "Darren Sim" wrote: >>>> > >>>> >> Sorry, I suppose more detail would help. >>>> >> >>>> >> The problem I am experiencing is that when I run the update message >>>> >> I get >>>> >> an >>>> >> error stating that >>>> >> >>>> >> "Dynamic SQL generation is not supported against multiple base >>>> >> tables" >>>> >> >>>> >> The code I am using for this is below, it works fine against a >>>> >> table >>>> >> taken >>>> >> directly from the source database, but encounters the above problem >>>> >> when >>>> >> trying to update a view. >>>> >> >>>> >> >>>> >> >>>> >> public void saveReview(ref DataSet review) >>>> >> >>>> >> { >>>> >> >>>> >> string select = "SELECT s.done_id, a.areaname, >>>> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>>> >> q.need,s.score,s.evidence, d.catcutoff"; >>>> >> >>>> >> select = select + " FROM tblcategory AS cat, tblarea AS a, >>>> >> tblquestion as >>>> >> q, >>>> >> tblsesscore as s, tbldonecut as d"; >>>> >> >>>> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>>> >> cat.cat_id >>>> >> and >>>> >> s.question_id = q.question_id and d.done_id = s.done_id and d.cat_id >>>> >> = >>>> >> cat.cat_id"; >>>> >> >>>> >> >>>> >> >>>> >> >>>> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>> >> conn.ConnectionString); >>>> >> >>>> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>> >> >>>> >> da.Update(review, "reviewquestions"); >>>> >> >>>> >> >>>> >> >>>> >> } >>>> >> >>>> >> >>>> >> >>>> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>>> >> > What is the problem? >>>> >> > >>>> >> > -- >>>> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>> >> > RightHand .NET consulting & development www.rthand.com >>>> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>> >> > >>>> >> >>>> >> >>>> >> >>>> >>>> >>>> >> > > Excellent, thank you Miha for the quick response - your help has been
greatly appreciated. All that is left now is to write some code :) Show quote "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:6F8F6FC4-1359-45E1-A798-75BDFF18C084@microsoft.com... > Yes, you need to pull whatever data you need. So, if you are going to do > updates or deletes you would need a key for each table affected. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "Darren Sim" <darr***@sirius.co.uk> wrote in message > news:%23EFXMUCUHHA.3996@TK2MSFTNGP04.phx.gbl... >> Thanks for your help :) >> >> One final question - if writing my own update etc commands do I need to >> pull the respective tables key values through in order to do this? >> >> Thanks again. >> Darren Sim >> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >> news:49BCAF9A-765A-471C-9621-D866FA7A9A54@microsoft.com... >>> Even if the view was updateable I would recommend writing sql statements >>> :-) >>> >>> -- >>> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>> RightHand .NET consulting & development www.rthand.com >>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>> >>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>> news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... >>>> Probably, your view is not updatable. >>>> >>>> So, you must write Update/Delete/Insert commands manually, as mentioned >>>> Miha. >>>> >>>> Garik >>>> >>>> "Darren Sim" wrote: >>>> >>>>> There is a view on the database created using the sql in the select >>>>> statement on the code snippet. I was having problems using the table >>>>> that >>>>> so I defined it in the select statement. >>>>> >>>>> da.Update(review, "reviewquestions"); >>>>> >>>>> reviewquestions is the view on the database. >>>>> >>>>> >>>>> if I use the code >>>>> public void saveReview(ref DataSet review) >>>>> >>>>> { >>>>> >>>>> string select = "SELECT * from reviewquestions"; >>>>> >>>>> >>>>> >>>>> >>>>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>> conn.ConnectionString); >>>>> >>>>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>> >>>>> da.Update(review, "reviewquestions"); >>>>> >>>>> >>>>> >>>>> } >>>>> >>>>> >>>>> I get the following error. >>>>> "Dynamic SQL generation is not supported against a SelectCommand that >>>>> does >>>>> not return any base table information." >>>>> >>>>> >>>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>>> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >>>>> > Or create a view in the database and use that created view as a >>>>> > table. >>>>> > >>>>> > -- >>>>> > Regards, >>>>> > Garik Melkonyan >>>>> > MCP, MCAD, MCSD .NET >>>>> > >>>>> > >>>>> > "Darren Sim" wrote: >>>>> > >>>>> >> Sorry, I suppose more detail would help. >>>>> >> >>>>> >> The problem I am experiencing is that when I run the update message >>>>> >> I get >>>>> >> an >>>>> >> error stating that >>>>> >> >>>>> >> "Dynamic SQL generation is not supported against multiple base >>>>> >> tables" >>>>> >> >>>>> >> The code I am using for this is below, it works fine against a >>>>> >> table >>>>> >> taken >>>>> >> directly from the source database, but encounters the above problem >>>>> >> when >>>>> >> trying to update a view. >>>>> >> >>>>> >> >>>>> >> >>>>> >> public void saveReview(ref DataSet review) >>>>> >> >>>>> >> { >>>>> >> >>>>> >> string select = "SELECT s.done_id, a.areaname, >>>>> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>>>> >> q.need,s.score,s.evidence, d.catcutoff"; >>>>> >> >>>>> >> select = select + " FROM tblcategory AS cat, tblarea AS a, >>>>> >> tblquestion as >>>>> >> q, >>>>> >> tblsesscore as s, tbldonecut as d"; >>>>> >> >>>>> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>>>> >> cat.cat_id >>>>> >> and >>>>> >> s.question_id = q.question_id and d.done_id = s.done_id and >>>>> >> d.cat_id = >>>>> >> cat.cat_id"; >>>>> >> >>>>> >> >>>>> >> >>>>> >> >>>>> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>> >> conn.ConnectionString); >>>>> >> >>>>> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>> >> >>>>> >> da.Update(review, "reviewquestions"); >>>>> >> >>>>> >> >>>>> >> >>>>> >> } >>>>> >> >>>>> >> >>>>> >> >>>>> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>>> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>>>> >> > What is the problem? >>>>> >> > >>>>> >> > -- >>>>> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>>> >> > RightHand .NET consulting & development www.rthand.com >>>>> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>>> >> > >>>>> >> >>>>> >> >>>>> >> >>>>> >>>>> >>>>> >>> >> >> > Darren,
I would recommend you a template based code generator (CodeSmith is an excellent choice) that does the boring code for you. Using proper template it can generate CRUD sql statements for every given table automatically. Another step forward is to use an ORM product, that again does a lot of boring stuff for you. LLBLGenPro would be an excellent choice. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ "Darren Sim" <darr***@sirius.co.uk> wrote in message news:O9HVRdCUHHA.4384@TK2MSFTNGP02.phx.gbl... > Excellent, thank you Miha for the quick response - your help has been > greatly appreciated. > > All that is left now is to write some code :) > > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > news:6F8F6FC4-1359-45E1-A798-75BDFF18C084@microsoft.com... >> Yes, you need to pull whatever data you need. So, if you are going to do >> updates or deletes you would need a key for each table affected. >> >> -- >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> RightHand .NET consulting & development www.rthand.com >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> "Darren Sim" <darr***@sirius.co.uk> wrote in message >> news:%23EFXMUCUHHA.3996@TK2MSFTNGP04.phx.gbl... >>> Thanks for your help :) >>> >>> One final question - if writing my own update etc commands do I need to >>> pull the respective tables key values through in order to do this? >>> >>> Thanks again. >>> Darren Sim >>> >>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>> news:49BCAF9A-765A-471C-9621-D866FA7A9A54@microsoft.com... >>>> Even if the view was updateable I would recommend writing sql >>>> statements :-) >>>> >>>> -- >>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>> RightHand .NET consulting & development www.rthand.com >>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>> >>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>> news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... >>>>> Probably, your view is not updatable. >>>>> >>>>> So, you must write Update/Delete/Insert commands manually, as >>>>> mentioned Miha. >>>>> >>>>> Garik >>>>> >>>>> "Darren Sim" wrote: >>>>> >>>>>> There is a view on the database created using the sql in the select >>>>>> statement on the code snippet. I was having problems using the table >>>>>> that >>>>>> so I defined it in the select statement. >>>>>> >>>>>> da.Update(review, "reviewquestions"); >>>>>> >>>>>> reviewquestions is the view on the database. >>>>>> >>>>>> >>>>>> if I use the code >>>>>> public void saveReview(ref DataSet review) >>>>>> >>>>>> { >>>>>> >>>>>> string select = "SELECT * from reviewquestions"; >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>>> conn.ConnectionString); >>>>>> >>>>>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>>> >>>>>> da.Update(review, "reviewquestions"); >>>>>> >>>>>> >>>>>> >>>>>> } >>>>>> >>>>>> >>>>>> I get the following error. >>>>>> "Dynamic SQL generation is not supported against a SelectCommand that >>>>>> does >>>>>> not return any base table information." >>>>>> >>>>>> >>>>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>>>> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >>>>>> > Or create a view in the database and use that created view as a >>>>>> > table. >>>>>> > >>>>>> > -- >>>>>> > Regards, >>>>>> > Garik Melkonyan >>>>>> > MCP, MCAD, MCSD .NET >>>>>> > >>>>>> > >>>>>> > "Darren Sim" wrote: >>>>>> > >>>>>> >> Sorry, I suppose more detail would help. >>>>>> >> >>>>>> >> The problem I am experiencing is that when I run the update >>>>>> >> message I get >>>>>> >> an >>>>>> >> error stating that >>>>>> >> >>>>>> >> "Dynamic SQL generation is not supported against multiple base >>>>>> >> tables" >>>>>> >> >>>>>> >> The code I am using for this is below, it works fine against a >>>>>> >> table >>>>>> >> taken >>>>>> >> directly from the source database, but encounters the above >>>>>> >> problem when >>>>>> >> trying to update a view. >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> public void saveReview(ref DataSet review) >>>>>> >> >>>>>> >> { >>>>>> >> >>>>>> >> string select = "SELECT s.done_id, a.areaname, >>>>>> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>>>>> >> q.need,s.score,s.evidence, d.catcutoff"; >>>>>> >> >>>>>> >> select = select + " FROM tblcategory AS cat, tblarea AS a, >>>>>> >> tblquestion as >>>>>> >> q, >>>>>> >> tblsesscore as s, tbldonecut as d"; >>>>>> >> >>>>>> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>>>>> >> cat.cat_id >>>>>> >> and >>>>>> >> s.question_id = q.question_id and d.done_id = s.done_id and >>>>>> >> d.cat_id = >>>>>> >> cat.cat_id"; >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>>> >> conn.ConnectionString); >>>>>> >> >>>>>> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>>> >> >>>>>> >> da.Update(review, "reviewquestions"); >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> } >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>>>> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>>>>> >> > What is the problem? >>>>>> >> > >>>>>> >> > -- >>>>>> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>>>> >> > RightHand .NET consulting & development www.rthand.com >>>>>> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>>>> >> > >>>>>> >> >>>>>> >> >>>>>> >> >>>>>> >>>>>> >>>>>> >>>> >>> >>> >> > > Hi Miha,
I have looked at codesmith recently - however it looked like it might take a bit of time to get to grips with using it and my project deadline is looming ever closer. How steep a learning curve is involved in using these tools? The problem I have came up against is the application I am developing needs to run against different database types, and needs to be completed in a very short time (I am the only developer on this project at the moment). I made the decision to use dataset(s) to hold the working data and act as the logic layer, but I am by no means expert in this technology as yet. My prior experience is in Java and Delphi - datasets are fairly new to me.. Thanks for your suggestions - I will defineately look further into these tools for future development. Show quote "Miha Markic [MVP C#]" <miha at rthand com> wrote in message news:DB81D57A-07E4-44FA-8D9E-8B759BD84C21@microsoft.com... > Darren, > > I would recommend you a template based code generator (CodeSmith is an > excellent choice) that does the boring code for you. Using proper template > it can generate CRUD sql statements for every given table automatically. > > Another step forward is to use an ORM product, that again does a lot of > boring stuff for you. LLBLGenPro would be an excellent choice. > > -- > Miha Markic [MVP C#, INETA Country Leader for Slovenia] > RightHand .NET consulting & development www.rthand.com > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > > "Darren Sim" <darr***@sirius.co.uk> wrote in message > news:O9HVRdCUHHA.4384@TK2MSFTNGP02.phx.gbl... >> Excellent, thank you Miha for the quick response - your help has been >> greatly appreciated. >> >> All that is left now is to write some code :) >> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >> news:6F8F6FC4-1359-45E1-A798-75BDFF18C084@microsoft.com... >>> Yes, you need to pull whatever data you need. So, if you are going to do >>> updates or deletes you would need a key for each table affected. >>> >>> -- >>> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>> RightHand .NET consulting & development www.rthand.com >>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>> >>> "Darren Sim" <darr***@sirius.co.uk> wrote in message >>> news:%23EFXMUCUHHA.3996@TK2MSFTNGP04.phx.gbl... >>>> Thanks for your help :) >>>> >>>> One final question - if writing my own update etc commands do I need to >>>> pull the respective tables key values through in order to do this? >>>> >>>> Thanks again. >>>> Darren Sim >>>> >>>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>> news:49BCAF9A-765A-471C-9621-D866FA7A9A54@microsoft.com... >>>>> Even if the view was updateable I would recommend writing sql >>>>> statements :-) >>>>> >>>>> -- >>>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>>> RightHand .NET consulting & development www.rthand.com >>>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>>> >>>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>>> news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... >>>>>> Probably, your view is not updatable. >>>>>> >>>>>> So, you must write Update/Delete/Insert commands manually, as >>>>>> mentioned Miha. >>>>>> >>>>>> Garik >>>>>> >>>>>> "Darren Sim" wrote: >>>>>> >>>>>>> There is a view on the database created using the sql in the select >>>>>>> statement on the code snippet. I was having problems using the >>>>>>> table that >>>>>>> so I defined it in the select statement. >>>>>>> >>>>>>> da.Update(review, "reviewquestions"); >>>>>>> >>>>>>> reviewquestions is the view on the database. >>>>>>> >>>>>>> >>>>>>> if I use the code >>>>>>> public void saveReview(ref DataSet review) >>>>>>> >>>>>>> { >>>>>>> >>>>>>> string select = "SELECT * from reviewquestions"; >>>>>>> >>>>>>> >>>>>>> >>>>>>> >>>>>>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>>>> conn.ConnectionString); >>>>>>> >>>>>>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>>>> >>>>>>> da.Update(review, "reviewquestions"); >>>>>>> >>>>>>> >>>>>>> >>>>>>> } >>>>>>> >>>>>>> >>>>>>> I get the following error. >>>>>>> "Dynamic SQL generation is not supported against a SelectCommand >>>>>>> that does >>>>>>> not return any base table information." >>>>>>> >>>>>>> >>>>>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>>>>> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >>>>>>> > Or create a view in the database and use that created view as a >>>>>>> > table. >>>>>>> > >>>>>>> > -- >>>>>>> > Regards, >>>>>>> > Garik Melkonyan >>>>>>> > MCP, MCAD, MCSD .NET >>>>>>> > >>>>>>> > >>>>>>> > "Darren Sim" wrote: >>>>>>> > >>>>>>> >> Sorry, I suppose more detail would help. >>>>>>> >> >>>>>>> >> The problem I am experiencing is that when I run the update >>>>>>> >> message I get >>>>>>> >> an >>>>>>> >> error stating that >>>>>>> >> >>>>>>> >> "Dynamic SQL generation is not supported against multiple base >>>>>>> >> tables" >>>>>>> >> >>>>>>> >> The code I am using for this is below, it works fine against a >>>>>>> >> table >>>>>>> >> taken >>>>>>> >> directly from the source database, but encounters the above >>>>>>> >> problem when >>>>>>> >> trying to update a view. >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >> public void saveReview(ref DataSet review) >>>>>>> >> >>>>>>> >> { >>>>>>> >> >>>>>>> >> string select = "SELECT s.done_id, a.areaname, >>>>>>> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>>>>>> >> q.need,s.score,s.evidence, d.catcutoff"; >>>>>>> >> >>>>>>> >> select = select + " FROM tblcategory AS cat, tblarea AS a, >>>>>>> >> tblquestion as >>>>>>> >> q, >>>>>>> >> tblsesscore as s, tbldonecut as d"; >>>>>>> >> >>>>>>> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>>>>>> >> cat.cat_id >>>>>>> >> and >>>>>>> >> s.question_id = q.question_id and d.done_id = s.done_id and >>>>>>> >> d.cat_id = >>>>>>> >> cat.cat_id"; >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>>>> >> conn.ConnectionString); >>>>>>> >> >>>>>>> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>>>> >> >>>>>>> >> da.Update(review, "reviewquestions"); >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >> } >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>>>>> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>>>>>> >> > What is the problem? >>>>>>> >> > >>>>>>> >> > -- >>>>>>> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>>>>> >> > RightHand .NET consulting & development www.rthand.com >>>>>>> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>>>>> >> > >>>>>>> >> >>>>>>> >> >>>>>>> >> >>>>>>> >>>>>>> >>>>>>> >>>>> >>>> >>>> >>> >> >> > Hi Darren,
"Darren Sim" <darr***@sirius.co.uk> wrote in message CodeSmith might be very easy to use if you already have proper templates (a news:uuaZVBDUHHA.4668@TK2MSFTNGP04.phx.gbl... > Hi Miha, > > I have looked at codesmith recently - however it looked like it might take > a bit of time to get to grips with using it and my project deadline is > looming ever closer. How steep a learning curve is involved in using > these tools? bunch of them come with CS). Otherwise you have to code a bit to create a template that in turns generates code for you. As per ORM products they might suite your needs even better - they usually take care of different databases automatically (they support a certain set of different databases) - you just say which one you want to use. There is a lerning curve though but once you get grip of it you'll be much more productive. I strongly recommend you to take a look at LLBLGenPro - it would help you dealing with different databases and much more. -- Show quoteMiha Markic [MVP C#, INETA Country Leader for Slovenia] RightHand .NET consulting & development www.rthand.com Blog: http://cs.rthand.com/blogs/blog_with_righthand/ > The problem I have came up against is the application I am developing > needs to run against different database types, and needs to be completed > in a very short time (I am the only developer on this project at the > moment). I made the decision to use dataset(s) to hold the working data > and act as the logic layer, but I am by no means expert in this technology > as yet. My prior experience is in Java and Delphi - datasets are fairly > new to me.. > > Thanks for your suggestions - I will defineately look further into these > tools for future development. > > > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message > news:DB81D57A-07E4-44FA-8D9E-8B759BD84C21@microsoft.com... >> Darren, >> >> I would recommend you a template based code generator (CodeSmith is an >> excellent choice) that does the boring code for you. Using proper >> template it can generate CRUD sql statements for every given table >> automatically. >> >> Another step forward is to use an ORM product, that again does a lot of >> boring stuff for you. LLBLGenPro would be an excellent choice. >> >> -- >> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >> RightHand .NET consulting & development www.rthand.com >> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >> >> "Darren Sim" <darr***@sirius.co.uk> wrote in message >> news:O9HVRdCUHHA.4384@TK2MSFTNGP02.phx.gbl... >>> Excellent, thank you Miha for the quick response - your help has been >>> greatly appreciated. >>> >>> All that is left now is to write some code :) >>> >>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>> news:6F8F6FC4-1359-45E1-A798-75BDFF18C084@microsoft.com... >>>> Yes, you need to pull whatever data you need. So, if you are going to >>>> do updates or deletes you would need a key for each table affected. >>>> >>>> -- >>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>> RightHand .NET consulting & development www.rthand.com >>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>> >>>> "Darren Sim" <darr***@sirius.co.uk> wrote in message >>>> news:%23EFXMUCUHHA.3996@TK2MSFTNGP04.phx.gbl... >>>>> Thanks for your help :) >>>>> >>>>> One final question - if writing my own update etc commands do I need >>>>> to pull the respective tables key values through in order to do this? >>>>> >>>>> Thanks again. >>>>> Darren Sim >>>>> >>>>> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>>> news:49BCAF9A-765A-471C-9621-D866FA7A9A54@microsoft.com... >>>>>> Even if the view was updateable I would recommend writing sql >>>>>> statements :-) >>>>>> >>>>>> -- >>>>>> Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>>>> RightHand .NET consulting & development www.rthand.com >>>>>> Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>>>> >>>>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>>>> news:A78D8BC9-8FB1-41C3-9698-1E6E304FF87C@microsoft.com... >>>>>>> Probably, your view is not updatable. >>>>>>> >>>>>>> So, you must write Update/Delete/Insert commands manually, as >>>>>>> mentioned Miha. >>>>>>> >>>>>>> Garik >>>>>>> >>>>>>> "Darren Sim" wrote: >>>>>>> >>>>>>>> There is a view on the database created using the sql in the select >>>>>>>> statement on the code snippet. I was having problems using the >>>>>>>> table that >>>>>>>> so I defined it in the select statement. >>>>>>>> >>>>>>>> da.Update(review, "reviewquestions"); >>>>>>>> >>>>>>>> reviewquestions is the view on the database. >>>>>>>> >>>>>>>> >>>>>>>> if I use the code >>>>>>>> public void saveReview(ref DataSet review) >>>>>>>> >>>>>>>> { >>>>>>>> >>>>>>>> string select = "SELECT * from reviewquestions"; >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>>>>> conn.ConnectionString); >>>>>>>> >>>>>>>> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>>>>> >>>>>>>> da.Update(review, "reviewquestions"); >>>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> } >>>>>>>> >>>>>>>> >>>>>>>> I get the following error. >>>>>>>> "Dynamic SQL generation is not supported against a SelectCommand >>>>>>>> that does >>>>>>>> not return any base table information." >>>>>>>> >>>>>>>> >>>>>>>> "Garik" <garik_ma@do not spam.yahoo.com> wrote in message >>>>>>>> news:696BBA30-5987-4DD1-AA30-D49A36D004DD@microsoft.com... >>>>>>>> > Or create a view in the database and use that created view as a >>>>>>>> > table. >>>>>>>> > >>>>>>>> > -- >>>>>>>> > Regards, >>>>>>>> > Garik Melkonyan >>>>>>>> > MCP, MCAD, MCSD .NET >>>>>>>> > >>>>>>>> > >>>>>>>> > "Darren Sim" wrote: >>>>>>>> > >>>>>>>> >> Sorry, I suppose more detail would help. >>>>>>>> >> >>>>>>>> >> The problem I am experiencing is that when I run the update >>>>>>>> >> message I get >>>>>>>> >> an >>>>>>>> >> error stating that >>>>>>>> >> >>>>>>>> >> "Dynamic SQL generation is not supported against multiple base >>>>>>>> >> tables" >>>>>>>> >> >>>>>>>> >> The code I am using for this is below, it works fine against a >>>>>>>> >> table >>>>>>>> >> taken >>>>>>>> >> directly from the source database, but encounters the above >>>>>>>> >> problem when >>>>>>>> >> trying to update a view. >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> public void saveReview(ref DataSet review) >>>>>>>> >> >>>>>>>> >> { >>>>>>>> >> >>>>>>>> >> string select = "SELECT s.done_id, a.areaname, >>>>>>>> >> a.area_id,cat.catname,cat.cat_id, q.question_id,q.quest, >>>>>>>> >> q.need,s.score,s.evidence, d.catcutoff"; >>>>>>>> >> >>>>>>>> >> select = select + " FROM tblcategory AS cat, tblarea AS a, >>>>>>>> >> tblquestion as >>>>>>>> >> q, >>>>>>>> >> tblsesscore as s, tbldonecut as d"; >>>>>>>> >> >>>>>>>> >> select = select+ " WHERE cat.area_id=a.area_id and q.cat_id = >>>>>>>> >> cat.cat_id >>>>>>>> >> and >>>>>>>> >> s.question_id = q.question_id and d.done_id = s.done_id and >>>>>>>> >> d.cat_id = >>>>>>>> >> cat.cat_id"; >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> OleDbDataAdapter da = new OleDbDataAdapter(select, >>>>>>>> >> conn.ConnectionString); >>>>>>>> >> >>>>>>>> >> OleDbCommandBuilder cb = new OleDbCommandBuilder(da); >>>>>>>> >> >>>>>>>> >> da.Update(review, "reviewquestions"); >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> } >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> "Miha Markic [MVP C#]" <miha at rthand com> wrote in message >>>>>>>> >> news:8FF7404C-95F6-43B7-A4CB-D6A22710DEC0@microsoft.com... >>>>>>>> >> > What is the problem? >>>>>>>> >> > >>>>>>>> >> > -- >>>>>>>> >> > Miha Markic [MVP C#, INETA Country Leader for Slovenia] >>>>>>>> >> > RightHand .NET consulting & development www.rthand.com >>>>>>>> >> > Blog: http://cs.rthand.com/blogs/blog_with_righthand/ >>>>>>>> >> > >>>>>>>> >> >>>>>>>> >> >>>>>>>> >> >>>>>>>> >>>>>>>> >>>>>>>> >>>>>> >>>>> >>>>> >>>> >>> >>> >> > >  |
|||||||||||||||||||||||
Other interesting topics