Problem with VB calling SQL stored procedure

All,
Having a strange problem in VB. Have a SQL stored procedure that I am calling from VB, works fine if I run it from Query Analyzer, but when I run it from the code, the SP just stops working in mid-execution. Here is the VB code:

strSql = "CreateBas " + "'" + curruser + "'" + ", " + "'" + bDate + "'" + ", " + "'" + edate + "'"

MyDb.Execute strSql

We tried setting the commandTimeout, but still get the same results.
Is there anything else that can affect the time that the SP needs to run?

I would recommend using a Command object and its Parameters collection to execute stored procedures.

I'm pretty new at VB, so do you have an example of using the command object? Thanks.

I'm pretty new at VB, so do you have an example of using the command object? Thanks.

Set objSPComm = New ADODB.Command
With objSPComm
.ActiveConnection = yourConnection
.CommandType = adCmdStoredProc
.CommandText = "yourSP"
End With

Dim objParam(2) as Variant

objParam(0) = yourTextField0
objParam(1) = yourTextField1
objParam(2) = yourTextField2

objSPComm.execute ,objPAram

I would actually recommend against using a variant array for the parameters. The Command object contains a Parameters collection object that you can Create and Append instance of the Parameter object to.

Dim objParam As Parameter

Set objParam = objSPComm.CreateParameter(<parameter name>, <data type>, <parameter direction>)
objParam.Value = <value>
objSPComm.Parameters.Append objParam

objSPComm.Execute

you could also do it without the parameter object

Set objSPComm = New ADODB.Command
With objSPComm
.ActiveConnection = yourConnection
.CommandType = adCmdStoredProc
.CommandText = "yourSP"

.Parameters("@Param1") = <value>
.Parameters("@Param2") = <value>
.Parameters("@Param3") = <value>

objSPComm.execute
End With

If you use this method, remember to call .Parameters.Refresh first so that the data type and parameter direction (ie Input, Output, etc) is determined prior to assigning your values to them.