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

Hello, I'm trying to update a DataSet using the DataAdapter Update method. However I get the error "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information." when I try.  In the original SQL database a primary key is set. But somehow the sqlcommandbuilder doesn't recognize it. Is there a way to manually set the pk in the dataset... or pointing the dataset to the original pk...?
I've tried almost everything
System.Data.SqlClient.SqlConnection Data_Connect = new System.Data.SqlClient.SqlConnection(conn_string);
        System.Data.SqlClient.SqlDataAdapter Login_Adapter = new System.Data.SqlClient.SqlDataAdapter("SELECT * FROM ongs", Data_Connect);
        Login_Adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
        System.Data.SqlClient.SqlCommandBuilder Cmd = new System.Data.SqlClient.SqlCommandBuilder(Login_Adapter);
        //Login_Set.Tables["ongs"].PrimaryKey[0] = Login_Set.Tables["ongs"].Columns[0];
        DataRow New_User = Login_Set.Tables["ongs"].NewRow();
        New_User["Email"] = TextBox3.Text;
        New_User["password"] = TextBox4.Text;
        New_User["Organizacion"] = TextBox6.Text;
        New_User["Org_siglas"] = TextBox7.Text;
        New_User["first_name"] = TextBox8.Text;
        if (DropDownList1.SelectedIndex.ToString().Equals("Masculino"))
            New_User["Sexo"] = "M";
        else if (DropDownList1.SelectedIndex.ToString().Equals("Femenino"))
            New_User["Sexo"] = "F";
        New_User["pais"] = TextBox10.Text;
        New_User["fax"] = TextBox11.Text;
        New_User["phone"] = TextBox12.Text;
        New_User["Direccion"] = TextBox13.Text;

Open in new window

Who is Participating?
Bob LearnedConnect With a Mentor Commented:
If you look at the screen shot that I attached, you will notice a yellow key, indicating the primary key column.  That is missing from the 'key_' column in your screen shot.   That means that you don't have the column marked as a primary key, and would explain the error that you get.
Bob LearnedCommented:
There isn't any way to override that behavior, but with SQL Server, the SqlCommandBuilder shouldn't have any problem finding a primary key, if you are getting data from the right table.
CharLieYvAuthor Commented:
For some reason the commandbuilder doesn't recognize the primary key in the column. When I try to manually specify a pk in the dataset as shown below, then it leaves the pk column null and I get an error(pk cant be null). I just want to add a new row to the database.
DataColumn[] keyColumn = new DataColumn[1];
        keyColumn[0] = Login_Set.Tables["ongs"].Columns["key_"];
        Login_Set.Tables["ongs"].PrimaryKey = keyColumn;

Open in new window

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Bob LearnedCommented:
Can you attach a screen shot from the table designer, so that I can see the primary key?
CharLieYvAuthor Commented:
The table was already created. I'm working on top of it (using it) so I cant create a new table or modify it.
Bob LearnedCommented:
No, I meant from the table designer.

Example from AdventureWorks sample table:

CharLieYvAuthor Commented:
Here it is...
CharLieYvAuthor Commented:
Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.