[skippy108]

I am trying to search an Access database per a C# winform. I have changed the SQL statement around, and I cannot find where the error is coming from. Here is the error as it comes up for the all textboxes of Dtae, Job Number and the Alloy/Temper combobox where the user chooses one already listed.

Error: Syntax error (missing operator) in query expression '(Job Number [email protected]')'.

I have tried without the @ and used '", but nothing.

     private void btnExit_Click(object sender, EventArgs e)
        {
            DialogResult dialog = MessageBox.Show("Do you really want to close the ECM program?", "Exit ECM", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (dialog == DialogResult.Yes)
            {
                Application.Exit();
            }
        }

        private void btnSearchDB_Click(object sender, EventArgs e)
        {
            OleDbConnection accessConnect = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\ECM\ECM\ECM\ECM.mdb");
            DataTable dt = new DataTable();

            if (txtJobNumber.Text.Length > 0)
            {
                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements  WHERE (Job Number [email protected]" + txtJobNumber.Text.ToString() + "')", accessConnect);
             
                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }

            if (txtDate.Text.Length > 0)
            {
                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements WHERE (Date [email protected]" + txtDate.Text.ToString() + "')", accessConnect);

                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }

            if (cbAlloyTemperDB.Text.Length > 0)
            {
                OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements WHERE (Alloy/Temper = @" + cbAlloyTemperDB.Text.ToString() + "')", accessConnect);

                da.Fill(dt);
                dataGridView1.DataSource = dt;
            }
        }
    }

[CharlieMay]

Give a more descriptive parameter, @Date or similar.

But then you need to add a value to the parameter

da.SelectParameter.AddWithValue("@Date", yourDateVariable)

WARNING: I'm just learning C# so I'm not sure I have the syntax correct. But you will need to do this with each parameter.

EDIT:
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements WHERE (Date [email protected])", accessConnect);

What it appears is that you're trying to use parameters AND mixing concatenation of the SQL string. (Stick to parameters)

So what this means is that you should be able to use the following

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements WHERE Date = @Date", accessConnect);
da.SelectCommand.Parameters.AddWithValue("@Date", yourdatevariable);

This post has been edited by CharlieMay: 16 October 2012 - 07:01 PM

[skippy108]

I added:

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements WHERE Date = @Date", accessConnect);  
                da.SelectCommand.Parameters.AddWithValue("@Date", txtDate.Text); 

                da.Fill(dt);
                dataGridView1.DataSource = dt;

And I still am getting:

Syntax error in string in query expression '(Date [email protected]')'.

[CharlieMay]

Ah, OK, Date is a keyword so try wrapping it with [].

OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements WHERE [Date] = @Date", accessConnect);

[skippy108]

Same error. Wish I could get you the whole project, so you could see.

[DiscimusVivere]

hi.. skippy108

Quote

Error: Syntax error (missing operator) in query expression '(Job Number [email protected]')'.

I think it's because single quote at last your sql. See your code below

      OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements  WHERE (Job Number [email protected]" + txtJobNumber.Text.ToString() + "')", accessConnect);

It should be

      OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM ECMeasurements  WHERE (Job Number [email protected]" + txtJobNumber.Text.ToString() + ")", accessConnect);

Let me know if it's work.

[Curtis Rutland]

Whether or not that would work is somewhat irrelevant, because it's just bad code. It's an attempt at mixing paradigms that don't go together. String concatenation for queries is a bad practice. In this case, you're using "@" symbols to indicate parameters, but then you're just concatenating values on.

Here's how to write proper parameterized queries:

http://www.dreaminco...topic268104.htm

Next, when you're using access and oledb, you must input the parameters in the order in which they appear in the querystring. That's because it doesn't honor parameter names. You could use "?" for each parameter and it would work just as well. It will add each one into the first available parameter.