Problems with primary key
-
I am using a dataset on a table with an already defined primary key in an sql server 2000:
SqlCommand currentControllerCommand = new SqlCommand(currentControllerSQL,IDGSConnection); SqlDataAdapter currentControllerAdapter = new SqlDataAdapter(currentControllerCommand); DataSet currentControllerDS = new DataSet(); currentControllerAdapter.Fill(currentControllerDS,collector["TargetUsersTable"].ToString());When later I try to update the table with the changes on the dataset:SqlCommandBuilder cb = new SqlCommandBuilder(currentControllerAdapter); currentControllerAdapter = cb.DataAdapter; currentControllerAdapter.Update(currentControllerDS,collector["TargetUsersTable"]);It tells me the following:Error submitting changes to the Collector Database. Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.Why does it tell me that if I have already the primary key defined in the primary table? Thanks for your thoughts! mrclash -
I am using a dataset on a table with an already defined primary key in an sql server 2000:
SqlCommand currentControllerCommand = new SqlCommand(currentControllerSQL,IDGSConnection); SqlDataAdapter currentControllerAdapter = new SqlDataAdapter(currentControllerCommand); DataSet currentControllerDS = new DataSet(); currentControllerAdapter.Fill(currentControllerDS,collector["TargetUsersTable"].ToString());When later I try to update the table with the changes on the dataset:SqlCommandBuilder cb = new SqlCommandBuilder(currentControllerAdapter); currentControllerAdapter = cb.DataAdapter; currentControllerAdapter.Update(currentControllerDS,collector["TargetUsersTable"]);It tells me the following:Error submitting changes to the Collector Database. Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.Why does it tell me that if I have already the primary key defined in the primary table? Thanks for your thoughts! mrclashThis is basically saying you can't autogenerate the command without primary key information. If you want to use the CommandBuilder, you need to be aware of it's limitations. The CommandBuilder classes can be used to build update commands for single-table select queries that include at least one unique column. Your SQL command doesn;t include a unique column, so it cannot be used. Include the primary key in your select query, and it should work, as long as it is a single-table query. If probably looks something like this Select col1, col2, col3 from table1 where col1 = somevalue change it to Select pkey, col1, col2, col3 from table1 where col1 = somevalue If it is not a single-table query, you will need to build your own insert, update, and delete queries using the InsertCommand, UpdateCommand, and DeleteCommand properties.
-
I am using a dataset on a table with an already defined primary key in an sql server 2000:
SqlCommand currentControllerCommand = new SqlCommand(currentControllerSQL,IDGSConnection); SqlDataAdapter currentControllerAdapter = new SqlDataAdapter(currentControllerCommand); DataSet currentControllerDS = new DataSet(); currentControllerAdapter.Fill(currentControllerDS,collector["TargetUsersTable"].ToString());When later I try to update the table with the changes on the dataset:SqlCommandBuilder cb = new SqlCommandBuilder(currentControllerAdapter); currentControllerAdapter = cb.DataAdapter; currentControllerAdapter.Update(currentControllerDS,collector["TargetUsersTable"]);It tells me the following:Error submitting changes to the Collector Database. Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.Why does it tell me that if I have already the primary key defined in the primary table? Thanks for your thoughts! mrclashWhat does the select command look like?
Jim Conigliaro jconigliaro@ieee.org
http://www.jimconigliaro.com -
What does the select command look like?
Jim Conigliaro jconigliaro@ieee.org
http://www.jimconigliaro.comSELECT ID,Email,New FROM Collector_UsersWhere ID is a primary key...