Grid problem

Hello everyone

the following is my connection string to the database

instantiate the connection object
Set cn = New ADODB.Connection
'specify the connectionstring
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Ruhsa Health Station\RHS.mdb"
'open the connection
cn.Open

'instantiate the recordset object
Set rs = New ADODB.Recordset
'open the recordset
With rs
.Open "tblPatient_Details", cn, adOpenKeyset, adLockPessimistic, adCmdTable
'loop through the records until reaching the end or last record

End With




Now if i want to use a Data grid, Flex grid or Grid how do i connect it with the database and how do i display it in the one of the GRIDS

Please help

:wave: Here i used the mysql and offcourse its going to be used to u why because after retrieving the data we are just palcing it in the grid by using the fillgrid function so go through it and make changes according to ur apps

Code:

---


 Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
    '---> Change your DSN <-----'
        .ActiveConnection = "Provider=MSDASQL;Driver={MySQL ODBC 5.1 Driver};Server=localhost;Port=3306;Database=worldspace;User=root; Password=password;Option=10;"
        .LockType = adLockOptimistic
        .CacheSize = 1
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .Source = "Select * from user
        .Open
        Call Fill_Grid(rs, Me.MSHFlexGrid1)
        Set .ActiveConnection = Nothing
        Set rs = Nothing
    End With

---

Write this code in a module or in the form itself its not a problem were your writing and go through the code once understand it its going to fill the grid according to your data

Code:

---



Function Fill_Grid(rs As ADODB.Recordset, _
                  ctr As MSHFlexGrid)

    Dim lCols As Long
    Dim lRows As Long
    Dim lCol As Long
    Dim lRow As Long
    Dim sColor As String
 

    'Check for BOF
    If (Not (rs.BOF)) Then
        'Num of Columns
        lCols = rs.Fields.Count
        With ctr
            .Cols = lCols
            .Row = 0
            'Fill Columns Headers
            For lCol = 0 To lCols - 1
                .Col = lCol
                .Text = rs(lCol).Name
                .ColWidth(lCol) = 1500
            Next
            'Get data
            lRow = 1
       
            Do While Not rs.EOF
                For lCol = 0 To lCols - 1
                    .Col = lCol
                    .Row = lRow
                    .Text = rs.Fields(lCol).Value
                                       
                Next
                rs.MoveNext
                lRow = lRow + 1
                .Rows = lRow + 1
            Loop
        End With
    End If
End Function

---

:D :thumb:

Try this joe

Code:

---

Dim vFldsArray() As Variant 'Array to hold the recordset
Dim CN As ADODB.Connection
Dim rS As ADODB.Recordset

Private Sub Form_Load()
Dim sSQL As String
Dim iCols As Integer
Dim iRows As Integer
Dim iNoOfRecords As Integer
Dim iNoOfFields As Integer

  Set CN = New ADODB.Connection

  'change the datasource
  CN.ConnectionString = "provider=microsoft.jet.oledb.4.0;" _
                        & "data source=" & App.Path & "\db1.mdb"
  CN.Open


  Erase vFldsArray
 
  Set rS = New ADODB.Recordset
  sSQL = "SELECT * FROM MyTable" 'change the table
  rS.Open sSQL, CN, adOpenKeyset, adLockOptimistic, adCmdText
 
  vFldsArray = rS.GetRows
  rS.Close
 
  iNoOfFields = UBound(vFldsArray, 1) + 1
  iNoOfRecords = UBound(vFldsArray, 2) + 1
 
 
  With Me.MSFlexGrid1
    .Cols = iNoOfFields + .FixedCols
    .Rows = iNoOfRecords + .FixedRows

   
    For iRows = 1 To .Rows - 1
      For iCols = 1 To .Cols - 1
       
        'Populating the Grid
        .TextMatrix(iRows, iCols) = vFldsArray(iCols - 1, iRows - 1)
       
      Next iCols
    Next iRows
   
  End With
End Sub

Private Sub Form_Unload(Cancel As Integer)
  If rS.State = adStateOpen Then rS.Close
  Set rS = Nothing 'Releasng the rS memory
 
  If CN.State = adStateOpen Then CN.Close
  Set CN = Nothing 'Releasing the connection memory
  Erase vFldsArray 'Releasing the Array
End Sub

---

Happy programming :wave: :afrog:

Thread moved to Database Development forum (the "VB6" forum is meant for questions which don't fit in more specific forums)

There is yet another option via the Fill FlexGrid link in my signature