ADO not returning .recordcount?

[DTJeff]

Hi,

Anyone have any idea as to why this always returns the recordset.count as "-1" no matter how many records there are?

Dim SQLConnection As New ADODB.Connection
SQLConnection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=MyDB;Data Source=(local);Application Name=MyApp"
        SQLConnection.Open()
Dim rsAssetSimple As New ADODB.Recordset
rsAssetSimple = SQLConnection.Execute("SELECT * FROM TblAssets")

Do Until rsAssetSimple.EOF
Debug.Write(rsAssetSimple(0).Value & vbCrLf)
Debug.Write(rsAssetSimple.RecordCount & vbCrLf & "-----" & vbCrLf)
rsAssetSimple.MoveNext()
Loop

I'm sure I'm missing something obvious, but I'm finding it hard work learning ADO after the joys of the data environment designer - yeah yeah, I know - I used to cheat ;-)

Thanks

 

[woyler]

Firstly , I think you are in the wrong forum. Try posting these issues in VB6, or look into upgrading your code to ADO.NET

secondly, it is due to the way you are opening your recordset.
I believe you want to use adopenkeyset. a read only recordset will return -1 as a record count. Its been a while so the keywords may be wrong, but the issue is with the type of RC

 

[DTJeff]

I've posted it here as I'm using .NET.

I'm also using the older ADO for a specific reason - record locking. As far as I am aware there is no way to do this with ado.net, and I want to stop people editing records if someone else already is.

If theres a way of doing this with ado.net then I'll use it, but I want it to be able to detect this before the user starts editing (and without using any "tempory" entries in other tables).

As for the recordcount problem, I think I've sussed it out. I've had to use adOpenStatic - none of the others seemed to work, although to be honest I will more than likely be transfering the data from a recordset to a dataset to display it.

 

[woyler]

You can achieve pessimistic record locking in ADO, by using transactions.
start transaction ,fill dataset, your selected records are then read only from other users. Its a little more involved then that, but it is possible

 

[baddos]

DTJeff.... You need to use adOpenStatic for it to show the recordcount property.