Syntax error (missing operator) in VB

Question

0.00/5 (No votes)

See more:

Trying to make a ms access database search engine, have been getting this error all day. ill post my code and error log

VB

Imports System.Data.OleDb

Public Class Form1

    Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Public myConnection As OleDbConnection = New OleDbConnection
    Public dr As OleDbDataReader

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
        dataFile = "C:\Users\CADPC\Documents\Parts# Database.mdb"
        connString = provider & dataFile
        myConnection.ConnectionString = connString
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        myConnection.Open()
        oldpartText.Clear()
        newpartText.Clear()
        descriptionText.Clear()
        materialText.Clear()
        Dim str As String
        str = "SELECT * FROM Cnc_Parts_List WHERE  Old Part# = '" & inputText.Text & "'"
        Dim cmd As OleDbCommand
        cmd = New OleDbCommand(str, myConnection)
        dr = cmd.ExecuteReader()
        While dr.Read()
            oldpartText.Text = dr("Old Part#").ToString
            newpartText.Text = dr("New Part#").ToString
            descriptionText.Text = dr("Description").ToString
            materialText.Text = dr("Material").ToString
        End While
        myConnection.Close()
    End Sub
End Class

Error message:

Exception thrown: 'System.Data.OleDb.OleDbException' in System.Data.dll
System.Transactions Critical: 0 : <TraceRecord xmlns="http://schemas.microsoft.com/2004/10/E2ETraceEvent/TraceRecord" Severity="Critical"><traceidentifier>http://msdn.microsoft.com/TraceCodes/System/ActivityTracing/2004/07/Reliability/Exception/Unhandled<description>Unhandled exception<appdomain>WindowsApp1.exe<exception><exceptiontype>System.Data.OleDb.OleDbException, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089<message>Syntax error (missing operator) in query expression 'Old Part# = '001-200''.<stacktrace>   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
   at WindowsApp1.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\CADPC\AppData\Local\Temporary Projects\WindowsApp1\Form1.vb:line 28
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message&amp; m)
   at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
   at System.Windows.Forms.Button.WndProc(Message&amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at WindowsApp1.My.MyApplication.Main(String[] Args) in :line 81<exceptionstring>System.Data.OleDb.OleDbException (0x80040E14): Syntax error (missing operator) in query expression 'Old Part# = '001-200''.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.ExecuteReader()
   at WindowsApp1.Form1.Button1_Click(Object sender, EventArgs e) in C:\Users\CADPC\AppData\Local\Temporary Projects\WindowsApp1\Form1.vb:line 28
   at System.Windows.Forms.Control.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnClick(EventArgs e)
   at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
   at System.Windows.Forms.Control.WmMouseUp(Message&amp; m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message&amp; m)
   at System.Windows.Forms.ButtonBase.WndProc(Message&amp; m)
   at System.Windows.Forms.Button.WndProc(Message&amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
   at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at WindowsApp1.My.MyApplication.Main(String[] Args) in :line 81

What I have tried:

have googled this problem for hours, any help would be appreciated

Posted 16-May-17 6:32am

Member 13204422

Updated 17-May-17 5:37am

Maciej Los

v2

Add a Solution

Comments

Member 13204422 16-May-17 12:37pm

getting this error, forgot to put in first post

System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression 'Old Part# = '001-200''.'

[no name] 16-May-17 12:41pm

You need to enclose your odd named table names with brackets.

3 solutions

Solution 3

Not your question, but still a problem you have.

VB

str = "SELECT * FROM Cnc_Parts_List WHERE  Old Part# = '" & inputText.Text & "'"

Never build an SQL query by concatenating with user inputs, it is named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability.
SQL injection - Wikipedia[^]
SQL Injection[^]

Posted 16-May-17 8:48am

Patrice T

Updated 17-May-17 6:36am

v2

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2017-05-16T06:43:00

Solution 1

Simple enough. You cannot have a column or table named Old Part#. Spaces are not really allowed without being enclosed in square brackets, "[]".

Your query should be

... WHERE [Old Part#] = 'value'

Your query is also vulnerable to SQL Injection Attacks. Don't use string concatenation to build SQL queries. Google for "SQL Injection Attack" to find out why what you're doing is so bad, then search for "VB.NET parameterized queries" to find out what to do about it.

Posted 16-May-17 6:43am

Dave Kreskowiak

Comments

Member 13204422 16-May-17 12:58pm

Thanks for the help, ill make sure to look that up, i did make the changes you said and im getting a new error

System.Data.OleDb.OleDbException: 'No value given for one or more required parameters.'

thats on the line 28( dr = cmd.ExecuteReader() )

ZurdoDev 16-May-17 13:47pm

Isn't the error message clear?

Member 13204422 16-May-17 14:25pm

but which parameter, im still very new to this, downloaded visual studio 3 days ago and have been teaching myself from a book, but this has just got me stumped for some reason

ZurdoDev 16-May-17 15:02pm

Don't you just have one parameter? First off, look at what your code is doing. You called cmd.ExecuteReader which means the command object will now be executed. That is the line of code causing the error so you know it has to do something with the sql statement.

Second, click out in the margin or use F9 (depending on your settings) and set a breakpoint in the code. You'll see a red bar go across the line. This means the code will stop running and be in a paused state when it gets to that line. You can then step through the code one line at a time and inspect the values that are in your variables and controls. Therefore, you can see exactly what is happening. This is the most important skill any developer will need, the ability to debug their code.

Dave Kreskowiak 16-May-17 14:19pm

I think it's pretty obvious. You put a parameter in your SQL statement but you never supplied a value for it in the subsequent C# code.

Maciej Los 16-May-17 13:27pm

Good point!
5!

Maciej Los · Accepted Answer · 2017-05-16T07:26:00

In addition to solution #1 by Dave Kreskowiak, there's one very important thing that you shoul know.

Your code is SqlInjection[^] vulnerable!

Never use such of queries:

str = "SELECT * FROM Cnc_Parts_List WHERE  [Old Part#] = '" & inputText.Text & "'"

Rather than it, use parameterized queries:

str = "SELECT * FROM Cnc_Parts_List WHERE  [Old Part#] = @part;"

Then you have to change your OleDbCommand by adding[^] @part parameter.

VB

cmd.Parameters.AddWithValue("@part", inputText.Text)

Now, you can execute that query.

Note: MS Access database engine likes [;] at the end of query!

For further details about SQLInjection, please see:
How To: Protect From SQL Injection in ASP.NET[^]
How To: Protect From Injection Attacks in ASP.NET[^]
SQL Injection and how to avoid it – ASP.NET Debugging[^]

Syntax error (missing operator) in VB

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Syntax error (missing operator) in VB

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us