我觉得是很基础的问题，有没有人能给我个正确的答复？

[minajo21]

是vb的代码：
<WebMethod()> _
Public Function TripCharge_Update(ByVal inDS As DataSet) As Boolean
Dim strConn As String
Dim strSQL As String

strConn = "..."
Dim sqlConn As SqlConnection = New SqlConnection(strConn)
Dim tmpDA As SqlDataAdapter = New SqlDataAdapter

'insert
strSQL = "INSERT INTO Trip_Charge..."
tmpDA.InsertCommand = New SqlCommand(strSQL, sqlConn)
tmpDA.InsertCommand.Parameters.Add("@company", ...)

'update
...
'delete
...
'select
...

Dim objBuilder As New SqlCommandBuilder(tmpDA)
Try
tmpDA.UpdateCommand = objBuilder.GetUpdateCommand()
tmpDA.DeleteCommand = objBuilder.GetDeleteCommand()
tmpDA.InsertCommand = objBuilder.GetInsertCommand()

tmpDA.TableMappings.Add("Trip_Charge", inDS.Tables(0).ToString)
tmpDA.Update(inDS, "Trip_Charge")
Return True
Catch ex As Exception
Return False
Finally
sqlConn.Close()
objBuilder = Nothing
tmpDA = Nothing
sqlConn = Nothing
tmpDB = Nothing
End Try
End Function


进参inDS肯定没问题，单不调试也没错，可是数据库就是没更新：（
谢谢各位

可以到这个贴子报到领分：
http://expert.csdn.net/Expert/topic/2247/2247500.xml?temp=.2394525

[雪狼1234567]

看一下如下：
首先取得数据,放到DataGrid里

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("server=localhost;database=northWind;uid=sa;password=110");
conn.Open();
System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter("select * from student",conn);
dt = new System.Data.DataSet();
da.Fill(dt,"student");

然后绑定数据集和DataGrid
DataGrid.SetDataBinding(dt,"student");
如果需要，可以绑定TextBox来作录入，而用DataGrid显示
this.textBox16.DataBindings.Add("Text",dt,"student.stuno");
然后进行数据的操作如：
增加:
this.BindingContext[dt,"student"].AddNew();
删除:
this.BindingContext[dt,"student"].RemoveAt(this.BindingContext[dt,"student"].Position);
最后把结果写回数据库:


// sqlInsertCommand1
//
this.sqlInsertCommand1.CommandText = "INSERT INTO student(stuno, name) VALUES (@stuno, @name)";
this.sqlInsertCommand1.Connection = this.conn;
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stuno", System.Data.SqlDbType.VarChar, 4, "stuno"));
this.sqlInsertCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 50, "name"));
//
// sqlUpdateCommand1
//
this.sqlUpdateCommand1.CommandText = "UPDATE student SET stuno = @stuno, name = @name WHERE (stuno = @Original_stuno)";
this.sqlUpdateCommand1.Connection = this.conn;
this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@stuno", System.Data.SqlDbType.VarChar, 4, "stuno"));
this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@name", System.Data.SqlDbType.VarChar, 50, "name"));
this.sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_stuno", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "stuno", System.Data.DataRowVersion.Original, null));

// sqlDeleteCommand1
//
this.sqlDeleteCommand1.CommandText = "DELETE FROM student WHERE (stuno = @Original_stuno)";
this.sqlDeleteCommand1.Connection = this.conn;
this.sqlDeleteCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Original_stuno", System.Data.SqlDbType.VarChar, 4, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "stuno", System.Data.DataRowVersion.Original, null));

this.sqlDa.DeleteCommand = this.sqlDeleteCommand1;
this.sqlDa.InsertCommand = this.sqlInsertCommand1;
this.sqlDa.UpdateCommand = this.sqlUpdateCommand1;
try
{
sqlDa.Update(dt.GetChanges,"student");
return true;
}
catch(System.Data.SqlClient.SqlException ex)
{

return false;
}
finally
{
conn.Close();
}

[雪狼1234567]

还有你的表要有主键，否则的话用自动生成 的更新语句有问题

[minajo21]

我的表有主键

[minajo21]

to snof(雪狼)

我的思路是这样的，记录然后给前段操作，结果是一个dataset，再把这个dataset传给这个函数。

这样有没有问题，恨你的思路是否有不同？

[storm97]

你用CommandBuilder的时候要给DataAdapter的SelectCommand定义一下，因为CommandBuilder是根据Select命令来自动生成其他的命令的。

[CMIC]

Public Function TripCharge_Update(ByVal inDS As DataSet) As Boolean
Dim strConn As String
Dim strSQL As String

strConn = "..."
Dim sqlConn As SqlConnection = New SqlConnection(strConn)
Dim tmpDA As SqlDataAdapter = New SqlDataAdapter

'insert
strSQL = "Select * from Trip_Charge..."
tmpDA.InsertCommand = New SqlCommand(strSQL, sqlConn)
tmpDA.InsertCommand.Parameters.Add("@company", ...)
DataSet cmicDs=new DataSet();
tmpDA.Fill(cmicDs);
'update
...
'delete
...
'select
...

Dim objBuilder As New SqlCommandBuilder(tmpDA)
Try
tmpDA.UpdateCommand = objBuilder.GetUpdateCommand()
tmpDA.DeleteCommand = objBuilder.GetDeleteCommand()
tmpDA.InsertCommand = objBuilder.GetInsertCommand()

cmicDs.Merge(inDS);
tmpDA.Update(cmicDs)
Return True
Catch ex As Exception
Return False
Finally
sqlConn.Close()
objBuilder = Nothing
tmpDA = Nothing
sqlConn = Nothing
tmpDB = Nothing
End Try
End Function
这是我的写法,不知我理解的对不对.

