<help>Populate DATAGRID using ADODB [SOLVED]

Hi guys,

I need help to populate a DataGrid usingi the ADODB 2.8 by MS-Access Table.

Here's the code which I use for ADODC but in my case I want to use ADODB.

VB Code:

Private Sub Combo1_Click()
 
SQL = "Select EmployeesData.[FirstName], EmployeesData.[SecondName], Contacts.[Mobile]"
SQL = SQL & "From [EmployeesData], [Contacts]"
SQL = SQL & "Where EmployeesData.[EmpID] = Contacts.[EmpID]"
SQL = SQL & "And EmployeesData.[FirstName]='" & Combo1.Text & "';"
 
Adodc1.RecordSource = SQL                   ' Execute your query
Set DataGrid1.DataSource = Adodc1    ' Bind to the data control

Can anyone help please ??

Many thanks in advance,

habibalby

Similar to what you already have...

VB Code:

Dim cn as new adodb.connection
Dim rs as new adodb.recordset
 
cn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=<Path of your database.mdb>;"
 
rs.open SQL, cn, adOpenKeyset, adLockOptimistic, adCmdText
 
if rs.eof = false then
  Set DataGrid1.DataSource = rs
else
  msgbox "No records"
end if

I've tried this code it doesn't do anything mate! Any further help ?

Are you reference Microsoft ADO in your project?

Yes, becuase I've tired the Same Query using ListView and everything works fine.

Try adding this:

VB Code:

rs.CursorLocation = adUseClient

Before the rs.Open line.

I've tried that also same and it says Operation is not allowed when the Object is Open.

VB Code:

Private Sub Combo1_Click()
 
If RS.State = adStateOpen Then RS.Close
SQL = "Select EmployeesData.[FirstName], EmployeesData.[SecondName], Contacts.[Mobile]"
SQL = SQL & "From [EmployeesData], [Contacts]"
SQL = SQL & "Where EmployeesData.[EmpID] = Contacts.[EmpID]"
SQL = SQL & "And EmployeesData.[FirstName]='" & Combo1.Text & "';"
RS.Open SQL, Conn, adOpenKeyset, adLockOptimistic, adCmdText
 
'RS.Requery
'Adodc1.RecordSource = SQL                   ' Execute your query
'Set DataGrid1.DataSource = Adodc1    ' Bind to the data control
 
'\\\\\\\\\\\\\\\\\\\\
 
If RS.EOF = False Then
RS.CursorLocation = adUseClient
 
  Set DataGrid1.DataSource = RS
Else
  MsgBox "No records"
End If
 
 
End Sub

Habibalby

You had the cursor location in the wrong spot. Try this:

VB Code:

Private Sub Combo1_Click()
 
If RS.State = adStateOpen Then RS.Close
SQL = "Select EmployeesData.[FirstName], EmployeesData.[SecondName], Contacts.[Mobile]"
SQL = SQL & "From [EmployeesData], [Contacts]"
SQL = SQL & "Where EmployeesData.[EmpID] = Contacts.[EmpID]"
SQL = SQL & "And EmployeesData.[FirstName]='" & Combo1.Text & "';"
 
RS.CursorLocation = adUseClient
 
RS.Open SQL, Conn, adOpenKeyset, adLockOptimistic, adCmdText
 
'RS.Requery
'Adodc1.RecordSource = SQL                   ' Execute your query
'Set DataGrid1.DataSource = Adodc1    ' Bind to the data control
 
'\\\\\\\\\\\\\\\\\\\\
 
If RS.EOF = False Then
  Set DataGrid1.DataSource = RS
Else
  MsgBox "No records"
End If
 
 
End Sub

Yes, it works like a charm Very Good Thank Your For you Help4, but how do I make a Caption for the Columns ?

Thanks

Something like the following:

VB Code:

For i = 0 To LeadsGrid.Columns.count - 1
  If LeadsGrid.Columns(i).DataField = "ProjJobNum" Then
    LeadsGrid.Columns(i).Width = Len(LeadsGrid.Columns(i)) + 1000
    LeadsGrid.Columns(i).Caption = "Job Number"
  End If
  'do the same with other columns...
Next i

Of course, you'll have to change the grid name and field names to suit your program. Also shows you how to change column width.

It's seems this will take a big effort, leave it.

Many Thanks for your help dude.

Best Regards,
Habibalby

You could also change the field names in the query to set them as the column names.

ie: Select EmployeesData.[FirstName] AS Name

That would make Name your column name.

Yes, I can do that too. I was thinking of it yesterday.

Thanks,
habibalby

rs.open SQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

can someone explain what this is (bold)?