Recordcount is -1

Hot Topic

COM hellp
capturing an dumping video streaming via http
minutes to hours and minutes
Need license for dbgrid32.ocx ?
How do I navigate to a folder and capture the path?
Making a picturebox have a transparent background
Portbability of Visual Basic application to ASP.NET
Save\Load values as text file
What are the differences between a DataAdaper and a DataReader?
Not able to publish project VS.net 2005 Professional

Visual Basic

How do I create a label when the mouse over a button?
Retrieve Data from ComboBox
Multiline strings in components
? difference between: Me, MyBase and Parent
Combo box refusing to set text
How to hide a Shell command window
Pricing for V/B Express 2005 etc?
Trouble Publishing Excel Application
Access Reports with VB.NET
Send and Receive text via serial port (Vb2005)

Recordcount is -1

I am having difficulty when deleting a record from an ado recordset. Even though the record has been deleted I am still seeing data in a datagrid that has its recordsource set to a query based on the deleted record. Trying to discover the root of this problem I have noticed that although the record I want is returned by the query the recordcount is set to -1. Can anyone help

'delete an entry from the database

Set cn2 = New ADODB.Connection
cn2.Open strConnect
Set adoRS2 = New ADODB.Recordset

strSQL = "SELECT * FROM CartXref "
strSQL = strSQL & " WHERE "
strSQL = strSQL & " [Printer_ID] = " & adoRS![printer_ID]
strSQL = strSQL & " AND "
strSQL = strSQL & " [Cartridge_ID] = " & adoRS![cartridge_ID]

adoRS2.Source = strSQL
adoRS2.ActiveConnection = cn2
adoRS2.LockType = adLockPessimistic
adoRS2.Open

Debug.Print adoRS2.RecordCount
Debug.Print "Prt ID" & adoRS2!printer_ID
Debug.Print "Crt ID" & adoRS2!cartridge_ID

With adoRS2
If .RecordCount = 0 Then
MsgBox "Unable to locate XREF record in Sub cmdRemoveEntry"
Else
.Delete
.UpdateBatch
End If
End With

Debug.Print adoRS2.RecordCount

Answer this question

Recordcount is -1

  • carthegenian

    Hi

    Thanks for the response. I am using VB6 (I know that this forum is not strictly for VB6 but I don't know where else to go!)

    I have tried MoveLast before using Recordcount but am still getting -1.

    The recordset I am querying is a table consisting solely of a primary key made up of two fields. These two fields are the primary keys of two other tables which have a many to many relationship.

    What I am attempting to do is: establish whether a link between these two tables exists, and if it does then delete it. Therefore, my query can only return either 0 or 1 record.

    I have coded around my -1 problem by using the BOF / EOF flags but am still stymied as to why I cannot get an accurate recordcount.

    I have also realised that to get my subsequent query to NOT include the deleted record I need to .Requery after .Delete

  • Server Girl

    A couple of questions to get a bit more information.

    Which version of VB are you using

    Is there any specific reason you are using ADO and not ADO.Net (This may be answered by the first question). ADO.Net is the latest technology for achieving this sort of action with .NET technologies. ADO is an earlier version which is designed around a connected data model.

    Your code appears to open an ADO recordset and then check the recordcount property to determine if there are any records and then run a delete method.

    A couple of issues here that may help. The delete method as you have used it will delete a single record - are you sure that this is the intention or do you want to delete all the records in the recordset.

    http://www.activeserverpages.ru/ADO/dameth02.htm

    Also the recordcount property has a value of -1 when it is unable to determine the actual recordcount. I seem to recall from a long while back that putting a .movelast in before the recordcount would help.

    http://www.activeserverpages.ru/ADO/daprop05_6.htm

    So if you want to actually delete all the records you can use the adAffectGroup constant or you can iterate through the records deleting them one at a time.

    Once you have deleted the records then repopulating / refreshing the datasource for the datagrid may be what you need to do.


  • haal1

    These forums are for VB.NET and there are better places to find answers for older versions of VB.

    Maybe the VB6 newgroups - http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.vb.general.discussion&lang=en&cr=US

    VB 6 Resource Center
    http://msdn.microsoft.com/vbrun/

    or perhaps www.vbcity.com may be useful places to search for answers on VB6 related questions.

    Or perhaps

    I'm not sure if the database you are trying to access is MS Access / SQL or something else.

    http://support.microsoft.com/kb/q194973/

    http://www.aspfaq.com/show.asp id=2193

    http://www.devx.com/vb2themax/Tip/18489

    But do you actually want to return the count or just know that a record exists. And if you using SQL server cant this simply be wrapped up in a stored procedure.

    From what your saying your trying to do is simply deleting a relationship if it exists. You should be able to issue just a delete SQL statement to do that rather than doing a select statement and then checking the recordset before deleting the record if it exists. This is pretty inefficient as it involves multiple trips to the database.

    Anyway good luck and hope this provides some more information and places to look.
  • Recordcount is -1
Answer this question