updating database views via tableadapter?

[Darren Sim]

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

 

[Miha Markic [MVP C#]]

What is the problem?

 

[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");



}

 

[Miha Markic [MVP C#]]

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.

 

[Guest]

Or create a view in the database and use that created view as a table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET

 

[Darren Sim]

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."

 

[RobinS]

Is the view defined as read-only inn the database?

Robin S.
------------------------------

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."

Or create a view in the database and use that created view as a table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET

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
What is the problem?

 

[Guest]

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as mentioned Miha.

Garik

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."

Or create a view in the database and use that created view as a table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET

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
What is the problem?

 

[Miha Markic [MVP C#]]

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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as mentioned
Miha.

Garik

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."

Or create a view in the database and use that created view as a table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?

 

[Darren Sim]

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

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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as mentioned
Miha.

Garik

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."


Or create a view in the database and use that created view as a table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?

 

[Miha Markic [MVP C#]]

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/

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

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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as mentioned
Miha.

Garik

:

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."


Or create a view in the database and use that created view as a
table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?

 

[Darren Sim]

Excellent, thank you Miha for the quick response - your help has been
greatly appreciated.

All that is left now is to write some code

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/

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

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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as mentioned
Miha.

Garik

:

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."


Or create a view in the database and use that created view as a
table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?

 

[Miha Markic [MVP C#]]

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/

Excellent, thank you Miha for the quick response - your help has been
greatly appreciated.

All that is left now is to write some code

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/

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
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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as
mentioned Miha.

Garik

:

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."


Or create a view in the database and use that created view as a
table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?

 

[Darren Sim]

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.

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/

Excellent, thank you Miha for the quick response - your help has been
greatly appreciated.

All that is left now is to write some code

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/

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
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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as
mentioned Miha.

Garik

:

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."


Or create a view in the database and use that created view as a
table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?

 

[Miha Markic [MVP C#]]

Hi Darren,

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?

CodeSmith might be very easy to use if you already have proper templates (a
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.

--
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/

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.

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/

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
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/

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
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/

Probably, your view is not updatable.

So, you must write Update/Delete/Insert commands manually, as
mentioned Miha.

Garik

:

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."


Or create a view in the database and use that created view as a
table.

--
Regards,
Garik Melkonyan
MCP, MCAD, MCSD .NET


:

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
What is the problem?