We help IT Professionals succeed at work.

vb6 with Sheridan ssOleDBGrid shows no data

Medium Priority
2,329 Views
Last Modified: 2012-05-12
My VB6 application uses theSheridan ssOleDBGrid to display table data from a MS SQL Server data base. The data grid is bound via a MS ADO Data Control named "adoDC". The connection occurs thru the DataEnvironment "deAlpha" via connection "Alpha" (an ODBC connection).
Test example:

Private Sub Form_Load()
  Dim sql As String
  Dim sConnect As String
 
  deAlpha.Alpha.Open
  sConnect = deAlpha.Alpha.ConnectionString
  sql = "Select * from Alpha"
  With adoDc
    .ConnectionString = sConnect
    .RecordSource = sql
    .Refresh
    MsgBox .Recordset.RecordCount
  End With 'adoDC
 
  ssgTest.Caption = "Rows: " & Format$(ssgTest.Rows, "0")
End Sub

On WinXP the message box reads"50782". The Grid displays data as expected and the caption is "Rows: 50782".
On Win7 the message box reads "50782". The Grid displays nothing and the caption is "Rows: 0".
So it seems that no data at all are connected to the grid.
As far as I know all needed dll's and ocx are placed into the directory where the exe file is residing.
What may be going wrong?
Comment
Watch Question

You might try (if you haven't already) running it in XP2 compatibility mode.

Author

Commented:
Hello jimthurgood
that is out of question. I definitely have to run my application on Win7.
Josef
Just to be clear, I am suggesting the compatibility mode built in to Windows 7.   Even if this is not the final solution it would be worth trying as a diagnostic tool.

http://windows.microsoft.com/en-US/windows7/Make-older-programs-run-in-this-version-of-Windows

Author

Commented:
Hello jimthurgood
XPMode as diagnostical tool may be a good idee so I will give it a try. But I am very sceptical because I do the development on a virtual XP machine (VmWare) hosted on a Win7 64 bit machine. My application running on this virtual XP is connected to the SQL Server running on the Win7 host.
So what do you think would I gain if I run my application in XP2 compatibility mode?
Which diagnostic tools do you suggest?
Many thanks
Josef
In those circumstances I am also sceptical and I cannot replicate that setup here so i don't know what to suggest.

Author

Commented:
Hi jimthurgood,
what a pity! Nevertheless many thanks again!
Meanwhile I discovered a workaround: Using the grid in unbound mode shows the data also with Win7.
To achieve this I did the following:
Add another ssOleDbGrid named ssgUnbound.

Add some code:
in General of the Form:
Option Explicit
Private iFldCount As Integer
Private rsRSet As ADODB.Recordset
Private bRstOpen As Boolean

in Form_Load:
  Set rsRSet = New ADODB.Recordset
  rsRSet.CursorType = adOpenKeyset
  rsRSet.LockType = adLockOptimistic
  rsRSet.Open sql, sConnect
  bRstOpen = True
  iFldCount = rsRSet.Fields.Count
 
  'the following line is explicitly show in code for clarification
  ssgUnbound.DataMode = ssDataModeUnbound
  ssgUnbound.Columns.RemoveAll

  Dim iFld As Integer
  For iFld = 0 To (iFldCount - 1)
    ssgUnbound.Columns.Add iFld
    ssgUnbound.Columns(iFld).Caption = rsRSet.Fields(iFld).Name
  Next iFld

and this:
Private Sub Form_Unload(Cancel As Integer)
  If bRstOpen Then
    rsRSet.Close
    bRstOpen = False
  End If
  deAlpha.Alpha.Close
  End
End Sub

and finally this (immediately from an example of Sheridan):
Private Sub ssgUnbound_UnboundReadData(ByVal RowBuf As SSDataWidgets_B_OLEDB.ssRowBuffer, StartLocation As Variant, ByVal ReadPriorRows As Boolean)
  Dim iRBRow As Integer
  Dim iGridRows As Integer
  Dim iFld As Integer

  iGridRows = 0
  If IsNull(StartLocation) Then 'If the Grid is empty
      If ReadPriorRows Then   'If ReadPriorRows is True
        rsRSet.MoveLast     'then the Grid is being
      Else                'scrolled up towards the top
        rsRSet.MoveFirst
      End If
    Else                  'If Grid contains data
      rsRSet.Bookmark = StartLocation
      If ReadPriorRows Then
        rsRSet.MovePrevious
      Else
        rsRSet.MoveNext
      End If
  End If
  For iRBRow = 0 To RowBuf.RowCount - 1
    If rsRSet.BOF Or rsRSet.EOF Then Exit For
    Select Case RowBuf.ReadType
    Case ssReadTypeAllData      'All data must be read
      RowBuf.Bookmark(iRBRow) = rsRSet.Bookmark
      For iFld = 0 To (iFldCount - 1)
        RowBuf.Value(iRBRow, iFld) = rsRSet(iFld)
      Next iFld
    Case ssReadTypeBookmarkOnly 'Only bookmarks must be read
      RowBuf.Bookmark(iRBRow) = rsRSet.Bookmark
    End Select    'Cases 2 and 3 are not used by DBGridEnd Sub

    If ReadPriorRows Then
      rsRSet.MovePrevious
    Else
      rsRSet.MoveNext
    End If

    iGridRows = iGridRows + 1

  Next iRBRow

  RowBuf.RowCount = iGridRows
 
End Sub


This solution works, but that means that I have to recode a very big project.
I think some internal umtees of the data connection machinery between data widgets and Win7.
Unfortunately Sheridan ceased to exist long before the ascendancy of Win7 and there seems to be nobody left to be asked ...
Or do you know sombody?
Josef

Author

Commented:
Soorythat we came to no satisfying solution. But I honor your engament and therefor grant the points to you
sincerly
Josef