Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Stored Proc
- Thread starter gieboy
- Start date
I'm not sure if it's the same in Oracle, but here's how we do it in SQL Server. This code was generated using the VB6 stored procedure add-in that you can get here:
It's a nice tool that we use all the time, and you can use it for Oracle and many other db's.
Dim cn as ADODB.Connection
Dim cmd as ADODB.Command
Dim params as ADODB.Parameters
Dim param as ADODB.Parameter
' Create connection and command objects
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
' Set connection properties and open
cn.ConnectionString = "your connection string"
cn.OpenWith cmd
Set .ActiveConnection = cn
.CommandText = "your stored proc name"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
params.Append cmd.CreateParameter("@MaterialID", adVarWChar, adParamInput, 18)
params.Append cmd.CreateParameter("@Batch", adVarWChar, adParamInput, 10)
' Specify input parameter values
params("@MaterialID"
= MyVariable
params("@Batch" = MyVariable
' Execute the command
cmd.Execute , , adExecuteNoRecords
' Retrieve stored procedure return value and output parameters
MyVariable = params("@RETURN_VALUE"
It's a nice tool that we use all the time, and you can use it for Oracle and many other db's.
Dim cn as ADODB.Connection
Dim cmd as ADODB.Command
Dim params as ADODB.Parameters
Dim param as ADODB.Parameter
' Create connection and command objects
Set cn = New ADODB.Connection
Set cmd = New ADODB.Command
' Set connection properties and open
cn.ConnectionString = "your connection string"
cn.OpenWith cmd
Set .ActiveConnection = cn
.CommandText = "your stored proc name"
.CommandType = adCmdStoredProc
Set params = .Parameters
End With
' Define stored procedure params and append to command.
params.Append cmd.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 0)
params.Append cmd.CreateParameter("@MaterialID", adVarWChar, adParamInput, 18)
params.Append cmd.CreateParameter("@Batch", adVarWChar, adParamInput, 10)
' Specify input parameter values
params("@MaterialID"
= MyVariableparams("@Batch"
= MyVariable' Execute the command
cmd.Execute , , adExecuteNoRecords
' Retrieve stored procedure return value and output parameters
MyVariable = params("@RETURN_VALUE"
- Status
Similar threads
