none
Syntax error (missing operator) in query expression

    Question

  • I have one select statement to execute on the access database on the click of a button. when i click on the button i am getting this error "Syntax error (missing operator) in query expression 'Customer_Name = Summer Terry'." The code that i have written is pasted below and also the error log. Can anyone please find and correct the error in my code.

     

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.OleDb;
    
    namespace Cyber_Application
    {
        public partial class RegularCustomer : Form
        {
            int flag = 0;
            string currentFile;
            string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\Clients\\S V Cyber Cafe\\Cyber Application\\Cyber Application\\param.accdb; Jet OLEDB:Database Password=12345";
    
            public RegularCustomer()
            {
                InitializeComponent();
            }
    
            private void btnOK_Click(object sender, EventArgs e)
            {
                string address = "";
                string date = "";
                string InTime = "";
                string OutTime = "";
                date = DateTime.Now.ToShortDateString();
                address = txtAddress.Text;
                InTime = txtInHour.Text + ":" + txtInMin.Text + " " + listBoxIn.SelectedItem.ToString();
                OutTime = txtOutHour.Text + ":" + txtOutMin.Text + " " + listBoxOut.SelectedItem.ToString();
                MessageBox.Show("Your address is:\n" + address + "\nToday's date is:\n" + date + "\nYour In Time is:\n" + InTime + "\nYour Out Time is:\n" + OutTime);
            }
    
            private void btnCancel_Click(object sender, EventArgs e)
            {
                this.Close();
            }
    
            private void addCustomers()
            {
                OleDbConnection cn = new OleDbConnection(connectionString);
                OleDbCommand cmd = new OleDbCommand("select Customer_Name from Customers", cn);
                cn.Open();
                OleDbDataReader dr = cmd.ExecuteReader();
    
                if (dr != null)
                {
                    while (dr.Read())
                    {
                        cBoxCustomers.Items.Add(dr["Customer_Name"]);
                    }
                }
                cBoxCustomers.SelectedIndex = 0;
            }
    
            private void RegularCustomer_Load(object sender, EventArgs e)
            {
                addCustomers();
                btnOK.Enabled = false;
                btnBrowse.Enabled = false;
                btnSelectCustomer.Enabled = false;
                txtName.Enabled = false;
                txtAddress.Enabled = false;
                txtContactNo.Enabled = false;
                txtIdProof.Enabled = false;
                txtIssuedBy.Enabled = false;
            }
    
            private void cBoxCustomers_SelectedIndexChanged(object sender, EventArgs e)
            {
                if (cBoxCustomers.SelectedIndex == 0)
                {
                    btnSelectCustomer.Enabled = false;
                }
                else
                {
                    btnSelectCustomer.Enabled = true;
                }
            }
    
            private void btnSelectCustomer_Click(object sender, EventArgs e)
            {
                using (OleDbConnection cn = new OleDbConnection(connectionString))
                {
                    cn.Open();
                    string scmd = "select [Customer_Name], [Address], [Contact_No], [Gender], [Issued_By] from Regular_Customers where Customer_Name = " + cBoxCustomers.SelectedItem.ToString();
                    OleDbCommand cmd = new OleDbCommand(scmd, cn);
                    OleDbDataReader sdr = cmd.ExecuteReader();
                    while (sdr.Read())
                    {
                        txtName.Text = sdr[0].ToString(); ;
                        txtAddress.Text = sdr[1].ToString();
                        txtContactNo.Text = sdr[2].ToString();
                        
                        if (sdr[3].ToString() == "M")
                        {
                            radioBtnMale.Select();
                        }
    
                        if (sdr[3].ToString() == "F")
                        {
                            radioBtnFemale.Select();
                        }
    
                        txtIssuedBy.Text = sdr[4].ToString();
                    }
                }
            }
        }
    }
    
    

     


    The error code is:

     

    System.Data.OleDb.OleDbException was unhandled
      Message=Syntax error (missing operator) in query expression 'Customer_Name = Summer Terry'.
      Source=Microsoft Office Access Database Engine
      ErrorCode=-2147217900
      StackTrace:
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
           at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& 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 Cyber_Application.RegularCustomer.btnSelectCustomer_Click(Object sender, EventArgs e) in E:\Clients\S V Cyber Cafe\Cyber Application\Cyber Application\RegularCustomer.cs:line 90
           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& m, MouseButtons button, Int32 clicks)
           at System.Windows.Forms.Control.WndProc(Message& m)
           at System.Windows.Forms.ButtonBase.WndProc(Message& m)
           at System.Windows.Forms.Button.WndProc(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
           at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
           at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
           at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& 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(Form mainForm)
           at Cyber_Application.Program.Main() in E:\Clients\S V Cyber Cafe\Cyber Application\Cyber Application\Program.cs:line 17
           at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 
    
    


     


    • Edited by tapan.desai Monday, September 26, 2011 3:30 AM
    Monday, September 26, 2011 3:29 AM

Answers

  • Instead of concatinating a string command, you would be better off using parameters.  I'm doing inserts into Access, but the code below should give you a general idea, of how to use parameters.  Then you don't need to worry if you have all the single quotes in the correct places.

                        using (dal.DBManager db = new dal.DBManager(dal.DataProvider.OleDb,
                            string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}{1};",
                            this.db_Directory, this.db_FileName)))
                        {
                            db.Open();
    
                            foreach (DataRow row in dt.Rows)
                            {
                                this.CurCount++;
                                this.TotalCount = (short)(((float)this.CurCount / (float)dt.Rows.Count) * 100);
                                this.StatusMessage = string.Format(Msg, this.CurCount, dt.Rows.Count.ToString(), this.TotalCount.ToString());
    
                                db.Command.CommandText = "INSERT INTO [ProgramLanguage Join](ProgramID, LanguageID, AudioFormatID) VALUES(?, ?, ?)";
    
                                db.CreateParameters(3);
    
                                db.AddParameters(0, "ProgramID", Convert.ToInt32(row["ProgramID"]));
                                db.AddParameters(1, "LanguageID", Convert.ToInt16(row["LanguageID"]));
                                db.AddParameters(2, "AudioFormatID", Convert.ToInt16(row["AudioFormatID"]));
    
                                db.ExecuteNonQuery(CommandType.Text, db.Command.CommandText);
    
                                worker.ReportProgress(this.TotalCount, StatusMessage);
                            }
    
                            db.Close();
                        }
    

    Monday, September 26, 2011 3:45 AM
  • enclose the value that you would like to pass with ( ' )... as in your code:

     " + cBoxCustomers.SelectedItem.ToString();

    should be: '" + cBoxCustomers.SelectedItem.ToString() + "'";


    • Edited by RoninB Monday, September 26, 2011 3:38 AM
    • Marked as answer by tapan.desai Monday, September 26, 2011 6:17 AM
    Monday, September 26, 2011 3:38 AM
  • hi Tapan,

    please try this:

    string scmd = "select [Customer_Name], [Address], [Contact_No], [Gender], [Issued_By] from Regular_Customers where Customer_Name = '" + cBoxCustomers.SelectedItem.ToString()+"'";
    
    

     


    Regards, http://shwetamannjain.blogspot.com
    Monday, September 26, 2011 3:41 AM

All replies

  • enclose the value that you would like to pass with ( ' )... as in your code:

     " + cBoxCustomers.SelectedItem.ToString();

    should be: '" + cBoxCustomers.SelectedItem.ToString() + "'";


    • Edited by RoninB Monday, September 26, 2011 3:38 AM
    • Marked as answer by tapan.desai Monday, September 26, 2011 6:17 AM
    Monday, September 26, 2011 3:38 AM
  • hi Tapan,

    please try this:

    string scmd = "select [Customer_Name], [Address], [Contact_No], [Gender], [Issued_By] from Regular_Customers where Customer_Name = '" + cBoxCustomers.SelectedItem.ToString()+"'";
    
    

     


    Regards, http://shwetamannjain.blogspot.com
    Monday, September 26, 2011 3:41 AM
  • Instead of concatinating a string command, you would be better off using parameters.  I'm doing inserts into Access, but the code below should give you a general idea, of how to use parameters.  Then you don't need to worry if you have all the single quotes in the correct places.

                        using (dal.DBManager db = new dal.DBManager(dal.DataProvider.OleDb,
                            string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}{1};",
                            this.db_Directory, this.db_FileName)))
                        {
                            db.Open();
    
                            foreach (DataRow row in dt.Rows)
                            {
                                this.CurCount++;
                                this.TotalCount = (short)(((float)this.CurCount / (float)dt.Rows.Count) * 100);
                                this.StatusMessage = string.Format(Msg, this.CurCount, dt.Rows.Count.ToString(), this.TotalCount.ToString());
    
                                db.Command.CommandText = "INSERT INTO [ProgramLanguage Join](ProgramID, LanguageID, AudioFormatID) VALUES(?, ?, ?)";
    
                                db.CreateParameters(3);
    
                                db.AddParameters(0, "ProgramID", Convert.ToInt32(row["ProgramID"]));
                                db.AddParameters(1, "LanguageID", Convert.ToInt16(row["LanguageID"]));
                                db.AddParameters(2, "AudioFormatID", Convert.ToInt16(row["AudioFormatID"]));
    
                                db.ExecuteNonQuery(CommandType.Text, db.Command.CommandText);
    
                                worker.ReportProgress(this.TotalCount, StatusMessage);
                            }
    
                            db.Close();
                        }
    

    Monday, September 26, 2011 3:45 AM