how to populate empty field

this has been driving me nuts for over an hour now.

I have a DB with a date field that is empty

Because of this the records sometimes get included in searches because their
contents are less than a particular search.

so..I had planned to populate them with any old date.

BUT I CAN'T!!!

I have tried all sorts..and nothing seems to work..

including

dim checkseperator
checkseperator = mid(rs("creation_date"), 3, 1) ' this will give "/" if the
field contains a date - 01/01/2001 etc
if checkseperator <> "/" then
rs("creation_date") = date()
rs.update

this does nothing, field remains empty

also tried

dim testforlength
testforlength len(rs("creation_date"))
if testforlength < "1" then
rs("creation_date") = date()
rs.update

nothing!!

even tried

dim testforbytelength
testforbytelength = lenB(rs("creation_date"))
if testforbytelength < "1" then
rs("creation_date") = date()
rs.update

again....nothing!!!

I did a response.write of the lenB of the fields..if it contains a date the
result is 20..if it contains nothing then result isn't 0 as I expected..it's
just blank

maybe this has something to do with strings instead of numbers but I just
can't get my head round it!!!

please help me...or just kill me

thanks

re: how to populate empty field

Alistair wrote:[color=blue]
> this has been driving me nuts for over an hour now.
>
> I have a DB with a date field that is empty
>
> Because of this the records sometimes get included in searches
> because their contents are less than a particular search.
>
> so..I had planned to populate them with any old date.
>
> BUT I CAN'T!!!
>
> I have tried all sorts..and nothing seems to work..
>
> including
>
> dim checkseperator
> checkseperator = mid(rs("creation_date"), 3, 1) ' this will give "/"
> if the field contains a date - 01/01/2001 etc
> if checkseperator <> "/" then
> rs("creation_date") = date()
> rs.update
>
> this does nothing, field remains empty
>
> also tried
>
> dim testforlength
> testforlength len(rs("creation_date"))
> if testforlength < "1" then
> rs("creation_date") = date()
> rs.update
>
> nothing!!
>
> even tried
>
> dim testforbytelength
> testforbytelength = lenB(rs("creation_date"))
> if testforbytelength < "1" then
> rs("creation_date") = date()
> rs.update
>
> again....nothing!!!
>
> I did a response.write of the lenB of the fields..if it contains a
> date the result is 20..if it contains nothing then result isn't 0 as
> I expected..it's just blank
>
> maybe this has something to do with strings instead of numbers but I
> just can't get my head round it!!!
>
> please help me...or just kill me
>
> thanks[/color]


UPDATE table set datefield = date() where datefield is null

HTH,
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"

re: how to populate empty field

[color=blue]
>
> UPDATE table set datefield = date() where datefield is null
>
> HTH,
> 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"
>
>[/color]
thanks bob..I have tried


if rs("creation_date") = null then
rs("creation_date") = date()
rs.update

but this didn't work either

I'm going mad...or I'm just plain stupid

re: how to populate empty field

"Alistair" <news@*remove*alistairb.co.uk> wrote in message
news:1000mhu63ftsi1c@corp.supernews.com...[color=blue]
>[color=green]
> >
> > UPDATE table set datefield = date() where datefield is null
> >
> > HTH,
> > 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"
> >
> >[/color]
> thanks bob..I have tried
>
>
> if rs("creation_date") = null then
> rs("creation_date") = date()
> rs.update
>
> but this didn't work either
>
> I'm going mad...or I'm just plain stupid
>
>[/color]

a query on number of records that contain a null in this field returns 0
a query on the byte length of this field for those that don't contain a date
returns blank

doesn't make sense to me

re: how to populate empty field

> if rs("creation_date") = null then

You can't say this in ASP. You would say if isNull(something) then

However, I think you should do this:

sql = "UPDATE table SET Creation_date = Date() WHERE Creation_date IS NULL"

(and other clauses to identify the single row, if necessary)

DON'T use a recordset and a cursor to perform UPDATEs.
http://www.aspaq.com/2191

An important thing to keep in mind is that an empty string is not the same
as NULL. And also, that you can't store an empty string in a date column.
It either has a value, or the value is unknown (NULL).

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

re: how to populate empty field

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:%23YWRzb71DHA.556@TK2MSFTNGP11.phx.gbl...[color=blue][color=green]
> > if rs("creation_date") = null then[/color]
>
> You can't say this in ASP. You would say if isNull(something) then
>
> However, I think you should do this:
>
> sql = "UPDATE table SET Creation_date = Date() WHERE Creation_date IS[/color]
NULL"[color=blue]
>
> (and other clauses to identify the single row, if necessary)
>
> DON'T use a recordset and a cursor to perform UPDATEs.
> http://www.aspaq.com/2191[/color]

I'm going phucking mad

