Make your own free website on Tripod.com

USING INPUT AND OUTPUT PARAMETERS


I've created a stored procedure which inserts a new order into the mdkOrders table using input parameters and subsequently returns an output parameter of the new order amount divided in half.
The code below runs the stored procedure from the VB Front end:


Option Explicit

Dim con As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim rs As New ADODB.Recordset


Private Sub cmdAddNew_Click()

ClearFields

Text1.SetFocus

End Sub


Private Sub cmdRunSP_Click()

Dim odid As String

Dim odamt As String

Dim odname As String


odid = Text1.Text


If Text2.Text = "" Then

odamt = "Null"

Else: odamt = Text2.Text

End If


If Text3.Text = "" Then

odname = "Null"

Else: odname = Text3.Text

End If


cmd.ActiveConnection = con


cmd.CommandText = "OPmdk"


cmd.Parameters.Append cmd.CreateParameter("P1", adVarChar, adParamInput, 10, Text1.Text)

cmd.Parameters.Append cmd.CreateParameter("P2", adVarChar, adParamInput, 10, Text2.Text)

cmd.Parameters.Append cmd.CreateParameter("P3", adVarChar, adParamInput, 20, Text3.Text)

cmd.Parameters.Append cmd.CreateParameter("P4", adVarChar, adParamOutput, 10)


cmd.CommandType = adCmdStoredProc


cmd.Execute


Text4.Text = "$" & cmd.Parameters(3).Value


End Sub


Private Sub Command1_Click()

rs.MovePrevious

If rs.BOF Then

rs.MoveFirst

Else

FillFields

End If

End Sub


Private Sub Command2_Click()

rs.MoveNext

If rs.EOF Then

rs.MoveLast

Else

FillFields

End If

End Sub


Private Sub Form_Load()

With con

.CursorLocation = adUseClient

.ConnectionString = "dsn=mdkOPP;uid=Scott;pwd=Tiger"

.Open

End With


With rs

.CursorLocation = adUseServer

.CursorType = adOpenKeyset

.LockType = adLockOptimistic

.Source = "Select * from mdkOrders"

.ActiveConnection = con

.Open

End With


FillFields


End Sub

Public Sub FillFields()

Text1.Text = rs.Fields(0).Value

Text2.Text = "$" & rs.Fields(1).Value

Text3.Text = rs.Fields(2).Value

End Sub


Public Sub ClearFields()

Text1.Text = ""

Text2.Text = ""

Text3.Text = ""

Text4.Text = ""

End Sub


RETURN TO HOME PAGE