[RESOLVED] Having trouble with SQL statement

[Pasvorto]

I am geeting an invalid syntax near keyword 'INNER' error on this.

Dim SQL2 As New SqlClient.SqlCommand("SELECT MACHINETOOL[TOOL], MACHINE[NAME] INNER JOIN MACHINETOOL[MACHINE] ON MACHINETOOL[MACHINE] = MACHINE[MACHINE] WHERE LTRIM(RTRIM(MACHINETOOL[TOOL])) = " & "'" & Trim(cboTool.Text) & "'")

Any ideas?

[GaryMazzone]

A couple of problems that I see.

First there is no FROM clause. You go straight from the Select to Inner Join... that is not valid.
Second you need a period ( . ) between the table names and the field name in the select , inner join and where clauses

like this for the SQL:

SELECT 
    MACHINETOOL.[TOOL], 
    MACHINE.[NAME] 
FROM MACHINETOOL 
INNER JOIN MACHINETOOL [MACHINE] 
    ON MACHINETOOL.[MACHINE] = MACHINE.[MACHINE] 
WHERE LTRIM(RTRIM(MACHINETOOL.[TOOL])) =

[si_the_geek]

Thread moved to 'Database Development' forum - which is where you should always post SQL questions (while SQL can be used in VB, it is certainly not specific to VB)

[Pasvorto]

So I have this now:

            Dim READER2 As SqlDataReader
            Dim SQL2 As New SqlClient.SqlCommand
            SQL2.CommandText = "SELECT MACHINETOOL.[TOOL], MACHINE.[NAME] FROM MACHINETOOL "
            SQL2.CommandText += " INNER JOIN MACHINE ON MACHINE.[MACHINE] = MACHINETOOL.[MACHINE] "
            SQL2.CommandText += " WHERE(LTrim(RTrim(MACHINETOOL.[TOOL])) = " & "'" & Trim(cboTool.Text) & "'"
            SQL2.Connection = Cnxn
            SQL2.CommandType = CommandType.Text
            READER2 = SQL2.ExecuteReader
            Using READER2
                If READER2.HasRows Then
                    While READER2.Read
                        lstMachine.Items.Add(READER2("machine") & " - " & READER2("Name"))
                    End While
                Else
                    lstMachine.Items.Add("No machine found")
                End If
            End Using
            SQL2.Dispose()
            '===========================

I select a tool (A-106). I get an error " Incorrect syntax near 'A-106' "

[GaryMazzone]

Set a breakpoint after you set the SQL Statement. Then See what is in the actual Statement and postit here.

With a quick look it looks to be missing a final close paren ) after the added text from the combobox

[Pradeep1210]

You are trying to access READER2("machine"), but there is no column named "machine" in the Reader2.
Also, a space is required after WHERE in " WHERE(LTrim ..."

[Pasvorto]

It is working now. Thanks. Here is the final code. Yes, I needed a ) and I needed to select 'machine' as well. You guys are good; :-)

Dim READER2 As SqlDataReader
            Dim SQL2 As New SqlClient.SqlCommand
            SQL2.CommandText = "SELECT MACHINETOOL.[TOOL], MACHINE.[MACHINE], MACHINE.[NAME] FROM MACHINETOOL "
            SQL2.CommandText += " INNER JOIN MACHINE ON MACHINE.[MACHINE] = MACHINETOOL.[MACHINE] "
            SQL2.CommandText += " WHERE(LTrim(RTrim(MACHINETOOL.[TOOL]))) = " & "'" & Trim(cboTool.Text) & "'"
            SQL2.Connection = Cnxn
            SQL2.CommandType = CommandType.Text
            READER2 = SQL2.ExecuteReader
            Using READER2
                If READER2.HasRows Then
                    While READER2.Read
                        lstMachine.Items.Add(READER2("machine") & " - " & READER2("Name"))
                    End While
                Else
                    lstMachine.Items.Add("No machine found")
                End If
            End Using
            SQL2.Dispose()
            '===========================