why does this simple script doesn't work

[Julian]

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]

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 Caine]

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?

 

[Ray at]

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