How to Call a Stored Procedure from VB6

[SProc]

Hello Falks,

I am not an extensive user of VB6.0. I am trying to build a simple application that would call an SP to be used to generate a report using Crystal Reports 6.0.
I don't know how to call the stored procedure. Could you please give me some hints.
Thanks!

 

[rorubin]

ADOConnectionString = ""your db connection string here"
Dim adoConnection As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Set adoConnection = CreateObject("ADODB.Connection&quot
Set adoRecordset = CreateObject("ADODB.Recordset&quot

' Build a connection object.
With adoConnection
.ConnectionString = ADOConnectionString
.Open
'Now do Some work with the recordset
With adoRecordset
.Open "Your SP Name '" & InputVar & "'", adoConnection, adOpenStatic, adLockReadOnly
If adoRecordset.RecordCount >= 1 Then
'do something
else
'do nothing
end if
adoRecordSet.close
adoConnection.close
Set adoRecordset = nothing
Set adoConnection = Nothing

 

[SProc]

Thanks rorubin.

 

[SemperFiDownUnda]

If your stored procedures have OUTPUT parameters then you should use the ADODB.Command object like this


Dim conn As ADODB.Connection
Set conn = NEW ADODB.Connection
Dim cmd AS ADODB.Command
Set cmd = NEW ADODB.Command
Dim rs as ADOSB.RecordSet

conn.ConnectionString = ....
conn.Open
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_SomeProc"
cmd.Parameters("CustID&quot.Value = ...
.
.
.
.
Set rs = cmd.Execute
MsgBox cmd.Parameters("AccountBalance&quot.Value


If your stored procedure does not return a recordset then you don't need to pipe it into one and can actually speed it up by useing the adExecuteNoRecords option.
On the way out you can look at the parameters collections values for anything that should have changed.