MEMANGGIL STORE PROCEDURE LEWAT VISUAL BASIC 6.0

Nama               :  Esti Yuliana

NIM                   :  SIR200938

Mata Kuliah     :  Database Server

Dosen              :  Jatmiko Indrianto, S.Kom

MEMANGGIL STORE PROCEDURE LEWAT VISUAL BASIC 6.0

1. Buat dbBarang di Ms. SQL Server, lalu buat tabel_barang dengan isian sebagai berikut :

2. Buat Store Procedure :

a.      Cari

create procedure cari @nobrg char(10)as

select*from Tabel_barang where nobrg=@nobrg

GO

b.      Simpan

create procedure simpan @nobrg char (10), @nmbrg nvarchar (50),@sat varchar (10) as

insert into Tabel_barang (nobrg,nmbrg,sat) values(@nobrg, @nmbrg, @sat)

GO

c.        Hapus

create procedure hapus @nobrg char (10) as delete from Tabel_barang where nobrg=@nobrg

GO

d.      Edit

create procedure ubah @nobrg char(10),@nmbrg nvarchar (50),@sat varchar (10) as

update Tabel_barang set nobrg=@nobrg,nmbrg=@nmbrg,sat=@sat where nobrg=@nobrg

GO

3. Kemudian Setting ODBC lewat control panel

4. Buat Form dan berikut Form yang dibuat di VB 6.0

 Keterangan :

Text1.text as no barang

Text2.text as nama barang

Combo1.text as satuan

Datagrid1 as tabel

Cari as CMD_Cari

Simpan as CMD Simpan

Edit as CMD Edit

Hapus as CMD Hapus

Koding :

Dim koneksi As New ADODB.Connection

Dim RS As New ADODB.Recordset

Dim RS1 As New ADODB.Recordset

Private Sub Form_Load()

koneksi.Open "Provider=MSDASQL.1;Persist Security Info=False;Data Source=ODBCbarang"

Set RS = New ADODB.Recordset

RS.CursorLocation = adUseClient

RS.Open "SELECT * FROM Tabel_barang", koneksi, adOpenDynamic, adLockOptimistic

Set DataGrid1.DataSource = RS

DataGrid1.Refresh

End Sub

Private Sub CMD_SIMPAN_Click()

Dim PARAMETER As New ADODB.PARAMETER

  Dim simpan As New ADODB.Command

  Dim RS2 As New ADODB.Recordset

  With simpan

    .ActiveConnection = koneksi

    .CommandType = adCmdStoredProc

    .CommandText = "simpan"

    Set PARAMETER = .CreateParameter("nobrg", adVarChar, adParamInput, 10, Text1.Text)

.Parameters.Append PARAMETER

koneksi.Execute "simpan '" & Text1.Text & "','" & Text2.Text & "','" & Combo1.Text & "'"

X = MsgBox("DATA TELAH TERSIMPAN", vbInformation, "INFORMASI")

  End With

 

  RS.Requery

End Sub

Private Sub CMD_CARI_Click()

Dim PARAMETER1 As New ADODB.PARAMETER

  Dim cari As New ADODB.Command

  Dim RS1 As New ADODB.Recordset

  With cari

    .ActiveConnection = koneksi

    .CommandType = adCmdStoredProc

    .CommandText = "cari"

    Set PARAMETER1 = .CreateParameter("nobrg", adVarChar, adParamInput, 10, Text1.Text)

.Parameters.Append PARAMETER1

Set RS1 = .Execute

  End With

  Text2.Text = RS1.Fields("nmbrg")

 Combo1.Text = RS1.Fields("sat")

 

End Sub

*Catatan : untuk mengaktifkan cari isi dulu no. barang yang akan dicari pada teksbox text1.text

*Untuk perintah edit dan hapus masih belum sempurna jadi belum diposting.

Terima kasih semoga bermanfaat.