When you use sqlcommandbuilder to update dataset, the 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 ");
The structure of emailme is as follows:
Email nvarchar 100
Validflag int
When you run this code, the error "selectcommand that does not return any key column information does not support dynamic SQL generation of updatecommand" occurs. I thought about it because the primary key field is not defined in the emailme table, so sqlcommandbuilder cannot automatically generate the required updatecommand for sqldataadapter. Modifying the definition of a table can certainly solve the problem of defining the email field as the primary key. But because there are too many tables in the database, it is almost impossible to modify them one by one. Is there any other way?
Search on the Internet, found a network name "Blue ideal" friend gave a solution (http://www.blueidea.com/tech/program/2004/1761.asp), hurry to give a 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 = emailadd. Tables ["Address"];
Mydt. primarykey = new datacolumn [] {mydt. Columns ["email"]};
... // Modify myds data
Emailadapter. Update (emailadd, "Address ");
The results are still the same! I don't know why (it's still being studied ). Instead of reading msdn, I found the method for defining updatecommand. As a result, the problem is solved as follows:
........
String emailsql = "select email, validflag from emailme ";
Dataset emailadd = new dataset ();
Sqldataadapter emailadapter = new sqldataadapter (emailsql, myconn );
Sqlcommandbuilder cb = new sqlcommandbuilder (emailadapter );
Sqlcommand upcmd = new sqlcommand ("Update [" + strtablename + "] SetValidflag = @ validflagWhereEmail = @ email ", myconn);
Upcmd. Parameters. Add ("@ validflag", sqldbtype. Int, 8, "validflag ");
Upcmd. Parameters. Add ("@ email", sqldbtype. nvarchar, 100, "email ");
Emailadapter. updatecommand = upcmd;
Emailadapter. Fill (emailadd, "Address ");
Myconn. Close ();
... // Modify myds data
Emailadapter. Update (emailadd, "Address ");
To sum up, there are two solutions to this problem:
1. Modify the table definition and define a primary key;
2. Specify updatecommand (deletecommand, insertcommand should be the same) for sqldataadapter );
3. Other methods I don't know. What do blue ideal friends' articles make sense? The Code also makes sense. Maybe I still don't understand it? Continue learning!