contoh form barang dengan vb.net & database mysql

Pertama sebagai contoh buat design seperti di bawah ini :

Lalu buat database dengan membuka web browser buka mysql seperti di bawah ini : 

Untuk coding Programnya :

Imports MySql.Data.MySqlClient

Public Class Barang
Dim ParamCari As String

Private Sub Form_Load()
Call cekkoneksi()
Call formKosong()
Call formMati()
btnTambah.Enabled = True
btnSimpan.Enabled = False
End Sub

Sub formMati()
txtKode.Enabled = False
txtNama.Enabled = False
txtHarga.Enabled = False
txtDiskon.Enabled = False
txtStock.Enabled = False
txtRetur.Enabled = False
txtHargaBeli.Enabled = False
End Sub

Sub formHidup()
txtKode.Enabled = True
txtNama.Enabled = True
txtHarga.Enabled = True
txtDiskon.Enabled = True
txtStock.Enabled = True
txtRetur.Enabled = True
txtHargaBeli.Enabled = True
End Sub

Sub formKosong()
txtKode.Text = “”
txtNama.Text = “”
txtHarga.Text = “”
txtDiskon.Text = “”
txtStock.Text = “”
txtRetur.Text = “”
txtHargaBeli.Text = “”
End Sub

Sub Kode_Otomatis()
Call cekkoneksi()

sqlCommand = New MySqlCommand(“Select * From barang where kd_barang  like ‘%” & txtSetting.Text & “%’ and kd_barang in(select max(kd_barang) from barang) “, sConnection)
DR = sqlCommand.ExecuteReader
DR.Read()

If DR.HasRows = 0 Then
txtKode.Text = “” & txtSetting.Text & “” + “001″
DR.Close()

End If
If Not DR.HasRows Then
txtKode.Text = “” & txtSetting.Text & “” + “001″
DR.Close()

Else
txtKode.Text = Val(Microsoft.VisualBasic.Mid(DR.Item(“kd_barang”).ToString, 3, 2)) + 1
If Len(txtKode.Text) = 1 Then
txtKode.Text = “” & txtSetting.Text & “” + “00″ & txtKode.Text & “”
ElseIf Len(txtKode.Text) = 2 Then
txtKode.Text = “” & txtSetting.Text & “” + “0″ & txtKode.Text & “”
ElseIf Len(txtKode.Text) = 3 Then
txtKode.Text = “” & txtSetting.Text & “” & txtKode.Text & “”
End If
DR.Close()

End If
End Sub

Private Sub btnSimpan_Click(sender As Object, e As EventArgs) Handles btnSimpan.Click

Dim KodeAnggota As String = “B.” & txtKode.Text.PadLeft(3, “0″)

If txtKode.Text = “” Then
MsgBox(“KODE BARANG Masih Kosong”, vbCritical, “Salah”)
txtKode.Focus()
ElseIf txtNama.Text = “” Then
MsgBox(“NAMA BARANG Masih Kosong”, vbCritical, “Salah”)
txtNama.Focus()
ElseIf txtHarga.Text = “” Then
MsgBox(“HARGA Masih Kosong”, vbCritical, “Salah”)
txtHarga.Focus()
ElseIf txtDiskon.Text = “” Then
MsgBox(“DISKON Masih Kosong”, vbCritical, “Salah”)
txtDiskon.Focus()
ElseIf txtStock.Text = “” Then
MsgBox(“STOCK Masih Kosong”, vbCritical, “Salah”)
txtStock.Focus()
ElseIf txtRetur.Text = “” Then
MsgBox(“STOCK Masih Kosong”, vbCritical, “Salah”)
txtRetur.Focus()
ElseIf txtHargaBeli.Text = “” Then
MsgBox(“HARGA BELI Masih Kosong”, vbCritical, “Salah”)
txtHargaBeli.Focus()
Else

Dim SqlSimpan As String = “INSERT INTO barang(nm_segmen,kd_barang,nm_barang,harga,diskon,stock,retur,harga_beli) VALUES (‘” & cmbSegmen.Text & “‘,’” & txtKode.Text & “‘,’” & txtNama.Text & “‘,’” & txtHarga.Text & “‘,’” & txtDiskon.Text & “‘,’” & txtStock.Text & “‘,’” & txtRetur.Text & “‘,’” & txtHargaBeli.Text & “‘)”
Try
cekkoneksi()
With sqlCommand
.CommandText = SqlSimpan
.Connection = sConnection
.ExecuteNonQuery()
End With
MsgBox(“Data Barang ” & txtNama.Text & ” Berhasil Ditambahkan”, MsgBoxStyle.Information)

