Excel Forum

Code: Select all

 Private Sub Doc()

        ' อ่านข้อมูลเก็บไว้ใน Dataset

        Dim Sql As String = "SELECT * FROM Document WHERE Key='KEY'"
        _Cmd = New OleDbCommand(Sql, _Conn)
        'Dim Adapter As New OleDbDataAdapter(_Cmd)
        '_Dataset = New DataSet
        'Adapter.Fill(_Dataset, "Doc")

        Dim Reader As OleDbDataReader = _Cmd.ExecuteReader()
        Reader.Read()
        Dim MM As Integer = Reader("Month")
        Dim No As Integer = Reader("No")
        Dim MMnow As Integer = Month(Now)
        Dim YYnow As Integer = Mid(Year(Now), 3)
        Dim Document As String = ""

        If MMnow <> MM Then
            No = 1
            Document = "R" & YYnow & "0" & MMnow & "/" & "000" & No

        ElseIf MMnow > 9 And No > 9 Then
            Document = "R" & YYnow & MMnow & "/" & "00" & No + 1

        ElseIf MMnow < 9 And No < 9 Then
            Document = "R" & YYnow & "0" & MMnow & "/" & "000" & No + 1

        ElseIf MMnow > 9 And No < 9 Then
            Document = "R" & YYnow & MMnow & "000" & "/" & No + 1

        ElseIf MMnow < 9 And No > 9 Then
            Document = "R" & YYnow & "0" & MMnow & "00" & "/" & No + 1

        End If


        Sql = "UPDATE Document SET Year='" & YYnow & "', Month='" & MMnow & "', No='" & No + 1 & "',Doc='" & Document & "' WHERE Key='KEY'"
        _Cmd = New OleDbCommand(Sql, _Conn)


        _Cmd.Parameters.AddWithValue("YY", YYnow)
        _Cmd.Parameters.AddWithValue("MM", MMnow)
        _Cmd.Parameters.AddWithValue("No", No + 1)
        _Cmd.Parameters.AddWithValue("Do", Document)

        Dim Affected As Integer = _Cmd.ExecuteNonQuery

        If Affected < 1 Then
            MsgBox("เกิดข้อผิดพลาดในการแก้ไขข้อมูล!", MsgBoxStyle.OkOnly, "ผลการแก้ไข")
        Else
            MsgBox("ข้อมูลถูกแก้ไขแล้ว!", MsgBoxStyle.OkOnly, "ผลการแก้ไข")
        End If