Adding a Where Clause to a OleDbCommand

Question

I am trying to add a where clause to the following line of code.

the reason for this is because i get the datatable from a dropdown combobox. now i want to filter that table on user name, so that only the user can see their records.

i need help on how to write the where clause into this code.

if you need any more information i will gladding add it.

thank you for any help.

OleDbCommand cmd = new OleDbCommand(String.Concat("Select * From ", comboBox1.Text), con);

After Comments

i added the sql injection protection.

OleDbCommand cmd = new OleDbCommand(String.Concat("Select * From 
@Companydetails where Research_ID = @Researcher_ID"), con);

        cmd.Parameters.AddWithValue("@Companydetails", comboBox1.Text);
        cmd.Parameters.AddWithValue("@Researcher_ID", usernumber_lab.Text);

but now it is giving me a error saying:

Additional information: Syntax error in query. Incomplete query clause.

is there something else i need to add to finnish this query off?

Your code is vulnerable to [SQL Injection](https://en.wikipedia.org/wiki/SQL_injection), if you have time I suggest you read a bit about it. — hendryanw, Oct 05 '16 at 09:19

score 2 · Answer 1 · answered Oct 05 '16 at 09:25

2

I would do it as follows;

string query = "Select * from MyTable Where username = @username";

using (OleDbCommand cmd = new OleDbCommand(query, con))
{
   cmd.Parameters.Add("@username", OleDbType.VarChar).Value = comboBox1.Text;
}

This way the object will dispose automatically and also you'll be safe from Sql Injection

answered Oct 05 '16 at 09:25

Izzy

6,740
7
40
84

The OP mentioned that the table name came from a dropdownlist as well. – hendryanw Oct 05 '16 at 09:28

score 1 · Accepted Answer · answered Oct 05 '16 at 09:25

1

Please try this

string sql = String.format("Select * From {0} where id = {1}", comboBox1.Text, id);
OleDbCommand cmd = new OleDbCommand(sql,con);

answered Oct 05 '16 at 09:25

Vaibhav Bhatia

528
1
4
12

score 0 · Answer 3 · answered Oct 05 '16 at 09:20

You can just make your sql statement longer:

OleDbCommand cmd = new OleDbCommand(String.Concat("Select * From table Where something = something", comboBox1.Text), con);

You don't have to work with multiline or anything. This is only needed in some database managers, but not in a c# sql statement.

score 0 · Answer 4 · answered Oct 05 '16 at 09:21

0

If you would like

OleDbCommand cmd = new OleDbCommand(String.Format("Select * From {0} WHERE username='{1}'", comboBox1.Text,username.Text), con);

answered Oct 05 '16 at 09:21

deviantxdes

469
5
17

score 0 · Answer 5 · answered Oct 05 '16 at 09:23

0

You can try the below code

OleDbCommand cmd = new OleDbCommand(string.Format(
                                  "SELECT * FROM {0} WHERE Username = '{1}'",
                                  comboBox1.Text, userName), con);

answered Oct 05 '16 at 09:23

Balagurunathan Marimuthu

2,927
4
31
44

Adding a Where Clause to a OleDbCommand

5 Answers5

Linked