‘Barang_Load(Me, New System.EventArgs)
LoadBarang()

formKosong()

‘ListAnggota.LoadAnggota()

Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
sqlCommand.Dispose()
TutupKoneksi()
btnTambah.Enabled = True
btnSimpan.Enabled = False
btnUbah.Enabled = False
btnHapus.Enabled = False
Call formKosong()
btnTambah.Focus()
End Sub

Private Sub btnTambah_Click(sender As Object, e As EventArgs) Handles btnTambah.Click

Call formHidup()
cmbSegmen.Focus()
btnTambah.Enabled = False
btnSimpan.Enabled = True

End Sub

Public Sub Barang_Load(sender As Object, e As EventArgs) Handles MyBase.Load

LoadBarang()
comboSegmen()
btnTambah.Enabled = False
btnUbah.Enabled = False
btnHapus.Enabled = False
End Sub

Sub LoadCari()
If sConnection.State = ConnectionState.Closed Then
sConnection.ConnectionString = serper
sConnection.Open()
End If

If cmbFilter.Text = “Silahkan Pilih” Then
ParamCari = “”
ElseIf cmbFilter.Text = “Kode Barang” Then
ParamCari = “Where kd_barang like ‘%” & txtFilter.Text & “%’”
ElseIf cmbFilter.Text = “Nama Barang” Then
ParamCari = “Where nm_barang like ‘%” & txtFilter.Text & “%’”
End If
Dim sqlQueryCari As String = “SELECT * FROM barang ” & ParamCari

Dim sqlLoadBarangTable As New DataTable
Dim i As Integer
Try
cekkoneksi()

With sqlCommand
.CommandText = sqlQueryCari
.Connection = sConnection
End With
With sqlAdapter
.SelectCommand = sqlCommand
.Fill(sqlLoadBarangTable)
End With

lvBarang.Items.Clear()

For i = 0 To sqlLoadBarangTable.Rows.Count – 1
With lvBarang
.Items.Add(sqlLoadBarangTable.Rows(i)(“kd_barang”))
With .Items(.Items.Count – 1).SubItems
.Add(sqlLoadBarangTable.Rows(i)(“nm_barang”))
.Add(sqlLoadBarangTable.Rows(i)(“harga”))
.Add(sqlLoadBarangTable.Rows(i)(“diskon”))
.Add(sqlLoadBarangTable.Rows(i)(“stock”))
.Add(sqlLoadBarangTable.Rows(i)(“retur”))
.Add(sqlLoadBarangTable.Rows(i)(“harga_beli”))

End With
End With
Next

Catch ex As Exception
MsgBox(ex.Message)
End Try
sqlCommand.Dispose()
sqlAdapter.Dispose()
sConnection.Close()
End Sub

Sub LoadBarang()
Dim sqlQueryBarang As String = “SELECT * FROM barang Order By kd_barang”
Dim sqlLoadBarangTable As New DataTable
Dim i As Integer
Try
cekkoneksi()

With sqlCommand
.CommandText = sqlQueryBarang
.Connection = sConnection
End With
With sqlAdapter
.SelectCommand = sqlCommand
.Fill(sqlLoadBarangTable)
End With

lvBarang.Items.Clear()

For i = 0 To sqlLoadBarangTable.Rows.Count – 1
With lvBarang
.Items.Add(sqlLoadBarangTable.Rows(i)(“kd_barang”))
With .Items(.Items.Count – 1).SubItems
.Add(sqlLoadBarangTable.Rows(i)(“nm_barang”))
.Add(sqlLoadBarangTable.Rows(i)(“harga”))
.Add(sqlLoadBarangTable.Rows(i)(“diskon”))
.Add(sqlLoadBarangTable.Rows(i)(“stock”))
.Add(sqlLoadBarangTable.Rows(i)(“retur”))
.Add(sqlLoadBarangTable.Rows(i)(“harga_beli”))
End With
End With
Next

