using a variable in oledb command

Question

What is the correct way to concatenate a variable when using the oledb command function in c# for a sql query. To apply some context im trying to make what a list box displays dependent on another selection made on a previous list box.This is what I've tried but doesn't work.

OleDbCommand projects = new OleDbCommand("SELECT *  FROM  Client_projects  
WHERE clients = " + (listBox1.SelectedItem.ToString()) , conn);


OleDbDataReader project = projects.ExecuteReader();
while (project.Read())

                {

                    // Add items from the query into the listbox

                    listBox2.Items.Add(project[1].ToString());


                }

Is clients a varchar? Then use `WHERE clients =' "+ (listBox1.SelectedItem.ToString()) + "'"` — Suraj S, Jul 09 '17 at 13:57
@SurajS I tried your code and it still fails what do you mean by a varchar at the moment im using dummy values which are 'Client 1' , 'Client 2' etc — James Piper, Jul 09 '17 at 14:33
@GordonLinoff how do you pass in as a paramter do you have any example code — James Piper, Jul 09 '17 at 14:34
@JamesPiper What exception do you get? and shouldnt `project[1]` be `project[0]`? — Suraj S, Jul 09 '17 at 15:16
@JamesPiper and `what do you mean by a varchar at the moment` I meant is the 'clients' column in sql defined as a varchar? — Suraj S, Jul 09 '17 at 15:17
@SurajS unfortunately due to restraints I have to use an access database and it is project[1] as [0] is the id column — James Piper, Jul 09 '17 at 15:22
@SurajS An unhandled exception of type 'System.NullReferenceException' occurred in Project V1.exe Additional information: Object reference not set to an instance of an object. — James Piper, Jul 09 '17 at 15:35
can you tell me which line does it show that error @JamesPiper — Suraj S, Jul 09 '17 at 15:39
@SurajS the c# program crashes and that is the error it displays it is not linked to a particular line — James Piper, Jul 09 '17 at 15:41
If you are using visual studio, you should be able to find the line number, since we do not know where the error occurs, a null reference can occur anywhere in the program. Anyway, try this `while(project!=null && project.Read())` Assuming that 'conn' is not null and that is not where the problem is! @JamesPiper — Suraj S, Jul 09 '17 at 15:46

score 2 · Accepted Answer · answered Jul 10 '17 at 09:37

String concatenation can lead to SQL Injection problems so you should not use it to build SQL. You should parameterize your query. So something like this...

using (OleDbCommand projects = new OleDbCommand("SELECT * FROM Client_projects WHERE clients = ?", conn))
{
    projects.Parameters.Add("?", OleDbType.VarChar).Value = listBox1.SelectedItem.ToString();

    OleDbDataReader project = projects.ExecuteReader();

    while (project.Read())
    {
        listBox2.Items.Add(project[1].ToString());
    }
}

OleDbCommand Parameters are set in the order they are specified in the SQL, so if you have more than one you must do them in order.

using (OleDbCommand projects = new OleDbCommand("SELECT * FROM Client_projects WHERE clients = ? AND Date = ?", conn))
{
    projects.Parameters.Add("?", OleDbType.VarChar).Value = listBox1.SelectedItem.ToString();
    projects.Parameters.Add("?", OleDbType.Date).Value = DateTime.Today;

    OleDbDataReader project = projects.ExecuteReader();

    while (project.Read())
    {
        listBox2.Items.Add(project[1].ToString());
    }
}

You are not using it but for other who might, SqlCommand uses named parameters...

SELECT * FROM Client_projects WHERE clients = @Clients
projects.Parameters.Add("@Clients", SqlDbType.VarChar).Value = listBox1.SelectedItem.ToString();

using a variable in oledb command

1 Answers1

Linked