Error:Dynamic sql generation for the update command is not supported against a select command that does not return any key column information

Question

0.00/5 (No votes)

See more:

In my code i try to update database when changes done in datagridview,for this i have added
Dim cb As New SqlCommandBuilder(adp)

adp.Update(ds, "tablename")
At adp.update(ds,"tablename")am getting Error:Dynamic sql generation for the update command is not supported against a select command that does not return any key column information
May i know the reason plz..

Posted 22-May-11 18:28pm

Member 7940184

Updated 4-Jul-20 18:13pm

Add a Solution

Comments

mukehs tyagi 31-Aug-12 3:13am

main droup down ki value ko datagridview main show krakar us vlue ko update karna hai in C# plz send me code

4 solutions

Solution 2

You Have to Mention primary key in your Table.then before executing update command of dataApdapter you have to set update command of dataAdapter with CommandBuilder object like this 

--- create connection ----
SqlDataAdapter adp = new SqlDataAdapter();
SqlCommandBuilder bldr;

bldr=new SqlCommandBuilder(adp);
adp.UpdateCommand = bldr.GetUpdateCommand();
adp.Update(ds.Tables[0]);

Posted 29-May-11 17:45pm

mayankkothari143

Solution 5

yes, this problem is resolved by update the primary key in your table because of data update and delete by unique data column.

Posted 23-Nov-15 3:19am

shetty786

Comments

Richard Deeming 23-Nov-15 10:12am

This question was asked, answered and solved over FOUR AND A HALF YEARS AGO!

Solution 6

I've spent two days to find some way to workaround. Lucky that I could get it done. The idea is to add a dump primary key in the DataTable, this new dump column does not affect the real database table. Here's my code sample

Dim currentRow = m_DataTable.Rows(m_RowPosition) Dim tmpRows() As DataRow = {currentRow}

Dim cmdBuilder As OracleCommandBuilder = New OracleCommandBuilder(m_Adapter)

       **If m_DataTable.PrimaryKey.Count = 0 Then
           m_DataTable.Columns.Add(New DataColumn("_ID_", System.Type.GetType("System.String")))
           m_DataTable.Columns("_ID_").Unique = True
       End If**

       m_Adapter.Update(tmpRows)

Note: The currentRow is the row is being edited . Please consider carefully to apply this tip because it might update all data of table. It's suitable for case your table has only one row ( some kind of configuration data )

Posted 4-Jul-20 18:13pm

Tuan tuanv2t

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2011-05-22T18:42:00

Solution 1

The table you're running the SELECT against doesn't have a primary key column. It cannot create any other UPDATE or DETELE statements because you have no way of uniquely identifying every single record in your table.

Posted 22-May-11 18:42pm

Dave Kreskowiak

Comments

Member 7940184 23-May-11 0:56am

But my table have primary key already and am using identity for autoincrement that value.is it the problem for getting those error.

Dave Kreskowiak 23-May-11 7:25am

Then your SELECT query isn't returning the primary key column. The column has to be MAKRED as the primary key. You cannot just assign values yourself to this column, treat is like a key column and expect it to work.

Thakur Varun Singh 18-May-13 3:57am

Ya Dave you are absolutely wright

PotatoJam 25-May-12 3:32am

thanks

ernestmachado 29-Aug-12 6:46am

but Why Primery Key Needed Without Primary Key I Cannot Update Or Delete row in Table?????

Dave Kreskowiak 29-Aug-12 8:03am

I said that in my post. Without a Primary Key column being returned there is no way for the CommandBuilder to identify every record in a table uniquely.

RadumixMatsad 19-May-13 4:26am

you so excellent dave, that's it.

dpokiya 3-Feb-14 6:56am

right, you are so talented...

O Zotaj 12-Jun-14 9:40am

Great :)