"Bit" field in SQL Server

Discussion in 'ASP .Net' started by bijoy, Feb 28, 2005.

  1. bijoy

    bijoy Guest

    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
     
    bijoy, Feb 28, 2005
    #1
    1. Advertising

  2. 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!
    ***************************

    "bijoy" wrote:

    > 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
    >
    >
     
    =?Utf-8?B?Q293Ym95IChHcmVnb3J5IEEuIEJlYW1lcikgLSBN, Feb 28, 2005
    #2
    1. Advertising

  3. bijoy

    Wilco Bauwer Guest

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

    ----
    - Wilco Bauwer
    Blog & Custom Controls @ http://wilcoding.xs4all.nl
     
    Wilco Bauwer, Feb 28, 2005
    #3
  4. bijoy

    Karl Seguin Guest

    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" <> wrote in message
    news:...
    > 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
    >
     
    Karl Seguin, Feb 28, 2005
    #4
  5. bijoy

    bijoy Guest

    I am using SqlHelper ( the MS Application Data Access Block). I believe
    it uses SQL Client. But I still have this prob.
     
    bijoy, Feb 28, 2005
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. J Sahoo
    Replies:
    3
    Views:
    5,297
    naikpalak
    Apr 30, 2009
  2. Replies:
    3
    Views:
    1,774
    Timothy Bendfelt
    Jan 19, 2007
  3. Replies:
    9
    Views:
    988
    Juha Nieminen
    Aug 22, 2007
  4. Jim Bancroft
    Replies:
    1
    Views:
    360
    Jon Skeet [C# MVP]
    Feb 9, 2005
  5. Jeff.M
    Replies:
    6
    Views:
    182
    Lasse Reichstein Nielsen
    May 4, 2009
Loading...

Share This Page