MSFlexgrid Error 30009 - Invalid Row Value

[sunil128]

I have a VB6 application that runs on a SQL Server database backend and uses an MSFlexgrid control to display a number of records returned from a stored procedure.

I am getting the following error:
Error number 30009
Error Description: Invalid Row Value

Because the users are regularly updating the data, the flexgrid gets refreshed every few minutes or so i.e. the stored proc gets re-run. This error happens intermittently and it does do not occur when the flexgrid is first populated, so I’m 99% certain the error happens when this refresh occurs.

But also on this form there are various drop-down-lists which allow the users to filter which records are displayed. When the users select the filter which displays ‘In progress’ calls, a procedure is called which colours certain rows depending on their date. I get the feeling the error occurs when the refresh is run and this particular filter is set.

Here is my populate flexgrid code, which I pinched off a the net (VBForums),

Public Sub FillFromRecordset_FlexGrid(p_ctlFlexGrid As Control, _
                                      p_objRecordset As Object, _
                                      p_booFieldNamesAsHeaders As Boolean)

'Fills an MSFlexGrid control with values from a recordset
'The code was taken from Si_the_geek, VBForums

'Parameters:
'  p_ctlFlexGrid            - The FlexGrid control to fill
'  p_objRecordset           - The Recordset to get the data from (can be ADO/DAO)
'  p_booFieldNamesAsHeaders - True to show field names as column headers

'Example usage:
'  Call FillFromRecordset_FlexGrid(MSFlexGrid1, objRS, True)

On Error GoTo ErrorHandler

Dim lngCol As Long
Dim lngRow As Long
Dim booOldRedraw As Boolean
Dim FlexGridName As String
Dim i As Long


FlexGridName = p_ctlFlexGrid.Name


  With p_ctlFlexGrid
  
    
      'Set up the required number of columns
    .Cols = p_objRecordset.Fields.Count
    .FixedCols = 0

      'If specified, show field names as headers
    If p_booFieldNamesAsHeaders Then
      .FixedRows = 1
      For lngCol = 0 To p_objRecordset.Fields.Count - 1
        .TextMatrix(0, lngCol) = p_objRecordset.Fields(lngCol).Name
        'sp - populate field headers to combo box for sort by
        'Combo1.AddItem p_objRecordset.Fields(lngCol).Name
      Next lngCol
    End If

      'remove all rows except any headers, and the first data row (cannot be removed)
    .Rows = .FixedRows + 1

      'Turn off screen updates (much faster to fill the data)
    booOldRedraw = .Redraw
    .Redraw = False

      'Check if there is any data
    If p_objRecordset.EOF Then
        'if there is no data, only allow the required blank row, and hide it (height=0)
      .AddItem ""
      .RemoveItem .FixedRows
      .RowHeight(.FixedRows) = 0
    Else

        'We have data, add it one row at a time
        '(nb: there are various ways to do this, this way is quick, and easy to read)
      lngRow = .Rows
      Do While Not p_objRecordset.EOF
          'Add the row (empty)
        .AddItem ""
          'Set the values once cell at a time (avoids problems with Nulls and data containing grid delimiters)
        For lngCol = 0 To p_objRecordset.Fields.Count - 1
            'if you want to format the text for some columns differenly, you can _
            'use If/Else or Select Case here - but its better to do that in your SQL _
            'statement, as it is more efficient, and allows this sub to be re-used easily
          .TextMatrix(lngRow, lngCol) = p_objRecordset.Fields(lngCol).Value & ""
          '.textmatrix(lngrow
        Next lngCol
          'Increment our row counter
        lngRow = lngRow + 1
          'Move to the next row of data
        p_objRecordset.MoveNext
      Loop

        'Remove the blank row we left at the top
      .RemoveItem .FixedRows

    End If


    'If p_ctlFlexGrid = frmNewLog.MSFlexGrid2 Then
    '    MsgBox "msflexgrid22"
    'End If
    
   
    
        If FlexGridName = "MSFlexGrid1" Then
        .ColWidth(0) = 750
        .ColWidth(1) = 1275
        .ColWidth(2) = 1050
        .ColWidth(3) = 500
        .ColWidth(4) = 1500
        .ColWidth(5) = 500
        .ColWidth(6) = 6280
        .ColWidth(7) = 1100
        .ColWidth(8) = 0
        .ColWidth(9) = 1730
    End If
    
    If FlexGridName = "MSFlexGrid2" Then
        .ColWidth(0) = 750
        .ColWidth(1) = 900
        .ColWidth(2) = 1050
        .ColWidth(3) = 530
        .ColWidth(4) = 0
        .ColWidth(5) = 0
        .ColWidth(6) = 6370
        .ColWidth(7) = 1050
        .ColWidth(8) = 0
        .ColWidth(9) = 1500
    End If
    
    For i = 0 To 8
    p_ctlFlexGrid.ColAlignment(i) = flexAlignLeftCenter
    Next i
    
   If FlexGridName = "MSFlexGrid1" Then
    'If MSFlexGrid1.Rows > 1 Then
        If cboStatus.ListIndex = 2 Then Form1.CheckFixByDue
        'dont run this again if its already happened
    'End If
End If
    
    
    

