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.MoveFirstElse
FillFieldsEnd If
End Sub
Private Sub Command2_Click()
rs.MoveNext
If rs.EOF Then
rs.MoveLastElse
FillFieldsEnd If
End Sub
Private Sub Form_Load()
With con
.CursorLocation = adUseClient.ConnectionString = "dsn=mdkOPP;uid=Scott;pwd=Tiger".OpenEnd With
With rs
.CursorLocation = adUseServer.CursorType = adOpenKeyset.LockType = adLockOptimistic.Source = "Select * from mdkOrders".ActiveConnection = con.OpenEnd 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