case consrtuct of sql in asp.net

Question

I want to use case construct of SQL:

CASE WHEN ans='A' THEN 1 ELSE 0 END

(Where ans is a string declared in ASP.NET) in an insert query like:

SqlCommand AnsCmd = new SqlCommand("insert into answers(ans_desc,istrue)values('" + ans1_desc + "',CASE WHEN ans='A' THEN 1 ELSE 0 END)", conn);

But it is not working... maybe the syntax is wrong? Please help.

Answer 1

It seems like the problem is at the ans variable you use. You should declare it at your query before use it at your CASE statement.

DECLARE @ans varchar(1);
SET @ans = 'A';
INSERT INTO answers(ans_desc,istrue)
VALUES('answer desc',CASE WHEN @ans='A' THEN 1 ELSE 0 END);

Answer 2

The SQL variable ans is declared nowhere in your SQL statement. Where does it come from? Sql or your ASP.NET app?