CODEWITHSUNDEEP
c#oledbcommand
Pio
Pio

Reputation: 533

How to use parameterized queries correctly?

I've been working on this now for 2 hours straight and read through a lot of so question but I cant see where or why my OleDbCommand is not working the way it should.
I wrote this code as a merge of all the question and answers i've seen: 

using (var connection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + @"data source= *path to mdp*"))
{
  try
  {
    connection.Open();
    foreach (KeyValuePair<string, string> pair in dictionary)
    {
        string query = "SELECT * FROM mytable WHERE db_id=?";
        var command = new OleDbCommand(query, connection);
        //command.Parameters.Add(new OleDbParameter("@ID", pair.Value));
        command.Parameters.Add("?", OleDbType.BSTR).Value = pair.Value;
        var reader = command.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(reader[0].ToString());
        }
        reader.Close();
    }
    connection.Close();
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message, "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
  }
}

However this gives me the "No Value given for one or more required Parameteres" Error. If i try the now commented line

command.Parameters.Add(new OleDbParameter("@ID", pair.Value));

and comment the other, the outcome is the exact same.
But if I use both those lines the reader reads but gives me every entry in my table column and not just the desired matching with pair.Value.

The KeyValuePair is nothing but a Tuple of a string id from my program as a key and its corresponding id in the database as value.

I'm thankful for any help or suggestion.

Upvotes: 2

Views: 137

Answers (2)

cSharma
cSharma

Reputation: 645

FYI, your code is right, just required to change in command line parameter and it successfully executed. 

        string result = string.Empty;
        using (var connection = new OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" + @"data source = employee.mdb"))
        {
            try
            {
                connection.Open();
                foreach (KeyValuePair<string, string> pair in dictionary)
                {
                    string query = "SELECT * FROM employeeTable WHERE ID=@id";
                    var command = new OleDbCommand(query, connection);
                    //command.Parameters.Add(new OleDbParameter("@ID", pair.Value));
                     command.Parameters.Add("@id", OleDbType.BSTR).Value = pair.Value;
                    var reader = command.ExecuteReader();
                    //result = reader.ToString();
                    while (reader.Read())
                    {
                        result += reader[1].ToString() + "\r\n";
                    }
                    reader.Close();
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                Response.Write("Exception: " + ex.Message);
            }
        }

Upvotes: 2

Samvel Petrosov
Samvel Petrosov

Reputation: 7706

This is working for me: 

string query = "SELECT * FROM mytable WHERE db_id=@ID";
var command = new OleDbCommand(query, connection);
command.Parameters.Add("@ID", OleDbType.BSTR);
command.Parameters[0].Value = pair.Value;

Upvotes: 5

Related Questions