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
– John Saunders, Commented Mar 5, 2013 at 17:09

Community · Accepted Answer · 2017-05-23 10:25:10Z

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, 2017 at 10:25

CommunityBot

11 silver badge

answered Mar 5, 2013 at 17:11

Mike C.

3,1442 gold badges24 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mark Taylforth Over a year ago

Your right it was an int, I have now parameterized aswell. Thanks for the quick reply

CR41G14 · Accepted Answer · 2013-03-05 17:08:57Z

-1

A simple Google shows how to Use parameters MSDN

answered Mar 5, 2013 at 17:08

CR41G14

5,6245 gold badges47 silver badges67 bronze badges

2 Comments

EtherDragon Over a year ago

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 Over a year ago

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 · Accepted Answer · 2013-03-05 17:11:44Z

-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 5, 2013 at 17:11

EtherDragon

2,7081 gold badge18 silver badges24 bronze badges

2 Comments

Mark Taylforth Over a year ago

Your right it was an int and now works perfectly, thanks for the quick reply and good explanation.

EtherDragon Over a year ago

@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.)

Collectives™ on Stack Overflow

Visual C# OleDbException when using variable in SQL WHERE clause

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related