Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand

Published on Monday, September 19, 2011

There was a coder wrote the following code about a SQL Server saving operation:

public static bool SaveToDatabase(string strTab, string strSql, SqlConnection Sqlcn, DataSet ds)     
{      
    SqlCommand comm = new SqlCommand();      
    comm.CommandText = strSql;      
    comm.Connection = Sqlcn;      
    comm.CommandType = CommandType.Text;_
  
    SqlDataAdapter sda = new SqlDataAdapter(comm);     
    SqlCommandBuilder scb = new SqlCommandBuilder(sda); //this sentence is key!_
  
    try     
    {      
        DataSet ds1 = ds;      
        sda.Update(ds1, strTab);      
        ds.AcceptChanges();_
  
       return true;     
    }      
    catch (System.Exception e)      
    {      
        MessageBox.Show("error is happen when Saving  data to Database:" + e.ToString());      
        return false;      
    }      
    finally      
    {      
        sda.Dispose();      
    }      
}

We got the following error when we ran:

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

NoPrimaryKey00

The reason is that we did not set a Primary key for related data table.

When we use data adapters and data tables, the select query needs to return the primary key of the table. If there is no key, then we should set one. Otherwise, we should create the DeleteCommand and UpdateCommand manually and assign them to the respective properties.