Visual C# OleDbException when using variable in SQL WHERE clause

Question

Im currently using C# in visual studio and am using an access database. Im attempting to bring back data from a database when a customer is selected from a list box. This works perfectly when the sql is hard coded in e.g.

command.CommandText = "SELECT * FROM Customers WHERE CustomerID = 2 ";

However when I attempt to use a String variable to store the selected user ID I receive a "Data type mismatch in criteria expression" on the

"OleDbDataReader reader = command.ExecuteReader();".

I have used message boxes to confirm that the s2 variable contains the correct ID when chosen so I am unsure of the problem.

Does anyone know a solution to this problem?

    private void lst_disp_SelectedIndexChanged(object sender, EventArgs e)
    {
        String s = (String)lst_disp.SelectedItem; // the s string contains the selected customers ID + name,
        string s2 = s.Split(' ').FirstOrDefault(); // by spliting we can gain only the ID and store in s2
        MessageBox.Show("Selected " + s2);
        showCust(s2);
    }

    private void showCust(string s2)
    {
        dataGridView1.AllowUserToAddRows = false;

        dataGridView1.Columns.Add("CustomerID", "Customer ID");
        dataGridView1.Columns.Add("CustomerName", "Customer Name");
        dataGridView1.Columns.Add("Description", "Description");
        dataGridView1.Columns.Add("Email", "Email");
        dataGridView1.Columns.Add("Telephone", "Telephone");
        dataGridView1.Columns.Add("DeliveryAddress", "Delivery Address");
        dataGridView1.Columns.Add("Notes", "Notes");

        OleDbConnection connect = new OleDbConnection();
        connect.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Uni\Year 3\Final Year Project\independent foods\coding\showCustomers\Database1.accdb;Persist Security Info=False";
        connect.Open();
        MessageBox.Show("Connection open");

        OleDbCommand command = new OleDbCommand();
        command.Connection = connect;

        MessageBox.Show("SELECT * FROM Customers WHERE CustomerID = '" + s2 + "' ");
        command.CommandText = "SELECT * FROM Customers WHERE CustomerID = '" + s2 + "' ";


        try
        {
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {

                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CustomerID"].Value = reader[0].ToString();
                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["CustomerName"].Value = reader[1].ToString();
                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Description"].Value = reader[2].ToString();
                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Email"].Value = reader[3].ToString();
                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Telephone"].Value = reader[4].ToString();
                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["DeliveryAddress"].Value = reader[5].ToString();
                dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Notes"].Value = reader[6].ToString();
            }
        }
        catch(Exception e)
        {
    MessageBox.Show("The File  cann't be read. Error: " + e.Message);
        }
    }

Ever hear of SQL Injection Attacks? If not yet, then you will. — John Saunders, Mar 05 '13 at 17:09

score 3 · Accepted Answer · edited May 23 '17 at 10:25

3

Remove the single quotes, looks like the column is an int.

command.CommandText = "SELECT * FROM Customers WHERE CustomerID = " + s2;

Also you should parameterize this in order to better guard against sql injection (something like this):

SqlParameter custID = new SqlParameter("custID",s2);
command.Parameters.Add(custID);
command.CommandText = "SELECT * FROM Customers WHERE CustomerID = @custID";

Have a look at this post, or do a simple search for parameterizing your query.

edited May 23 '17 at 10:25

Community

1
1

answered Mar 05 '13 at 17:11

Mike C.

3,024
2
21
18

Your right it was an int, I have now parameterized aswell. Thanks for the quick reply – Mark Taylforth Mar 05 '13 at 17:16

score -1 · Answer 2 · answered Mar 05 '13 at 17:08

-1

A simple Google shows how to Use parameters MSDN

answered Mar 05 '13 at 17:08

CR41G14

5,464
5
43
64

1

I considered downvoting this (although others have already), but instead wanted to comment. "Search Google" is never a helpful answer in S.O. If you provide links, samples, and clarifications on the results you found when you searched google, then including the google search and the articles you chose would make a lot of sense. – EtherDragon Mar 05 '13 at 17:15
But no further help as an answer. Take a look at Mice C.'s post, he offers a direct answer, then an better solution, as well as a link from a google search. Also, `I` didn't downvote your post, I commented instead - so your retaliatory downvote on my answer was misplaced. – EtherDragon Mar 05 '13 at 17:24

score -1 · Answer 3 · answered Mar 05 '13 at 17:11

-1

It's because the resulting query is not the same when you parameterise it like that.

In your original, hard-coded, statement you have

SELECT * FROM Customers WHERE CustomerID = 2

In your new statement you end up with

SELECT * FROM Customers WHERE CustomerID = '2'

CustomerID is an int, but you are trying to compare it to a string in your second example.

Try this line of code instead:

        command.CommandText = "SELECT * FROM Customers WHERE CustomerID = " + s2;

I removed the single quotes.

EDIT Mike C makes a really good point. You should parameterize it instead, for even more reliability.

answered Mar 05 '13 at 17:11

EtherDragon

2,679
1
18
24

Your right it was an int and now works perfectly, thanks for the quick reply and good explanation. – Mark Taylforth Mar 05 '13 at 17:16
@MarkTaylforth No problem. As soon as you are able, please be sure to mark your answer (I suggest Mike C's answer as it's the most complete.) – EtherDragon Mar 05 '13 at 17:51

Visual C# OleDbException when using variable in SQL WHERE clause

3 Answers3