DataSet保存不进去问题
'===为什么第一次可以更新进去,若上面有错退出,重新保存无法更新上去!
代码如下:
Imports System.Data.SqlClient
Public Class Form1
Dim SqlCnn As New SqlConnection("Data Source=.;Initial Catalog=pubs;User ID=sa;Password=++++")
Dim SqlDa As New SqlDataAdapter
Dim Dst As New DataSet1
Dim BakDst As New DataSet1
Dim StrSql As String
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If GetCBuilder(SqlCnn, "SELECT * FROM employee WHERE 1=0", SqlDa) = False Then Exit Sub
SqlDa.Fill(Dst.employee)
btnQuery_Click(btnQuery, New System.EventArgs)
End Sub
Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuery.Click
StrSql = "SELECT * FROM employee where emp_id='H-B39728F'"
SqlDa.SelectCommand = New SqlCommand(StrSql, SqlCnn)
Dst.employee.Clear()
SqlDa.Fill(Dst.employee)
End Sub
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Dim Dremployee As DataSet1.employeeRow
Dim Updateemployee As DataSet1.employeeDataTable
Dim Trans As SqlTransaction
If SqlCnn.State = ConnectionState.Closed Then
SqlCnn.Open()
End If
If Dst.employee.Rows.Count = 0 Then Exit Sub
Trans = SqlCnn.BeginTransaction
Try
'===事务==
SqlDa.SelectCommand.Transaction = Trans
SqlDa.InsertCommand.Transaction = Trans
SqlDa.DeleteCommand.Transaction = Trans
SqlDa.UpdateCommand.Transaction = Trans
'=====更新DataSet=======
Dremployee = Dst.employee.Rows(0)
Dremployee.BeginEdit()
Dremployee.minit = "Y"
Dremployee.EndEdit()
'====从DataSet更新数据库
Updateemployee = Dst.employee.GetChanges
If Not Updateemployee Is Nothing Then
SqlDa.Update(Updateemployee)
Dst.Merge(Updateemployee)
End If
'========假设有错==
'=========检查资料是否更新到数据库==============
'===为什么第一次可以更新进去,若上面有错退出,重新保存无法更新上去!
If ChkData(Trans) = False Then Exit Sub
Trans.Commit()
Catch ex As Exception
Trans.Rollback()
End Try
End Sub
Private Function ChkData(ByVal Trans As SqlTransaction) As Boolean
'===========检查更新是否成功==============
Dim Da As New SqlDataAdapter
Dim Dt As New DataTable
StrSql = "SELECT * FROM employee where emp_id='H-B39728F'"
Da.SelectCommand = New SqlCommand(StrSql, SqlCnn, Trans)
Da.Fill(Dt)
If Dt.Rows.Count > 0 Then
If Dt.Rows(0).Item("minit").ToString.Trim = "Y" Then
'1.点保存可以保存成功
MessageBox.Show("成功")
Trans.Rollback()
Return False
Else
'=2.若发生意外的情况保存不成功,则在点一次保存为什么不能更新到数据库
MessageBox.Show("失败")
Trans.Rollback()
Return False
End If
Else
MessageBox.Show("资料有问题")
Return False
End If
End Function
Public Function GetCBuilder(ByVal Cnn As SqlConnection, ByVal StrSql As String, ByRef Dad As SqlDataAdapter) As Boolean
'Dim CB As New SqlCommandBuilder
Try
If Cnn.State = ConnectionState.Closed Then
Cnn.Open()
End If
Dad = New SqlDataAdapter(StrSql, Cnn)
'CB = New SqlCommandBuilder(Dad)
Dim CB As New SqlCommandBuilder(Dad)
Dad.UpdateCommand = CB.GetUpdateCommand
Dad.InsertCommand = CB.GetInsertCommand
Dad.DeleteCommand = CB.GetDeleteCommand
Cnn.Close()
Return True
Catch ex As SqlClient.SqlException
MessageBox.Show(ex.Message)
Return False
End Try
End Function
End Class