DataAdapter fails to update the server database

G

Guest

I am probably missing something obvious, but I cant seem to get this to work.
I have been reading several posts and trying different combinations, but the
gist is that the server database never gets updated with the changed dataset.
The Clinic Table does have a primary key. If i make a call to
da.UpdateCommand = cb.GetUpdateCommand() i get a runtime error saying:

Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information.

When i comment out that line the code seems to run, but it doesnt update the
server data.

Any help would be greatly appreciated!


public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{

InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id, "+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url, "+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2, "+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM dbo.Clinic";
da = new SqlDataAdapter(sqlString,Iconnection);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
ds = new DataSet();
da.Fill(ds, "dbo.Clinic");
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "dbo.Clinic";

}

private void clinicBindingNavigatorSaveItem_Click(object sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
ds.AcceptChanges();
da.Update(ds.Tables["dbo.Clinic"]);
 
R

RobinS

Does your table have a primary key?

Does your select statement select the primary key of your table?

Robin S.
 
G

Guest

yes the table has a primary key, uid and it is selected in the initial
connection when the dataAdapter is constructed.

RobinS said:
Does your table have a primary key?

Does your select statement select the primary key of your table?

Robin S.
---------------------------------------------------
Jack Wasserstein said:
I am probably missing something obvious, but I cant seem to get this to
work.
I have been reading several posts and trying different combinations,
but the
gist is that the server database never gets updated with the changed
dataset.
The Clinic Table does have a primary key. If i make a call to
da.UpdateCommand = cb.GetUpdateCommand() i get a runtime error saying:

Dynamic SQL generation for the UpdateCommand is not supported against
a
SelectCommand that does not return any key column information.

When i comment out that line the code seems to run, but it doesnt
update the
server data.

Any help would be greatly appreciated!


public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{

InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id, "+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url, "+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2, "+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM dbo.Clinic";
da = new SqlDataAdapter(sqlString,Iconnection);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
ds = new DataSet();
da.Fill(ds, "dbo.Clinic");
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "dbo.Clinic";

}

private void clinicBindingNavigatorSaveItem_Click(object
sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
ds.AcceptChanges();
da.Update(ds.Tables["dbo.Clinic"]);
Click to expand...
Click to expand...
 
C

Cor Ligthert [MVP]

Jack,

There will never be anything inserted in your database as long as you set
everything to "unchanged" what means updated, before you update with the
method AcceptChanges. Strange name that AcceptChanges confuses everybody,
however this name is unchangable because than you get breaking changes.

Cor
 
G

Guest

Cor,

When i comment out

ds.acceptchanges();

then i get this error:

Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information.

The table that is being updated only has one row in it and it does have a
primary key which is used in the original select statement.

It appears that an update command is not being generated by the command
builder. Any ideas on this one?

Cor Ligthert said:
Jack,

There will never be anything inserted in your database as long as you set
everything to "unchanged" what means updated, before you update with the
method AcceptChanges. Strange name that AcceptChanges confuses everybody,
however this name is unchangable because than you get breaking changes.

Cor


Jack Wasserstein said:
I am probably missing something obvious, but I cant seem to get this to
work.
I have been reading several posts and trying different combinations, but
the
gist is that the server database never gets updated with the changed
dataset.
The Clinic Table does have a primary key. If i make a call to
da.UpdateCommand = cb.GetUpdateCommand() i get a runtime error saying:

Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information.

When i comment out that line the code seems to run, but it doesnt update
the
server data.

Any help would be greatly appreciated!


public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{

InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id, "+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url, "+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2, "+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM dbo.Clinic";
da = new SqlDataAdapter(sqlString,Iconnection);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
ds = new DataSet();
da.Fill(ds, "dbo.Clinic");
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "dbo.Clinic";

}

private void clinicBindingNavigatorSaveItem_Click(object sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
ds.AcceptChanges();
da.Update(ds.Tables["dbo.Clinic"]);
Click to expand...
Click to expand...
 
R

RobinS

Cor -- good catch, I missed that completely. I probably read it as
coming after the
update because that's what I expected to see.

Jack -- The reason you're getting that error is because the
dynamically-created
UpdateCommand is not working. That's probably because you are getting
the insert
command before you are filling your dataset. Also, I don't know why you
are doing the
MissingSchemaAction thing.

I don't know what version of SqlServer you're using, but you may not
need
the "dbo." prefix on the table name. You certainly don't need to
propagate it
on the table name that you assign to your table in your dataset. I left
it
in; if this works, try taking it out and see if it still works.

Try this. I do VB, not C#, but I read a lot of C#, so I think I've made
the changes
appropriately. I added comments to explain.

public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{
InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id, "+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url, "+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2, "+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM dbo.Clinic";

da = new SqlDataAdapter(sqlString,Iconnection);
//the following is not needed
//da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
ds = new DataSet();
da.Fill(ds, "Clinic");


//Instantiating the SqlCommandBuilder creates the
// InsertCommand, DeleteCommand, and UpdateCommand
// command objects for you.
cb = new SqlCommandBuilder(da);

//all GetInsertCommand does is get the command text so you
can
// see the SQL that is going to be used.
//da.InsertCommand = cb.GetInsertCommand();
Debug.Print(cb.GetInsertCommand().CommandText);
Debug.Print(cb.GetUpdateCommand().CommandText);
Debug.Print(cb.GetDeleteCommand().CommandText);

//data binding
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "Clinic";
}

private void clinicBindingNavigatorSaveItem_Click(object sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
da.Update(ds,"Clinic"); //I changed the syntax of this as
well

//This sets the rowstate of all of the changed records back
to unchanged.
//So if you do this before you call the update method, the
update method
// won't find any rows to update, as Cor pointed out.
ds.AcceptChanges();
}
}

By the way, I don't think people use the SqlCommandBuilder very often.
While it does make it simple to do updates, it does not offer the best
possible run-time performance, because it generates updating logic
for you at run-time by querying for additional metadata. You can run
your own updating logic in code in less time than it takes the
SqlCommandBuilder
to request and process the metadata required to generate similar
updating logic.
It also doesn't give you the same level of control over the updating
logic. For
example, it will not help you submit updates using stored procedures.
Just FYI, in case you wanted to know.

Robin S.
--------------------------
Jack Wasserstein said:
Cor,

When i comment out

ds.acceptchanges();

then i get this error:

Dynamic SQL generation for the UpdateCommand is not supported against
a
SelectCommand that does not return any key column information.

The table that is being updated only has one row in it and it does
have a
primary key which is used in the original select statement.

It appears that an update command is not being generated by the
command
builder. Any ideas on this one?

Cor Ligthert said:
Jack,

There will never be anything inserted in your database as long as you
set
everything to "unchanged" what means updated, before you update with
the
method AcceptChanges. Strange name that AcceptChanges confuses
everybody,
however this name is unchangable because than you get breaking
changes.

Cor


"Jack Wasserstein" <[email protected]>
schreef in
bericht news:[email protected]...
I am probably missing something obvious, but I cant seem to get this
to
work.
I have been reading several posts and trying different
combinations, but
the
gist is that the server database never gets updated with the
changed
dataset.
The Clinic Table does have a primary key. If i make a call to
da.UpdateCommand = cb.GetUpdateCommand() i get a runtime error
saying:

Dynamic SQL generation for the UpdateCommand is not supported
against a
SelectCommand that does not return any key column information.

When i comment out that line the code seems to run, but it doesnt
update
the
server data.

Any help would be greatly appreciated!


public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{

InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id,
"+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url,
"+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2,
"+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM
dbo.Clinic";
da = new SqlDataAdapter(sqlString,Iconnection);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
ds = new DataSet();
da.Fill(ds, "dbo.Clinic");
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "dbo.Clinic";

}

private void clinicBindingNavigatorSaveItem_Click(object
sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
ds.AcceptChanges();
da.Update(ds.Tables["dbo.Clinic"]);
Click to expand...
Click to expand...
Click to expand...
 
W

W.G. Ryan [MVP]

Don't use commandbuilders ;-)

Cor's absolutely right about the AcceptChanges. So what's happening now is
that there's actually commands being fired against the db.

So the question I'd have is, although it has a key, is the key column
included in the SELECT command? My guess is that it isn't.
Jack Wasserstein said:
Cor,

When i comment out

ds.acceptchanges();

then i get this error:

Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information.

The table that is being updated only has one row in it and it does have a
primary key which is used in the original select statement.

It appears that an update command is not being generated by the command
builder. Any ideas on this one?

Cor Ligthert said:
Jack,

There will never be anything inserted in your database as long as you set
everything to "unchanged" what means updated, before you update with the
method AcceptChanges. Strange name that AcceptChanges confuses everybody,
however this name is unchangable because than you get breaking changes.

Cor


Jack Wasserstein said:
I am probably missing something obvious, but I cant seem to get this to
work.
I have been reading several posts and trying different combinations,
but
the
gist is that the server database never gets updated with the changed
dataset.
The Clinic Table does have a primary key. If i make a call to
da.UpdateCommand = cb.GetUpdateCommand() i get a runtime error saying:

Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information.

When i comment out that line the code seems to run, but it doesnt
update
the
server data.

Any help would be greatly appreciated!


public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{

InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id, "+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url, "+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2, "+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM dbo.Clinic";
da = new SqlDataAdapter(sqlString,Iconnection);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
ds = new DataSet();
da.Fill(ds, "dbo.Clinic");
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "dbo.Clinic";

}

private void clinicBindingNavigatorSaveItem_Click(object sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
ds.AcceptChanges();
da.Update(ds.Tables["dbo.Clinic"]);
Click to expand...
Click to expand...
Click to expand...
 
R

Robert Simpson

What's the table's schema look like?

Robert

Jack Wasserstein said:
uid is the key field and it is being called for in the select statement.
What
is an alternative in this case for bypassing the commandbuilder if you
think
that is the problem.

W.G. Ryan said:
Don't use commandbuilders ;-)

Cor's absolutely right about the AcceptChanges. So what's happening now
is
that there's actually commands being fired against the db.

So the question I'd have is, although it has a key, is the key column
included in the SELECT command? My guess is that it isn't.
Jack Wasserstein said:
Cor,

When i comment out

ds.acceptchanges();

then i get this error:

Dynamic SQL generation for the UpdateCommand is not supported against a
SelectCommand that does not return any key column information.

The table that is being updated only has one row in it and it does have
a
primary key which is used in the original select statement.

It appears that an update command is not being generated by the command
builder. Any ideas on this one?

:

Jack,

There will never be anything inserted in your database as long as you
set
everything to "unchanged" what means updated, before you update with
the
method AcceptChanges. Strange name that AcceptChanges confuses
everybody,
however this name is unchangable because than you get breaking
changes.

Cor


"Jack Wasserstein" <[email protected]> schreef
in
bericht I am probably missing something obvious, but I cant seem to get this
to
work.
I have been reading several posts and trying different combinations,
but
the
gist is that the server database never gets updated with the changed
dataset.
The Clinic Table does have a primary key. If i make a call to
da.UpdateCommand = cb.GetUpdateCommand() i get a runtime error
saying:

Dynamic SQL generation for the UpdateCommand is not supported
against a
SelectCommand that does not return any key column information.

When i comment out that line the code seems to run, but it doesnt
update
the
server data.

Any help would be greatly appreciated!


public partial class UserInfo : Form
{
SqlConnection Iconnection;
SqlDataAdapter da;
SqlCommandBuilder cb;
DataSet ds;

public UserInfo()
{

InitializeComponent();
Iconnection = InvDB.GetInvConnection();
Iconnection.Open();
string sqlString = "SELECT uid, clinic_name, clinic_id,
"+
"clinic_address1, clinic_address2, clinic_city, "+
"clinic_state, clinic_zip, clinic_country, clinic_url, "+
"clinic_inquiry_contact, clinic_phone1, clinic_phone2, "+
"clinic_fax, clinic_email, clinic_product_code, "+
"clinic_support_plan, clinic_install_date FROM
dbo.Clinic";
da = new SqlDataAdapter(sqlString,Iconnection);
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
cb = new SqlCommandBuilder(da);
da.InsertCommand = cb.GetInsertCommand();
ds = new DataSet();
da.Fill(ds, "dbo.Clinic");
this.clinicBindingSource.DataSource = ds;
this.clinicBindingSource.DataMember = "dbo.Clinic";

}

private void clinicBindingNavigatorSaveItem_Click(object
sender,
EventArgs e)
{
this.Validate();
this.clinicBindingSource.EndEdit();
ds.AcceptChanges();
da.Update(ds.Tables["dbo.Clinic"]);
Click to expand...
Click to expand...
Click to expand...
 
C

Cor Ligthert [MVP]

Jack,

What is the purpose of this command?

da.InsertCommand = cb.GetInsertCommand();

I never use that, because the commandbuilder is dynamic until the last
moment.

Cor
 
R

RobinS

It basically sets it up before the call to fill the
dataset, and allows you to view the SQL. Not necessary
in this case. His code is in the wrong order.
That's my two cents' worth!

Robin S.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to get the value of autoincrement field from Database in dataset on client 1
Update data in Grid on the basis of several tables 1

Top