โปรแกรมจองห้องพัก อยากให้เลขห้องไม่เกิน100ห้องต่อชั้น ขอด่วนนะค่ะ

Imports System
Imports System.Data         ' ระบุ Namespace สำหรับการใช้งานฐานข้อมูล
Imports System.Data.OleDb   ' ระบุ Namespace สำหรับการใช้งาน Oledb
Imports System.Data.Common
Imports System.Data.DataTable
Imports System.IO

Public Class BK_ROOM_R
    Dim OleDbConnection1 As New OleDbConnection ' ประกาศตัวแปรสำหรับจัดเก็บการเชื่อมต่อฐานข้อมูล
    Dim da As OleDbDataAdapter = Nothing    ' ประกาศตัวแปรสำหรับจัดเก็บ DataAdapter
    Dim ds As New DataSet                   ' ประกาศตัวแปรสำหรับจัดเก็บ DataSet
    Dim dt As DataTable = Nothing           ' ประกาศตัวแปรสำหรับจัดเก็บ DataTable
    Dim da1 As OleDbDataAdapter = Nothing    ' ประกาศตัวแปรสำหรับจัดเก็บ DataAdapter
    Dim ds1 As New DataSet                   ' ประกาศตัวแปรสำหรับจัดเก็บ DataSet
    Dim dt1 As DataTable = Nothing           ' ประกาศตัวแปรสำหรับจัดเก็บ DataTable
    Dim tempshowrecord As Integer
    Dim reccnt As Integer
    Dim DDR As DbDataReader
    Dim DTB As DataTable

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim cmd As OleDbCommand = Nothing
        Dim sqlSave As String = ""
        Dim sqlUpdate As String = ""
        Dim j As Integer

        Dim sqlif As String = "SELECT BRR_ROOM_CODE FROM BK_ROOM_R WHERE BRR_ROOM_CODE LIKE  '" & Txt_Id.Text & "'  "
        da1 = New OleDbDataAdapter(sqlif, OBCon)
        da1.Fill(ds1, "dsif")
        dt1 = ds1.Tables("dsif")
        j = Val(dt1.Rows.Count)
        ds1.Tables("dsif").Clear()
        dt1 = ds1.Tables("dsif")


        If Txt_Id.Text = "" Then
            MessageBox.Show("กรุณากรอกข้อมูล", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Cbo1.Focus()
            Exit Sub
        ElseIf Cbo1.Text = "" Then
            MessageBox.Show("กรุณากรอกข้อมูล", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Cbo1.Focus()
            Exit Sub
        ElseIf Cbo2.Text = "" Then
            MessageBox.Show("กรุณากรอกข้อมูล", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Cbo2.Focus()
            Exit Sub


        ElseIf MessageBox.Show("ทำการบันทึก " & Txt_Id.Text & " ", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then

            Exit Sub


        End If


        Try

        Catch ex As Exception

        End Try
        Try


            If j = 0 Then
                'R_Number()

                sqlSave = "INSERT INTO BK_ROOM_R(BRR_ROOM_CODE,BRR_ROOM_LEVEL,BRR_TYPE_ID) VALUES( '" & Txt_Id.Text & "','" & Cbo1.SelectedValue & "','" & Cbo2.SelectedValue & "')"
                'sqlSave = "INSERT INTO BK_ROOM_R(BRR_ROOM_CODE,BRR_ROOM_LEVEL) VALUES( '" & Txt_Id.Text & "','" & Cbo1.SelectedValue & "')"


                tempshowrecord = Val(Txt_Id.Text.ToString)
                cmd = New OleDbCommand(sqlSave, OBCon)

                cmd.ExecuteNonQuery()





            Else

                sqlUpdate = "UPDATE  BK_ROOM_R SET BRR_ROOM_CODE='" & Txt_Id.Text & "',BRR_ROOM_LEVEL= '" & Cbo1.SelectedValue & "',BRR_TYPE_ID= '" & Cbo2.SelectedValue & "' WHERE BRR_ROOM_CODE = '" & Txt_Id.Text & "'"

                tempshowrecord = Val(Txt_Id.Text.ToString)
                cmd = New OleDbCommand(sqlUpdate, OBCon)
                cmd.ExecuteNonQuery()



            End If



        Catch ex As Exception   ' กรณีประมวลผลในช่วงของ Try แล้วมีข้อผิดพลาด จะมีการกระโดด(jump) ลงมาที่บรรทัดนี้เลย เพื่อแสดงข้อผิดพลาดของโปรแกรม
            MessageBox.Show(ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        Finally

            'cleargrid()
            Showrecord()


        End Try


    End Sub
    Private Sub ROOMCbo1()
        Dim SqlTxt As String
        SqlTxt = "SELECT  BLR_ROOM_LEVEL FROM BK_LEVEL_R "
        OBCom = New OleDbCommand()
        Dim DDR As DbDataReader
        Dim DTB As DataTable
        With OBCom
            .CommandText = SqlTxt
            .CommandType = CommandType.Text
            .Connection = OBCon
            DDR = .ExecuteReader()
        End With
        If DDR.HasRows Then
            DTB = New DataTable()
            DTB.Load(DDR)
            With Cbo1
                .BeginUpdate()
                .DisplayMember = "BLR_ROOM_LEVEL"
                .ValueMember = "BLR_ROOM_LEVEL"
                .DataSource = DTB
                .EndUpdate()
            End With

        End If
        Cbo1.Enabled = True

    End Sub
    Private Sub ROOMCbo2()
        Dim SqlTxt As String
        SqlTxt = "SELECT  BTRR_TYPE_NAME,BTRR_TYPE_ID  FROM BK_TYPE_ROOM_R "
        OBCom = New OleDbCommand()
        Dim DDR As DbDataReader
        Dim DTB As DataTable
        With OBCom
            .CommandText = SqlTxt
            .CommandType = CommandType.Text
            .Connection = OBCon
            DDR = .ExecuteReader()
        End With
        If DDR.HasRows Then
            DTB = New DataTable()
            DTB.Load(DDR)
            With Cbo2
                .BeginUpdate()
                .DisplayMember = "BTRR_TYPE_NAME"
                .ValueMember = "BTRR_TYPE_ID"
                .DataSource = DTB
                .EndUpdate()
            End With

        End If
        Cbo2.Enabled = True

    End Sub
    Private Sub Showrecord()



        Dim sqlselect As String
        'sqlselect = "  select a.BRR_ROOM_CODE,a.BRR_ROOM_LEVEL,a.BRR_TYPE_ID,b.BTRR_TYPE_NAME FROM BK_ROOM_R a,BK_TYPE_ROOM_R b where a.BRR_TYPE_ID = b.BTRR_TYPE_NAME  order by BRR_ROOM_CODE ASC "
        sqlselect = "select bk_room_r.brr_room_code,BK_ROOM_R.brr_room_level,bk_type_room_r.btrr_type_name  from  bk_type_room_r,BK_ROOM_R  where  BK_ROOM_R.brr_type_id = bk_type_room_r.btrr_type_id  order by bk_room_r.brr_room_code ASC"

        'da1 = New OleDbDataAdapter(sqlselect, OleDbConnection1)
        'da1.Fill(ds1, "dataset2")
        'dt1 = ds1.Tables("dataset2")
        'Dgv1.DataSource = dt1

        'Dgv1.Columns(0).HeaderText = "รหัสของขั้นตอนการทำงาน"

        'Dgv1.Columns(0).Width = 250
        'Dgv1.Columns(1).HeaderText = "ชื่อขั้นตอนการทำงาน"
        'Dgv1.Columns(1).Width = 620





        OBCom = New OleDbCommand()
        Dim DDR As DbDataReader
        Dim DTB As DataTable
        With OBCom
            .CommandText = sqlselect
            .CommandType = CommandType.Text
            .Connection = OBCon
            DDR = .ExecuteReader()
        End With
        If DDR.HasRows Then
            DTB = New DataTable()
            DTB.Load(DDR)
            dgv1.ReadOnly = True
            dgv1.DataSource = DTB
            dgv1.Columns(0).HeaderText = "เลขที่ห้อง"
            dgv1.Columns(0).Width = 150
            dgv1.Columns(0).DataPropertyName = "BRR_ROOM_CODE"
            dgv1.Columns(1).HeaderText = "ลำดับชั้น"
            dgv1.Columns(1).Width = 300
            dgv1.Columns(1).DataPropertyName = "BRR_ROOM_LEVEL"
            dgv1.Columns(2).HeaderText = "ประเภทห้อง"
            dgv1.Columns(2).Width = 350
            dgv1.Columns(2).DataPropertyName = "BTRR_TYPE_NAME"

        Else
            dgv1.DataSource = Nothing
            dgv1.Refresh()
        End If


    End Sub
    Private Sub R_Number()


        Dim sql_ As String

        sql_ = " SELECT MAX(BRR_ROOM_CODE)+1  AS id FROM BK_ROOM_R  ORDER   BY  BRR_ROOM_CODE ASC "

        Dim comStr As New OleDbCommand(sql_, OBCon)
        Dim crm As OleDbDataReader = comStr.ExecuteReader


        If crm.Read Then

            If IsDBNull(crm.Item("id")) = True Then ' ถ้า CustomerID ในดาต้าเบส ว่าง

                Txt_Id.Text = ""

            ElseIf IsDBNull(crm.Item("id")) = False Then ' ถ้า CostomerID ไม่ว่าง

                Dim Last_CostomerID As String

                Last_CostomerID = (crm.Item("id"))

                Dim Next_CostomerID As Integer

                Next_CostomerID = (Last_CostomerID.Substring(0)) + 1 - 1

                Txt_Id.Text = Next_CostomerID.ToString("00")

            End If

        End If


    End Sub
    Private Sub Dgv1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles dgv1.SelectionChanged

        reccnt = dgv1.CurrentRow.Index
        Txt_Id.Text = dgv1.Rows(reccnt).Cells(0).Value.ToString()
        Cbo1.Text = dgv1.Rows(reccnt).Cells(1).Value.ToString()
        Cbo2.Text = dgv1.Rows(reccnt).Cells(2).Value.ToString()


    End Sub

    Private Sub Dgv1_ColumnAdded(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewColumnEventArgs) Handles dgv1.ColumnAdded
        dgv1.Columns.Item(e.Column.Index).SortMode = DataGridViewColumnSortMode.NotSortable ' sort ข้อมูลไม่ได้
    End Sub

    Private Sub BK_DEPARTMENT_R_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Connect()

        ROOMCbo1()
        ROOMCbo2()
        Showrecord()
    End Sub

    Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
        Txt_Id.Focus()

        ClereAllData()

    End Sub
    Private Sub ClereAllData()


        Txt_Id.Text = ""
        Cbo1.Text = ""
        Cbo2.Text = ""


    End Sub
    
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim cmd As OleDbCommand = Nothing
        Dim sqlDelete As String = ""
        sqlDelete = "DELETE FROM BK_ROOM_R WHERE BRR_ROOM_CODE = '" & Txt_Id.Text & "'"
        If Txt_Id.Text = "" Then
            MessageBox.Show("กรุณากรอกข้อมูล", "คำเตือน", MessageBoxButtons.OK, MessageBoxIcon.Information)
            Txt_Id.Focus()
            Exit Sub


        ElseIf MessageBox.Show("ทำการลบรหัส" & Txt_Id.Text & " ชื่อ " & Txt_Id.Text & " ", "คำยืนยัน", MessageBoxButtons.YesNo, MessageBoxIcon.Question) = Windows.Forms.DialogResult.No Then

            Exit Sub
        End If

        Try
            cmd = New OleDbCommand(sqlDelete, OBCon)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ข้อผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)

        Finally

            ClereAllData()
            Showrecord()



        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Me.Close()

    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        ClereAllData()
    End Sub

   

    Private Sub dgv1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgv1.CellContentClick

    End Sub


    Private Sub GroupBox1_Enter(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GroupBox1.Enter

    End Sub

    Private Sub Cbo1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Cbo1.SelectedIndexChanged

    End Sub

    Private Sub Txt_Id_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Txt_Id.TextChanged
        
    End Sub
   
    Private Sub Txt_Id_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Txt_Id.KeyPress
        If Asc(e.KeyChar) = 13 Then
            If (Txt_Id.Text = "") Then
                Txt_Id.Focus()
            Else
                Cbo1.Focus()
            End If
        End If
    End Sub
    Private Sub Cbo1_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Cbo1.KeyPress
        If Asc(e.KeyChar) = 13 Then
            If (Cbo1.Text = "") Then
                Cbo1.Focus()
            Else
                Cbo2.Focus()
            End If
        End If
    End Sub
    Private Sub Cbo2_KeyPress(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles Cbo2.KeyPress
        If Asc(e.KeyChar) = 13 Then
            If (Cbo2.Text = "") Then
                Cbo2.Focus()
            Else
                Button2_Click(sender, e)
            End If
        End If
    End Sub
End Class