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'.

you can try `Parameters.AddWithValue` to make a parameterized query which probably might help yop — Mohit S, Sep 17 '15 at 07:06

Salah Akbari · Answer 1 · 2016-04-05T13:22:10.087

5

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

edited Apr 05 '16 at 13:22

answered Sep 17 '15 at 07:00

Salah Akbari

39,330
10
79
109

Ok cool, I thought I was going to have to do "Convert.ToInt16 your Boolean value. That will make true/false, 0 or 1 " – Sep 17 '15 at 07:03
4

Yes to: "use parameterized queries"! Don't ever build sql statements by simply concatenating strings/values. – Corak Sep 17 '15 at 07:06

score 1 · Answer 2 · edited Sep 17 '15 at 08:10

1

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

edited Sep 17 '15 at 08:10

marc_s

732,580
175
1,330
1,459

answered Sep 17 '15 at 07:08

Byyo

2,163
4
21
35

What is difference from Add vs AddWithValue ? – Sep 17 '15 at 07:10
4

I'd personally avoid `AddWithValue`, specify the type directly and use the `Value` property, but this is at least better than the original code... – Jon Skeet Sep 17 '15 at 07:10
http://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue – Byyo Sep 17 '15 at 07:11
@JonSkeet I don't follow you. use Value property ? example? – Sep 17 '15 at 07:12
@Jon is this just a personal thing or are there good reasons to avoid `AddWithValue` – Byyo Sep 17 '15 at 07:12
1

@Byyo: It avoids using inferences you don't want. See http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ for example. – Jon Skeet Sep 17 '15 at 07:14
@MillRunner: `command.Parameters.Add("@id", SqlDbType.Int).Value = customerID;` – Jon Skeet Sep 17 '15 at 07:15

score -1 · Answer 3 · edited Apr 06 '16 at 05:59

-1

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

edited Apr 06 '16 at 05:59

Salah Akbari

39,330
10
79
109

answered Sep 17 '15 at 07:10

Irshad

3,071
5
30
51

that was rude copy & paste – Byyo Sep 17 '15 at 07:20
@Byyo If you really think that I copy paste your answer, tell me, I'll remove mine.. – Irshad Sep 17 '15 at 07:25

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

3 Answers3