CODEWITHSUNDEEP
c#sql-servertype-conversionsqldatatypes
user5299399
user5299399

Reputation:

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

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

Upvotes: 6

Views: 17450

Answers (4)

Luciano Pagano
Luciano Pagano

Reputation: 11

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

Upvotes: 0

Irshad
Irshad

Reputation: 3131

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

Upvotes: -1

Salah Akbari
Salah Akbari

Reputation: 39946

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

Upvotes: 6

Byyo
Byyo

Reputation: 2243

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

Upvotes: 1

Related Questions