Displaying data in the Stored Procedure With Parameter.

18.19 | Labels: Diplaying Data using Stored Procedure in VB

Stored Procedure is a program stored in the data base as well as data. It is actually quite unusual, because we expect the stored data in the development of data base is not his program.

The core competency is in SQL Server Store Procedures and Functions (Functions only found in SQL Server 2000).
With the Store Procedure, then the SQL program that we created:

can be used anytime.
more quickly and efficiently because it is Server Side.
easily created and maintained by a small but 'Power Full'.

Strored Making Procedure.
Syntax:

CREATE PROCEDURE nama_procedure
[@ Parameter1 tipe_data_parameter1, ... ... ... .., [@ ParameterNtipe_data_parameterN]
U.S.
SQL or TSQL statement

TSQL is the development of SQL, thus becoming more Powerful.
The following store procedure creation through the Enterprise Manager:

Click Stored Procedure in Database Northwind.
Right click on Area Name.
Select New Stored Procedure.

Change the statement that there is a Stored Procedure the following:

Then To display the data into a form using a stored procedure, create a form like the following picture:

The following program code:

Dim Conn As New ADODB.Connection
Dim rsTransaksi As New ADODB.Recordset
Dim StrSQL As String, OpenConnecting As String

Private Sub Form_Load()
Dim CustomerId As String

CustomerId = InputBox("Please Input Customer ID !")

OpenConnecting = "Provider=SQLOLEDB.1;Persist Security Info=False;" _
      & "User ID=sa;Initial Catalog=NorthWind;Data Source=(local)"

If Conn.State = adStateOpen Then
    Conn.Close
    Set Conn = New ADODB.Connection
    Conn.Open OpenConnecting
Else
    Conn.Open OpenConnecting
End If

rsTransaksi.ActiveConnection = Conn
rsTransaksi.CursorLocation = adUseClient
rsTransaksi.CursorType = adOpenDynamic
rsTransaksi.LockType = adLockOptimistic
rsTransaksi.Open "EXEC CustOrderHist '" & CustomerId & "'"

Set DataGrid1.DataSource = rsTransaksi

End Sub

The result: