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

 

[onedaywhen]

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

And not just performance issues e.g. the date format the OP is using
(yyyy-m-d) is it is 'unsafe' format whereas a Parameter object typed
as TIMESTAMP would handle such formatting issues via the OLE DB
provider.

Jamie.

--

 

[Brian]

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

 

[onedaywhen]

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

It's advice to check that your code is safe.

If you use an ADO Command+Parameter object, you can pass a strongly-
typed 'DateTime' value and the formatting will be handled by the
provider. However, if you pass the 'datetime' value as in SQL text
using a non- language neutral data format there is a greater degree of
risk involved. See:

The [self-styled] ultimate guide to the datetime datatypes
http://www.karaszi.com/sqlserver/info_datetime.asp

"Warnings and common misconceptions: Let me say this again: you don't
want to use a format which isn't language neutral unless you make sure
that you have the correct DATEFORMAT and LANGUAGE settings in your
applications."

Jamie.

--