CharLieYv
asked on
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
I've tried almost everything
System.Data.SqlClient.SqlConnection Data_Connect = new System.Data.SqlClient.SqlConnection(conn_string);
Data_Connect.Open();
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;
Login_Set.Tables["ongs"].Rows.Add(New_User);
//New_User.AcceptChanges();
//New_User.BeginEdit();
//New_User.EndEdit();
Login_Adapter.Update(Login_Set,"ongs");
Data_Connect.Close();
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.
ASKER
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;
Can you attach a screen shot from the table designer, so that I can see the primary key?
ASKER
The table was already created. I'm working on top of it (using it) so I cant create a new table or modify it.
screen.JPG
screen.JPG
No, I meant from the table designer.
Example from AdventureWorks sample table:
Table-Designer----Primary-Key-In.png
Example from AdventureWorks sample table:
Table-Designer----Primary-Key-In.png
ASKER
Here it is...
screen.JPG
screen.JPG
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!