SQL syntax error in vb net

I get this error message:
Syntax error in string in query expression 'Active_No="lastname like '%' ORDER BY LastName,FirstName, MI"
I have also used the bool Active=no  but get the same error message.
The class is below:
'//////////////////////////////////////////////////////////////////
Public Class CustomerNamesCollection
    Inherits BindingListView(Of CustomerName)

    Dim con As OleDb.OleDbConnection

    Private Sub New(ByVal connectionstring As String)
        con = New OleDb.OleDbConnection(connectionstring)
        loadList("lastname like '%'")
    End Sub

    Private Sub New(ByVal connection As OleDb.OleDbConnection)
        con = connection
        loadList("lastname like '%'")
    End Sub

    Private Sub New(ByVal connectionstring As String, ByVal whereclause As String)
        con = New OleDb.OleDbConnection(connectionstring)
        loadList(whereclause)
    End Sub

    Private Sub New(ByVal connection As OleDb.OleDbConnection, ByVal whereclause As String)
        con = connection
        loadList(whereclause)
    End Sub

    Public Sub New()

    End Sub
    Private Sub loadList(ByVal whereclause As String)

        Dim iOpenedConnection As Boolean

        Dim cmd As New OleDb.OleDbCommand
        Dim reader As OleDb.OleDbDataReader

        With cmd
            .Connection = con
            .CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email FROM customers WHERE " _
           & whereclause & " ORDER BY LastName,FirstName,MI"


        End With

        If con.State = ConnectionState.Closed Then
            iOpenedConnection = True
            con.Open()
        End If


        reader = cmd.ExecuteReader

        If reader.HasRows Then

            While reader.Read
                Me.Add(CustomerName.LoadCustomerNameFromReader(reader))
            End While

        End If

        reader.Close()

        If (iOpenedConnection) And con.State = ConnectionState.Open Then
            con.Close()
        End If

    End Sub
'/////////////////////////////////////////////

When I use similar code for the Employee class it works as expected. Expected is that the employee name is removed from the sheduler. I would expect the customer name to be removed from the customers list but it is not.
Employee class
 .CommandText = "SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, Employees.NotActive, Employees.Active FROM(Employees)WHERE Employees.NotActive=0 Order By FirstName, LastName"
This sql statement works...for employees. On the employees form is active and not active boolean as checkmarks. So I can use either a string or a boolean expression but it appears that in this class it needs to be a string after the whereclause but what about before the whereclause?
'/////////////////////////////////////////////////////////////
This is customers. whereclause is a string. I have no idea of how to put in Active or not active in the sql statement. I keep getting error messages related to syntax.( see first sentence for syntax error message)
In the customers table Active as boolean and NotActive as Bool both send data to text as Active_Yes and NotActive_No so those last two appear in the database and the check boxes send the data to those textboxes. It does the same for employees table.
'////////////////////////////////////////////////////////////////////////////////
Private Sub loadList(ByVal whereclause As String)

        Dim iOpenedConnection As Boolean

        Dim cmd As New OleDb.OleDbCommand
        Dim reader As OleDb.OleDbDataReader

        With cmd
            .Connection = con
            .CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email FROM customers WHERE " _
           & whereclause & " ORDER BY LastName,FirstName,MI"


        End With

I have tried:
.CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email ,active, notactive FROM customers WHERE active=0 " _
           & whereclause & " ORDER BY LastName,FirstName,MI"

and I have also tried:
CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email ,Active_Yes, Active_No FROM customers WHERE Active_No="Active_No" _
           & whereclause & " ORDER BY LastName,FirstName,MI"

LVL 1
mindserveAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
we will need to see the value of "whereclause". ..

eventually like this:
customers WHERE Active_No='Active_No' AND " _
           & whereclause & " ORDER BY LastName,FirstName,MI "

Open in new window

0
 
Steveg48Commented:
suggest you breakpoint on end with and quick watch your command.text to see if your sql statement looks correct after it is built. You may havge a ' vs " problem
0
 
JezWaltersCommented:
What is the full CommandText when you get an error?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
HERE Active_No="Active_No" _
           & whereclause & " ORDER BY LastName,FirstName,MI"

must be:

HERE Active_No='Active_No' " _
           & whereclause & " ORDER BY LastName,FirstName,MI"
0
 
