
Problem passing the default parameters in sp
My stored procedures don't work after I changed the driver from Native/ODBC
to OLE DB for MS SQL Server 7.0. Most of my stored procedures have default
arguments and so I don't pass all the parameters from VB to SQL server.
Ex. stored procedure defination is:
CREATE PROCEDURE my_sp
This code in VB does not work anymore and get SQL error saying parameters
are not passed:
Dim MyDb As ADODB.Connection
Dim StProc As ADODB.Command
Dim params As ADODB.Parameters
Dim lrs_recordeset As ADODB.Recordset
Set MyDb = New Connection
MyDb.Open "Provider=SQLOLEDB;Data Source=my_server;
database=my_database;USER ID=me;PASSWORD=***;"
Set StProc = New Command
With StProc
.ActiveConnection = MyDb
.CommandText = "my_sp"
.CommandType = adCmdStoredProc
Set params = StProc.Parameters
' Define a stored procedure parameter and append to command.
adParamInput, 25, 895)
Set lrs_recordeset = .Execute
End With
Set StProc = Nothing
MyDb.Close
Set MyDb = Nothing
Let me know if there is an alternative.
Thanks.