CODEWITHSUNDEEP
c#mysqlwpf
Xaphann
Xaphann

Reputation: 3677

WPF insert Bit Field into MySQL database

This should be simple and I feel stupid for asking but I cannot figure out what I am doing wrong. Trying to insert a 0 into bit field in a MySQL database and no mater what I do it returns a 1. So in a query program this works perfectly:

insert into MyTable (VarcharField, BitField) values ('0', 0)

This returns a result of 

VarcharField: 1
BitField:     1

Now go over to my WPF application. Please note this is a simplified version of my generic insert method and it works for all other field type except bit:

string sqlCMD = "insert into MyTable (VarcharField, BitField) values (@varcharField, @bitField)";
int value = 0;
using (MySqlConnection con = new MySqlConnection(ConString))
{
    MySqlCommand cmd = new MySqlCommand();
    con.Open();
    cmd = new MySqlCommand(sqlCMD, con);
    cmd.Parameters.Add(new MySqlParameter("@varcharField", value.ToString()));
    cmd.Parameters.Add(new MySqlParameter("@bitField", value.ToString()));
    cmd.ExecuteNonQuery();
    con.Close();
    cmd.Dispose();
    con.Close();
}

This returns a result of 

VarcharField: 0
BitField:     1

I even tried putting the parameter value to "false" and this did not work either.

Upvotes: 1

Views: 406

Answers (1)

Steve
Steve

Reputation: 216303

Use the specific overload of the Add method that allows you to specify the datatype of the parameter

cmd.Parameters.Add("@varcharField", MySqlDbType.VarChar).Value = value.ToString();
cmd.Parameters.Add("@bitField", MySqlDbType.Bit).Value = value;

In your current code, for your second parameter, you add a string to the parameter without specifying the datatype of the parameter. This creates a parameter of type VarChar and when the value reaches the database engine an automatic conversion is applied to get back the expected bit. I suppose that this conversion looks at the ascii code of the string "0" and this is not zero.

It is always better to be explicit when you supply data to your database engine to avoid these conversions made by the engine with its own assumptions

Upvotes: 1

Related Questions