JezWaltersCommented:
If your Active and NotActive fields are of Data Type boolean (aka Yes/No) , then the syntax should be just:
SELECT * FROM customers WHERE Active
SELECT * FROM customers WHERE NotActive
Although I'm a bit lost as to why you need both flags!?
0
 
mindserveAuthor Commented:
Angell, I get this error when I run your code:

Syntax error (missing operator) in query expression 'Active_No='Active_No' lastname like '%''.

Which is pretty much what I was getting to begin with:
Syntax error in string in query expression 'Active_No="lastname like '%' ORDER BY LastName,FirstName, MI"
0
 
mindserveAuthor Commented:
What I see in the WhereClause is that it is a string so maybe I should use the boolean expression before the string.
0
 
mindserveAuthor Commented:
Syntax error (missing operator) in query expression 'Active ='0' lastname like '%''.

nope, that didn't work either.
0
 
mindserveAuthor Commented:
Can there be a second Where clause in a sql statement?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Can there be a second Where clause in a sql statement?
no, you need to "append" it with AND

so, if whereclause  has WHERE .... , it must be AND ....
0
 
mindserveAuthor Commented:
.CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email, Active_Yes, Active_No FROM customers WHERE " _
           & whereclause & " ORDER BY LastName,FirstName,MI AND Active_No='Active_No"
????
Like that? Because I still get a query expression error...so it's not formatted right I think.

Syntax error in string in query expression 'MI AND Active_No='Active_No'.
0
 
JezWaltersCommented:
A simple SQL SELECT query like you're after takes the form:
SELECT <Field 1>, <Field 2>, <Field 3>
FROM <Table>
WHERE <Condition 1> AND
<Condition 2> AND
<Condition 3>
ORDER BY <Field 1>, <Field 2>, <Field 3>
Where the terms in angled brackets (including the brackets) need replacing with the corresponding items.
The string manipulations you are performing need to result in a ".CommandText" value of the above form.
0
 
JezWaltersCommented:
When you get the error, click the Debug option, press Ctrl-G to View the Immediate Window and then type the following:
Print cmd.CommandText
Post the text that you get here.
0
 
mindserveAuthor Commented:
needed space is 66
A first chance exception of type 'System.Data.OleDb.OleDbException' occurred 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</TraceIdentifier><Description>Unhandled exception</Description><AppDomain>SalonSalonIILT.vshost.exe</AppDomain><Exception><ExceptionType>System.Data.OleDb.OleDbException, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</ExceptionType><Message>Syntax error (missing operator) in query expression 'active_No='Active_No' AND &amp;amp; whereclause &amp;amp;'.</Message><StackTrace>   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp;amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp;amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp;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 SalonAppointments.Data.CustomerNamesCollection.loadList(String whereclause) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\AppointmentDataAccessLayer\Lists\CustomerNamesCollection.vb:line 54
   at SalonAppointments.Data.CustomerNamesCollection..ctor(String connectionstring) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\AppointmentDataAccessLayer\Lists\CustomerNamesCollection.vb:line 8
   at SalonAppointments.Data.CustomerNamesCollection.GetAllCustomerNames(String connectionstring) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\AppointmentDataAccessLayer\Lists\CustomerNamesCollection.vb:line 74
   at SalonAppointments.frmAppointments.RefreshCustomerNames() in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 622
   at SalonAppointments.frmAppointments.LoadCustomerNames() in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 617
   at SalonAppointments.frmAppointments.QuickAddAppointment(CustomerService serv) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 1320
   at SalonAppointments.frmAppointments.QuickAddContextMenuClick(Object sender, EventArgs e) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 950
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ToolStrip.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ToolStripDropDown.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 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 System.Windows.Forms.Application.Run(ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at SalonAppointments.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
   at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
   at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
   at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
   at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
   at System.Activator.CreateInstance(ActivationContext activationContext)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</StackTrace><ExceptionString>System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'active_No='Active_No' AND &amp;amp; whereclause &amp;amp;'.
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object&amp;amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object&amp;amp; executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object&amp;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 SalonAppointments.Data.CustomerNamesCollection.loadList(String whereclause) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\AppointmentDataAccessLayer\Lists\CustomerNamesCollection.vb:line 54
   at SalonAppointments.Data.CustomerNamesCollection..ctor(String connectionstring) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\AppointmentDataAccessLayer\Lists\CustomerNamesCollection.vb:line 8
   at SalonAppointments.Data.CustomerNamesCollection.GetAllCustomerNames(String connectionstring) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\AppointmentDataAccessLayer\Lists\CustomerNamesCollection.vb:line 74
   at SalonAppointments.frmAppointments.RefreshCustomerNames() in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 622
   at SalonAppointments.frmAppointments.LoadCustomerNames() in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 617
   at SalonAppointments.frmAppointments.QuickAddAppointment(CustomerService serv) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 1320
   at SalonAppointments.frmAppointments.QuickAddContextMenuClick(Object sender, EventArgs e) in C:\Documents and Settings\xtremeblue\Desktop\++April207PMMBESTmNewSalon 14042010\SalonAppointmentManager\Forms\Dialogs\frmAppointments.vb:line 950
   at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
   at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
   at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
   at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
   at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
   at System.Windows.Forms.Control.WmMouseUp(Message&amp;amp; m, MouseButtons button, Int32 clicks)
   at System.Windows.Forms.Control.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ScrollableControl.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ToolStrip.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.ToolStripDropDown.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message&amp;amp; m)
   at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message&amp;amp; m)
   at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
   at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG&amp;amp; msg)
   at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 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 System.Windows.Forms.Application.Run(ApplicationContext context)
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
   at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
   at SalonAppointments.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
   at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
   at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
   at System.Runtime.Hosting.ManifestRunner.Run(Boolean checkAptModel)
   at System.Runtime.Hosting.ManifestRunner.ExecuteAsAssembly()
   at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext, String[] activationCustomData)
   at System.Runtime.Hosting.ApplicationActivator.CreateInstance(ActivationContext activationContext)
   at System.Activator.CreateInstance(ActivationContext activationContext)
   at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssemblyDebugInZone()
   at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
   at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
   at System.Threading.ThreadHelper.ThreadStart()</ExceptionString></Exception></TraceRecord>
