grgl44
asked on
DataGrid + ador.recordset is showing 0 recordcount
I have a project that uses a datagrid to display two fields. The datasource is a disconnected ADOR recordset that I create at runtime. The user selects two items from a Treeview and clicks a button. When the button is fired I place the items into the recordset which are displayed in the datgrid. This all works fine untill the user deletes all of the records in the datagrid(This is allowed). When the user goes to add another two items to the now empty recordset the items are added but it is never reflected in the recordsets recordcount. Here is what I am doing, rdis is the datagrids datasource:
rDis.AddNew
rDis.Fields(0).Value = Trim(Left(txtMapS.Text, (InStr(txtMapS, "(") - 1)))
rDis.Fields(1).Value = Trim(Left(txtMapA.Text, (InStr(txtMapA, "(") - 1)))
rDis.Fields(2).Value = (Left(sStageKeyACT, (InStr(sStageKeyACT, "%") - 1)))
rDis.Update
Debug.Print rDis.RecordCount
The user can click the button all day and the only way the recordcount is updated is if I go to the datagrid and manualy delete a record, but the records are added and visible in the datagrid.
If I step through the code when the datagrid already has records the recordcount is incremented as soon I call addnew but after the record count has been brought to zero this is not the case. I have tried updating, refreshing and requerying both the datagrid and the recordset with no luck. If I even highlight one of the rows in the datagrid this also seems to somehow sync the data so that I get an accurate record count.
Can anyone help? I would even do something clunky like programaticaly select a row but have not figured out how.
Thanks for your time.
rDis.AddNew
rDis.Fields(0).Value = Trim(Left(txtMapS.Text, (InStr(txtMapS, "(") - 1)))
rDis.Fields(1).Value = Trim(Left(txtMapA.Text, (InStr(txtMapA, "(") - 1)))
rDis.Fields(2).Value = (Left(sStageKeyACT, (InStr(sStageKeyACT, "%") - 1)))
rDis.Update
Debug.Print rDis.RecordCount
The user can click the button all day and the only way the recordcount is updated is if I go to the datagrid and manualy delete a record, but the records are added and visible in the datagrid.
If I step through the code when the datagrid already has records the recordcount is incremented as soon I call addnew but after the record count has been brought to zero this is not the case. I have tried updating, refreshing and requerying both the datagrid and the recordset with no luck. If I even highlight one of the rows in the datagrid this also seems to somehow sync the data so that I get an accurate record count.
Can anyone help? I would even do something clunky like programaticaly select a row but have not figured out how.
Thanks for your time.
ASKER
Thanks for the reply. Yes I have tried all variations of moving through the recordset including this:
While Not rDis.EOF '=FALSE or true with the negate
Debug.Print rDis.Fields(0).Value '=Shows a Value
rDis.MoveNext
Wend
debug.print rDis.recordCount '=Still shows 0 even though I watched it move through.
Thanks for your time
While Not rDis.EOF '=FALSE or true with the negate
Debug.Print rDis.Fields(0).Value '=Shows a Value
rDis.MoveNext
Wend
debug.print rDis.recordCount '=Still shows 0 even though I watched it move through.
Thanks for your time
Is this a server side recordset or client. What type of cursor are you using
Extracted from the VB help file
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.
If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor, the actual count for a static or keyset cursor, and either -1 or the actual count, depending on the data source, for a dynamic cursor.
Extracted from the VB help file
Use the RecordCount property to find out how many records are in a Recordset object. The property returns -1 when ADO cannot determine the number of records or if the provider or cursor type does not support RecordCount. Reading the RecordCount property on a closed Recordset causes an error.
If the Recordset object supports approximate positioning or bookmarks—that is, Supports (adApproxPosition) or Supports (adBookmark), respectively, return True—this value will be the exact number of records in the Recordset regardless of whether it has been fully populated. If the Recordset object does not support approximate positioning, this property may be a significant drain on resources because all records will have to be retrieved and counted to return an accurate RecordCount value.
The cursor type of the Recordset object affects whether the number of records can be determined. The RecordCount property will return -1 for a forward-only cursor, the actual count for a static or keyset cursor, and either -1 or the actual count, depending on the data source, for a dynamic cursor.
ASKER
The cursor is set up like this:
rDis.CursorType = adOpenDynamic
rDis.CursorLocation = adUseClient
The issue is the first time a user adds a record everything works fine (RecordCount goes to 1). If the user has deleted all of the records through the datagrid the RecordCount goes to 0. At this point everything changes. When adding a record the same way that it is added in the beginning the recordCount is never incremented.
Thanks for your time.
rDis.CursorType = adOpenDynamic
rDis.CursorLocation = adUseClient
The issue is the first time a user adds a record everything works fine (RecordCount goes to 1). If the user has deleted all of the records through the datagrid the RecordCount goes to 0. At this point everything changes. When adding a record the same way that it is added in the beginning the recordCount is never incremented.
Thanks for your time.
The adUseClient setting should be OK. Have you tried changing adOpenDynamic to either static or keyset, suggest trying that first.
Failing that could you show me the entire sub routine that picks up the recordcount value.
ASKER
Tried different settings with no sucess.
When the project loads the recordset is set like this:
Set rDis = New ADOR.Recordset
rDis.Fields.Append "MySQL", adVarChar, 100
rDis.Fields.Append "ACT!", adVarChar, 100
rDis.Fields.Append "aID", adInteger
rDis.CursorType = adOpenStatic
rDis.CursorLocation = adUseClient
rDis.Open
Set Start.dgMaps.DataSource = rDis 'START IS THE NAME OF THE FORM
Start.dgMaps.Columns(2).Vi sible = False
This is the sub that is picking the recordcount value. The idea is to not allow duplicate entries in the datagrid/recordset:
Private Sub cmdMap_Click()
Dim bgood As Boolean
Dim sSQL As String
bgood = False
Debug.Print rDis.RecordCount
If bHaveMaps Then
If rDis.RecordCount > 0 Then
rDis.MoveFirst
While Not rDis.EOF
If rDis.Fields(0).Value = Trim(Left(txtMapS.Text, (InStr(txtMapS, "(") - 1))) And _
rDis.Fields(1).Value = Trim(Left(txtMapA.Text, (InStr(txtMapA, "(") - 1))) Then
cmdMap.Enabled = False
Exit Sub
End If
rDis.MoveNext
Wend
End If
If checkTable Then
bgood = True
Else
MsgBox "Please Choose fields from a single table only", vbInformation, "Table Conflict"
bgood = False
End If
Else
bgood = True
End If
If bgood Then
rDis.AddNew
rDis.Fields(0).Value = Trim(Left(txtMapS.Text, (InStr(txtMapS, "(") - 1)))
rDis.Fields(1).Value = Trim(Left(txtMapA.Text, (InStr(txtMapA, "(") - 1)))
rDis.Fields(2).Value = (Left(sStageKeyACT, (InStr(sStageKeyACT, "%") - 1)))
rDis.Update
Debug.Print rDis.RecordCount
sCurrentTableSQL = sStageTableSQl
sCurrentTableACT = sStageTableACT
If Not bHaveMaps Then bHaveMaps = True
If Not bDirty Then bDirty = True
End If
End Sub
Thanks for your time.
When the project loads the recordset is set like this:
Set rDis = New ADOR.Recordset
rDis.Fields.Append "MySQL", adVarChar, 100
rDis.Fields.Append "ACT!", adVarChar, 100
rDis.Fields.Append "aID", adInteger
rDis.CursorType = adOpenStatic
rDis.CursorLocation = adUseClient
rDis.Open
Set Start.dgMaps.DataSource = rDis 'START IS THE NAME OF THE FORM
Start.dgMaps.Columns(2).Vi
This is the sub that is picking the recordcount value. The idea is to not allow duplicate entries in the datagrid/recordset:
Private Sub cmdMap_Click()
Dim bgood As Boolean
Dim sSQL As String
bgood = False
Debug.Print rDis.RecordCount
If bHaveMaps Then
If rDis.RecordCount > 0 Then
rDis.MoveFirst
While Not rDis.EOF
If rDis.Fields(0).Value = Trim(Left(txtMapS.Text, (InStr(txtMapS, "(") - 1))) And _
rDis.Fields(1).Value = Trim(Left(txtMapA.Text, (InStr(txtMapA, "(") - 1))) Then
cmdMap.Enabled = False
Exit Sub
End If
rDis.MoveNext
Wend
End If
If checkTable Then
bgood = True
Else
MsgBox "Please Choose fields from a single table only", vbInformation, "Table Conflict"
bgood = False
End If
Else
bgood = True
End If
If bgood Then
rDis.AddNew
rDis.Fields(0).Value = Trim(Left(txtMapS.Text, (InStr(txtMapS, "(") - 1)))
rDis.Fields(1).Value = Trim(Left(txtMapA.Text, (InStr(txtMapA, "(") - 1)))
rDis.Fields(2).Value = (Left(sStageKeyACT, (InStr(sStageKeyACT, "%") - 1)))
rDis.Update
Debug.Print rDis.RecordCount
sCurrentTableSQL = sStageTableSQl
sCurrentTableACT = sStageTableACT
If Not bHaveMaps Then bHaveMaps = True
If Not bDirty Then bDirty = True
End If
End Sub
Thanks for your time.
Just a suggestion - try placing a Debug.Print rDis.RecordCount after rDis.MoveFirst in the Private Sub cmdMap_Click().
ASKER
It makes no difference. I can add mutiple records but the recordcount is never changed until I manualy delete a row from the datagrid.
Thanks for your time.
Thanks for your time.
Have you checked for an error condition whilst running this routine? ie from within VB
Select Tools
Select Options
Select General
Set error trapping to Break on all errors
Then run the routine and see if you get an error generated.
I'm just trying to determine if a null field value is causing your problem.
Select Tools
Select Options
Select General
Set error trapping to Break on all errors
Then run the routine and see if you get an error generated.
I'm just trying to determine if a null field value is causing your problem.
ASKER
Thanks for the suggestion. It does not generate an error. It is like this: When the program is first run the recordcount = 0 as the user adds items it goes up and as the user deletes items it goes down. If the user takes out all of the items it goes to zero. At that point if the user adds an item the item is processed and shows in the grid but the recordcount does not reflect it. No matter how many items are added the recordcount stays at zero. If the user deletes one of the rows the recordcount magically is restored.
Thanks for your time.
Thanks for your time.
ASKER
Thanks for your time and help here is how I am dealing with the bug:
I add the real record then update
rDis.Update
If rDis.RecordCount = 0 Then 'clunky fix for bug
With rDis
.AddNew
.Fields(0) = "squib"
.Update
.Delete adAffectCurrent
End With
End If
Debug.Print rDis.RecordCount 'This will now read 1
This seems to work fine although it is funky.
I add the real record then update
rDis.Update
If rDis.RecordCount = 0 Then 'clunky fix for bug
With rDis
.AddNew
.Fields(0) = "squib"
.Update
.Delete adAffectCurrent
End With
End If
Debug.Print rDis.RecordCount 'This will now read 1
This seems to work fine although it is funky.
Not ideal - but if it works .....
Hi
What version of ADO are you using 2.6?
Anyway you could try this one liner before querying record count:
adoRs.Filter = ADODB.FilterGroupEnum.adFi lterNone
Then query the record count, this will update the record count and also move the cursor to the either BOF or the first row.
Also define the recordset as adUseClient and KeySet.
Give it a whizz
What version of ADO are you using 2.6?
Anyway you could try this one liner before querying record count:
adoRs.Filter = ADODB.FilterGroupEnum.adFi
Then query the record count, this will update the record count and also move the cursor to the either BOF or the first row.
Also define the recordset as adUseClient and KeySet.
Give it a whizz
ASKER
Thanks
I tried your solution but it had no effect.
I tried your solution but it had no effect.
ASKER
I have asked for a refund of points and that this question be closed. If you read the above you will see that I found a funky solution that worked for me and used it to solve my problem.
Thank you to everyone that offered sugestions.
Thank you to everyone that offered sugestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
rDis.Movefirst (placed immediatly after rDis.Update?