[minajo21]

楼上的，我好像明白了

[minajo21]

to CMIC(大象)

我觉得你很有道理，但是，还是不对：（

我再仔细查查看

[minajo21]

还是没搞定

[xixigongzhu]

斑竹先看两个例子：
public DataSet CreateCmdsAndUpdate(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName)
{
OleDbConnection myConn = new OleDbConnection(myConnection);
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter();
myDataAdapter.SelectCommand = new OleDbCommand(mySelectQuery, myConn);
OleDbCommandBuilder custCB = new OleDbCommandBuilder(myDataAdapter);

myConn.Open();

DataSet custDS = new DataSet();
myDataAdapter.Fill(custDS);

//code to modify data in dataset here

myDataAdapter.Update(custDS, myTableName);

myConn.Close();

return custDS;
}

[xixigongzhu]

public void Run()
{

SqlConnection myConnection = new SqlConnection("server=(local)\\NetSDK;Integrated Security=SSPI;database=northwind");
SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter("select * from customers", myConnection);
SqlDataAdapter mySqlDataAdapter1 = new SqlDataAdapter("select * from orders", myConnection);

// Restore database to it's original condition so sample will work correctly.
Cleanup();

try
{
DataSet myDataSet = new DataSet();
DataRow myDataRow;

// Create command builder. This line automatically generates the update commands for you, so you don't
// have to provide or create your own.
SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySqlDataAdapter);

// Set the MissingSchemaAction property to AddWithKey because Fill will not cause primary
// key & unique key information to be retrieved unless AddWithKey is specified.
mySqlDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
mySqlDataAdapter1.MissingSchemaAction = MissingSchemaAction.AddWithKey;

mySqlDataAdapter.Fill(myDataSet,"Customers");
Console.WriteLine("Loaded data from Customers table into dataset.");

mySqlDataAdapter1.Fill(myDataSet,"Orders");
Console.WriteLine("Loaded data from Orders table into dataset.");

// ADD RELATION
myDataSet.Relations.Add("CustOrders",myDataSet.Tables["Customers"].Columns["CustomerId"],myDataSet.Tables["Orders"].Columns["CustomerId"]);

// EDIT
myDataSet.Tables["Customers"].Rows[0]["ContactName"]="Peach";

// ADD
myDataRow = myDataSet.Tables["Customers"].NewRow();
myDataRow["CustomerId"] ="NewID";
myDataRow["ContactName"] = "New Name";
myDataRow["CompanyName"] = "New Company Name";
myDataSet.Tables["Customers"].Rows.Add(myDataRow);
Console.WriteLine("Inserted new row into Customers.");

// Update Database with SqlDataAdapter
mySqlDataAdapter.Update(myDataSet, "Customers");
Console.WriteLine("Sent Update to database.");

Console.WriteLine("DataSet processing has completed successfully!");
}
catch(Exception e)
{
Console.WriteLine(e.ToString());
}
}

[minajo21]

晕，msdn里的例子

[w915]

感谢 学习中

[minajo21]

晕，msdn里的例子

[xixigongzhu]

然后再看看这个文档：
对于在运行时动态指定 SelectCommand（例如通过采用用户提供的文本命令的查询工具）的情况，可能无法在设计时指定合适的 InsertCommand、UpdateCommand 或 DeleteCommand。如果 DataTable 映射到单个数据库表或从单个数据库表生成，则可以利用 CommandBuilder 对象自动生成 DataAdapter 的 DeleteCommand、InsertCommand 和 UpdateCommand。

为了自动生成命令，必须设置 SelectCommand 属性，这是最低的要求。SelectCommand 所检索的表架构确定自动生成的 INSERT、UPDATE 和 DELETE 语句的语法。

为了返回构造插入、更新和删除命令所必需的元数据，CommandBuilder 必须执行 SelectCommand。因此，必须额外经历一次到数据源的行程，这可能会降低性能。若要实现最佳性能，请显式指定命令而不是使用 CommandBuilder。

SelectCommand 还必须返回至少一个主键或唯一列。如果不存在任何主键或唯一列，则将生成 InvalidOperation 异常，并且不会生成命令。

当与 DataAdapter 关联时，CommandBuilder 将自动生成 DataAdapter 的 InsertCommand、UpdateCommand 和 DeleteCommand 属性（如果它们是空引用）。如果已存在用于某属性的 Command，则将使用现有 Command。

通过联接两个或更多个表来创建的数据库视图不会被视为单个数据库表。在这种情况下，将无法使用 CommandBuilder 来自动生成命令，而需要显式地指定命令。有关显式设置命令以便将对 DataSet 的更新解析回数据源的信息，请参见使用 DataAdapter 和 DataSet 更新数据库。

您可能需要将输出参数映射回 DataSet 的更新行。一项常见的任务是从数据源中检索自动生成的标识字段或时间戳的值。默认情况下，CommandBuilder 不会将输出参数映射到更新行中的列。在这种情况下，将需要显式指定命令。

从以上可以很容易的看出问题可能出在"为了自动生成命令，必须设置 SelectCommand 属性，这是最低的要求。"，我看斑竹就没有设置这个属性。斑竹不妨试一下。

[minajo21]

还是要谢谢你，我再仔细看看

[xixigongzhu]

斑竹，仔细看看上面那段文档，很有帮助的，这个来自：
ms-help://MS.NETFrameworkSDKv1.1.CHS/cpguidenf/html/cpconautomaticallygeneratedcommands.htm
如果不是2003中文版本，请截取cpguidenf/html/cpconautomaticallygeneratedcommands.htm

[nik_Amis]

public

[Sean918]

友情up

[wirte]

gz