I need help. I am having trouble clearing a field in an Access database.

Discussion in 'ASP General' started by Tim, Aug 1, 2003.

  1. Tim

    Tim Guest

    I don't know what to use to clear a field in an MSAccess database. I
    can update fields in the database and delete whole records but I have
    not figured out how to clear a single field.

    This is what I am using to update the database.

    Set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open "DSN=DataBase"
    SQL = "UPDATE Table SET "
    SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
    SQL = SQL & "theDate='" & FormatDateTime(Now,2) & "' "
    SQL = SQL & "WHERE theName = '" & Request.Form("theName") & "' "

    Set RSUpdate=Conn.Execute(SQL)
    Conn.Close
    Set RSUpdate=nothing

    I get an error if I try to update a field with a value of nothing "".
    Tim, Aug 1, 2003
    #1
    1. Advertising

  2. Tim

    Ray at Guest

    Either set it to '' or to null, depending on what you'd like.

    UPDATE TheTable SET TheColumn='' WHERE Something=AcertainValue
    or
    UPDATE TheTable SET TheColumn=NULL WHERE Something=AcertainValue

    Ray at work

    "Tim" <> wrote in message
    news:...
    > I don't know what to use to clear a field in an MSAccess database. I
    > can update fields in the database and delete whole records but I have
    > not figured out how to clear a single field.
    >
    > This is what I am using to update the database.
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "DSN=DataBase"
    > SQL = "UPDATE Table SET "
    > SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
    > SQL = SQL & "theDate='" & FormatDateTime(Now,2) & "' "
    > SQL = SQL & "WHERE theName = '" & Request.Form("theName") & "' "
    >
    > Set RSUpdate=Conn.Execute(SQL)
    > Conn.Close
    > Set RSUpdate=nothing
    >
    > I get an error if I try to update a field with a value of nothing "".
    Ray at, Aug 1, 2003
    #2
    1. Advertising

  3. Tim

    Roy in Guest

    set it to null instead
    SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
    SQL = SQL & "theDate=NULL "
    SQL = SQL & " WHERE theName = '" & Request.Form("theName") & "' "



    "Tim" <> wrote in message
    news:...
    > I don't know what to use to clear a field in an MSAccess database. I
    > can update fields in the database and delete whole records but I have
    > not figured out how to clear a single field.
    >
    > This is what I am using to update the database.
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "DSN=DataBase"
    > SQL = "UPDATE Table SET "
    > SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
    > SQL = SQL & "theDate='" & FormatDateTime(Now,2) & "' "
    > SQL = SQL & "WHERE theName = '" & Request.Form("theName") & "' "
    >
    > Set RSUpdate=Conn.Execute(SQL)
    > Conn.Close
    > Set RSUpdate=nothing
    >
    > I get an error if I try to update a field with a value of nothing "".
    Roy in, Aug 1, 2003
    #3
  4. Tim

    Tom B Guest

    As Ray and Roy said set it to null, but ensure that the field allows nulls.

    "Tim" <> wrote in message
    news:...
    > I don't know what to use to clear a field in an MSAccess database. I
    > can update fields in the database and delete whole records but I have
    > not figured out how to clear a single field.
    >
    > This is what I am using to update the database.
    >
    > Set Conn = Server.CreateObject("ADODB.Connection")
    > Conn.Open "DSN=DataBase"
    > SQL = "UPDATE Table SET "
    > SQL = SQL & "thefield='" & Request.Form("thefield1") & "', "
    > SQL = SQL & "theDate='" & FormatDateTime(Now,2) & "' "
    > SQL = SQL & "WHERE theName = '" & Request.Form("theName") & "' "
    >
    > Set RSUpdate=Conn.Execute(SQL)
    > Conn.Close
    > Set RSUpdate=nothing
    >
    > I get an error if I try to update a field with a value of nothing "".
    Tom B, Aug 1, 2003
    #4
  5. Tim

    Tim Guest

    Thanks for the info. I'll check things out and try the changes.
    Tim, Aug 5, 2003
    #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. Replies:
    3
    Views:
    10,726
    arindam_mitra1985
    Dec 28, 2011
  2. Tony Johansson
    Replies:
    7
    Views:
    496
    Gregory A. Beamer
    Dec 23, 2009
  3. Ken Varn
    Replies:
    1
    Views:
    108
    Ken Varn
    Jul 9, 2004
  4. Anand
    Replies:
    1
    Views:
    194
    Bob Barrows
    Feb 6, 2004
  5. Sound
    Replies:
    2
    Views:
    436
    Randy Webb
    Sep 28, 2006
Loading...

Share This Page