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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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)?
Click to expand...

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

Jon
 
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.
 
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.
Click to expand...

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.
Click to expand...

So instead of running that line, run:

da.UpdateCommand = "UPDATE BLAH BLAH";

except using the appropriate SQL.

Jon
 
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!
 
Since the table does not have any keys, I can't see how I can use UPDATE
Click to expand...

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
 
Back
Top