Return Value

[Jagjit]

Hi

I have written this code , it is giving me value -1 . Table has no record , it should not give 0 if no record exists.

rscompany.Open "select * from owner", cnn1, adOpenDynamic, adLockOptimistic, adCmdText

I am using SQL Database.

Thanks

[si_the_geek]

That line of code doesn't give a value, I presume you are using other code too which does.

Also, "SQL Database" is not descriptive enough... there are hundreds of database systems which use SQL, dozens of which have SQL as part of their name (MySQL, SQL Server, ...), and each of those has multiple versions.

[TysonLPrice]

You don't say where you are seeing the -1. I guessing it is rscompany.RecoredCount. I think getting that to populate with zero depends on your cursor options. Using these I get a recordcount of zero. I freely admit I don't understand how they function together and maybe someone else can chime in.

rsTempRecordset.Open strSQL, cnRate, adOpenStatic, adLockReadOnly

[DataMiser]

I am assuming that there is a line that follows which check the recordcount of the recordset returned.

Rs.Recordcount will always return -1 unless you are using a Client side cursor as it will not know how many records there are. -1 indicates that there is an unknown number of records could be 0 could be 500 or more

if you had used

cnn1.CursorLocation=adUseClient

when you open your connection then your recordcount would reflect the number of records returned

[SamOscarBrown]

Interesting.....this line works fine with an empty table in MS ACCESS.
And when I look at rscompany.recordcount. it is ZERO.

So, you are either not using MS ACCESS or you are getting the -1 from some other check than recordCount (or as DM points out, not client side).

[TysonLPrice]

There are also a number of posts on this forum why you cannot trust that number (RecordCount). I always have used it and have not had any issues I'm aware of. There are a number of warning on this site though as I mentioned.

[SamOscarBrown]

@Tyson...aye....always best to add a counter and increment as one runs through the recordset.-That will give a TRUE record count

[DataMiser]

In my experience using a client side cursor has always returned an accurate recordcount in ADO. DAO requires you to do a movelast before the recordcount can be trusted and a server side cursor makes for faster execution with less network traffic.

In general you should not use the client side cursor and thus not the recordcount property. Most times people are just checking to see if the count is > 0 anyway for which a simple test of the .EOF flag works just as well and when using a server side cursor makes for better performance.