Auto Number เพิ่มรหัสตั้งแต่ 11 ขึ้นไปเพิ่มไม่ได้ช่วยอีกทีท่านเทพ [vb.net]

   With Conn 
       If .State = ConnectionState.Open Then .Close() 
       .ConnectionString = strConn 
       .Open() 
   End With
   Dim MaxID As Integer
   sqlCheckNumber = "SELECT ISNULL(MAX(Comment_ID),0) FROM Comment"
'ใช้บรรทัดนี้แล้ว 3 บรรทัดก่อนหน้านี้ไม่ต้องใช้แล้ว
   ComEdit = New OleDbCommand(sqlCheckNumber, Conn) 
ใส่ Try ตรงนี้ด้วยน่าจะดี
   MaxID = ComEdit.ExecuteScalar() 
   tmpNumber = (MaxID + 1).tostring("0000") 

SELECT RIGHT('000000' + CONVERT(nvarchar, [ID]), 6) AS [RunningID] FROM [YouTable]

Dim RunningID As String = dataTable.Rows(0)("ID").ToString("000000")

'หมายเหตุ: query id มาใส่ datatable

Option Explicit On
'Option Strict On 
Imports System.Text
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.SqlClient
Imports System.Drawing.Drawing2D
Imports System.String

Public Class FormAdd_Comment

    Dim strConn, Objrecored As String
    Dim Autorun As Integer
    Dim Conn As New OleDbConnection
    Dim da, da1 As OleDbDataAdapter
    Dim ds, dataset As New DataSet()
    Dim ds1 As New DataSet()

    Dim CurrentEmployees As Integer
    Dim IsFind As Boolean = False
    Dim instance As OleDbException
    Dim comSearch As New OleDbCommand


    Private Sub ProvinceBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Validate()
        Formdisplay_Colum()
    End Sub

    Private Sub LoadFiledata()

        ' Code ติดต่อกับฐานข้อมูล Accress 2007
        'strConn = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
        '         "Data Source=..\\Computer_CheckcoseEmployees.accdb;Persist Security Info=False"

        ' Code ติดต่อกับฐานข้อมูล Accress 2003
        'Dim conStr As String = _
        strConn = "provider=Microsoft.Jet.oleDb.4.0;" & _
                "Data Source=..\\Computer_CheckcoseEmployees.mdb"

        Dim Conn As New OleDbConnection(strConn)
        Conn.Open()

        Dim Sql As String = "select * from Comment"

        Dim Cmd As New OleDbCommand(Sql, Conn)

        Dim Adapter As New OleDbDataAdapter(Cmd)

        Dim Data As New DataSet()
        Adapter.Fill(Data, "cat")

        Dim Data1 As New DataSet()

        DataGridView1.DataSource = Data.Tables("cat")
        Conn.Close()

        Dim sqlCategories As String

        sqlCategories = "SELECT * from Comment"
        da = New OleDbDataAdapter(sqlCategories, Conn)
        da.Fill(ds, "Comment")
        Return
    End Sub

    Private Sub ClearButtom()
        BtnAdd.Enabled = True
        BtnSave.Enabled = False
        BtnEdit.Enabled = False
        BtnSave_Edit.Enabled = False
        BtnDelete.Enabled = False
        BtnExit.Enabled = True
    End Sub

    Private Sub ClearText()
        txtComment_ID.Text = "000"
        lblComment_ID.Text = "000"
        txtComment_Title.Text = ""
        txtComment_ID.Enabled = True
        txtComment_Title.Enabled = True
    End Sub

    '--------------------------------------------->>>> (FORMLOAD PAGE ข้อมูล)
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadFiledata()
        showAllProvince()     ' Function Call เรียกใช้งาน Table provinces

        'txtComment_ID.ContextMenu = New ContextMenu()
        txtComment_ID.Enabled = False
        txtComment_Title.Enabled = False
        txtComment_ID.Text = "000"
        lblComment_ID.Text = "000"
        txtComment_Title.Text = "หมายเหตุ"
        ClearButtom()
        'AutoRune()
        Return
    End Sub

    Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
        ClearText()
        AutoRune()

        lblComment_ID.Show()
        txtComment_ID.Hide()
        BtnSave.Enabled = True
        BtnAdd.Enabled = False
    End Sub

    Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
        Me.Close()
    End Sub

    '--------------------------------------------->>>> (SAVE ข้อมูล)
    Private Sub BtnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave.Click
        Dim sqlAdd As String
        Dim comAdd As New OleDbCommand
      
        If txtComment_ID.Text = "" Then
            MessageBox.Show("กรอกข้อมูลรหัส  !!!", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtComment_ID.Focus()
            Exit Sub
        End If
        If txtComment_Title.Text = "" Then
            MessageBox.Show("กรอกข้อมูลหมายเหตุ  !!!", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtComment_ID.Focus()
            Exit Sub
        End If

        If MessageBox.Show("คุณต้องการเพิ่มรายการหมายเหตุ ใช่หรือไม่?", "แจ้งให้ทราบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            sqlAdd = "INSERT INTO Comment(Comment_ID,Comment_Title)"
            sqlAdd &= " VALUES ('" & lblComment_ID.Text & "',"
            sqlAdd &= "'" & txtComment_Title.Text & "')"

            ' Code ติดต่อกับฐานข้อมูล Accress 2007
            'strConn = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
            '         "Data Source=..\\Computer_CheckcoseEmployees.accdb;Persist Security Info=False"

            ' Code ติดต่อกับฐานข้อมูล Accress 2003
            'Dim conStr As String = _
            'strConn = ("provider=Microsoft.Jet.oleDb.4.0;" & _
            '      "Data Source=..\\Computer_CheckcoseEmployees.mdb")

            With Conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = strConn
                .Open()
            End With

            With comAdd
                .CommandType = CommandType.Text
                .CommandText = sqlAdd
                .Connection = Conn
                .ExecuteNonQuery()
            End With

            MessageBox.Show("เพิ่มรายการหมายเหตุ  เรียบร้อยแล้ว !!!", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If
        LoadFiledata()  
        showAllProvince() 
        Formdisplay_Colum() 

        BtnAdd.Enabled = True
        BtnSave.Enabled = False
        'txtComment_ID.Enabled = False
        txtComment_Title.Enabled = False
        Return
    End Sub

    Private Sub showAllProvince() ' Function ใช้เพื่อ Call Table Doctor
        Dim SqlDoctor As String
        SqlDoctor = "SELECT * from Comment"

        SqlDoctor &= " FROM Comment"
        da.SelectCommand.CommandText = SqlDoctor

        If IsFind = True Then
            ds.Tables("Comment").Clear()

        End If

        If ds.Tables("Comment").Rows.Count <> 0 Then
            IsFind = True

            With DataGridView1
                .ReadOnly = True
                .DataSource = ds.Tables("Comment")
            End With
        Else
            IsFind = False
        End If
        Formdisplay_Colum()
        Return
    End Sub

    Private Sub Formdisplay_Colum() ' Function แสดงหัวข้อบน Gridviwe Table Doctor
        Dim cs As New DataGridViewCellStyle()

        cs.Font = New Font("AngsanaUPC", 17, FontStyle.Bold)

        With DataGridView1
            If .RowCount > 0 Then
                .Columns(0).HeaderText = "รหัส"
                .Columns(1).HeaderText = "หมายเหตุ"

                .Columns(0).Width = 30
                .Columns(1).Width = 440
            End If
        End With
        Return
    End Sub

    Private Sub DataGridView1_CellMouseUp(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseUp
        If e.RowIndex = DataGridView1.Rows.Count - 1 Then
            Exit Sub
        End If
        If e.RowIndex = -1 Then Exit Sub
        With DataGridView1
            CurrentEmployees = CInt(.Rows.Item(e.RowIndex).Cells(0).Value)
            txtComment_ID.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString()
            lblComment_ID.Text = .Rows.Item(e.RowIndex).Cells(0).Value.ToString()
            txtComment_Title.Text = .Rows.Item(e.RowIndex).Cells(1).Value.ToString()
        End With
        BtnEdit.Enabled = True : BtnAdd.Enabled = False : BtnDelete.Enabled = True
        Return
    End Sub

    Private Sub ButDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnDelete.Click
        Dim SqlDelete As String
        Dim ComDelete As New OleDbCommand

        If (txtComment_ID.Text = "") And (lblComment_ID.Text = "") Then
            Return
        End If

        If MessageBox.Show("ต้องการลบข้อมูลแผนก  หรือไม่", "แจ้งให้ทราบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then

            SqlDelete = "DELETE * FROM Comment"
            SqlDelete = "WHERE Comment_ID = txtComment_ID.Text"
            ComDelete = New OleDbCommand(SqlDelete, Conn)

            'strConn = "provider=Microsoft.Jet.oleDb.4.0;" & _
            '       "Data Source=..\\Computer_CheckcoseEmployees.mdb"

            With Conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = strConn
                .Open()
            End With

            SqlDelete = "DELETE * FROM Comment WHERE Comment_ID = txtComment_ID.Text"
            ComDelete = New OleDbCommand(SqlDelete, Conn)
            ComDelete.Parameters.AddWithValue("txtComment_ID.Text", txtComment_ID.Text)
            ComDelete.ExecuteNonQuery()
        End If
        MessageBox.Show("ลบข้อมูลหมายเหตุนี้  เรียบร้อยแล้ว", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
        LoadFiledata()          ' Function Call เรียกใช้งาน Table Loadtable Form Province
        showAllProvince()       ' Function Call เรียกใช้งาน Table province
        Formdisplay_Colum()     ' Function Call เรียกใช้งาน Display Field
        ClearButtom()
        'AutoRune()
        Return
    End Sub

    Private Sub BtnEdit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnEdit.Click
        If txtComment_ID.Text <> "" Then
            If MessageBox.Show("ต้องการแก้ไขข้อมูลหมายเหตุนี้  หรือไม่", "แจ้งให้ทราบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
                BtnAdd.Enabled = False
                BtnSave.Enabled = False
                BtnEdit.Enabled = False
                BtnSave_Edit.Enabled = True
                BtnDelete.Enabled = False
                txtComment_ID.Enabled = False
                txtComment_Title.Enabled = True
                txtComment_Title.Focus()
            End If
        Else
            Exit Sub
        End If
    End Sub

    Private Sub BtnSave_Edit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSave_Edit.Click
        Dim SqlEdit As String
        Dim ComEdit As New OleDbCommand

        If txtComment_ID.Text = "" Then
            MessageBox.Show("กรอกข้อมูลรหัส  !!!", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtComment_ID.Focus()
            Exit Sub
        End If

        If txtComment_Title.Text = "" Then
            MessageBox.Show("กรอกข้อมูลหมายเหตุ  !!!", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
            txtComment_ID.Focus()
            Exit Sub
        End If

        If MessageBox.Show("ต้องการแก้ไขข้อมูลชื่อแผนก  หรือไม่", "แจ้งให้ทราบ", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.Yes Then
            SqlEdit = "UPDATE Comment"
            SqlEdit &= " SET Comment_Title = '" & txtComment_Title.Text & "'"
            SqlEdit &= " WHERE (Comment_ID = '" & txtComment_ID.Text & "')"

            ' Code ติดต่อกับฐานข้อมูล Accress 2007
            'strConn = "Provider=Microsoft.ACE.OLEDB.12.0; " & _
            '         "Data Source=..\\Computer_CheckcoseEmployees.accdb;Persist Security Info=False"

            ' Code ติดต่อกับฐานข้อมูล Accress 2003
            ' Dim Strconn As String
            'strConn = "provider=Microsoft.Jet.oleDb.4.0;" & _
            '         "Data Source=..\\Computer_CheckcoseEmployees.mdb"
            With Conn
                If .State = ConnectionState.Open Then .Close()
                .ConnectionString = strConn
                .Open()
            End With
            ComEdit.CommandType = CommandType.Text
            ComEdit.CommandText = SqlEdit
            ComEdit.Connection = Conn
            ComEdit.ExecuteNonQuery()
        End If
        MessageBox.Show("แก้ไขข้อมูลหมายเหตุนี้   เรียบร้อยแล้ว", "แจ้งให้ทราบ", MessageBoxButtons.OK, MessageBoxIcon.Information)
        LoadFiledata()          ' Function Call เรียกใช้งาน Table Loadtable Form Province
        showAllProvince()       ' Function Call เรียกใช้งาน Table province
        Formdisplay_Colum()     ' Function Call เรียกใช้งาน Display Field
        BtnAdd.Enabled = True
        BtnSave_Edit.Enabled = False
        txtComment_Title.Enabled = False
        Return
    End Sub

 
   '------------------------------------------------------------------------------------------------------>>>>> > (PAGE Call AutoNumber )

Private Sub AutoRune()
        Dim tmpNumber As Integer
        Dim ComEdit As New OleDbCommand
        Dim sqlCheckNumber As String
        Dim price As Integer = 0
        dataset = New DataSet
        Dim i As Integer

        ' strConn = "provider=Microsoft.Jet.oleDb.4.0;" & _
        ' "Data Source=..\\Computer_CheckcoseEmployees.mdb"

        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()

            sqlCheckNumber = "SELECT Comment_ID FROM Comment ORDER BY Comment_ID asc"
            With ComEdit
                ComEdit.CommandType = CommandType.Text
                ComEdit.CommandText = sqlCheckNumber
                ComEdit.Connection = Conn
                ComEdit.ExecuteReader()

                ComEdit = New OleDbCommand(sqlCheckNumber, Conn)

                Dim adapter As New OleDbDataAdapter(ComEdit)
                adapter.Fill(dataset, "pro")

                For i = 0 To dataset.Tables("pro").Rows.Count '+ 1
                    tmpNumber = i + 1

                Next
                MsgBox(i)
                txtComment_ID.Text = Format(CInt(tmpNumber.ToString("000")))
                lblComment_ID.Text = Format(CInt(tmpNumber.ToString("000")))
                Return

            End With
        End With
    End Sub

    Private Sub txtComment_ID_KeyPress(ByVal sender As System.Object, ByVal e As  System.Windows.Forms.KeyPressEventArgs) Handles txtComment_ID.KeyPress
        e.Handled = ClassCall.CurrencyOnly(txtComment_ID, e.KeyChar)
    End Sub
End Class

Private Sub AutoRune()
        Dim tmpNumber As Integer
        Dim ComEdit As New OleDbCommand
        Dim sqlCheckNumber As String
        Dim price As Integer = 0
        dataset = New DataSet
        Dim i As Integer

        strConn = "provider=Microsoft.Jet.oleDb.4.0;" & _
        "Data Source=..\\Computer_CheckcoseEmployees.mdb"

        With Conn
            If .State = ConnectionState.Open Then .Close()
            .ConnectionString = strConn
            .Open()

            sqlCheckNumber = "SELECT Top 1 Comment_ID FROM Comment ORDER BY Comment_ID Desc"
            With ComEdit
                .CommandType = CommandType.Text
                .CommandText = sqlCheckNumber
                .Connection = Conn
                tmpNumber = .ExecuteScalar()


                txtComment_ID.Text = Format$(tmpNumber + 1, ("000"))
                lblComment_ID.Text = Format$(tmpNumber + 1, ("000"))
        

            End With
        End With
    End Sub

private void GetNum()
        {
            sb = new StringBuilder();
            sb.Append("SELECT IFNULL(MAX(DISTINCT PRNumber),0) AS MaxofPRNumber FROM purchase");
            string sqlNum;
            sqlNum = sb.ToString();
            com = new OdbcCommand();
            com.CommandText = sqlNum;
            com.CommandType = CommandType.Text;
            com.Connection = Conn;          
            dr = com.ExecuteReader();
            if (dr.HasRows)
            {
                dr.Read();
                int i;
                i = Convert.ToInt32(dr["MaxofPRNumber"])+ 1;                
                txtPr.Text = i.ToString("00000000");
            }
            else
            {
                txtPr.Text = "00000001";
            }
            dr.Close();
        }

Private Sub SelectMax()
        Dim ItemID As Integer
        sqlItem = New SqlCommand("select MAX( ชื่อฟิลที่อยากเอาออกมา ) FROM ชื่อตาราง ", Conn)
        Dim readerA As SqlDataReader = sqlItem.ExecuteReader()

        While readerA.Read()
            ItemID = CInt(readerA(0))
            ItemID = ItemID + 1
            tbItemID.Text = CStr(ItemID)
        End While
        readerA.Close()

    End Sub