C# boolean needs converted to bit for SQL Server so instead of True it needs to be 1

Question

I have a C# property which is of data type bool and when it gets set, it becomes a True or False.

However I need for it to match up with a SQL Server table column of type bit so that it is saved as a 1 or 0.

SQL Server column:

StormOut bit

C# property

public bool StormOut { get; set; }

C# SQL statement:

string querystring = "UPDATE tblSignOnOff SET StormOut = " + storm.StormOut + " WHERE id = 1902";

Otherwise currently the SQL statement in C# is

UPDATE tblSignOnOff 
SET StormOut = True 
WHERE id = 1902

Which results in an error:

Invalid column name 'True'.

Fowlkes

you can try Parameters.AddWithValue to make a parameterized query which probably might help yop – Banquette

Answer 1

You have missed single quotes. Change like this:

string querystring = "UPDATE tblSignOnOff SET StormOut = '" + storm.StormOut + "' WHERE id = 1902";

But an important note: You should always use parameterized queries like below. This kind of string concatenations are open for SQL Injection:

string querystring = "UPDATE tblSignOnOff SET StormOut = @StormOut WHERE id = @id";
yourCommand.Parameters.AddWithValue("@id", 1902);
yourCommand.Parameters.AddWithValue("@StormOut", storm.StormOut);

Murdock

Ok cool, I thought I was going to have to do "Convert.ToInt16 your Boolean value. That will make true/false, 0 or 1 " – Fowlkes

Yes to: "use parameterized queries"! Don't ever build sql statements by simply concatenating strings/values. – Clouse

Answer 2

You should use parameters and avoid string concatenation

string Command = "UPDATE tblSignOnOff SET StormOut @StormOut WHERE id = @id";
using (SqlConnection mConnection = new SqlConnection(ConnectionString))
{
    mConnection.Open();

    using (SqlCommand myCmd = new SqlCommand(Command, mConnection))
    {
        myCmd.Parameters.AddWithValue("@id", 1902); // TODO set this value dynamically
        myCmd.Parameters.AddWithValue("@StormOut", storm.StormOut);

        int RowsAffected = myCmd.ExecuteNonQuery();
    }
}

Dihedral

What is difference from Add vs AddWithValue ? – Fowlkes

I'd personally avoid AddWithValue, specify the type directly and use the Value property, but this is at least better than the original code... – Overexcite

#21110501 – Dihedral

@JonSkeet I don't follow you. use Value property ? example? – Fowlkes

@Jon is this just a personal thing or are there good reasons to avoid AddWithValue – Dihedral

@Byyo: It avoids using inferences you don't want. See blogs.msmvps.com/jcoehoorn/blog/2014/05/12/… for example. – Overexcite

@MillRunner: command.Parameters.Add("@id", SqlDbType.Int).Value = customerID; – Overexcite

Answer 3

Further modifying the answer from @S.Akbari with his suggestion for parameterized queries;

SqlCommand command = new SqlCommand();
command.CommandText = "UPDATE tblSignOnOff SET StormOut = @StormOut WHERE id = @id";    
command.Parameters.AddWithValue("@StormOut", storm.StormOut);
command.Parameters.AddWithValue("@id", 1902);

Ewell

that was rude copy & paste – Dihedral

@Dihedral If you really think that I copy paste your answer, tell me, I'll remove mine.. – Ewell

Answer 4

int bitValue = booleanVar == true? 1: 0;

Brammer

Welcome to StackOverflow. Please, edit and try for How to Answer, describe the effect of what you propose and explain why it helps to solve the problem. Find help with formatting your post here: stackoverflow.com/help/formatting . Consider taking the tour. – Incubation