CODEWITHSUNDEEP
sql-server
Samantha J T Star
Samantha J T Star

Reputation: 32818

How can I set the value of a SQL Server column to True / False?

I have this SQL:

var sql = @"UPDATE TestQuestion
                        SET    UserDataJSON = @UserDataJSON,
                               Answered = TRUE
                        WHERE  TestQuestionId = @TestQuestionId";
            List<SqlParameter> parameterList = new List<SqlParameter>();
            parameterList.Add(new SqlParameter("@TestQuestionId", testQuestionId));

CREATE TABLE [dbo].[TestQuestion] (
    [TestQuestionId] INT              IDENTITY (1, 1) NOT NULL,
    [TestId]         VARCHAR (20)     NOT NULL,
    [Answered]       BIT              DEFAULT ((0)) NULL,
    CONSTRAINT [PK_TestQuestion] PRIMARY KEY CLUSTERED ([TestQuestionId] ASC)
);

How can I set the Answered to TRUE? I tried this but it mentions about invalid column name.

Upvotes: 1

Views: 1156

Answers (3)

Jesuraja
Jesuraja

Reputation: 3844

Try this: (Give the value within ' i.e. 'TRUE')

var sql = @"UPDATE TestQuestion
                        SET    UserDataJSON = @UserDataJSON,
                               Answered = 'TRUE'
                        WHERE  TestQuestionId = @TestQuestionId";

Otherwise, it will be taken as Column Name. So set the value with '

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

SQL Server, technically, has neither a boolean data type nor any boolean literals. It has the bit data type, that you've indicated that you're using, which is a numeric type accepting the values 0 or 1. Some data access layers (such as ADO) will pretend that this is a boolean type in foreign languages (such as C#), but such data access layers aren't involved when you're writing a piece of literal SQL code as a string

If you wish to continue to pretend that SQL Server has a boolean data type, because ADO pretends that it does, then you need to let ADO perform the necessary adjustments for you:

var sql = @"UPDATE TestQuestion
                    SET    UserDataJSON = @UserDataJSON,
                           Answered = @Answered
                    WHERE  TestQuestionId = @TestQuestionId";
        List<SqlParameter> parameterList = new List<SqlParameter>();
        parameterList.Add(new SqlParameter("@TestQuestionId", testQuestionId));
        parameterList.Add(new SqlParameter("@Answered", true));

ADO will translate a boolean value in a parameter into a value suitable for a SQL Server bit type - 0 or 1.

Upvotes: 4

Del Lee
Del Lee

Reputation: 482

Replace TRUE with 1: ie, you want to set Answered = 1 because this is a bit type. 1 represents TRUE in TSQL.

Upvotes: 0

Related Questions