set conn = server.CreateObject ("ADODB.Connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\dataset213.mdb"

set rs = server.CreateObject ("ADODB.Recordset")
rs.CursorType = 3
rs.locktype = 3
newdate = dateadd("d",-365,date) 'because I need an old date
strsql = "UPDATE usersdata SET Creation_date = " & newdate & " WHERE
Creation_date IS NULL"
rs.open strsql, conn


I get an error message telling me that the operation in line 22 (the strSQL
= line) is not allowed because the object is closed....
of course it's closed!!!..I haven't phucking opened it yet!!..it's just a
string ffs!!!!

if I include the line response.write strSQL I get the correct string (I
think)

UPDATE users SET Creation_date = 10/01/2003 WHERE Creation_date IS NULL

but as soon as I remove these lines...it goes pearshaped..

I know it's a lot to ask..but

can someone please explain how this is going wrong???

I have been having soo much trouble with sql queries

I'm getting very frustrated now, been on this since 6 and it's now nearly
9:30!!

6 million thank yous

re: how to populate empty field

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/[color=blue][color=green]
> > DON'T use a recordset and a cursor to perform UPDATEs.
> > http://www.aspaq.com/2191[/color]
>
> I'm going phucking mad[/color]

Because you didn't read the article. Grow up and stop swearing like a kid
at recess.
[color=blue]
> set conn = server.CreateObject ("ADODB.Connection")
> conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data[/color]
Source=c:\dataset213.mdb"[color=blue]
>
> set rs = server.CreateObject ("ADODB.Recordset")
> rs.CursorType = 3
> rs.locktype = 3
> newdate = dateadd("d",-365,date) 'because I need an old date
> strsql = "UPDATE usersdata SET Creation_date = " & newdate & " WHERE
> Creation_date IS NULL"
> rs.open strsql, conn[/color]

Once again. Don't use a recordset to affect data. Recordsets are for
*reading* data. Also, you need to delimit your date correctly.

How about this.

set Conn = CreateObject("ADODB.Connection")
conn.open "Provider=Microsoft ... "
newdate = dateadd("d",-365,date)
strsql = "UPDATE usersdata SET Creation_date = #" & _
newdate & "# WHERE Creation_date IS NULL"
conn.execute(sql)
[color=blue]
> UPDATE users SET Creation_date = 10/01/2003 WHERE Creation_date IS NULL[/color]

This will try to set the date to 10 divided by 1 (10), divided by 2003 (some
very small number).
[color=blue]
> I'm getting very frustrated now, been on this since 6 and it's now nearly
> 9:30!![/color]

If you read the articles posted to you, and spend time reading tutorials
instead of diving into things you know little about, you might have better
luck, instead of having a big temper tantrum.

re: how to populate empty field

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:u0XtV%2381DHA.3196@TK2MSFTNGP11.phx.gbl...[color=blue]
>
>
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/[color=green][color=darkred]
> > > DON'T use a recordset and a cursor to perform UPDATEs.
> > > http://www.aspaq.com/2191[/color]
> >
> > I'm going phucking mad[/color]
>
> Because you didn't read the article. Grow up and stop swearing like a kid
> at recess.
>[color=green]
> > set conn = server.CreateObject ("ADODB.Connection")
> > conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data[/color]
> Source=c:\dataset213.mdb"[color=green]
> >
> > set rs = server.CreateObject ("ADODB.Recordset")
> > rs.CursorType = 3
> > rs.locktype = 3
> > newdate = dateadd("d",-365,date) 'because I need an old date
> > strsql = "UPDATE usersdata SET Creation_date = " & newdate & " WHERE
> > Creation_date IS NULL"
> > rs.open strsql, conn[/color]
>
> Once again. Don't use a recordset to affect data. Recordsets are for
> *reading* data. Also, you need to delimit your date correctly.
>
> How about this.
>
> set Conn = CreateObject("ADODB.Connection")
> conn.open "Provider=Microsoft ... "
> newdate = dateadd("d",-365,date)
> strsql = "UPDATE usersdata SET Creation_date = #" & _
> newdate & "# WHERE Creation_date IS NULL"
> conn.execute(sql)
>[color=green]
> > UPDATE users SET Creation_date = 10/01/2003 WHERE Creation_date IS NULL[/color]
>
> This will try to set the date to 10 divided by 1 (10), divided by 2003[/color]
(some[color=blue]
> very small number).
>[color=green]
> > I'm getting very frustrated now, been on this since 6 and it's now[/color][/color]
nearly[color=blue][color=green]
> > 9:30!![/color]
>
> If you read the articles posted to you, and spend time reading tutorials
> instead of diving into things you know little about, you might have better
> luck, instead of having a big temper tantrum.
>
>[/color]

Aaron,

my sincere apologies.

I was just very frustrated last night after looking at something which
appeared to be right but wasn't and not having the skill to work it out.

thank you for your help and patience

Alistair

re: how to populate empty field

> I was just very frustrated last night after looking at something which[color=blue]
> appeared to be right but wasn't and not having the skill to work it out.[/color]

In the future, please keep that frustration to yourself. It doesn't do
anyone any good here.

People are here to help and be helped, not to put up with grade school
antics.

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/