DataAdapter fails to update the server database

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

Does your table have a primary key?

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

Robin S.
---------------------------------------------------
"Jack Wasserstein" <JackWasserstein@discussions.microsoft.com> wrote in
message news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
>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"]);
>

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

"RobinS" wrote:

> Does your table have a primary key?
>
> Does your select statement select the primary key of your table?
>
> Robin S.
> ---------------------------------------------------
> "Jack Wasserstein" <JackWasserstein@discussions.microsoft.com> wrote in
> message news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
> >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"]);
> >
>
>
>

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" <JackWasserstein@discussions.microsoft.com> schreef in
bericht news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
>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"]);
>

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 [MVP]" wrote:

> 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" <JackWasserstein@discussions.microsoft.com> schreef in
> bericht news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
> >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"]);
> >
>
>
>

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" <JackWasserstein@discussions.microsoft.com> wrote in
message news:828E0631-6E88-4265-9149-E0B13FC6F5D2@microsoft.com...
> 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 [MVP]" wrote:
>
>> 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" <JackWasserstein@discussions.microsoft.com>
>> schreef in
>> bericht news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
>> >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"]);
>> >
>>
>>
>>

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" <JackWasserstein@discussions.microsoft.com> wrote in
message news:828E0631-6E88-4265-9149-E0B13FC6F5D2@microsoft.com...
> 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 [MVP]" wrote:
>
>> 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" <JackWasserstein@discussions.microsoft.com> schreef in
>> bericht news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
>> >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"]);
>> >
>>
>>
>>

What's the table's schema look like?

Robert

"Jack Wasserstein" <JackWasserstein@discussions.microsoft.com> wrote in
message newsC4E9E35-BB91-44E7-8C33-71E357241AD4@microsoft.com...
> 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 [MVP]" wrote:
>
>> 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" <JackWasserstein@discussions.microsoft.com> wrote in
>> message news:828E0631-6E88-4265-9149-E0B13FC6F5D2@microsoft.com...
>> > 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 [MVP]" wrote:
>> >
>> >> 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" <JackWasserstein@discussions.microsoft.com> schreef
>> >> in
>> >> bericht news:CF7F1B9E-5F58-4D13-AC0F-27F54D2E4053@microsoft.com...
>> >> >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"]);
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

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

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.
--------------------
"Cor Ligthert [MVP]" <notmyfirstname@planet.nl> wrote in message
news:%23hlliipLHHA.2028@TK2MSFTNGP03.phx.gbl...
> 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
>
>