943,579 Members | Top Members by Rank

Software Development > Visual Basic 4 / 5 / 6 > How to Execute Stored Procedure in VB .. HELP
Ad:
You are currently viewing page 1 of this multi-page discussion thread
Permalink
May 10th, 2009
0

How to Execute Stored Procedure in VB .. HELP

Expand Post »
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
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
koolsamjust4u is offline Offline
1 posts
since May 2009
Permalink
May 10th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

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...
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. MyQueryString = "MySp " & MyValue
Then execute the query as you normally would.

Good Luck
Reputation Points: 156
Solved Threads: 296
Posting Virtuoso
vb5prgrmr is offline Offline
1,670 posts
since Mar 2009
Permalink
May 11th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

Click to Expand / Collapse  Quote originally posted by 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

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
Reputation Points: 10
Solved Threads: 2
Light Poster
maheshsayani is offline Offline
45 posts
since Jul 2006
Permalink
May 11th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

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...
Reputation Points: 156
Solved Threads: 296
Posting Virtuoso
vb5prgrmr is offline Offline
1,670 posts
since Mar 2009
Permalink
May 18th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

Click to Expand / Collapse  Quote originally posted by vb5prgrmr ...
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.
Reputation Points: 69
Solved Threads: 19
Junior Poster
samir_ibrahim is offline Offline
155 posts
since Sep 2008
Permalink
May 18th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

yup please specify waht db you are using and what connection you use, is it either DAO or ADO.
Reputation Points: 11
Solved Threads: 49
Posting Whiz
jireh is offline Offline
316 posts
since Jul 2007
Permalink
May 18th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

Click to Expand / Collapse  Quote originally posted by samir_ibrahim ...
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...
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. Set recordset = command.Execute( RecordsAffected, Parameters, Options )
but the way maheshsayani used it...
Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
  1. 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
Reputation Points: 156
Solved Threads: 296
Posting Virtuoso
vb5prgrmr is offline Offline
1,670 posts
since Mar 2009
Permalink
Jun 18th, 2009
0

Re: How to Execute Stored Procedure in VB .. HELP

'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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
Tassilo is offline Offline
2 posts
since Jun 2009
Permalink
Feb 14th, 2010
0
Re: How to Execute Stored Procedure in VB .. HELP
Click to Expand / Collapse  Quote originally posted by maheshsayani ...
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.
Reputation Points: 26
Solved Threads: 0
Junior Poster in Training
RahulV is offline Offline
92 posts
since Jun 2007
Permalink
Feb 16th, 2010
0

store procedure in vb

Click to Expand / Collapse  Quote originally posted by 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
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
Reputation Points: 10
Solved Threads: 0
Newbie Poster
SANJAY.DESHMUKH is offline Offline
4 posts
since Aug 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: calling crystal subreport in visual basic backend ms sql
Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: vb 6 printing





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC