469,282 Members | 1,732 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

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

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.
Jun 12 '07 #1
5 19604
On Jun 12, 9:09 am, AA2e72E <AA2e...@discussions.microsoft.comwrote:
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

Jun 12 '07 #2
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.
Jun 12 '07 #3
On Jun 12, 10:36 am, AA2e72E <AA2e...@discussions.microsoft.com>
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

Jun 12 '07 #4
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!
Jun 12 '07 #5
On Jun 12, 10:58 am, AA2e72E <AA2e...@discussions.microsoft.com>
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

Jun 12 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Martin Mrazek | last post: by
reply views Thread by Hugo Ferreira | last post: by
5 posts views Thread by Markus Broy | last post: by
3 posts views Thread by Jawad Rehman | last post: by
3 posts views Thread by Jawad Rehman | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.