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

转载于:https://www.cnblogs.com/net2008/archive/2009/07/17/1525737.html