How to write back to Access-2000 Yes/No fields

Discussion in 'ASP General' started by Les Juby, Jun 11, 2005.

  1. Les Juby

    Les Juby Guest

    Getting really stumped here.....

    Using Classic.ASP and after an editing page has been completed I need
    to write the values back to an Access 2000 database. But I'm battling
    with a checkbox resultwhich has to be stored in aYes/No field.

    I'm testing the variable no_mail to see if it returns "on" in ASP
    which means the checkbox is ticked. I then load a variable called
    sql_val_no_mail with the text that the SCP statement will use to
    update into Access.

    ie.
    If no_mail = "on" Then
    sql_val_no_mail = "Yes"

    and then.....
    " no_e-mail='" & sql_val_no_mail & "',"
    goes into the SQL string

    But what do you feed into Access for the Yes/No field to record a
    positive

    Tried: Yes "Yes" True "True" 1 "1" 0 "0" on

    Any suggestions, please.....

    thanks

    .les.
     
    Les Juby, Jun 11, 2005
    #1
    1. Advertising

  2. Les Juby wrote:
    > Getting really stumped here.....
    >
    > Using Classic.ASP and after an editing page has been completed I need
    > to write the values back to an Access 2000 database. But I'm battling
    > with a checkbox resultwhich has to be stored in aYes/No field.
    >
    > I'm testing the variable no_mail to see if it returns "on" in ASP
    > which means the checkbox is ticked. I then load a variable called
    > sql_val_no_mail with the text that the SCP statement will use to
    > update into Access.
    >
    > ie.
    > If no_mail = "on" Then
    > sql_val_no_mail = "Yes"
    >
    > and then.....
    > " no_e-mail='" & sql_val_no_mail & "',"
    > goes into the SQL string
    >
    > But what do you feed into Access for the Yes/No field to record a
    > positive
    >
    > Tried: Yes "Yes" True "True" 1 "1" 0 "0" on
    >


    -1 = true/yes/on
    0 = false/no/off

    Write the numeric value to the database field
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 11, 2005
    #2
    1. Advertising

  3. Les Juby wrote:
    > Getting really stumped here.....
    >
    > Using Classic.ASP and after an editing page has been completed I need
    > to write the values back to an Access 2000 database. But I'm battling
    > with a checkbox resultwhich has to be stored in aYes/No field.
    >
    > I'm testing the variable no_mail to see if it returns "on" in ASP
    > which means the checkbox is ticked. I then load a variable called
    > sql_val_no_mail with the text that the SCP statement will use to
    > update into Access.
    >
    > ie.
    > If no_mail = "on" Then
    > sql_val_no_mail = "Yes"
    >
    > and then.....
    > " no_e-mail='" & sql_val_no_mail & "',"
    > goes into the SQL string
    >
    > But what do you feed into Access for the Yes/No field to record a
    > positive
    >
    > Tried: Yes "Yes" True "True" 1 "1" 0 "0" on
    >
    > Any suggestions, please.....
    >
    > thanks
    >
    > .les.


    And stop using dynamic sql. use saved parameter queries (my preference):
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    or

    ODBC placeholders (?) with an explicit Command object to pass the values to
    the placeholders:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Jun 11, 2005
    #3
  4. Les Juby

    Les Juby Guest

    Oi, thanks for that help.!

    I really do take your point about the horrors of dynamic SQL but this
    site in particular is full of legacy code so we will have to keep it
    in place. But I'm looking hard at switching to the parameter queries.

    Main reason for my reply, especially if it can help someone else, is
    that the real problem here was that I was using an illegal construct
    in the field name. ie.
    >> " no_e-mail='" & sql_val_no_mail & "',"


    Once I changed the fieldname from no_e-mail to no_email then the 0 and
    -1 was accepted fine.! This particular code is a construction of
    backend code to replace manual maintenance of Access databases so the
    problem never occurred when an Access Form was being used to update
    details. We live and we learn......

    Can't even begin to recount how many times I have hit major problems
    because of reserved names for fields. Now I need to remember that a
    hyphen should probably be always avoided in Access 2000 field names,
    especially numeric field names.!

    thanks again

    .les.



    On Sat, 11 Jun 2005 13:49:06 -0400, "Bob Barrows [MVP]"
    <> wrote:

    >Les Juby wrote:
    >> Getting really stumped here.....
    >>
    >> Using Classic.ASP and after an editing page has been completed I need
    >> to write the values back to an Access 2000 database. But I'm battling
    >> with a checkbox resultwhich has to be stored in aYes/No field.
    >>
    >> I'm testing the variable no_mail to see if it returns "on" in ASP
    >> which means the checkbox is ticked. I then load a variable called
    >> sql_val_no_mail with the text that the SCP statement will use to
    >> update into Access.
    >>
    >> ie.
    >> If no_mail = "on" Then
    >> sql_val_no_mail = "Yes"
    >>
    >> and then.....
    >> " no_e-mail='" & sql_val_no_mail & "',"
    >> goes into the SQL string
    >>
    >> But what do you feed into Access for the Yes/No field to record a
    >> positive
    >>
    >> Tried: Yes "Yes" True "True" 1 "1" 0 "0" on
    >>
    >> Any suggestions, please.....
    >>
    >> thanks
    >>
    >> .les.

    >
    >And stop using dynamic sql. use saved parameter queries (my preference):
    >http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=
    >
    >or
    >
    >ODBC placeholders (?) with an explicit Command object to pass the values to
    >the placeholders:
    >http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e
    >
    >Bob Barrows
     
    Les Juby, Jun 12, 2005
    #4
    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. =?Utf-8?B?Z2F1cmF2?=

    Uploading Data From MS Acess 2000 ti SQL server 2000

    =?Utf-8?B?Z2F1cmF2?=, Jan 9, 2006, in forum: ASP .Net
    Replies:
    2
    Views:
    844
    Mary Chipman [MSFT]
    Jan 9, 2006
  2. Mark Richards
    Replies:
    1
    Views:
    837
    Martin Honnen
    Jul 24, 2004
  3. teo
    Replies:
    3
    Views:
    569
  4. Denis

    Access 2000 or SQL Server 2000

    Denis, Jan 25, 2004, in forum: ASP General
    Replies:
    1
    Views:
    236
    Nicole Calinoiu
    Jan 25, 2004
  5. Jamie Herre
    Replies:
    1
    Views:
    227
    why the lucky stiff
    Jan 7, 2005
Loading...

Share This Page