how to populate empty field

[Alistair]

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

[Bob Barrows]

Alistair wrote:

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

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"

[Alistair]

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"

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

[Alistair]

"Alistair" <news@*remove*alistairb.co.uk> wrote in message
news:10*************@corp.supernews.com...

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"

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

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

[Aaron Bertrand [MVP]]

> 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/

[Alistair]

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:%2***************@TK2MSFTNGP11.phx.gbl...

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

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

[Aaron Bertrand [MVP]]

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

DON'T use a recordset and a cursor to perform UPDATEs.
http://www.aspaq.com/2191
I'm going phucking mad

Because you didn't read the article. Grow up and stop swearing like a kid
at recess.
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
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)
UPDATE users SET Creation_date = 10/01/2003 WHERE Creation_date IS NULL
This will try to set the date to 10 divided by 1 (10), divided by 2003 (some
very small number).
I'm getting very frustrated now, been on this since 6 and it's now nearly
9:30!!

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.

[Alistair]

"Aaron Bertrand [MVP]" <aa***@TRASHaspfaq.com> wrote in message
news:u0****************@TK2MSFTNGP11.phx.gbl...

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

DON'T use a recordset and a cursor to perform UPDATEs.
http://www.aspaq.com/2191
I'm going phucking mad

Because you didn't read the article. Grow up and stop swearing like a kid
at recess.

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

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)

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

This will try to set the date to 10 divided by 1 (10), divided by 2003

(some very small number).

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

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.

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

[Aaron Bertrand [MVP]]

> 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.

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/