0
 
mindserveAuthor Commented:
Private Sub New(ByVal connectionstring As String, ByVal whereclause As String)
        con = New OleDb.OleDbConnection(connectionstring)
        loadList(whereclause)
    End Sub

    Private Sub New(ByVal connection As OleDb.OleDbConnection, ByVal whereclause As String)
        con = connection
        loadList(whereclause)
    End Sub
0
 
mindserveAuthor Commented:
Private Sub loadList(ByVal whereclause As String)

        Dim iOpenedConnection As Boolean

        Dim cmd As New OleDb.OleDbCommand
        Dim reader As OleDb.OleDbDataReader

        With cmd
            .Connection = con
            .CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email FROM customers WHERE " _
          & whereclause & " ORDER BY LastName,FirstName,MI"


            ' .CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email, Active_Yes, Active_No FROM customers WHERE active_No='Active_No' AND & whereclause & ORDER BY LastName,FirstName,MI"



        End With

        If con.State = ConnectionState.Closed Then
            iOpenedConnection = True
            con.Open()
        End If


        reader = cmd.ExecuteReader

        If reader.HasRows Then

            While reader.Read
                Me.Add(CustomerName.LoadCustomerNameFromReader(reader))
            End While

        End If

        reader.Close()

        If (iOpenedConnection) And con.State = ConnectionState.Open Then
            con.Close()
        End If

    End Sub
0
 
JezWaltersCommented:
In order to establish what the syntax error is, you need to show the full value of the cmd.CommandText property at the time the error occurs - not the code that is used to build it.
0
 
mindserveAuthor Commented:
Got it!
    .CommandText = "SELECT customerid,lastname,firstname,MI,homephone,cellphone,workphone,address,email,NotActive FROM customers WHERE NotActive=0 ORDER BY LastName,FirstName,MI"

Syntax error..removed the WhereClause and it works...wonder why?
0
 
mindserveAuthor Commented:
Had to remove the whereclause and will test but it seems to work without that. It's a syntax error for sure but still not sure what happened or why but your code did provide me with most of the help I needed to get it working.
0
 
JezWaltersCommented:
You haven't removed the WHERE clause (the WHERE keyword is still present), you've just simplified it.
It you start from a working state (i.e. how it is now) and change the code slowly we can figure out what you're doing wrong.  It looks like the value of the "whereclause" variable is wrong in the context for where you're putting it in the SQL statement you're building
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.