Exception: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key "

Question

Hey everyone,

 

I have a datagridview which is binding my database.

 

Here is part of the sourcecode:

 

Code Snippet

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

namespace MyProject

{

public partial class Form1 : Form

{

private SqlDataAdapter dAdapter;

private SqlCommandBuilder cBuilder;

private DataTable dTable;

private BindingSource bSource;

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

//create the connection string

string connString = "MyStringConnection";

//create the database query

string query = "SELECT * FROM TABLE";

//create an OleDbDataAdapter to execute the query

dAdapter = new SqlDataAdapter(query, connString);

//create a command builder

cBuilder = new SqlCommandBuilder(dAdapter);

//create a DataTable to hold the query results

dTable = new DataTable();

//fill the DataTable

dAdapter.Fill(dTable);

//BindingSource to sync DataTable and DataGridView

bSource = new BindingSource();

//set the BindingSource DataSource

bSource.DataSource = dTable;

//set the DataGridView DataSource

dgw.DataSource = bSource; //dgw is the DataGridView

}

private void button1_Click(object sender, EventArgs e)

{

dAdapter.Update(dTable); //Here is the problem

}

}

}

 

 

If I try to delete or update a row in my DataGrigView (dgw), I get two kinds of exceptions:

 

Deleting Exception: "Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information."
Updating Exception: "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."

 

Anyone can help me, please?

 

Answer 1

I'm not an expert at using data adapters and data tables, but I'm pretty sure that those exceptions mean that your select query needs to return the primary key of the table.  If you table doesn't have a primary key, you need to set one.

 

Otherwise, you should create your DeleteCommand and UpdateCommand manually and assign them to the respective properties.

 

Chris

 

 

 

Answer 2

  Ensure that your table consists of a primary key.

Visit http://www.articlescloud.com/ for worked examples.

Answer 3

Make sure sql table has primary key.

Answer 4

heyy thanx man for the solution... I was stuck in the same problem and now got resolved after 2 hrs. 

Answer 5

Thanks a lot , Finally i was able to make the update, Thanks Again.

Answer 6

unlike Insert , update needs a primary key for at least one of the column in your database. Make sure you 've set primary key to one of the columns with proper increments.

It seems that there is a bug in my database version at least . I used Microsoft Visual Studio Professional 2008 and Microsoft SQL 2005 express (lates service pack though) running on Windows 7.
However if you first create the databse and insert the data and later you add primary key it does not work. at least on my system.
You need to recreate the database set the primary key first then add your data and it's done.

DataAdapter.Update (DataSet, "tablename");

good luck.

Answer 7

Thanks Chris!  The use of a primary key worked for me in trying to delete records. I suppose the information you supplied is to be found somewhere in that thick MSDN forest but your help was much more direct.
Thanks again,
JimF, Toledo

Answer 8

or, you choise Event of Datagridview1 is UserDeletingRow; When user choise entire row then press delete key:

//the Table in Database must have a Primary key.

//OleDbCommandBuilder oComBuilder 

//DataTable dtt 

private void dataGridView1_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)

        {

            if (MessageBox.Show("Are you sure delete this row?\r\n - Click Yes will always delete in database" + 

                "\r\n - Click No would stop the execution Delete ", "Warning!!", 

                MessageBoxButtons.YesNo, MessageBoxIcon.Warning).Equals(DialogResult.No))

                e.Cancel = true; 

            else

            {                oComBuilder.DataAdapter.Update(dtt);            }

        }

good luck

Answer 9

Thanks buddy, Your Solvations was working for me.

Answer 10

Thankyou friend.  working fine.

Answer 11

Thanks Chris. I was stuckuped long time in this exception. Because of your valuable comment I got solution.

Answer 12

Got it.. Thanks..

Answer 13

Tnx a tonn buddy.

Answer 14

thanks

Answer 15

        public void DbSaveDatatableUpdate(string databaseName, string tableName, System.Data.DataTable data)
        {
            String connectionString = connectionStringCalico + "DATABASE=" + databaseName + ";";
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
            string identity = getIdentityField(tableName, databaseName);
            foreach (System.Data.DataRow row in data.Rows)
            {
                string sql = "UPDATE " + tableName + " SET ";
                foreach (System.Data.DataColumn column in data.Columns)
                {
                    if (!column.ColumnName.Equals(identity))
                    {
                        if (column.DataType == System.Type.GetType("System.Int16") || column.DataType == System.Type.GetType("System.Int32") || column.DataType == System.Type.GetType("System.Int64") || column.DataType == System.Type.GetType("System.Decimal"))
                            sql += column.ColumnName + " = " + row[column];
                        else
                            sql += column.ColumnName + " = '" + row[column] + "'";
                        if (data.Columns.IndexOf(column) < data.Columns.Count - 1)
                            sql += ", ";
                    }
                }
                sql += " WHERE " + identity + " = " + row[identity];
                System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql, connection);
                connection.Open();
                command.ExecuteNonQuery();
                connection.Close();
            }
        }

        private String getIdentityField(String tablename, string databaseName)
        {
            //========================
            String connectionString = connectionStringCalico + "DATABASE=" + databaseName + ";";
            String queryString = "SELECT all_columns.name " +
                                 "FROM sys.tables " +
                                 "INNER JOIN sys.all_columns " +
                                 "ON tables.object_id = all_columns.object_id " +
                                 "WHERE tables.name = '" + tablename + "' AND " +
                                 "all_columns.is_identity = 1";
            System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(connectionString);
            System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);
            System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter();
            adapter.SelectCommand = command;

            command.Connection.Open();
            //GET DATA FROM DATABASE
            System.Data.DataTable trickleFeedData = new System.Data.DataTable();
            adapter.Fill(trickleFeedData);
            command.Connection.Close();
            //=========================
            if (trickleFeedData.Rows.Count > 0)
                return trickleFeedData.Rows[0].ItemArray[0].ToString();
            else
                return null;

        }

This work around seemed to work.

Answer 16

I just wanted to say thanks for the information and solution...been working on the same problem for days, recreated the table, set the Primary key and now good to go....thx again!