C#/ inserting data into Access 2003/ does not work with variables

Question

I am new to C#. This example below works as long as I write the value directly into the OleDBcommand.CommandText.

The format of the column C_1 in the DB is integer, just like the variable “b” is.

Running the example code, I get the error “types incompatible”. If I change the column format to “text”, the program writes a “b” into the DB. As my way of code is, the CommandText would not accept any variable with its value.

Tried all kinds and combinations of brackets and quotation marks. Can it be?

Thanks for a hint!

 private void CmdNeuerDatenSatz_Click(object sender, RoutedEventArgs e)
    {
        int c = 8;
        int b = 110;

        OleDbConnection con = new OleDbConnection();
        OleDbCommand cmd = new OleDbCommand();

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source = C:\\Users\\Linner OHG\\Desktop\\TestDB.mdb";

        cmd.Connection = con;
        cmd.CommandText = "INSERT INTO DB_Test (Col_1) VALUES (b)";

        try
        {
            con.Open();

            c = cmd.ExecuteNonQuery();
            MessageBox.Show("Row has been inserted!");

            con.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

Do a google search on how to add Parameters using OleDbCommand and you will see how to create a `Parameters` `cmd.AddWithValues` — MethodMan, Dec 18 '17 at 22:36
You should use [SQL Parameters](https://stackoverflow.com/a/11910114/621827). — Stephen Gilboy, Dec 18 '17 at 22:36
when dealing with AccessDB they use `?` when dealing with Parameters, do some more research while you have this free time — MethodMan, Dec 18 '17 at 22:39
That _b_ in the CommandText is not a variable, that _b_ is a letter inside a string. If you want to pass values using variables then you need to learn how to write a parameterized query. — Steve, Dec 18 '17 at 22:40
I said I am new… After many hours searching here and other places I got completely stuck. I found AddWithValues, found obviously the right using-statement but “Provider” in connString caused an error. On the other hand I was so close, as the answer of Cam Bruce (many thanks!) shows. To the impatient pros here: a newbie thinks, that he can replace a value by a parameter just with the right special characters around it. Is that comprehensible? — rlinner, Dec 20 '17 at 17:04

score 1 · Answer 1 · answered Dec 18 '17 at 22:40

You can use an OleDbParameter to add values. Replace your literal values in the sql statement with a ?, then add an OleDbParameter to the OleDbCommand.Parameters collection.

 private void CmdNeuerDatenSatz_Click(object sender, RoutedEventArgs e)
    {
        int c = 8;
        int b = 110;

        OleDbConnection con = new OleDbConnection();
        OleDbCommand cmd = new OleDbCommand();

        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
            "Data Source = C:\\Users\\Linner OHG\\Desktop\\TestDB.mdb";

        cmd.Connection = con;
        cmd.CommandText = "INSERT INTO DB_Test (Col_1) VALUES (?)";

        // add parameter value
        // note: parameters need to be in the same order as they are in the statement
        var param = new OleDbParameter("Col_1", OleDbType.VarChar));
        param.Value = "my value";

        cmd.Parameters.Add(param);

        try
        {
            con.Open();

            c = cmd.ExecuteNonQuery();
            MessageBox.Show("Row has been inserted!");

            con.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

C#/ inserting data into Access 2003/ does not work with variables

1 Answers1