How to Execute Stored Procedure in VB .. HELP

[koolsamjust4u]

Hi Friends .. need a small help can anyone help me in showing how to call a stored procedure which returns a recordset .. yea i need to pass one parameter also for getting that required data .. thanks in advance..

Sameer

[vb5prgrmr]

Just use a recordset object as you normally would. i.e. say your SP has a name of MySP and it accepts one parameter. So you query string would be something like...

MyQueryString = "MySp " & MyValue

Then execute the query as you normally would.

Good Luck

[maheshsayani]

Hi Friends .. need a small help can anyone help me in showing how to call a stored procedure which returns a recordset .. yea i need to pass one parameter also for getting that required data .. thanks in advance..

Sameer

Hi Sameer,

If you are using VB6 then u need to use Command object.

Please see the below example
Dim mobjConn As ADODB.Connection
Dim mobjCmd As ADODB.Command
Dim mobjRst As ADODB.Recordset

Set mobjCmd = New ADODB.Command

mobjCmd.CommandType = adCmdStoredProc

mobjCmd.Parameters.Append mobjCmd.CreateParameter("<Param Name> ", <Param Datatype>, <Param Type>, ,<Value>)

mobjCmd.CommandText = strSPName
mobjCmd.Execute

Please let me know if you have any issues

Mahesh B Sayani

[vb5prgrmr]

Correct me if I am wrong maheshsayani or anyone else, but using a command object used that way will not return a recorset....

>need a small help can anyone help me in showing how to call a stored procedure which returns a recordset

Hence, my suggestion to use a recordset object...

[samir_ibrahim]

Correct me if I am wrong maheshsayani or anyone else, but using a command object used that way will not return a recorset....

I am sure you know that the result of command.execute is record set. so may be explain your question more?

As I did not understand the PO question, I did not on which back end he talking about. does he mean a SP in SQL Server or a normal function in vb he is calling it stored procedure.

[jireh]

yup please specify waht db you are using and what connection you use, is it either DAO or ADO.

[vb5prgrmr]

I am sure you know that the result of command.execute is record set. so may be explain your question more?

Yes, I do realize that if you...

Set recordset = command.Execute( RecordsAffected, Parameters, Options )

but the way maheshsayani used it...

command.Execute RecordsAffected, Parameters, Options

does not return a recordset, hence my post...

As for understanding the OP's origional Q see post #1 above. It seems pretty strait forward to me...

Good Luck

[Tassilo]

'ADO is a DataEnvironment
'conGeneral is a DEConnection (to connect to the SQL Server)

'on the form dim the recordset

Dim rsRecord As New ADODB.Recordset

'then call the Stored Procedure "pro_StoredProc" with the parameter
With rsRecord
If .state = adStateOpen Then .Close
'
' set propertie of recordset (don't need if only read records)
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
' open recordset with a numeric Parameter
.Open ("{Call pro_StoredProc(" & Parameter & ")}"), ADO.conGeneral
' open recordset with a String Parameter
.Open ("{Call pro_StoredProc('" & Parameter & "')}"), ADO.conGeneral
'then go on as usual
Do Until .EOF
'read the record
.MoveNext
Loop

End With

[RahulV]

Hi Sameer,

If you are using VB6 then u need to use Command object.

Please see the below example
Dim mobjConn As ADODB.Connection
Dim mobjCmd As ADODB.Command
Dim mobjRst As ADODB.Recordset

Set mobjCmd = New ADODB.Command

mobjCmd.CommandType = adCmdStoredProc

mobjCmd.Parameters.Append mobjCmd.CreateParameter("<Param Name> ", <Param Datatype>, <Param Type>, ,<Value>)

mobjCmd.CommandText = strSPName
mobjCmd.Execute

Please let me know if you have any issues

Mahesh B Sayani

Hi,
Sorry if im too late.
But please can you explain, what is "strSPName" and will the "mobjCmd" return a resultset. If yes, then how must the resultset be handled if needed?

Thank you.

[SANJAY.DESHMUKH]

Hi Friends .. need a small help can anyone help me in showing how to call a stored procedure which returns a recordset .. yea i need to pass one parameter also for getting that required data .. thanks in advance..

Sameer

Dear sameer
the code for store procedure as below

Dim TDSCmd As ADODB.Command
Dim prm1 As ADODB.Parameter

Set TDSCmd = New ADODB.Command
Set TDSCmd.ActiveConnection = con
con.CommandTimeout = 600

TDSCmd.CommandType = adCmdStoredProc
TDSCmd.CommandText = "SAN_BOM_COST_UPDATE"
Set prm1 = New ADODB.Parameter
With prm1
.Type = adVarChar
.Direction = adParamInput
.Size = 40
.value = MACHINE_NAME
End With
TDSCmd.CommandTimeout = 300
TDSCmd.Parameters.Append prm1
TDSCmd.CommandTimeout = 300
TDSCmd.Execute

[SANJAY.DESHMUKH]

Dear sameer the code for call procedure in vb