none
Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information RRS feed

  • Question

  • Hi,

    I am using a DataTable to update a database.
    I have a datagridview binded to the DataTable .

    When a user does any changes like deleting a row in the datagridview, changing data etc., and thereafter click a save button,
    it has to save the changes into the database in sync with datagridview.


    But when i click the save button, I get the follwing exception.
    "Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information"

    Similarly when updating:
    "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information".

    Here is the code:

    *********************************************

    dataAdapter = new OleDbDataAdapter(query, connectionString);

    // query is something like: select * from datatable;

     

    cBuilder = new OleDbCommandBuilder(dataAdapter);

     

    dataTable = new DataTable();

     

    dataAdapter.Fill(dataTable);

     

    bindingSource = new BindingSource();

     

    bindingSource.DataSource = dataTable;

     

     

    viewGrid.DataSource = bindingSource;

    ********************************************

    I am using the following code to save changes:

    ********************************************

    dataAdapter.Update(dataTable);

    ********************************************


    I have searched a lot for a solution to this problem but failed.

    One thing I would like to mention is that the table in the select statement in the database
     does have a primary key.

    Please suggest !!







    Neotricks
    • Moved by OmegaMan Wednesday, May 13, 2009 1:42 PM (From:Visual C# General)
    • Moved by Matt Meehan - MSFTModerator Wednesday, May 13, 2009 4:43 PM Not ADO.NET Data Services related (From:ADO.NET Data Services)
    • Moved by Mark.Ashton - MSFTModerator Wednesday, May 13, 2009 8:07 PM Adapter & Dynamic SQL Generation (From:ADO.NET DataSet)
    Wednesday, May 13, 2009 11:11 AM

All replies

  • Well, it really does sound like there is no PK in the select.
    Make sure there is by running this:

                        OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                        OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                        
                        da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                        DataTable dt = new DataTable();
                        da.Fill(dt);
    
                        if (dt.PrimaryKey.Length > 0)
                        {
                            foreach (DataColumn key in dt.PrimaryKey)
                            {
                                Console.WriteLine(key.ColumnName);
                            }
                        }
                        else 
                        {
                            Console.WriteLine("No key(s)!");
                        }


    Also, you do not mention what the datasource is, if it is an Excel sheet for example, there will be no keys.
    "How to update an Excel worksheet using DataSet and the OleDbDataAdapter"
    http://blogs.msdn.com/spike/archive/2008/10/29/how-to-update-an-excel-worksheet-using-dataset-and-the-oledbdataadapter.aspx

    HTH
    //Michael

    This posting is provided "AS IS" with no warranties.
    Thursday, May 14, 2009 8:21 AM
  • Hi Michael,

    Thanks for the prompt reply.

    I am connecting to an oracle database using oledb.
    And I have checked the tables and they do have primary key.

    The exception suggests something related to primary key but I am still
    not able to figure out what the problem is ...




    Neotricks
    Thursday, May 14, 2009 10:14 AM
  • In my scenario, I am having a connection to an oracle database, but the table is mapped as a Synonym, not a "native" table.

    I assume that Synonyms do not "forward" the primary key information.
     

    See me working: www.magerquark.de
    Monday, September 14, 2009 10:39 AM
  • I now managed to solve it by using the ODP.NET classes (like OracleConnection, OracleCommandBuilder, etc.) as described in this article.

    The seem to be able to go through the synonym to the remote DB and actually query for the primary key (all done automatically).
     

    See me working: www.magerquark.de
    Tuesday, September 15, 2009 2:23 PM