Populate read-only form from SQL stored proc

[Brian]

Hello,
I'm trying to open a recordset from a stored procedure and read the rs to
populate my form but it keeps telling me that the rs is closed.
I do...
Set rs = cnnBE.Execute("exec dbo.uspGetBalance 1, '2007-1-1', 1")
With rs
Do While Not .EOF
Debug.Print !AcNo
.MoveNext
Loop
.Close
End With
and it does not work.

However this works...
cnnBE.Execute "exec dbo.uspGetBalance 1, '2007-1-1', 1"
Set rs = cnnBE.Execute("SELECT * from tbltmpLastID WHERE UserNo = 1")
With rs
Do While Not .EOF
Debug.Print !AcNo
.MoveNext
Loop
.Close
End With


The procedure is
CREATE PROCEDURE uspGetBalance
@AcType as TinyInt,
@EndDate as DateTime,
@UserNo as TinyInt
AS
/*fill tbltmpLastID with the balances */
exec uspGetbalances @AcType, @EndDate, @UserNo
SELECT * from tbltmpLastID WHERE UserNo = @UserNo
GO

I can use the sql query analyser an do
exec dbo.uspGetBalance 1, '2007-1-1', 1
and it gives me the results straight off.

What am I doing wrong?

Thanks a lot
Brian

 

[Sylvain Lafontaine]

Usually, while calling a procedure with parameters, it's best to use an ADO
Command Object instead of a Connection Object because you can have typed
parameters and Output parameters. For example, in your case, the format
'2007-1-1' won't work correctly in many cases if the default language for
the login used in the connection is not set to english.

I didn't have time to make a test but in your case, the most likely
explanation is that you forgot to use the option « SET NOCOUNT ON » at the
beginning of the stored procedure; so the first returned recordset is a
closed recordset containing an info about the line count of the Select
statement.

You will have to either use the SET NOCOUNT ON instruction at the beginning
of the SP or to use the Recordset.NextRecordset function to retrieve the
next recordset in the returned resultset.

 

[Brian]

Thanks,
the NOCOUNT OPTION did the trick
Thank you very much.

Would the command object be faster running or anything like that?
Brian

 

[Sylvain Lafontaine]

Hard to say if using a command object would be faster, there are to many
factors involved here. However, it cannot be slower.

 

[Brian]

I'll take your advise and standardize on them anyway.
Thanks
Brian

 

[Brian]

Is this a question or a comment? What do you mean by unsafe?
Brian