CALLING STORED PROCEDURE FROM OPEN STATEMENT IN ADO

[TNN]

I cannot get my called SQL procedure to work. DisplayF3 is the name of my SQL stored procedure. strEmployee_Number is the string variable holding the parameter for DisplayF3.
I am getting error message: "Expected query name after EXECUTE" Run-Time error -2147217900

I checked the database to be sure the stored procedure is there. Do I need a command object or something??

I need the recordset opened at the same time I EXECUTE the query. The sql for the recordset is in the stored procedure. See below for copy of stored procedure as stored in database.

strSQL = "EXECUTE DisplayF3 " & (strEmployee_Number)

'Open recordset.
adoRsF3Screen.CursorType = adOpenStatic
adoRsF3Screen.LockType = adLockOptimistic
adoRsF3Screen.Open Source:=strSQL, _ ActiveConnection:=adoConnection, _
Options:=adCmdStoredProc

*********STORED PROCEDURE**********
DisplayF3
PARAMETERS [Employee_Number] IEEEDouble;
SELECT EMPNO, DEPT, WRATE, MTD_AMT, QTD_AMT, YTD_AMT, WAGENAME, BASSEARN.WAGENO
FROM BASSEARN INNER JOIN BASSWAGE ON BASSEARN.WAGENO = BASSWAGE.WAGENO
WHERE EMPNO =[Employee_Number];

Any-Body with help
Thank You
TNN, Tom
TNPAYROLL@AOL.COM





TOM

http://WWW.TNPAYROLL@AOL.COM

 

[culshaja]

The best way to do this is to use a Command object and then execute that and return reults into a Recordset. It is more efficient that way. The following is an example:

dim cnDB as ADODB.Connection
dim cmDisplayF3 as ADODB.Command
dim rsDisplayF3 as ADODB.Recordset
dim dblEmployeeNo as Double

set cnDB = New ADODB.Command
cnDB.ConnectionString = adoConnection
cnDB.Open

set cmDisplayF3 = New ADODB.Command
set cmDisplay.ActiveConnection = cnDB
cmDisplay.CommandType = adStoredProc
cmDisplay.CommandText = "DisplayF3" 
cmDisplay.Parameters.Append cmDisplay.CreateParameter(@Employee_Number, adDouble, adParamInput,,dblEmployeeNo)
set rsDisplayF3 = cmDisplay.Execute

Hope this helps.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[TNN]

Thank You James for the code.
I did make my code work by changing the Open Options to AdCmdText.
Can you help me understand why it is more efficient to use a command object?

Thank You for your response.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TomSark]

Use command objects if they need to be re-used. I use them regardless of whether they need to be reused, because it helps me to simplify debugging if needed.

But, if you have parameters in your proc, it's usually much easier to append your parameters to the parameters collection and associate that with a command object. It spells out the code more, making it a little easier to maintain and to degub...er.. debug.

Tom

 

[culshaja]

The program also has less work to do as it is spelt out to it what it is doing and it is complied more optimised.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[TNN]

Thank You Tom & James.
Your help in understanding is appreciated.
TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[TNN]

Hey Guys,
I'm just noticing something. If I open a recordset with a command object then the bookmark feature is unavailable.I get error message "..........not available from provider".
If I open the same recordset with the Open statement then bookmarks work fine. Any idea what's going on ????
TNN Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM

 

[culshaja]

You probably have a server side connection. You might also need to check the cursor type of the recordset. You need to have the Commands cursor location set to client side.

James James Culshaw
jculshaw@active-data-solutions.co.uk

http://www.active-data-solutions.co.uk

http://www.brainbench.com/transcript.jsp?pid=193305

 

[TNN]

Hi James,
I'm being told that the default for a recordset created with a command object is read only, cursor forward only and that it cannot be changed. The Update method, ie. adoRs.update, or refresh will not work. The recordset is updatable via SQL. This would explain why the bookmark would not work.

I did use the client side cursor to fix another problem I was having. Thank you for bringing that to my attention.

TNN, Tom
TNPAYROLL@AOL.COM

TOM

http://WWW.TNPAYROLL@AOL.COM