<help>Populate DATAGRID using ADODB [SOLVED]

[Habibi]

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.

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

[demotivater]

Similar to what you already have...

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

[Habibi]

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

[demotivater]

Are you reference Microsoft ADO in your project?

[Habibi]

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

[demotivater]

Try adding this:

rs.CursorLocation = adUseClient

Before the rs.Open line.

[Habibi]

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

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

[demotivater]

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

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

[Habibi]

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

Thanks

[demotivater]

Something like the following:

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.

[Habibi]

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

Many Thanks for your help dude.

Best Regards,
Habibalby

[demotivater]

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.

[Habibi]

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

Thanks,
habibalby

[zen_master]

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

can someone explain what this is (bold)?