"Bit" field in SQL Server

[bijoy]

I am in the process of upgrading my application db from Access to SQL
Server.

With the Access backend, I had queries such as "UPDATE table SET
fieldName = True". SQL Server doesnt seem to like TRUE as a bool value,
instead it requires 0 and 1.

My queries are based on user input, so if a checkbox is checked, I
create my query as follows:

"UPDATE table SET fieldName = " + checkbox.selected

which translates to

"UPDATE table SET fieldName = True"

Do I have to rewrite all my queries to conver the "True" to 0/1? Or is
there an easier way?

Bijoy

 

[Guest]

If you use the SqlClient namespace (most likely works with OleDb as well),
you can throw a bool at a bit field and ADO.NET will translate for you. You
do not have to create code to turn true into 1 and false into 0.

---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************

 

[Wilco Bauwer]

You should use parameterized queries. The way you do it, is not immune
to SQL injection (even though the input may be very limited in this
case). It is _always_ a good idea to use parameterized queries, because
some important things are taken into account, so you do not have to
bother with it yourself.

Your code will then look something like the following instead:

XXXCommand command = new XXXCommand("UPDATE table SET fieldName =
'@fieldName'", connection);
command.Parameters.Add(new XXXParameter("@fieldName",
checkbox.Checked));

 

[Karl Seguin]

I second what Cowboy said, but also, you should use parameterized queries,
so your update statement should look like:

command.commandText = "Update Table set fieldName = @Value"
command.Parameters.Add("@Value", SqlDbType.Bit).Value = checkbox.selected

this will (a) help protect against SQL Injection (b) make it easier for you
to move to sprocs (if you decide to do so).

Karl

--
MY ASP.Net tutorials
http://www.openmymind.net/index.aspx - New and Improved (yes, the popup is
annoying)
http://www.openmymind.net/faq.aspx - unofficial newsgroup FAQ (more to
come!)

 

[bijoy]

I am using SqlHelper ( the MS Application Data Access Block). I believe
it uses SQL Client. But I still have this prob.