SQL Sp with variable params- passing recordset thru to VB

[suel]

I have written a SP in SQL2000. It accepts a variable number of params (using @hcode int = null ) .
The SP works fine from Query Analyser -

Exec slValidation_Criteria 145,5,2

Here 3 of a possible 4 params are passed - the resultset is displayed fine - 186 hits.

Then I try and call the sp from VB (using ADO)

Call de1.slValidation_criteria(hcode,syear,survperiod,impid)
Set rs = de1.rsslvalidation_criteria

if rs.recordcount > 0 then
whatever ......
else
msgbox "No Recs Found"
end if

The SP always returns 0 recordsets
I have used Debug and tracked that the correct params are being passed to the SP.
Please can anyone see where I'm going wrong ...

Regards

Suel

 

[johnwm]

Check the type of cursor and the provider that you are using - many don't support recordcount property.

Check out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconrsrecordcount.asp

Also see FAQ referred below
________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first

'People who live in windowed environments shouldn't cast pointers.'

 

[MarkSweetland]

I'm assuming the de1 is a class with a method to actually call the sp, and that de1.rsslvalidation_criteria returns the resultant recordeset.

Check that your ADO connection used in the class uses a Client Side cursor (.CursorLocation = asUseClient) and the call in the class uses the .Open method of a recordset object or the .Execute of a Command object. If you use the .Execute method of the Connection object, the returned recordset is Read-only and forward-only--which would leave rs.recordcount always at -1.







Mark