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)

            'If editForm.WindowState = FormWindowState.Minimized Then
            '    editForm.WindowState = FormWindowState.Normal
            'End If
        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")
        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)
        Dim contactreader As OleDbDataReader = command.ExecuteReader
        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
    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.
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