Dari pada basa-basi mending kita ke tkp saja lah..
keterangan:
1. beri nama textbox pada id menjadi txt_id
2. beri nama textbox pada negara menjadi txt_nama
3. beri nama datagridnya menjadi dg_negara
4. beri nama tombol tambah menjadi btn_tambah
5. beri nama tombol ubah menjadi btn_ubah
6. beri nama tombol hapus menjadi btn_hapus
7. beri nama tombol simpan menjadi btn_simpan
8. beri nama datagridview menjadi dg_negara
9. bikin database pada sqlserver dengan nama bebas
10. pada database buat tabel dengan nama tbl_negara dengan isi (id_negara varchar(10) dan nama_negara varchar(255))
11. jadikan primary key pada id_negara
12. Visual Basic yang digunakan adalah Visual Basic 2010
13. Database yang digunakan adalah SQLServer 2008 Express Edition
berikut kodingannya :
Imports System.Data.SqlClient
Public Class Form1
Dim Conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As DataSet
Dim dc As SqlCommand
Dim dr As SqlDataReader
Dim status As Integer
Public cone As SqlConnection
Public Function konek() As SqlConnection
cone = New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testing;Integrated Security=SSPI;Persist Security Info=true;")
Try
cone.Open()
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Return cone
End Function
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Call tampil_data()
Call button_awal()
Call design_tabel()
End Sub
Private Sub id_otomatis()
Dim sementara As Integer
Dim isi As String = ""
dc = New SqlCommand
dc.Connection = konek()
dc.CommandType = CommandType.StoredProcedure
dc.CommandText = "otomatis_id_negara"
dr = dc.ExecuteReader
If dr.Read Then
sementara = CInt(Replace(dr.Item("id_negara"), "N-", ""))
txt_id.Text = "N-" & CStr(sementara + 1)
Else
txt_id.Text = "N-1"
End If
End Sub
Private Sub tampil_data()
dc = New SqlCommand
da = New SqlDataAdapter
ds = New DataSet
dc.Connection = konek()
dc.CommandType = CommandType.StoredProcedure
dc.CommandText = "tampil_negara"
da.SelectCommand = dc
da.Fill(ds, "Negara")
dg_negara.DataSource = ds
dg_negara.DataMember = "Negara"
End Sub
Private Sub button_awal()
btn_simpan.Enabled = False
btn_tambah.Enabled = True
btn_ubah.Enabled = True
btn_hapus.Enabled = True
dg_negara.Enabled = True
End Sub
Private Sub design_tabel()
dg_negara.Columns(0).Width = 100
dg_negara.Columns(1).Width = 175
End Sub
Private Sub button_tambah()
btn_simpan.Enabled = True
btn_tambah.Enabled = False
btn_ubah.Enabled = False
btn_hapus.Enabled = False
dg_negara.Enabled = False
txt_id.Enabled = False
End Sub
Private Sub button_ubah()
btn_simpan.Enabled = True
btn_tambah.Enabled = False
btn_ubah.Enabled = True
btn_hapus.Enabled = False
dg_negara.Enabled = True
txt_id.Enabled = False
End Sub
Private Sub kosongkan()
txt_id.Text = ""
txt_nama.Text = ""
End Sub
Private Sub btn_ubah_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_ubah.Click
Call kosongkan()
Call button_ubah()
txt_id.Text = CStr(dg_negara.Item(0, dg_negara.CurrentRow.Index).Value)
txt_nama.Text = CStr(dg_negara.Item(1, dg_negara.CurrentRow.Index).Value)
End Sub
Private Sub btn_hapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_hapus.Click
Dim ans = MsgBox("Apakah anda yakin ingin menghapus data ini?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "")
If ans = vbNo Then
Exit Sub
Else
dc = New SqlCommand
dc.Connection = konek()
dc.CommandType = CommandType.StoredProcedure
dc.CommandText = "delete_negara"
dc.Parameters.Add("@id_negara", SqlDbType.VarChar, 10).Value = CStr(dg_negara.Item(0, dg_negara.CurrentRow.Index).Value)
Try
dc.ExecuteNonQuery()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Call tampil_data()
Call kosongkan()
Call button_awal()
konek.Close()
End If
End Sub
Private Sub btn_simpan_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_simpan.Click
If status = 1 Then
dc = New SqlCommand
If txt_nama.Text = "" Then
MsgBox("Nama negara masih kosong")
Else
Try
dc.Connection = konek()
dc.CommandType = CommandType.StoredProcedure
dc.CommandText = "insert_negara"
dc.Parameters.Add("@id_negara", SqlDbType.VarChar, 10).Value = txt_id.Text
dc.Parameters.Add("@nama_negara", SqlDbType.VarChar, 255).Value = txt_nama.Text
dc.ExecuteNonQuery()
MsgBox("Data Negara Berhasil DItambahkan")
Call kosongkan()
Call tampil_data()
Call button_awal()
status = 0
Catch ex As Exception
MsgBox("Data Gagal Disimpan")
End Try
End If
Else
Dim ans = MsgBox("Apakah anda yakin ingin mengubah data negara ?", MsgBoxStyle.YesNo + MsgBoxStyle.Question, "")
If ans = vbNo Then
Exit Sub
Else
If txt_nama.Text = "" Then
MsgBox("Nama Negara masih kosong")
Else
Dim Cd As New SqlCommand
Cd.Connection = konek()
Cd.CommandType = CommandType.StoredProcedure
Cd.CommandText = "update_negara"
Cd.Parameters.Add("@id_negara", SqlDbType.VarChar, 10).Value = txt_id.Text
Cd.Parameters.Add("@nama_negara", SqlDbType.VarChar, 255).Value = txt_nama.Text
status = 0
Call button_awal()
txt_id.Enabled = True
Try
Cd.ExecuteNonQuery()
Call tampil_data()
Call kosongkan()
MsgBox("Data negara berhasil diubah")
konek.Close()
Call kosongkan()
Catch ex As Exception
MsgBox(ex.Message.ToString)
End Try
End If
End If
End If
End Sub
Private Sub btn_tambah_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_tambah.Click
status = "1"
Call button_tambah()
Call id_otomatis()
End Sub
End Class
Penjelasan :
1. isi dari StoredProcedure tampil_negara:
create proc tampil_negara
select id_negara as [ID Negara], nama_negara as [Nama Negara] from tbl_negara
2. isi dari StoredProcedur delete_negara:
create proc delete_negara
as
delete from tbl_negara where id_negara = @id_negara
3. isi dari StoredProcedur update_negara:
create proc update_negara (@id_negara varchar(10),@nama_negara varchar(255))
as
update tbl_negara set id_negara=@id_negara,nama_negara=@nama_negara where id_negara=@id_negara
4. isi dari StoredProcedur insert_negara:
create proc insert_negara (@id_negara varchar(10),@nama_negara varchar(255))
as
insert into tbl_negara values (@id_negara, @nama_negara)
6. untuk ubah, pilih dulu data yang mau di ubah pada datagrid trus klik tombol ubah -> nama negara baru -> simpan
7.untuk hapus, pilih dulu data yang mau dihapus pada datagrid trus klik tombol hapus dan selesai
0 komentar:
Posting Komentar