troubleshooting Question

SELECT statement with multiple inner joins in visual basic 2005

Avatar of systems_ax
systems_ax asked on
.NET ProgrammingVisual Basic.NET
22 Comments1 Solution1085 ViewsLast Modified:
I am developing with visual basic 2005 and everything was working great until I divided my 1 very large access table into other 5 tables.  I have 2 forms, 1 form is the search form with a datagrid that populates multiple record data.  When I double click on a particular record, the following code executes successfully when I am reading from only 1 table:

 Private Sub dgvLastNames_CellDoubleClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvLastNames.CellDoubleClick
        If dgvLastNames.CurrentRow.Index > -1 Then
            Dim editForm As New frmAddClient
            editForm.ClientId = CType(dgvLastNames.CurrentRow.Cells(0).Value.ToString(), Int32)

                     Me.Dispose()
            'If editForm.WindowState = FormWindowState.Minimized Then
            '    editForm.WindowState = FormWindowState.Normal
            'End If
           editForm.ShowDialog()
            RetrievClients()
        Else : MessageBox.Show("please select a row for editing.", "No Row selected", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

    End Sub



Private Sub RetrievClients()
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb")

     
       Dim adapter As New OleDbDataAdapter("select * from Clients", conn)
        Dim dt As New DataTable("Clients")
        adapter.Fill(dt)
        dgvLastNames.DataSource = dt
      End Sub

on the form where the record populates executes this code:

   Private Sub RetrievClientsDetails()
       
        Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=test.mdb")
        Dim command As New OleDbCommand("select * from Clients where ClientId=" & _clientID, conn)
         conn.Open()
        Dim contactreader As OleDbDataReader = command.ExecuteReader
        contactreader.Read()
        Me.txtFirst.Text = contactreader("ClientFirstName").ToString
        Me.txtMiddle.Text = contactreader("ClientMiddleName").ToString
        Me.txtLast.Text = contactreader("ClientLastName").ToString
        Me.txtCompany.Text = contactreader("ClientCompany").ToString
        Me.txtAddress1.Text = contactreader("ClientStreetAddress1").ToString
        Me.txtAddress2.Text = contactreader("ClientStreetAddress2").ToString
        Me.txtID.Text = contactreader("ClientIdNumber").ToString

        Me.txtCity.Text = contactreader("ClientCity").ToString
        Me.txtState.Text = contactreader("ClientState").ToString
        Me.txtPostal.Text = contactreader("ClientPostalCode").ToString
        Me.txtCountry.Text = contactreader("ClientCountry").ToString
        Me.txtDate.Text = contactreader("ClientDateofBirth").ToString
        Me.txtPhone1.Text = contactreader("ClientTelephoneNumberOne").ToString
        Me.txtPhone2.Text = contactreader("ClientTelephoneNumberTwo").ToString
        Me.txtFax.Text = contactreader("ClientFaxNumber").ToString
        Me.txtEmail.Text = contactreader("ClientEmail").ToString
        conn.Close()
           
    End Sub

HOWEVER WHEN I TRY to join multiple table data for 1 client on which I double clicked within the grid it is not working:

this is what I tried for both and replaced the current select statement:

please offer a suggestion.
Dim command As New OleDbCommand("select * from tblClients INNER JOIN tblClientDetails ON tblClients.ClientId = tblClientDetails.ClientId where ClientId=" & _clientID, conn)
 
error that I receive is:
The specified field 'ClientId' could refer to more than one table listed in the FROM clause of your SQL statement.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 22 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 22 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros