VB .NET MYSQL : Input Data, Edit, Hapus

Imports System.Data.Odbc

Imports CrystalDecisions.CrystalReports.Engine

Imports CrystalDecisions.Shared

Imports CrystalDecisions.Windows.Forms

Imports CrystalDecisions.ReportSource


Public Class Form1

    Dim koneksi As OdbcConnection = Nothing

    Dim stringKoneksi As String = "DSN=localhost4"

    Dim stringSQL As String


    Sub tampilIsisDataGrid()

        koneksi = New OdbcConnection(stringKoneksi)

        stringSQL = "SELECT * FROM pelanggan"

        Dim adapter As New OdbcDataAdapter(stringSQL, koneksi)

        Dim ds As New DataSet

        adapter.Fill(ds, "nama")

        Try

            koneksi.Open()

            DataGridView1.DataSource = ds.Tables(0)


        Catch ex As Exception

            MessageBox.Show(ex.Message)


        End Try



    End Sub



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

        koneksi = New OdbcConnection(stringKoneksi)

        stringSQL = "INSERT INTO pelanggan(idpelanggan,namaawal,namaakhir,alamat,negara,kodepos,email,telepon,kota,tambahmailinglist,catatanpelanggan) VALUES (?,?,?,?,?,?,?,?,?,?,?)"

        Try

            koneksi.Open()

            Dim perintah = New OdbcCommand(stringSQL, koneksi)

            perintah.Prepare()

            perintah.Parameters.AddWithValue("@idpelanggan", TextBox1.Text)

            perintah.Parameters.AddWithValue("@namaawal", TextBox2.Text)

            perintah.Parameters.AddWithValue("@namaakhir", TextBox3.Text)

            perintah.Parameters.AddWithValue("@alamat", TextBox4.Text)

            perintah.Parameters.AddWithValue("@negara", TextBox5.Text)

            perintah.Parameters.AddWithValue("@kodepos", TextBox6.Text)

            perintah.Parameters.AddWithValue("@email", TextBox7.Text)

            perintah.Parameters.AddWithValue("@telepon", TextBox8.Text)

            perintah.Parameters.AddWithValue("@kota", TextBox9.Text)

            perintah.Parameters.AddWithValue("@tambahmailinglist", ComboBox1.Text)

            perintah.Parameters.AddWithValue("@catatanpelanggan", TextBox10.Text)


            perintah.ExecuteNonQuery()


            koneksi.Close()



        Catch ex As Exception

            MessageBox.Show(ex.Message)


        End Try


        TextBox1.Text = ""

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""

        TextBox5.Text = ""

        TextBox6.Text = ""

        TextBox7.Text = ""

        TextBox8.Text = ""

        TextBox9.Text = ""

        TextBox10.Text = ""

        ComboBox1.SelectedIndex = 0

        tampilIsisDataGrid()




    End Sub


    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        ComboBox1.Items.Add("Ya")

        ComboBox1.Items.Add("Tidak")

        koneksi = New Odbc.OdbcConnection

        koneksi.ConnectionString = "Dsn=localhost4"

        Dim perintah As String = "SELECT * FROM pelanggan"

        Dim adapter As New OdbcDataAdapter(perintah, koneksi)

        Dim ds As New DataSet()

        adapter.Fill(ds, "pelanggan")


        Try

            koneksi.Open()

            DataGridView1.DataSource = ds.Tables(0)

        Catch ex As Exception

            MsgBox("Terjadi Error: " & ex.Message)


        Finally

            koneksi.Close()


        End Try


    End Sub







    Private Sub DataGridView1_CellMouseClick(sender As Object, e As System.Windows.Forms.DataGridViewCellMouseEventArgs) Handles DataGridView1.CellMouseClick

        TextBox1.Text = DataGridView1.Rows(e.RowIndex).Cells(0).Value

        TextBox2.Text = DataGridView1.Rows(e.RowIndex).Cells(1).Value

        TextBox3.Text = DataGridView1.Rows(e.RowIndex).Cells(2).Value

        TextBox4.Text = DataGridView1.Rows(e.RowIndex).Cells(3).Value

        TextBox5.Text = DataGridView1.Rows(e.RowIndex).Cells(4).Value

        TextBox6.Text = DataGridView1.Rows(e.RowIndex).Cells(5).Value

        TextBox7.Text = DataGridView1.Rows(e.RowIndex).Cells(6).Value

        TextBox8.Text = DataGridView1.Rows(e.RowIndex).Cells(7).Value

        TextBox9.Text = DataGridView1.Rows(e.RowIndex).Cells(8).Value

        ComboBox1.Text = DataGridView1.Rows(e.RowIndex).Cells(9).Value

        TextBox10.Text = DataGridView1.Rows(e.RowIndex).Cells(10).Value


    End Sub

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


        Form4.Show()

    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        ' Hapus data

        koneksi = New OdbcConnection(stringKoneksi)

        stringSQL = "DELETE FROM pelanggan WHERE idpelanggan=(?)"

        Try

            koneksi.Open()

            Dim perintah = New OdbcCommand(stringSQL, koneksi)

            perintah.Prepare()

            perintah.Parameters.AddWithValue("@idpelanggan", TextBox1.Text)

            perintah.ExecuteNonQuery()

            koneksi.Close()

        Catch ex As Exception

            MessageBox.Show(ex.Message)


        End Try


        TextBox1.Text = ""

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""

        TextBox5.Text = ""

        TextBox6.Text = ""

        TextBox7.Text = ""

        TextBox8.Text = ""

        TextBox9.Text = ""

        TextBox10.Text = ""

        ComboBox1.SelectedIndex = 0

        tampilIsisDataGrid()



    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click

        'Update Data

        koneksi = New OdbcConnection(stringKoneksi)

        stringSQL = "UPDATE pelanggan SET namaawal=(?),namaakhir=(?),alamat=(?),negara=(?),kodepos=(?),email=(?),telepon=(?),kota=(?),tambahmailinglist=(?),catatanpelanggan=(?) WHERE idpelanggan=(?)"

        Try

            koneksi.Open()

            Dim perintah = New OdbcCommand(stringSQL, koneksi)

            perintah.Prepare()

            perintah.Parameters.AddWithValue("@namaawal", TextBox2.Text)

            perintah.Parameters.AddWithValue("@namaakhir", TextBox3.Text)

            perintah.Parameters.AddWithValue("@alamat", TextBox4.Text)

            perintah.Parameters.AddWithValue("@negara", TextBox5.Text)

            perintah.Parameters.AddWithValue("@kodepos", TextBox6.Text)

            perintah.Parameters.AddWithValue("@email", TextBox7.Text)

            perintah.Parameters.AddWithValue("@telepon", TextBox8.Text)

            perintah.Parameters.AddWithValue("@kota", TextBox9.Text)

            perintah.Parameters.AddWithValue("@tambahmailinglist", ComboBox1.Text)

            perintah.Parameters.AddWithValue("@catatanpelanggan", TextBox10.Text)

            perintah.Parameters.AddWithValue("@idpelanggan", TextBox1.Text)

            perintah.ExecuteNonQuery()

            koneksi.Close()


        Catch ex As Exception

            MessageBox.Show(ex.Message)


        End Try

        TextBox1.Text = ""

        TextBox2.Text = ""

        TextBox3.Text = ""

        TextBox4.Text = ""

        TextBox5.Text = ""

        TextBox6.Text = ""

        TextBox7.Text = ""

        TextBox8.Text = ""

        TextBox9.Text = ""

        TextBox10.Text = ""

        ComboBox1.SelectedIndex = 0

        tampilIsisDataGrid()



    End Sub
End Class