why does this simple script doesn't work

Discussion in 'ASP General' started by Julian, Sep 30, 2003.

  1. Julian

    Julian Guest

    Hi

    I am trying to update a date field in my table but some how this simple code
    does not work, I know the select work because if I write the fields, it will
    show the data from the table but why the update does not work?
    <%

    Set ConnGallery = Server.CreateObject("ADODB.Connection")
    ConnGallery.Open ConnectionString
    Set cmdTemp = Server.CreateObject("ADODB.Command")

    Set CmdUpdateAd = Server.CreateObject("ADODB.Recordset")
    cmdTemp.CommandText = "SELECT Ads.* FROM Ads WHERE Advertiser=0 "
    cmdTemp.CommandType = 1
    Set cmdTemp.ActiveConnection = ConnGallery
    CmdUpdateAd.Open cmdTemp, , 1, 3

    CmdUpdateAd.Fields("Ad_Expires") = " "


    CmdUpdateAd.Update
    CmdUpdateAd.Close

    response.Write ("hej")

    %>
    Julian, Sep 30, 2003
    #1
    1. Advertising

  2. Julian

    Ray at Guest

    What "does not work" about it? Are you getting an error? Are there any
    records where advertiser=0? Why are you creating a recordset for an update?
    Is Ad_Expires a text column?


    Do this

    sSQL = "UPDATE [Ads] SET [Ad_Expires]='' WHERE [Advertiser]=0"
    Set ConnGallery = Server.CreateObject("ADODB.Connection")
    ConnGallery.Open ConnectionString
    ConnGallery.Execute sSQL
    ConnGallery.Close
    Set ConnGallery = Nothing


    Ray at work


    "Julian" <> wrote in message
    news:...
    > Hi
    >
    > I am trying to update a date field in my table but some how this simple

    code
    > does not work, I know the select work because if I write the fields, it

    will
    > show the data from the table but why the update does not work?
    > <%
    >
    > Set ConnGallery = Server.CreateObject("ADODB.Connection")
    > ConnGallery.Open ConnectionString
    > Set cmdTemp = Server.CreateObject("ADODB.Command")
    >
    > Set CmdUpdateAd = Server.CreateObject("ADODB.Recordset")
    > cmdTemp.CommandText = "SELECT Ads.* FROM Ads WHERE Advertiser=0 "
    > cmdTemp.CommandType = 1
    > Set cmdTemp.ActiveConnection = ConnGallery
    > CmdUpdateAd.Open cmdTemp, , 1, 3
    >
    > CmdUpdateAd.Fields("Ad_Expires") = " "
    >
    >
    > CmdUpdateAd.Update
    > CmdUpdateAd.Close
    >
    > response.Write ("hej")
    >
    > %>
    >
    >
    Ray at, Sep 30, 2003
    #2
    1. Advertising

  3. Julian

    Julian Caine Guest

    Thanks Ray.

    I did as you suggested, made a new file like this:

    <%@ LANGUAGE="VBSCRIPT" %>


    <!--#INCLUDE FILE="../dataconn_inc.asp"-->
    <!--#INCLUDE FILE="../config_inc.asp"-->

    <%
    ' to put the date for all non-advertiser to blank.
    sSQL = "UPDATE [Ads] SET [Ad_expires]='' WHERE [Advertiser]= 0"
    Set ConnGallery = Server.CreateObject("ADODB.Connection")
    ConnGallery.Open ConnectionString
    ConnGallery.Execute sSQL
    ConnGallery.Close
    Set ConnGallery = Nothing


    response.Write ("hej")

    %>

    run the code and it executes without any error, the problem is that
    database do not get updated.
    Advertiser is type YES/NO
    Ad_Expires is short date
    and both defined so, they can be emty. My connection is correct since I
    can get all the rows listed when I write it.
    Any idea?


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Julian Caine, Oct 1, 2003
    #3
  4. Julian

    Ray at Guest

    If you try to set a date/time column to '' in Access, you will get a data
    conversion error. I suggest either setting the date to something like
    1/1/1900, or setting it to null, depending on which would work better in
    your environment.

    sSQL = "UPDATE [Ads] SET [Ad_expires]=#1/1/1900# WHERE [Advertiser]= 0"
    or
    sSQL = "UPDATE [Ads] SET [Ad_expires]=NULL WHERE [Advertiser]= 0"

    Ray at work

    "Julian Caine" <> wrote in message
    news:%...
    >
    > Thanks Ray.
    >
    > I did as you suggested, made a new file like this:
    >
    > <%@ LANGUAGE="VBSCRIPT" %>
    >
    >
    > <!--#INCLUDE FILE="../dataconn_inc.asp"-->
    > <!--#INCLUDE FILE="../config_inc.asp"-->
    >
    > <%
    > ' to put the date for all non-advertiser to blank.
    > sSQL = "UPDATE [Ads] SET [Ad_expires]='' WHERE [Advertiser]= 0"
    > Set ConnGallery = Server.CreateObject("ADODB.Connection")
    > ConnGallery.Open ConnectionString
    > ConnGallery.Execute sSQL
    > ConnGallery.Close
    > Set ConnGallery = Nothing
    >
    >
    > response.Write ("hej")
    >
    > %>
    >
    > run the code and it executes without any error, the problem is that
    > database do not get updated.
    > Advertiser is type YES/NO
    > Ad_Expires is short date
    > and both defined so, they can be emty. My connection is correct since I
    > can get all the rows listed when I write it.
    > Any idea?
    >
    >
    > *** Sent via Developersdex http://www.developersdex.com ***
    > Don't just participate in USENET...get rewarded for it!
    Ray at, Oct 1, 2003
    #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. jblazi
    Replies:
    5
    Views:
    427
    jblazi
    Aug 16, 2004
  2. Horace Nunley

    why why why does function not work

    Horace Nunley, Sep 27, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    456
    =?Utf-8?B?UGV0ZXIgQnJvbWJlcmcgW0MjIE1WUF0=?=
    Sep 27, 2006
  3. Mr. SweatyFinger

    why why why why why

    Mr. SweatyFinger, Nov 28, 2006, in forum: ASP .Net
    Replies:
    4
    Views:
    878
    Mark Rae
    Dec 21, 2006
  4. Mr. SweatyFinger
    Replies:
    2
    Views:
    1,836
    Smokey Grindel
    Dec 2, 2006
  5. Sara
    Replies:
    6
    Views:
    254
    John W. Krahn
    Apr 12, 2004
Loading...

Share This Page