Syntax error problem. 2

[stillwillyboy]

I am trying to join three tables and pull some info. With the following code, I keep getting an Exception error stating Incorrect syntax near '.'. The exception error is pointing at the line: m_daDataAdapter.Fill(m_dtClients)

(Please note that there are proper line continuations in my code, but the formatting changes when copying and pasting.)

I have looked and looked and can't see the problem. Any help will be rewarded. Thanks, Bill.

Public Class frmConnectToSQLTest
    Private m_cnADONetConnection As New System.Data.SqlClient.SqlConnection()
    Private m_daDataAdapter As SqlClient.SqlDataAdapter
    Private m_cbCommandBuilder As SqlClient.SqlCommandBuilder
    Private m_dtClients As New DataTable
    Private m_rowPosition As Integer = 0

Private Sub frmConnectToSQLTest_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

m_cnADONetConnection.ConnectionString = _
"Data Source=B-7M2KXC45F9FT4\SQLEXPRESS;Initial Catalog=ClientPayroll;Trusted_Connection=yes;User Id=B-7M2KXC45F9FT4\administrator"

       m_cnADONetConnection.Open()

m_daDataAdapter = New SqlClient.SqlDataAdapter("Select " & _
"tbl_ClntBasicInfo.ClientNumber, tbl_ClntBasicInfo.LastName, " & _
"tbl_ClntBasicInfo.FirstName, tbl_ClntBasicInfo.MiddleInitial, " & _
"tbl_ClntPayRates.Average, tbl_ClntPayRates.Fringe " & _
"tbl_ClntClientBranch.BranchNumber " & _
"FROM (tbl_ClntBasicInfo " & _
"Inner Join tbl_ClntPayRates " & _
"ON tbl_ClntBasicInfo.ClientNumber = tbl_ClntPayRates.ClientNumber )" & _
"INNER JOIN tbl_ClntClientBranch " & _
"ON tbl_ClntBasicInfo.ClientNumber = tbl_ClntClientBranch.ClientNumber", m_cnADONetConnection)

        m_cbCommandBuilder = New SqlClient.SqlCommandBuilder(m_daDataAdapter)
        m_daDataAdapter.Fill(m_dtClients)
        Me.ShowCurrentRecord()

End Sub

Private Sub frmConnectToSQLTest_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        m_cnADONetConnection.Close()
        m_cnADONetConnection.Dispose()

End Sub

Private Sub ShowCurrentRecord()
        If m_dtClients.Rows.Count = 0 Then
            txtClientNumber.text = ""
            txtLastName.text = ""
            txtFirstName.Text = ""
            txtMiddleInitial.Text = ""
            txtAverage.Text = ""
            txtFringe.Text = ""
            txtLocation.Text = ""
            Exit Sub
        End If

txtClientNumber.text = _
m_dtClients.Rows(m_rowPosition)("ClientNumber").ToString

txtLastName.Text = _
m_dtClients.Rows(m_rowPosition)("LastName").ToString

txtFirstName.Text = _
m_dtClients.Rows(m_rowPosition)("FirstName").ToString()

txtMiddleInitial.Text = _
m_dtClients.Rows(m_rowPosition)("MiddleInitial").ToString()

txtAverage.Text = _
m_dtClients.Rows(m_rowPosition)("Average").ToString()

txtFringe.Text = _
m_dtClients.Rows(m_rowPosition)("Fringe").ToString()

txtLocation.Text = _
m_dtClients.Rows(m_rowPosition)("BranchNumber").ToString()

End Sub
End Class

 

[djj55]

Try querying only one table, then two, then all to see if there is a problem. This involves some work but can tell you if the problem is in the SQL.
djj

 

[onpnt]

Typical debugging methods go along with any language. If you have to build a string I still feel the best method of debugging that string is

1) Write it as it should look built prior to adding it to your code. Example in your case would be to use your SQL query tool of choice to write a working statement and then put it into your code.

2) If it is not working either add a richtextbox or some control easily removed and write the string to it so you can copy/paste it into your query tool and debug the issue.

If you do that with your SELECT you should see a missing ","

Select  
tbl_ClntBasicInfo.ClientNumber, tbl_ClntBasicInfo.LastName,  
tbl_ClntBasicInfo.FirstName, tbl_ClntBasicInfo.MiddleInitial,  
tbl_ClntPayRates.Average, tbl_ClntPayRates.Fringe  
tbl_ClntClientBranch.BranchNumber  
FROM tbl_ClntBasicInfo  
Inner Join tbl_ClntPayRates ON tbl_ClntBasicInfo.ClientNumber = tbl_ClntPayRates.ClientNumber 
INNER JOIN tbl_ClntClientBranch ON tbl_ClntBasicInfo.ClientNumber = tbl_ClntClientBranch.ClientNumber

General FAQ faq333-2924
5 steps to asking a question faq333-3811

 

[stillwillyboy]

Both suggestions helpful I see the problem now.

Thanks.

Bill