Catch ex As Exception
MsgBox(ex.Message)
End Try
sqlCommand.Dispose()
sqlAdapter.Dispose()
sConnection.Close()
End Sub

Private Sub btnUbah_Click(sender As Object, e As EventArgs) Handles btnUbah.Click
If txtKode.Text = “” Then
MsgBox(“KODE BARANG Masih Kosong”, vbCritical, “Salah”)
txtKode.Focus()
ElseIf txtNama.Text = “” Then
MsgBox(“NAMA BARANG Masih Kosong”, vbCritical, “Salah”)
txtNama.Focus()
ElseIf txtHarga.Text = “” Then
MsgBox(“HARGA Masih Kosong”, vbCritical, “Salah”)
txtHarga.Focus()
ElseIf txtDiskon.Text = “” Then
MsgBox(“DISKON Masih Kosong”, vbCritical, “Salah”)
txtDiskon.Focus()
ElseIf txtStock.Text = “” Then
MsgBox(“STOCK Masih Kosong”, vbCritical, “Salah”)
txtStock.Focus()
ElseIf txtRetur.Text = “” Then
MsgBox(“STOCK Masih Kosong”, vbCritical, “Salah”)
txtRetur.Focus()
ElseIf txtHargaBeli.Text = “” Then
MsgBox(“HARGA BELI Masih Kosong”, vbCritical, “Salah”)
txtHargaBeli.Focus()
Else

Dim SqlSimpan As String = “UPDATE barang SET nm_barang=’” & txtNama.Text & “‘, harga=’” & txtHarga.Text & “‘, diskon=’” & txtDiskon.Text & “‘, stock=’” & txtStock.Text & “‘, retur=’” & txtRetur.Text & “‘, harga_beli=’” & txtHargaBeli.Text & “‘ WHERE kd_barang=’” & txtKode.Text & “‘”
Try
cekkoneksi()
With sqlCommand
.CommandText = SqlSimpan
.Connection = sConnection
.ExecuteNonQuery()
End With
MsgBox(“Data Barang ” & txtNama.Text & ” Berhasil Diubah”, MsgBoxStyle.Information)

LoadCari()

‘Barang_Load(Me, New System.EventArgs)
formKosong()

‘ListAnggota.LoadAnggota()

Catch ex As Exception
MsgBox(ex.ToString)
End Try
End If
sqlCommand.Dispose()
TutupKoneksi()
End Sub

Private Sub lvBarang_click(sender As Object, e As EventArgs) Handles lvBarang.Click
btnUbah.Enabled = True
btnHapus.Enabled = True
btnSimpan.Enabled = False
Dim values As New List(Of String)
Dim SqlSimpan As String = “SELECT * FROM barang WHERE kd_barang=’” & txtKode.Text & “‘”
Try
cekkoneksi()
Catch ex As Exception

End Try

With lvBarang.SelectedItems.Item(0)
txtKode.Text = .Text
txtNama.Text = .SubItems(1).Text
txtHarga.Text = .SubItems(2).Text
txtDiskon.Text = .SubItems(3).Text
txtStock.Text = .SubItems(4).Text
txtRetur.Text = .SubItems(5).Text
txtHargaBeli.Text = .SubItems(6).Text
End With
sqlCommand.Dispose()

TutupKoneksi()

End Sub

Private Sub btnCari_Click(sender As Object, e As EventArgs) Handles btnCari.Click
LoadCari()
btnTambah.Enabled = False
btnSimpan.Enabled = False
End Sub

Private Sub btnKeluar_Click(sender As Object, e As EventArgs) Handles btnKeluar.Click
Me.Close()

End Sub

Private Sub btnHapus_Click(sender As Object, e As EventArgs) Handles btnHapus.Click
Dim SqlHapus As String = “DELETE from barang WHERE kd_barang =’” & txtKode.Text & “‘”
Try
cekkoneksi()
With sqlCommand
.CommandText = SqlHapus
.Connection = sConnection
.ExecuteNonQuery()
End With
MsgBox(“Data Barang ” & txtNama.Text & ” Berhasil Dihapus”, MsgBoxStyle.Information)

Barang_Load(Me, New System.EventArgs)
formKosong()

‘ListAnggota.LoadAnggota()

Catch ex As Exception
MsgBox(ex.ToString)
End Try

