locked
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?

     

    Friday, May 02, 2008 5:05 PM

Answers

  • 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

     

     

     

    Friday, May 02, 2008 9:37 PM

All replies

  • 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

     

     

     

    Friday, May 02, 2008 9:37 PM
  •   Ensure that your table consists of a primary key.

    Visit http://www.articlescloud.com/ for worked examples.
    Sunday, March 15, 2009 6:03 AM
  • Make sure sql table has primary key.
    Friday, April 03, 2009 7:13 PM
  • heyy thanx man for the solution... I was stuck in the same problem and now got resolved after 2 hrs. 
    Wednesday, September 23, 2009 12:56 PM
  • Thanks a lot , Finally i was able to make the update, Thanks Again.
    Monday, February 15, 2010 1:21 AM
  • 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.
    • Proposed as answer by Sachin thakare Tuesday, February 28, 2012 12:25 PM
    Saturday, February 20, 2010 10:40 AM
  • 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
    Wednesday, March 03, 2010 10:43 PM
  • 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
    Wednesday, May 26, 2010 3:45 PM
  • Thanks buddy, Your Solvations was working for me.
    Thursday, February 10, 2011 7:26 AM
  • Thankyou friend.  working fine.
    Monday, April 04, 2011 4:20 AM
  • Thanks Chris. I was stuckuped long time in this exception. Because of your valuable comment I got solution.
    Saturday, July 16, 2011 6:39 PM
  • Got it.. Thanks..
    Thursday, December 22, 2011 4:53 AM
  • Tnx a tonn buddy.
    Friday, April 20, 2012 10:21 AM
  • thanks
    Friday, May 25, 2012 7:33 AM
  •         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.


    Friday, May 25, 2012 12:23 PM
  • 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!
    Friday, September 14, 2012 1:00 AM