access two where parameters command in c#

Question

This is how I did it and I get a data type miss match, but tamp is an integer so I am thinking it is some thing else (I also pass an integer in to tamp at another stage in the program). Do I need to pass it though another int? Or is it a problem with my access statement? The whole block of code worked without the extra where parameter and the for loop.

The for loop help me load them in that order and that is important for what I am doing with it

        for (int i = -100; i < 100; i++)
        {              
            try
            {
                connection.Open();
                OleDbCommand command = new OleDbCommand();
                command.Connection = connection;
                string query = "select * from Table1 where Status='" + comboBox5.Text + "'AND Tamp='" + i + "'";
                command.CommandText = query;
                listBox9.Items.Clear();
                OleDbDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {

                    listBox9.Items.Add(reader["Description"].ToString());
                }

                // MessageBox.Show("Data Saved");
                connection.Close();

            }
            catch (Exception ex)
            { MessageBox.Show("error " + ex); }

        }

That's a fun comic but beginners that are most likely to write insecure code won't know what you're trying to tell them with just a link to the comic. — Eric J., Feb 08 '16 at 17:00

score 3 · Accepted Answer · edited May 23 '17 at 12:07

3

Assuming tamp is defined as an integer in the database, change

"'AND Tamp='" + i + "'";

to

"' AND Tamp=" + i;

By placing the single quotes around it in your SQL command, you are providing a string rather than an integer to your database.

If you are using C# 6 or later, you can use string interpolation to write the string in a clearer manner

$"select * from Table1 where Status='{comboBox5.Text}' AND Tamp={i}"

Note that this is not good practice from a security perspective. You should never take user input and place it directly in your SQL command, as it allows users to provide malicious input that can e.g. delete data or tables. Instead, use parameterized queries. The comic linked in the comments exemplifies this.

edited May 23 '17 at 12:07

Community

1
1

answered Feb 08 '16 at 16:19

Eric J.

147,927
63
340
553

with that i get syntax error in string in query expression 'Status='Arches - PreProduction' AND Tamp='-100'. – AllianceMaterials Feb 08 '16 at 16:25
You still have single quotes around -100, which you should not have. – Eric J. Feb 08 '16 at 16:26
string query = "select * from Table1 where Status='" + comboBox5.Text + "' AND Tamp='" + i; is exactly how i have it but yes the error has '' in it – AllianceMaterials Feb 08 '16 at 16:28
the bottom select statement worked flawlessly just had to remove the list items clear, your a scholar thank you so much for this solution !!! – AllianceMaterials Feb 08 '16 at 16:34
1

Please also note the part about security. Someone can wreck your database if you allow them to enter whatever data they want in the user interface (and an experienced user can often modify the user interface to add things that you didn't have in your combo box). – Eric J. Feb 08 '16 at 17:02
1

With a MS Access `SELECT` statement, there is no way to use SQL injection to make it do something like `DROP TABLE Students;` But parameter queries are still good. ;-) – HansUp Feb 08 '16 at 17:05

access two where parameters command in c#

1 Answers1