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
– MethodMan, Commented Dec 18, 2017 at 22:36
when dealing with AccessDB they use ? when dealing with Parameters, do some more research while you have this free time — MethodMan
– MethodMan, Commented Dec 18, 2017 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
– Steve, Commented Dec 18, 2017 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
– rlinner, Commented Dec 20, 2017 at 17:04

Cam Bruce · Accepted Answer · 2017-12-18 22:40:44Z

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);
        }

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related