Binding Data Grids & Adding References [RESOLVED]

[Myriad_Rocker]

It's been a while since I've had VB.Net and even longer since I've had VB6. I'm working in VB6 because I don't have a choice.

My questions are:

1) What is the process for binding a data grid, or simply showing a tables contents on a grid?

2) Are there any specific references that I need to add to a project to get it to work?

3) Can someone give me a brief quick and dirty example of pulling all records from a table (MS Access) and display them in a data grid?



Thanks SO much!

[RhinoBull]

1. Go to components and add MS DataGrid 6.0 (OLEDB) and also ADO Data Control
2. Paste both controls on the form
3. Set (at design in the properties window) grid's Datasource = ADODC1
4. Right click on the data control and select properties.
5. Select from list of drivers MS Jet 4.0 (or 3.51) - depends on Access you might have
6. Select "Use Connection String" and click "Bui;d ..."
7. Follow simple instructions and click OK.
8. Select Recordsource tab on the Property Pages (ado data control)
9. Select adCmdTable from Command Type dropdown box
10. Select Table from next dropdown box.

Save your project and run it.

[demotivater]

Without using the data control, you could do the following...

Add a reference to Microsoft ActiveX Data Objects (2.7 Library in my case)

Add a datagrid to your form and a command button

Private Sub Command1_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    'open connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;Persist Security Info=False"
    
    'set recordset cursor location to client (sometimes grid acts up without this line)
    rs.CursorLocation = adUseClient
    
    'open recordset
    rs.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockReadOnly, adCmdText
    
    'make sure records returned
    If rs.RecordCount = 0 Then
        MsgBox "No records returned"
        Exit Sub
    End If
    
    'set grids datasource
    Set DataGrid1.DataSource = rs
    DataGrid1.Refresh
End Sub

[Myriad_Rocker]

Thanks very much to both of you! Got it.

[ted_123]

Without using the data control, you could do the following...

Add a reference to Microsoft ActiveX Data Objects (2.7 Library in my case)

Add a datagrid to your form and a command button

Private Sub Command1_Click()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
    'open connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;Persist Security Info=False"
    
    'set recordset cursor location to client (sometimes grid acts up without this line)
    rs.CursorLocation = adUseClient
    
    'open recordset
    rs.Open "SELECT * FROM table1", cn, adOpenKeyset, adLockReadOnly, adCmdText
    
    'make sure records returned
    If rs.RecordCount = 0 Then
        MsgBox "No records returned"
        Exit Sub
    End If
    
    'set grids datasource
    Set DataGrid1.DataSource = rs
    DataGrid1.Refresh
End Sub

I find your code very useful. But I have *.dbf file. And when I replace *mdb with *.dbf , it says "unrecognized database format". Is it problem with provider??

[RhinoBull]

To open DBF type data file you'll have to use slightly different technic:

sDbfConString = "Provider=SDEOLEDB;Data Source=C:\DBF\;Mode=Read|Write;SDE OLEDB: RDE Type=FoxPro"
adoConDbf.Open sDbfConString
 
adoRST.Open "File=FILENAME;Index=TAG:INDEXNAME", adoConDbf, adOpenStatic, adLockOptimistic, adCmdTable

[ted_123]

To open DBF type data file you'll have to use slightly different technic:

sDbfConString = "Provider=SDEOLEDB;Data Source=C:\DBF\;Mode=Read|Write;SDE OLEDB: RDE Type=FoxPro"
adoConDbf.Open sDbfConString
 
adoRST.Open "File=FILENAME;Index=TAG:INDEXNAME", adoConDbf, adOpenStatic, adLockOptimistic, adCmdTable

thanks a lot, but it says provider can't be found. can i use other providers ? I think i have following provider installed

Microsoft OLE DB Provider for ODBC
Microsoft OLE DB Provider for Microsoft Index Server
Microsoft OLE DB Provider for Microsoft Active Directory Service
OLE DB Provider for Microsoft Jet
Microsoft SQL Server OLE DB Provider
Microsoft OLE DB Provider for Oracle

[RhinoBull]

It is clearly tells you that Foxpro (or DBase or Clipper ...) drivers have to be installed ...

[demotivater]

You'll have to get the Visual FoxPro ODBC Provider - you can find it here

[ted_123]

You'll have to get the Visual FoxPro ODBC Provider - you can find it here

thanks for your replies.I installed the Microsoft OLE DB Provider for Visual FoxPro 9.0. I also re-installed my visual basic and visual foxpro but still not working. It keeps giving error message "Provider unknown or may not be installed properly"

[demotivater]

Can you post your connection string?

[ted_123]

here it is :

Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
    
cn.Open "Provider=SDEOLEDB;Data Source=C:\DBF\;Mode=Read|Write;SDE OLEDB: RDE Type=FoxPro"

[ted_123]

I used this. and it works !

cn.Open "Provider=VFPOLEDB.1;Data Source=C:\DBF;Persist Security 
Info=False;"

thanks a lot for your help