Update DataSet with SqlCommandBuilder, encounter "SelectCommand for UpdateCommand without returning any key column information" problem, key code is as follows (C #):
........ string emailSql = "select email, validFlag from emailMe"; DataSet emailAdd = new DataSet (); SqlDataAdapter emailAdapter = new SqlDataAdapter (emailSql, myConn); SqlCommandBuilder cb = new SqlCommandBuilder (emailAdapter); emailAdapter. Fill (EmailAdd, "Address"); MyConn.close (); ... // Modify MYDS Data EmailAdapter.Update (Emailadd, "Address"); Emailme's structure is like this: email nvarchar 100validflag Int
Run this code, "" SELECTCOMMAND does not support UpdateCommand "error for updateCommand without returning any key column information. Citing it, because the primary key field is not defined in the emailme table, so SQLCommandBuilder cannot automatically generate the required UpdateCommand for SqlDataAdapter. Modify the definition of the table, define the Email field as the primary key problem. It can of course solve it, but because the library is too much, it is almost impossible to modify one by one. Is there any other way?
I have searched online and found a friend who "blue ideal" to give a solution (http://www.blueidea.com/tech/program/2004/1761.asp), hurry to try:
........ string emailSql = "select email, validFlag from emailMe"; DataSet emailAdd = new DataSet (); SqlDataAdapter emailAdapter = new SqlDataAdapter (emailSql, myConn); SqlCommandBuilder cb = new SqlCommandBuilder (emailAdapter); emailAdapter. Fill (EmailAdd, "Address"); MyConn.close (); DataTable mydt = emaildd.tables ["address"]; mydt.primarykey = new datacolumn [] {mydt.columns ["email"]}; ... .. // Modify MYDS data EmailAdapter.Update (EmailAdd, "Address"); the result is the same problem! I don't know why (still continuing research). Helpless over MSDN, discovered the method of defining updateCommand, resulting, question is to solve this: ....... String emailsql = "select email, validflag from emailme"; dataset emaildd = new dataset (); sqldataadapter emailadapter = new SqlDataAdapter (emailSql, myConn); SqlCommandBuilder cb = new SqlCommandBuilder (emailAdapter); SqlCommand upCmd = new SqlCommand ( "update [" strTableName "] set validFlag = @ validFlag where email = @ email", myConn); upCmd.Parameters. Add ("@ ValidFlag", SqldbType.int, 8, "ValidFlag"); Upcmd.Parameters.Add ("@ email", sqldbtype.nvarchar, 100, "email"); emailapter.UpdateCommand = Upcmd; EmailAdapter.Fill EmailAdd, "Address"); MyConn.close (); ... // Modify MYDS Data EmailApterapter.Update (EmailAdd, "Address"); summarize, to solve the problem in such problems:
1. Modify the definition of the table, define a primary key; 2. Specify UpdateCommand for SqlDataAdapter (deleteCommand, INSERTCOMMAND); 3. I don't know other ways, how to see the blue ideal friend's article, the code is also It is very reasonable, maybe I still don't understand? keep studing!
