Getting Exception error saying: Syntax error (missing operator) in query expression

Question

I am working on a Database app in c# that connects to a Microsoft Access database. Now I had it working where when I would click on Save it would say the data was entered successfully, but I found out that it actually wasn't entering the data into the Database.

So with help from the fine folks here at stackoverflow I was able to find out what I needed to do to get it to work, however I am now getting an unhandled exception saying the following:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: Syntax error (missing operator) in query expression '@[Guest First Name]'.

I'm curious as to where the problem is. This exception is thrown when it reaches the com.ExecuteNonQuery(); line of the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.OleDb;
using System.Data;
using System.ComponentModel;


namespace ParkingDatabase
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : Window
{

    public MainWindow()
    {
        InitializeComponent();
    }


    private void btnSave_Click(object sender, RoutedEventArgs e)
    {
        using (OleDbConnection DBConnect = new OleDbConnection())
        {
            DBConnect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source = C:\Users\bkoso\documents\visual studio 2015\Projects\ParkingDatabase\ParkingDatabase\ParkingData.accdb";
            using (OleDbCommand com = new OleDbCommand("INSERT INTO [Guest Info]([Guest First Name], [Guest Last Name], [Room Number], [Departure Date], [Return Date], [Vehicle Colour], [Vehicle Make], [Plate Number], [Contact First Name], [Contact Last Name], [Contact Number], [Contact Email], [Tag Number]) Values(@[Guest First Name], @[Guest Last Name], @[Room Number], @[Departure Date], @[Return Date], @[Vehicle Colour], @[Vehicle Make], @[Plate Number], @[Contact First Name], @[Contact Last Name], @[Contact Email], @[Contact Email], @[Tag Number])", DBConnect))

//the section below was recently updated
            {
                com.Parameters.AddWithValue("@GuestFirstName", txtBxGstFName.Text);
                com.Parameters.AddWithValue("@GuestLastName", txtBxGstLName.Text);
                com.Parameters.AddWithValue("@RoomNumber", txtBxRm.Text);
                com.Parameters.AddWithValue("@DepartureDate", txtBxDDate.Text);
                com.Parameters.AddWithValue("@ReturnDate", txtBxRDate.Text);
                com.Parameters.AddWithValue("@VehicleColour", txtBxVColour.Text);
                com.Parameters.AddWithValue("@VehicleMake", txtBxVMake.Text);
                com.Parameters.AddWithValue("@PlateNumber", txtBxPlate.Text);
                com.Parameters.AddWithValue("@ContactFirstName", txtBxContactFName.Text);
                com.Parameters.AddWithValue("@ContactLastName", txtBxContactLName.Text);
                com.Parameters.AddWithValue("@ContactNumber", txtBxPhone.Text);
                com.Parameters.AddWithValue("@ContactEmail", txtBxEmail.Text);
                com.Parameters.AddWithValue("@TagNumber", txtBxTag.Text);

                DBConnect.Open();
                com.ExecuteNonQuery();
                DBConnect.Close();
            }   

            if (DBConnect.State == ConnectionState.Open)
            {
                //com.ExecuteNonQuery();
                MessageBox.Show("Guest Information Saved Successfully");
                txtBxGstFName.Text = "";
                txtBxGstLName.Text = "";
                txtBxRm.Text = "";
                txtBxDDate.Text = "";
                txtBxRDate.Text = "";
                txtBxVColour.Text = "";
                txtBxVMake.Text = "";
                txtBxPlate.Text = "";
                txtBxContactFName.Text = "";
                txtBxContactLName.Text = "";
                txtBxPhone.Text = "";
                txtBxEmail.Text = "";
                txtBxTag.Text = "";
            }   
        }
    }    

    private void btnClear_Click(object sender, RoutedEventArgs e)
    {
        txtBxGstFName.Text = "";
        txtBxGstLName.Text = "";
        txtBxRm.Text = "";
        txtBxDDate.Text = "";
        txtBxRDate.Text = "";
        txtBxVColour.Text = "";
        txtBxVMake.Text = "";
        txtBxPlate.Text = "";
        txtBxContactFName.Text = "";
        txtBxContactLName.Text = "";
        txtBxPhone.Text = "";
        txtBxEmail.Text = "";
        txtBxTag.Text = "";
    }

    private void btnView_Click(object sender, RoutedEventArgs e)
    {

    }

    private void btnSame_Click(object sender, RoutedEventArgs e)
    {

    }

    private void txtBoxGuestFirstName_TextChanged(object sender, TextChangedEventArgs e)
    {

    }

    private void btnDelete_Click(object sender, RoutedEventArgs e)
    {

    }

    private void btnSearch_Click(object sender, RoutedEventArgs e)
    {

    }
}
}