'Form1.MSFlexGrid1.CellBackColor = vbRed
'Form1.MSFlexGrid1.TopRow


    
    
    
       'Re-enable screen updates (if was previously enabled)
    .Redraw = booOldRedraw
    
    
      'Force a redraw of the grid
    .Refresh
    
    
    
MSFlexGrid1.Row = iCurrentRow
MSFlexGrid1.TopRow = iTopRow
MSFlexGrid1.RowSel = iCurrentRow
MSFlexGrid1.Col = 0
MSFlexGrid1.ColSel = .Cols - 1



    'MSFlexGrid1.ColAlignment(6) = flexAlignLeftCenter
  
  End With
    
    'If Status = InProgess and there are no rows on the grid check the fixby date
    'is in the past

    'If MSFlexGrid1.Rows <> 2 Then
        
     '   If cboStatus.ListIndex = 2 Then CheckFixByDue
        'dont run this again if its already happened
               
    'End If


    
Exit Sub

Here is the code the changes the row colours

Public Sub CheckFixByDue()
'This higlights jobs that should have been completed up to the end of last week
'so that the users have an easy way of identifying them

On Error GoTo ErrorHandler

Dim currentrow As Long
Dim FixBydate As Date
Dim i As Integer
Dim i2 As Integer

currentrow = 0

currentrow = MSFlexGrid1.Row

If currentrow = 0 Then
    MsgBox "Please select a row", vbInformation, MsgBoxCaption
    Exit Sub
End If

'Loop through every record, check the fixby column, if fixBydate is before today
'set the colour of that row/record to another color, by looping form col 0 to end
'N.B. row = current row, rows = total number of rows
With MSFlexGrid1
For i = 1 To .Rows - 1
    FixBydate = IIf(.TextMatrix(i, 8) = "", Format("31/12/2999", "DD/MM/YYYY"), .TextMatrix(i, 8)) 'v.1.4.4 Checks is no rows returned and exits sub
    
    If FixBydate = "31/12/2999" Then
        Exit Sub
    End If
    
    '1.4.3
    If FixBydate < Date Then
        'change the rows colour
        .Row = i
        
        For i2 = 0 To .Cols - 1
            .Col = i2
            .CellBackColor = QBColor(14)
        Next i2
    End If
Next i
End With

Exit Sub

ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Error Description: " & Err.Description & vbCrLf & _
"Error Source: " & Me.Name & "." & "CheckFixByDue", _
vbCritical, MsgBoxCaption & ": Error"

Call GlobalErr(Err.Number, Err.Description, Me.Name, "CheckFixByDue")

And here is my refresh code:

Private Sub Timer1_Timer()

Static TimerCounter As Long

TimerCounter = TimerCounter + 1
If TimerCounter Mod 10 = 0 Then

    iTopRow = MSFlexGrid1.TopRow
    iCurrentRow = MSFlexGrid1.Row
    
    Call RunMainSql(SelectedBranch, SelectedStatus, SelectedArea, SelectedContractor, Form1.MSFlexGrid1)
    
    'If cboStatus.ListIndex = 2 Then CheckFixByDue
    
    TimerCounter = 0
    
End If
 
End Sub

Can anyone see what might be causing the error? Your help will be greatly appreciated; it will stop my users from nagging me for a start!

 

[tedsmith]

I'd put a number of On error goto statements throughout the subroutine and have a number of separate Errorhandler routines .
And have an On Error Goto in the Timer routine - in fact in every routine in your app!

That way you can quickly find exactly where the error is.

Also because you can never trust clients it is a good idea to store the error in a text file log. Eg

-------------

ErrorHandler1:
RecordError Now() & " R1 " & Error '(R2---R9 or whatever)
'optional show error on screen
Resume Next

------------

Sub RecordError(ErrorMessage as string)
Open Report.txt for Append as #1
Print #1, Errormessage
Close #1
End sub

If you can recreate the error in the IDE it is better to have On Error Goto 0 at the head of the subs than the program will stop where the error is.

 

[sunil128]

Many thanks tedsmith for your reply, we have however just worked out what was causing the problem. Basicallly the users requested that the vertical scroll bar for the MSflexgrid kept its position if the grid was refreshed or if a new form is opened. Previously it would just jump back to the top row.

Here is the code we added to maitain the scroll bar position:

MSFlexGrid1.Row = iCurrentRow
MSFlexGrid1.TopRow = iTopRow
MSFlexGrid1.RowSel = iCurrentRow
MSFlexGrid1.Col = 0
MSFlexGrid1.ColSel = .Cols - 1

Basically what was happeneing was that records were being updated (i.e changing status) and therefore being removed from the current MSFlexgrid view, but the above code was trying to maintain the position the current row for a row that now doesnt exist.
Hopefully this will help someone with a similar problem!

I will use your suggestions in future anyway tedsmith, they seem like good practice.

 

[sunil128]

just to add, the actual solution was this do while loop shown below:

'v1.4.5 - if a row is removed from view (i.e. change of status) this code will make icurrent row = the updated max no of rows displayed

    Do While iCurrentRow >= MSFlexGrid1.Rows
        iCurrentRow = iCurrentRow - 1
    Loop

    'this forces the vertical bar position
    MSFlexGrid1.Row = iCurrentRow
    MSFlexGrid1.TopRow = iTopRow
    MSFlexGrid1.RowSel = iCurrentRow
    MSFlexGrid1.Col = 0
    MSFlexGrid1.ColSel = .Cols - 1