Dynamic SQL generation for the UpdateCommand is not supported ...

[=?Utf-8?B?QUEyZTcyRQ==?=]

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

I am getting this error when updating a table bound to a datagridview (using
the OLEDataAdapter) when the table does not have a primary key; my code works
when the table does have primary key(s).

Is there a workaround for working with tables that do not have any primary
key(s)?

Thanks for your help.

[Jon Skeet [C# MVP]]

On Jun 12, 9:09 am, AA2e72E <[e-mail]:

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

I am getting this error when updating a table bound to a datagridview (using
the OLEDataAdapter) when the table does not have a primary key; my code works
when the table does have primary key(s).

Is there a workaround for working with tables that do not have any primary
key(s)?

Any reason you can't manually specify the appropriate update command
yourself?

Jon

[=?Utf-8?B?QUEyZTcyRQ==?=]

Thanks Jon; I did not know that I could and now that you have hinted that it
is possible, I do not know how/where.

Here's my code:

public partial class Form1 : Form
{
static string DBTable = "TABLE1";
OleDbDataAdapter da = new OleDbDataAdapter(("SELECT * FROM " +
DBTable), "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\\db2.MDB");
DataSet ds = new DataSet();
OleDbCommandBuilder builder = new OleDbCommandBuilder();

public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
da.Fill(ds, DBTable);
dataGridView1.DataSource = ds.Tables[DBTable].DefaultView;
}

private void button1_Click(object sender, EventArgs e)
{
OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
da.UpdateCommand = builder.GetUpdateCommand();

da.Update(ds.Tables[0].Select(null,null,DataViewRowState.CurrentRows));
}
}

The culprint line is

da.UpdateCommand = builder.GetUpdateCommand();

If my table has a primary key, it works fine and fails it it doesn't.

I 'd appreciate some clues. Thanks.

[Jon Skeet [C# MVP]]

On Jun 12, 10:36 am, AA2e72E <[e-mail]>
wrote:

Thanks Jon; I did not know that I could and now that you have hinted that it
is possible, I do not know how/where.

<snip>

The culprint line is

da.UpdateCommand = builder.GetUpdateCommand();

If my table has a primary key, it works fine and fails it it doesn't.

I 'd appreciate some clues. Thanks.

So instead of running that line, run:

da.UpdateCommand = "UPDATE BLAH BLAH";

except using the appropriate SQL.

Jon

[=?Utf-8?B?QUEyZTcyRQ==?=]

Thanks Jon.

Since the table does not have any keys, I can't see how I can use UPDATE; I
can see how I might do it i.e

DELETE FROM TABLE1 // drop all the records

then loop to

INSERT INTO TABLE1 (field list) Values(values)

every row in the datagrid view.

There must be a simpler way, surely!

[Jon Skeet [C# MVP]]

On Jun 12, 10:58 am, AA2e72E <[e-mail]>
wrote:

Since the table does not have any keys, I can't see how I can use UPDATE

Well, that entirely depends on exactly what you want to do. If you
really haven't got any way of identifying the row that you want to
update, then indeed you can't do the update, and you've got bigger
problems than ADO.NET at that point - your table is badly designed.

If, however, there *is* some way of identifying the row, but it hasn't
been set as a primary key (for whatever reason) then you can just use
that.

Jon