Try using no spaces between the words in the parameters - I'm not sure that SQL supports spaces between words in parameters. I.e., @GuestFirstName instead of @[Guest First Name]. — Tim, Sep 15 '15 at 16:42
Maybe [this](http://stackoverflow.com/questions/21636957/access-form-syntax-error-missing-operator-in-query-expression) can help. — Yuval Itzchakov, Sep 15 '15 at 16:42
so why the `[ ]` in the params get rid of the spaces.. also try replacing the values section with `?,?,?,?...etc` also there are tons of examples on how to Insert into Access DB using C# try showing more effort in regards to doing a google search here is a good example as well http://stackoverflow.com/questions/5893837/using-parameters-inserting-data-into-access-database — MethodMan, Sep 15 '15 at 16:43
@MethodMan the reason for the [] brackets was because I was under the understanding that certain words are reserved words or is that only for Jet 4.0? Also I had been searching for quite a while but nothing seemed similar to what i was doing. — Bloodstalker, Sep 15 '15 at 16:49

Greg · Answer 1 · 2015-09-15T17:39:30.987

The problem stems from your parameters, they're declared in the following manner:

@[Guest Name Here]

The @ is correct, since it represents your parameter however the bracket is a huge no, no. The bracket's symbolize a Column Name in the database, which could partially be the cause of your issue. Couple that with the spaces, that error is more than likely going to fail.

You should have your parameter in the following format:

@GuestNameHere

Let me know if that resolves your problem. Also, I noticed that you open up your connection, below your parameters, add it above. Structured more like:

private const string dbConnection = "Provider=Microsoft.ACE.OLEDB.12.0;...";
private const string query = "INSERT INTO...";
using((OleDbConnection connection = new OleDbConnection(dbConnection))
     using(OleDbCommand command = new OleDbCommand(query, connection))
     {
          connection.Open();    
          // Parameter(s) here
          command.ExecuteNonQuery();
          // Any other logic here.
     }

So about the above snippet, the dbConnection would be a readonly so it could access the data from your config. This will make it flexible, if your database changes, you change it in one location rather than many.

The query parameter is similar, it will allow you to hold a const on the page, with all your queries in a single location with ease of access to change without multiple query changes.

Also note, when you utilize the using it implements IDispose. So once the application exits the using block, any value will become out of scope, so it will close your connection.

You should denote the connection to be open towards the top, it is easier to read and will ensure the connection is open before your attempt to manipulate the query.

Since you noted your struggling with dbConnection and query I partially filled in, to indicate location you would have to place full content.

Update:

I also noticed that your query doesn't reflect the changes, you need to ensure the column is indeed present, and in the following format:

[GuestName]

Not in your format, [Guest Name]. Also the sample applies for the inserted values, those values are the parameters they both need to match.

So I changed the parameters to exclude the square brackets and spaces but I'm still getting the same exception at the same spot — Bloodstalker, Sep 15 '15 at 16:55
@Bloodstalker please update the question with your new code so we can see. — dmeglio, Sep 15 '15 at 16:58
Ok I've updated the question to reflect the changes I made in the code — Bloodstalker, Sep 15 '15 at 17:11
Ok I believe I get what you are saying with the changes you made, but what about the connection string? Do I need to reference it above the snippet of code? That's the only thing confusing me about the little snippet of code — Bloodstalker, Sep 15 '15 at 17:33
You have yours in the following: parameter, connection, execute, close. It should be: connection, parameter, execute — Greg, Sep 15 '15 at 17:35
I understand what you are meaning there, but in the code snippet you show, there is no connection string. Where does that go? — Bloodstalker, Sep 15 '15 at 17:37

Getting Exception error saying: Syntax error (missing operator) in query expression

1 Answers1