passing parameter to access query from c#

Question

i have design an access query which seem like this:

SELECT Replace(names,'lion','kiss') AS Expr1
FROM table1;

the two values that is lion and kiss, which are temporary, now i want these to be two variables, so that i can pass value to it from c#.

how to call this query from c#, and pass it two values.

I am using access 2007.

Thanks for your help :)

Answer 1

Try something like this (I found this on the subject):

public void ReplaceColumnA(string oldvalue, string newvalue)
{
    using(OleDbConnection connection1 = (OleDbConnection)DatabaseConnection.Instance.GetConnection())
    {
        connection1.Open();  
        using(OleDbCommand sqlcmd2 = new OleDbCommand("queryname", connection1))
        {
            sqlcmd2.Parameters.AddWithValue("param1", newvalue); 
            sqlcmd2.Parameters.AddWithValue("param2", oldvalue);
            sqlcmd2.ExecuteNonQuery();
        }
    }
}

The Access query would look like this:

UPDATE [t]
SET [a] = ?
WHERE [a] = ?

The names of the parameters you pass on don't matter, it's the order you pass them as.

By using the "using" statement you are ensure .NET is properly releasing the connections and resources.

Additionally I STRONGLY recommend switching to SQL Server Express Edition. It's free and a LOT more potent than what you can cook up in Access. Really, you're just shooting yourself in the foot continuing in Access...

Answer 2

You execute it like this:

sqlcmd2.ExecuteNonQuery();

Answer 3

I think you need to modify code like this

    string q = "SELECT Replace(names,'{0}','{1}') AS Expr1 FROM table1";
//You can provide any values instead of LION and KISS
    string query = string.format(q,"LION","KISS") 

    using(OleDbConnection cnn = (OleDbConnection)DatabaseConnection.Instance.GetConnection())
    {
        cnn.Open();  
        using(OleDbCommand cmd = new OleDbCommand(query, cnn))
            {
            OleDbDataReader reader = cmd.ExecuteReader();
            while(reader.Read())
            {
                Console.WriteLine(reader["Expr1"].toString());
            }
            }
    }