Dim SQL_Header As String = "CP Reference No.,CP Version No.,Call Lot,Remark ID,Remarks,UPDUSER"
    Dim SQL_Where As String = ""
    Dim SQL As String = "SELECT  CPREFNO, CPVERNO,CLOT,REMLIN,REMARK,UPDUSER FROM CPM_Remark"
    Dim netDs As New DataSet
    Dim tn As String = "TB"
    Dim m As Int32
Private Sub Frm_CP_Management_Remark_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Try
            Me.txtCPREF.Text = CPREF
            Me.txtCPVER.Text = CPVERSION
            Me.txtCallLot.Text = CPCLOT
            Me.txtCustomer.Text = CPEKEY
            Me.txtGroupName.Text = CPGROUP
            Me.txtLocation.Text = CPLOCATION

            SQL_Where = " WHERE CPREFNO='" & CPREF & "' AND CPVERNO='" & CPVERSION & "'"
            netDs = gData.GetDataSet(SQL & SQL_Where & " ORDER BY REMLIN", sqlConn, tn)
            If netDs.Tables(tn).Rows.Count > 0 Then
                m = netDs.Tables(tn).Compute("max(remlin)", "") + 1
            Else
                m = 1
            End If

            netDs.Tables(tn).DefaultView.AllowNew = True
            netDs.Tables(tn).DefaultView.AllowDelete = True
            netDs.Tables(tn).DefaultView.AllowEdit = True
            dgv.DataSource = netDs.Tables(tn)
            dgv.AllowDrop = False
            Dim myGridStyle As DataGridTableStyle = New DataGridTableStyle
            myGridStyle.AllowSorting = False
            myGridStyle.ColumnHeadersVisible = False
            myGridStyle.RowHeadersVisible = False
            myGridStyle.AlternatingBackColor = Color.WhiteSmoke
            myGridStyle.MappingName = tn
            myGridStyle.RowHeadersVisible = True
            myGridStyle.SelectionForeColor = Color.DarkSeaGreen

            For i As Integer = 0 To netDs.Tables(tn).Columns.Count - 1
                Select Case i
                    Case 0
                        myGridStyle.GridColumnStyles.Add(TextBoxColumnStyle("CLOT", "Call Lot", 0, True))
                    Case 1
                        myGridStyle.GridColumnStyles.Add(TextBoxColumnStyle("REMLIN", "Remark ID", 0, True))
                    Case 2
                        myGridStyle.GridColumnStyles.Add(TextBoxColumnStyle("REMARK", "Remarks", 700, False))
                End Select
            Next
            dgv.TableStyles.Add(myGridStyle)
            dgv.CaptionVisible = False
            gMainForm.StatusBar1.Panels(1).Text = "Total Records: " & netDs.Tables(tn).Rows.Count & "   Current: " & dgv.CurrentCell.RowNumber + 1
        Catch ex As Exception
            MsgBox(ex.ToString(), MsgBoxStyle.Information, Me.Text)
        End Try
    End Sub

 

Public Function SaveSub() As String
        If Not netDs.HasChanges Then
            Return "No data change."
        End If
        Dim mytran As OleDb.OleDbTransaction
        Dim n As Int16 = m
        Try
            For Each r As DataRow In netDs.Tables(tn).Rows
                If r.RowState = DataRowState.Added Or r.RowState = DataRowState.Modified Then
                    If r.RowState = DataRowState.Added Then
                        r.BeginEdit()
                        r.Item("CPREFNO") = Me.CPREF
                        r.Item("CPVERNO") = Me.CPVERSION
                        r("clot") = Me.CPCLOT
                        r("remlin") = n
                        r("UPDUSER") = g.gUserId
                        r("remark") = StrConv(r("remark"), Microsoft.VisualBasic.VbStrConv.TraditionalChinese)
                        r.EndEdit()
                        n = n + 1
                    End If
                    If r.RowState = DataRowState.Modified Then
                        r.BeginEdit()
                        r("clot") = Me.CPCLOT
                        r("UPDUSER") = g.gUserId
                        r("remark") = StrConv(r("remark"), Microsoft.VisualBasic.VbStrConv.TraditionalChinese)
                        r.EndEdit()

                    End If

                    If r("remark").ToString().Length > 120 Then
                        Return "Max 120 characters for each line!"
                    ElseIf r("remark").ToString().Length <= 0 Then
                        Return "The record can not be blank."
                    End If
                End If
            Next
            Dim chds As DataSet = netDs.GetChanges(DataRowState.Deleted + DataRowState.Added + DataRowState.Modified)
            mytran = sqlConn.BeginTransaction()
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(SQL, sqlConn)
            Dim cmb As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(da)
            da.SelectCommand.Connection = sqlConn
            da.SelectCommand.Transaction = mytran
            da.UpdateCommand = cmb.GetUpdateCommand()
            da.DeleteCommand = cmb.GetDeleteCommand()
            da.InsertCommand = cmb.GetInsertCommand()
            da.Update(chds, tn)
            netDs.AcceptChanges()
            mytran.Commit()
            Me.dgv.Refresh()
            m = n
            Return "Save successfully!"
        Catch ex As Exception
            Return "Commit Exception!" & vbCrLf & ex.ToString()
            Try
                mytran.Rollback()
            Catch ex2 As Exception
                Return "Rollback Exception!" & vbCrLf & ex.ToString()
            End Try
        End Try
    End Function

字符编码转换函数:StrConv(str,字符编码)
eg:把简体中文转换成繁体中文   StrConv(r("remark"), Microsoft.VisualBasic.VbStrConv.TraditionalChinese)

posted on 2013-05-10 09:30  vinsonLu  阅读(824)  评论(0编辑  收藏  举报