tags:

views:

21

answers:

3

Thanks dretzlaff17 for replying,

I am giveing details..........

SP from SQL Server 2005 is not returning the records in recordSet (VB6) records return are -1. If access the records using query and through record set, record set are filling with records.

Same connection string is used. I checked properly and there is no issue in code written in VB6 for command object, then what is the wrong?

Is there any thing else that we have to do while accessing SQL Server 2005.

my code is like

Dim Conn as new ADODB.Connection

Dim RS as new ADODB.RecordSet

Dim CMD as new ADODB.Command

Conn.Open "Connection String" ' Its working

CMD.ActiveConnection = Conn

CMD.CommandType = adCmdStoredProc

CMD.CommandText = "SPName" 

Set RS = CMD.Execute

Debug.Print RS.RecordCount ' /* here result is -1 means CMD is not executing and RS is not filling with records */

and if use

RS.Open "Select query", conn   'then this record set is filling with records.

I also check by setting RS (Cursor) location values to client side and SP is simple only select query is present in SP no I/O parameters.

One more thing records are present into database table are not empty.

on this Your thoughts please

Thanks

A: 

Could you show text of you stored proc? VB6 is old stuff, may be you just need put

set nocount on

at the beginning procedure or may be even execute this as query after opening connection. If it does not help try simplify stored procedure for something is definitely working like create stored proc as

set nocount on
select 1
A: 

RS.RecordCount ' /* here result is -1 means CMD is not executing and RS is not filling with records

No it doesn't: it means the default cursor type does not support the RecordCount property.

As a better test of contents, try:

Debug.Print RS.GetString
onedaywhen
A: 

If you want a record count, it's usually best to do this:

set RS = new ADODB.Recordset
RS.Open cmd, , adOpenDynamic, adLockReadOnly
If Not RS.EOF then Debug.Print RS.RecordCount
RS.Close
C-Pound Guru