CODEWITHSUNDEEP
vb.net
Alfons Galceran
Alfons Galceran

Reputation: 21

How to: Break and Combine Statements in Code vb.net

I am getting following error:

Syntax error (missing operator) in query expression 'tblUnit.Unit_DescFROM tblUnit INNER JOIN tblStocks ON tblUnit.[Unit_Control] = tblStocks.[Stk_LinkUnit]'. andthis one Syntax error in FROM clause.

Part of code that is failing:

Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim connetionString As String
    Dim connection As OleDb.OleDbConnection
    Dim oledbAdapter As New OleDb.OleDbDataAdapter
    Dim ds As New DataSet
    Dim i As Integer
    Dim sql As String
    connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbInventor.mdb"
    connection = New OleDb.OleDbConnection(connetionString)

    connection.Open()
    sql = "SELECT tblStocks.Stk_LinkUnit, tblUnit.Unit_Desc" _
        & "FROM tblUnit INNER JOIN tblStocks ON tblUnit.[Unit_Control] = tblStocks.[Stk_LinkUnit];"

    oledbAdapter.SelectCommand = New OleDb.OleDbCommand(sql, connection)
    oledbAdapter.Fill(ds)
        oledbAdapter.Dispose()
        connection.Close()
        For i = 0 To ds.Tables(0).Rows.Count - 1
        MsgBox(ds.Tables(0).Rows(i).Item(1))
        Next
End Sub

End Class

Upvotes: 2

Views: 361

Answers (3)

user3697824
user3697824

Reputation: 536

You can also use an xml literal to format long or complex SQL however you like:

Dim sql = <sql>
        SELECT tblStocks.Stk_LinkUnit, tblUnit.Unit_Desc 
        FROM tblUnit INNER JOIN tblStocks ON 
                        tblUnit.[Unit_Control] = tblStocks.[Stk_LinkUnit]
        WHERE Foo=@p1 
            AND Bar=@p2
        </sql>.Value

Console.WriteLine(sql)

Be sure to include the .Value

Upvotes: 0

Coding Duchess
Coding Duchess

Reputation: 6919

sql = "SELECT tblStocks.Stk_LinkUnit, tblUnit.Unit_Desc " _
    & "FROM tblUnit INNER JOIN tblStocks ON tblUnit.[Unit_Control] = tblStocks.[Stk_LinkUnit];"

or

sql = "SELECT tblStocks.Stk_LinkUnit, tblUnit.Unit_Desc" _
    & " FROM tblUnit INNER JOIN tblStocks ON tblUnit.[Unit_Control] = tblStocks.[Stk_LinkUnit];"

Upvotes: 2

Maraboc
Maraboc

Reputation: 11093

You should add a space before FROM like this :

sql = "SELECT tblStocks.Stk_LinkUnit, tblUnit.Unit_Desc" _
    & " FROM tblUnit INNER JOIN tblStocks ON tblUnit.[Unit_Control] = tblStocks.[Stk_LinkUnit];"

Upvotes: 2

Related Questions