sqlCommand.Dispose()
TutupKoneksi()
End Sub
Private Sub txtKode_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtKode.KeyPress
If e.KeyChar = Chr(13) Then
Me.txtNama.Focus() ‘contoh untuk pindah antar kontrol pada text box
End If
End Sub
Private Sub txtNama_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtNama.KeyPress
If e.KeyChar = Chr(13) Then
Me.txtHarga.Focus() ‘contoh untuk pindah antar kontrol pada text box
End If
End Sub
Private Sub txtHarga_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtHarga.KeyPress
If e.KeyChar = Chr(13) Then
Me.txtDiskon.Focus() ‘contoh untuk pindah antar kontrol pada text box
End If
End Sub
Private Sub txtDiskon_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtDiskon.KeyPress
If e.KeyChar = Chr(13) Then
Me.txtStock.Focus() ‘contoh untuk pindah antar kontrol pada text box
End If
End Sub
Private Sub txtStock_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtStock.KeyPress
If e.KeyChar = Chr(13) Then
Me.txtRetur.Focus() ‘contoh untuk pindah antar kontrol pada text box
End If
End Sub
Private Sub txtRetur_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtRetur.KeyPress
If e.KeyChar = Chr(13) Then
Me.txtHargaBeli.Focus() ‘contoh untuk pindah antar kontrol pada text box
End If
End Sub
Private Sub txtHargaBeli_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs) Handles txtHargaBeli.KeyPress
If e.KeyChar = Chr(13) Then
btnSimpan_Click(sender, AcceptButton) ‘contoh Klik tombol button
End If
End Sub
Private Sub txtFilter_TextChanged(sender As Object, e As EventArgs) Handles txtFilter.TextChanged
LoadCari()
End Sub
Sub comboSegmen()
Try
sConnection.Open()
sqlCommand = New MySqlCommand(“select * from segmen_barang order by kd_segmen”, sConnection)
DR = sqlCommand.ExecuteReader()
While DR.Read()
cmbSegmen.Items.Add(DR(“nm_segmen”))

End While
Catch ex As Exception
MessageBox.Show(“Koneksi Gagal !!!, karena ” & ex.Message)
End Try

sConnection.Close()
DR.Close()

End Sub

Private Sub cmbSegmen_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSegmen.SelectedIndexChanged
If sConnection.State = ConnectionState.Closed Then
sConnection.ConnectionString = serper
sConnection.Open()
End If
‘No Urut Cabang
Dim KodeUnit As String
Dim abal As String = cmbSegmen.SelectedItem.ToString
Dim urut As Integer
Dim sqlItungUrutCabang As String = “SELECT COUNT(kd_barang) FROM barang WHERE nm_segmen = ‘” & abal & “‘”
With sqlCommand
.CommandText = sqlItungUrutCabang
.Connection = sConnection
End With

If IsDBNull(sqlCommand.ExecuteScalar) Then
urut = 1
txtSetting.Text = urut

Else
urut = sqlCommand.ExecuteScalar + 1
txtSetting.Text = urut

End If
‘Kode awal Cabang
Dim queryKodeCabang As String = “SELECT setting_kode FROM segmen_barang WHERE nm_segmen = ‘” & cmbSegmen.SelectedItem.ToString & “‘”
With sqlCommand
.CommandText = queryKodeCabang
.Connection = sConnection
End With
Dim dR As MySqlDataReader = sqlCommand.ExecuteReader()
While dR.Read()
If dR(0) IsNot Nothing Then
KodeUnit = dR(0).ToString
End If
End While
”cuba test kode cabang
‘Dim unit As String = cbUnit.SelectedItem.ToString
‘Dim kdCabangPad As String = txtUrut.Text.PadLeft(3, “0″)
‘Dim kdCabang As String = KodeUnit & “.” & kdCabangPad
‘txtpad.Text = kdCabang
”end cuba test

Dim unit As String = cmbSegmen.SelectedItem.ToString
Dim kdCabangPad As String = txtSetting.Text.PadLeft(3, “0″)
txtKode.Text = KodeUnit & “.” & kdCabangPad

dR.Dispose()
dR.Close()
sqlCommand.Dispose()
sConnection.Close()
End Sub
End Class

Terima kasih & semoga bermanfaat.

sumber : http://www.coding-vbnet.com/source-code/cara-membuat-design-form-barang-dengan-vb-net-dan-mysql-database/