Problem with VB calling SQL stored procedure

j_mact
01-12-2004, 08:08 AM
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?

00100b
01-12-2004, 08:10 AM
I would recommend using a Command object and its Parameters collection to execute stored procedures.

j_mact
01-12-2004, 09:25 AM
I'm pretty new at VB, so do you have an example of using the command object? Thanks.

TheDutch IceMan
01-12-2004, 09:35 AM
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

00100b
01-12-2004, 09:39 AM
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

davie
01-12-2004, 09:45 AM
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

00100b
01-12-2004, 09:46 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum