CRUD vb.net 2012 database sql server 2008

Imports System.Data.SqlClient
Imports System.Data

Module Koneksi
    Public strConnection As String = "Data Source=EL_RE-PC\SQLEXPRESS;initial catalog=RekamMedis;Integrated Security=True;"

    Public conn As New SqlConnection
    Public Sub hubungkan()
        conn = New SqlConnection(strConnection)
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If
    End Sub

    Public Sub tutupkoneksi()
        If conn.State = ConnectionState.Open Then
            Try
                conn.Close()
            Catch ex As Exception
                MsgBox("Gagal Menutup Koneksi" & ex.ToString)
            End Try
        End If
    End Sub
End Module

Imports System.Data.SqlClient
Imports System.Data


Dim ds As DataSet
Public Class FormObat

Dim dt As New DataTable
Dim myadp As SqlDataAdapter

Dim DREADER As SqlDataReader
Dim query As String
    Dim CMD As SqlCommand

Public comSQL As New SqlClient.SqlCommand
Dim sql As String

    Sub refreshtampil()
        hubungkan()

myadp = New SqlDataAdapter(query, conn)
query = ("SELECT * FROM Tbl_Obat")
        dt.Clear()
        myadp.Fill(dt)

TextBox3.Enabled = False
DataGridView1.DataSource = dt
    End Sub

    Private Sub NoNable()
        TextBox2.Enabled = False

Button3.Enabled = False
TextBox4.Enabled = False
        ComboBox2.Enabled = False
        ComboBox1.Enabled = False
        Button4.Enabled = False

TextBox4.Enabled = True
Button5.Enabled = False
    End Sub

    Private Sub Enable()
        TextBox2.Enabled = True
        TextBox3.Enabled = True

TextBox1.Text = ""
ComboBox2.Enabled = True
        ComboBox1.Enabled = True
    End Sub

    Private Sub clear()
        Button2.Enabled = True
        NoNable()

Private Sub kodeotomatis()
TextBox2.Text = ""
        TextBox3.Text = ""
        TextBox4.Text = ""
        ComboBox2.Text = ""
        ComboBox1.Text = ""
    End Sub

        hubungkan()

If dr.Read = False Then
sql = "select max(kodeobat) as no from Tbl_Obat"
        comSQL = New SqlCommand(sql, conn)
        Dim dr As SqlDataReader = comSQL.ExecuteReader()

            TextBox1.Text = "OB-0001"
        Else

TextBox1.Text = "OB-000" & (Val(TextBox1.Text) + 1)
TextBox1.Text = dr("no").ToString
            TextBox1.Text = Microsoft.VisualBasic.Right(TextBox1.Text, 4)
            If Val(TextBox1.Text) < 10 Then
            ElseIf Val(TextBox1.Text) >= 10 And Val(TextBox1.Text) < 100 Then

TextBox1.Text = "OB-" & (Val(TextBox1.Text) + 1)
TextBox1.Text = "OB-00" & (Val(TextBox1.Text) + 1)
            ElseIf TextBox1.Text > 99 Then
                TextBox1.Text = "OB-0" & (Val(TextBox1.Text) + 1)
            ElseIf TextBox1.Text > 999 Then
            End If
        End If
        tutupkoneksi()
    End Sub


Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
Private Sub Button7_Click(sender As Object, e As EventArgs) Handles Button7.Click
        Dispose()
    End Sub

    Private Sub FormObat_Load(sender As Object, e As EventArgs) Handles Me.Load
        refreshtampil()
        NoNable()
        kodeotomatis()
        TextBox1.Enabled = False
    End Sub

        Enable()
        Button2.Enabled = False

CMD = New SqlCommand("SELECT * FROM Tbl_Obat WHERE kodeobat= '" & TextBox1.Text & "'", conn)
Button3.Enabled = True
        TextBox2.Focus()
    End Sub

    Private Sub Button6_Click(sender As Object, e As EventArgs) Handles Button6.Click
        clear()
        kodeotomatis()
    End Sub

    Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        hubungkan()
        DREADER = CMD.ExecuteReader
        If DREADER.Read Then

Dim tambah As String = "INSERT INTO Tbl_Obat VALUES ('" & UCase(TextBox1.Text) & "','" & UCase(TextBox2.Text) & "','" & ComboBox1.SelectedItem & "','" & ComboBox2.SelectedItem & "','" & UCase(TextBox3.Text) & "','" & UCase(TextBox4.Text) & "')"
MsgBox("Data Sudah Ada..!!")
            clear()
            TextBox1.Focus()
        ElseIf TextBox1.Text = "" Or TextBox2.Text = "" Or ComboBox1.Text = "" Or ComboBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Then
            MsgBox("Lengkapi Data Anda..!!")
            TextBox1.Focus()
        Else
            DREADER.Close()
            Dim cmd As New SqlCommand

If TextBox1.Text = "" Or TextBox2.Text = "" Or ComboBox1.Text = "" Or ComboBox2.Text = "" Or TextBox3.Text = "" Or TextBox4.Text = "" Then
cmd.Connection = conn
            cmd.CommandText = tambah
            cmd.ExecuteNonQuery()
            refreshtampil()
            DataGridView1.Refresh()
            clear()
            NoNable()
            conn.Close()
            kodeotomatis()
        End If
    End Sub

    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
            MsgBox("Lengkapi Data Anda..!!")

cmd.Connection = conn
TextBox1.Focus()
        Else
            hubungkan()
            Dim tambah As String = "UPDATE Tbl_Obat SET namaobat ='" & UCase(TextBox2.Text) & "',jenis='" & ComboBox1.SelectedItem & "',kategori='" & ComboBox2.SelectedItem & "', harga='" & UCase(TextBox3.Text) & "', jumlah='" & UCase(TextBox4.Text) & "' WHERE kodeobat = '" & TextBox1.Text & "'"
            Dim cmd As New SqlCommand
            cmd.CommandText = tambah

Dim cmd As New SqlCommand
cmd.ExecuteNonQuery()
            refreshtampil()
            DataGridView1.Refresh()
            clear()
            NoNable()
            kodeotomatis()
            conn.Close()
        End If
    End Sub

    Private Sub Button5_Click(sender As Object, e As EventArgs) Handles Button5.Click
        hubungkan()
        Dim tambah As String = "DELETE FROM Tbl_Obat WHERE kodeobat = '" & TextBox1.Text & "'"
        cmd.Connection = conn

CMD = New SqlCommand("SELECT * FROM Tbl_Obat WHERE kodeobat= '" & a & "'", conn)
cmd.CommandText = tambah
        cmd.ExecuteNonQuery()
        refreshtampil()
        DataGridView1.Refresh()
        clear()
        NoNable()
        conn.Close()
        kodeotomatis()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        hubungkan()
        Dim a As String
        a = InputBox("Please Insert Kode Pasien..!! : ", "PENCARIAN")
        DREADER = CMD.ExecuteReader
        If DREADER.Read Then

MsgBox("Dala Tidak Ditemukan")
Enable()
            Button2.Enabled = False
            Button4.Enabled = True
            Button5.Enabled = True
            TextBox1.Text = DREADER.Item("kodeobat")
            TextBox2.Text = DREADER.Item("namaobat")
            ComboBox1.Text = DREADER.Item("jenis")
            ComboBox2.Text = DREADER.Item("kategori")
            TextBox3.Text = DREADER.Item("harga")
            TextBox4.Text = DREADER.Item("jumlah")
        Else
            NoNable()
        End If

End Class
DREADER.Close()

End Sub