C# update a boolean value in an Access database

Question

I have made a program in C# and now I want to update a boolean value in my Access database. This boolean value will always be updated to false. I have absolutely tried everything and there is no change in my value. Please help.

Here is my code :

The [In mail] column is my boolean value. I have tried this query in Access and it works perfectly there.

        String query = "UPDATE Ontwikkeldossier SET Ontwikkeldossier.[In mail] = @fals WHERE (((Ontwikkeldossier.[OntwikkeldossierID])=@ontwikkeldossierid));";
        using(OleDbConnection conn = new OleDbConnection(connstring))
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand(query, conn);
            cmd.Parameters.Add("@fals", OleDbType.Boolean, 1, "[In mail]").Value = false;
            cmd.Parameters.Add("@ontwikkeldossierid", OleDbType.Numeric).Value = Convert.ToInt32(newrow.Cells[0].Value.ToString());
            cmd.ExecuteNonQuery();
        }

Thanks in advance.

if you want it to be always false then why have it as a parameter, just set it to false in the query — V4Vendetta, May 31 '13 at 11:50
Is the value in the @fals = false or 0? Do you need to pass 0 to it to work? — Azhar Khorasany, May 31 '13 at 11:53
I have tried both false and 0 and they didn't work. I think i need to pass 0 but I don't know how — Charlotte Vancraeynest, May 31 '13 at 11:57
I believe the issue may be the difference between how .NET defines a boolean and Access defines it. Please see http://stackoverflow.com/questions/8940986/how-to-read-yes-no-value-as-boolean-from-ms-access — CM Kanode, May 31 '13 at 12:03
I have read that article to but for me it is always false so always 0, or am i seeing things wrong? — Charlotte Vancraeynest, May 31 '13 at 12:05

score 1 · Answer 1 · answered Oct 02 '13 at 13:08

updateCmd.Parameters.Add("@3", OleDbType.Boolean).Value = "true";

this worked for me! I have "Yes/No" column in Access. Update command is:

OleDbCommand updateCmd = new OleDbCommand("UPDATE email SET server = @1, port = @2, ssl = @3, utilizator = @4, parola = @5, subiect = @6, email = @7 WHERE ID = @8", connection);

DonBoitnott · Answer 2 · 2013-05-31T12:07:33.910

0

Should this:

cmd.Parameters.Add("@fals", OleDbType.Boolean, 1, "[In mail]").Value = false;

Instead be:

cmd.Parameters.Add("@fals", OleDbType.Boolean).Value = false;  //or zero, since it's an object, the DB might care...

Is the source an issue? If you're supplying the value, why do you want a source for the value?

Of course, since it's always false for you, you could simply change the query and eliminate the parameter altogether:

String query = "UPDATE Ontwikkeldossier SET Ontwikkeldossier.[In mail] = 0 WHERE (((Ontwikkeldossier.[OntwikkeldossierID])=@ontwikkeldossierid));";

edited May 31 '13 at 12:07

answered May 31 '13 at 12:00

DonBoitnott

10,787
6
49
68

Stil doesn't work for me, what should i put behind cmd.parameters.add("@fals", OleDbType.Boolean) ? – Charlotte Vancraeynest May 31 '13 at 12:04
Is the field in the database of type Boolean? It's not an Integer being _used_ as a Boolean, right? – DonBoitnott May 31 '13 at 12:06
In Access its a yes/no field – Charlotte Vancraeynest May 31 '13 at 12:07
@CharlotteVancraeynest Since it's an Access yes/no (a _very_ weird type, by the way!)...try passing the true/false value in quotes, such as `cmd.Parameters.Add("@fals", OleDbType.Boolean).Value = "false";` – DonBoitnott May 31 '13 at 12:09
No success... don't know what to do anymore – Charlotte Vancraeynest May 31 '13 at 12:11
That "false" thing would apply to putting it in the query too, such as `String query = "UPDATE Ontwikkeldossier SET Ontwikkeldossier.[In mail] = 'false' WHERE (((Ontwikkeldossier.[OntwikkeldossierID])=@ontwikkeldossierid));";` (I might have the wrong quote mark) – DonBoitnott May 31 '13 at 12:12
I'm also seeing that '0' would suffice for the false inline. – DonBoitnott May 31 '13 at 12:14
Then i have the Data type mismatch in criteria expression error – Charlotte Vancraeynest May 31 '13 at 12:14
@CharlotteVancraeynest So then that only works when supplied as the Value component to a command parameter. Inline then would be without quotes. – DonBoitnott May 31 '13 at 12:17
What do you mean? I have juist tried to add the parameter with the quotes but it doesn't work either – Charlotte Vancraeynest May 31 '13 at 12:20

score 0 · Accepted Answer · answered May 31 '13 at 12:21

For what it's worth, I just tested the following code and it works for me:

using (var conn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\__tmp\testData.accdb;"))
{
    conn.Open();
    using (var cmd = new OleDbCommand(
            "UPDATE Ontwikkeldossier SET [In mail]=? WHERE OntwikkeldossierID=?",
            conn))
    {
        cmd.Parameters.AddWithValue("?", false);
        cmd.Parameters.AddWithValue("?", 1);
        cmd.ExecuteNonQuery();
    }
    conn.Close();
}

C# update a boolean value in an Access database

3 Answers3