CREATE PROCEDURE SP_tambahbuku(@kode_buku char(5),@judul varchar(50),@jenis varchar(30),@pengarang varchar(30),@penerbit varchar(30))
as
begin
insert into buku(kode_buku,judul,jenis,pengarang,penerbit)
values(@kode_buku,@judul,@jenis,@pengarang,@penerbit)
end
CREATE PROCEDURE SP_deletebuku(@kode_buku char(5))
as
DELETE FROM buku
WHERE kode_buku=@kode_buku
RETURN
CREATE PROCEDURE SP_updatebuku(@kode_buku char(5),@judul varchar(50),@jenis varchar(30),@pengarang varchar(30),@penerbit varchar(30))
AS
BEGIN
UPDATE buku SET kode_buku=@kode_buku,judul=@judul,jenis=@jenis,pengarang=@pengarang,penerbit=@penerbit
WHERE kode_buku=@kode_buku
END
Script ini berada di FormBuku :
Imports System.Data
Imports System.Data.SqlClient
Public Class FromBuku
Dim koneksi As New SqlConnection("Data Source=GINONG;Initial Catalog=pustaka;Integrated Security=True")
Private PosisiRecord As BindingManagerBase
#Region "umum"
Sub FormClear()
TxtKode.Clear()
TxtJudul.Clear()
CbJenis.Text = ""
TxtPengarang.Clear()
TxtPenerbit.Clear()
TxtKode.Focus()
Me.CbJenis.Text = "- Pilih -"
Me.CbJenis.Items.Add("Teknologi")
Me.CbJenis.Items.Add("Pemrograman")
Me.CbJenis.Items.Add("Sistem Informasi")
Me.CbJenis.Items.Add("Lainnya")
End Sub
#End Region
#Region "Akses-Data"
Sub ViewRecord()
Dim sql As String = "SELECT * FROM buku"
Dim da As SqlDataAdapter = New SqlDataAdapter(sql, koneksi)
Dim ds As DataSet = New DataSet
da.Fill(ds, "buku")
PosisiRecord = MyBase.BindingContext(ds, "buku")
TxtKode.DataBindings.Add("Text", ds, "buku.kode_buku")
TxtJudul.DataBindings.Add("Text", ds, "buku.judul")
CbJenis.DataBindings.Add("Text", ds, "buku.jenis")
TxtPengarang.DataBindings.Add("Text", ds, "buku.pengarang")
TxtPenerbit.DataBindings.Add("Text", ds, "buku.penerbit")
End Sub
Sub SimpanRecord()
Dim sqltrans As SqlTransaction = koneksi.BeginTransaction
Dim cmdtrans As SqlCommand = koneksi.CreateCommand
If TxtKode.Text <> "" Then
Try
With cmdtrans
.CommandText = "SP_tambahbuku"
.CommandType = CommandType.StoredProcedure
.Connection = koneksi
.Transaction = sqltrans
.Parameters.Add("@kode_buku", SqlDbType.Char, 5).Value = TxtKode.Text
.Parameters.Add("@judul", SqlDbType.VarChar, 50).Value = TxtJudul.Text
.Parameters.Add("@jenis", SqlDbType.VarChar, 30).Value = CbJenis.Text
.Parameters.Add("@pengarang", SqlDbType.VarChar, 30).Value = TxtPengarang.Text
.Parameters.Add("@penerbit", SqlDbType.VarChar, 30).Value = TxtPenerbit.Text
.ExecuteNonQuery()
End With
sqltrans.Commit()
MsgBox("Data Telah Tersimpan")
Catch ex As Exception
sqltrans.Rollback()
MessageBox.Show(ex.Message, "Data Gagal Tersimpan", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
sqltrans.Dispose()
cmdtrans.Dispose()
koneksi.Close()
End Try
End If
End Sub
Sub DeleteRecord()
Dim sqltrans As SqlTransaction = koneksi.BeginTransaction
Dim cmdtrans As SqlCommand = koneksi.CreateCommand
Dim hapus As Int16
If TxtKode.Text <> "" Then
Try
With cmdtrans
hapus = MsgBox("Benar record ini akan dihapus " & TxtKode.Text & "?", MsgBoxStyle.OkCancel, "Delete record")
Select Case hapus
Case vbCancel
Exit Sub
Case vbOK
.CommandText = "SP_deletebuku"
.CommandType = CommandType.StoredProcedure
.Connection = koneksi
.Transaction = sqltrans
.Parameters.Add("@kode_buku", SqlDbType.Char, 5).Value = TxtKode.Text
.ExecuteNonQuery()
End Select
End With
sqltrans.Commit()
MsgBox("Data Telah TerHapus")
Catch ex As Exception
sqltrans.Rollback()
MessageBox.Show(ex.Message, "Data Gagal Terhapus", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
sqltrans.Dispose()
cmdtrans.Dispose()
koneksi.Close()
End Try
End If
End Sub
Sub UpdateRecord()
Dim sqltrans As SqlTransaction = koneksi.BeginTransaction
Dim cmdtrans As SqlCommand = koneksi.CreateCommand
If TxtKode.Text <> "" Then
Try
With cmdtrans
.CommandText = "SP_updatebuku"
.CommandType = CommandType.StoredProcedure
.Connection = koneksi
.Transaction = sqltrans
.Parameters.Add("@kode_buku", SqlDbType.Char, 5).Value = TxtKode.Text
.Parameters.Add("@judul", SqlDbType.VarChar, 50).Value = TxtJudul.Text
.Parameters.Add("@jenis", SqlDbType.VarChar, 30).Value = CbJenis.Text
.Parameters.Add("@pengarang", SqlDbType.VarChar, 30).Value = TxtPengarang.Text
.Parameters.Add("@penerbit", SqlDbType.VarChar, 30).Value = TxtPenerbit.Text
.ExecuteNonQuery()
End With
sqltrans.Commit()
MsgBox("Data Telah Terupdate")
Catch ex As Exception
sqltrans.Rollback()
MessageBox.Show(ex.Message, "Data Gagal Terupdate", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
sqltrans.Dispose()
cmdtrans.Dispose()
koneksi.Close()
End Try
End If
End Sub
#End Region
Private Sub FromBuku_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.Text = "~~~ Master Data Buku ~~~"
Me.BackColor = Color.AliceBlue
ControlBox = False
Me.WindowState = FormWindowState.Maximized
Me.PanelLuar.Location = New System.Drawing.Size(250, 80)
Me.PanelLuar.Size = New System.Drawing.Size(802, 519)
Me.ViewRecord()
koneksi.Open()
End Sub
Private Sub BtnTambah_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSimpan.Click
SimpanRecord()
FormClear()
End Sub
Private Sub BtnBersih_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
FormClear()
End Sub
Private Sub BtnSelesai_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnKeluar.Click
End
End Sub
Private Sub BtnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnPrev.Click
PosisiRecord.Position -= 1
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnFirst.Click
PosisiRecord.Position = 0
End Sub
Private Sub BtnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnNext.Click
PosisiRecord.Position += 1
End Sub
Private Sub BtnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnLast.Click
PosisiRecord.Position = PosisiRecord.Count - 1
End Sub
Private Sub BtnTambah_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnTambah.Click
FormClear()
End Sub
Private Sub BtnHapus_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnHapus.Click
DeleteRecord()
End Sub
Private Sub BtnUbah_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUbah.Click
UpdateRecord()
End Sub
End Class
Untuk yang maw download scriptnya dan aplikasinya klik disini (StoreProcedure.txt dan Pustaka.rar)
0 komentar:
Posting Komentar