Solved

ADO +Access - can't set a date field to null

Posted on 2006-06-28
16
256 Views
Last Modified: 2008-03-17
Hi there,

When I do an update of a record in my Access DB straight into Access, my update works successfully and sets the date field to null, but when I use the same query from ASP, using this sort of object:
set Crs = Server.CreateObject("ADODB.Recordset")

the update works in all respects except that the field I am setting to Null is being left as it was (ie. with a date in it).

Can someone please help?  I've tried various syntaxes eg. = null, = Null, and it wont let me set the field to ''.

Help would be most appreciated!

Here's my raw code:

set Crs = Server.CreateObject("ADODB.Recordset")
strsql = "UPDATE PropertyDetails SET UploadedToInternet = #" & Date() & "#, RemovedFromInternet = Null WHERE Internet=True AND UploadedToInternet is Null AND Active = true"
Crs.Open strsql, xConn      

As I say, the UploadedToInternet field is set correctly, but the RemovedFromInternet date field is left as it was.

Regards,
Heather
0
Comment
Question by:hmaloney
  • 11
  • 5
16 Comments
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000151
Does your table allow null fields?

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000163
In the table design, have a look at the date field

What does Required say

With ADO, I think your opening a recordset, is this not better off for SELECT?
Have u just tried

DoCmd.RunSQL strSQL

or CurrentDB.Execute strSQL


do a debug.print strSQL
then take that from the immediate window and try run it in query editor, what happens



0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000184
Using ADO, I thnk u may be better off trying this

define a recordset - do a select

strSQL = "SELECT * FROM PropertyDetails WHERE Internet=True AND UploadedToInternet is Null AND Active = true"

then open your recordset, u may need to provide LockOptimistic or some other flag

then to update fields

do while not crs.EOF

    crs.Edit
    crs!UploadedToInternet = Date()
    crs!RemovedFromInternet = Null
    crs.Update

    crs.MoveNext
loop


0
 

Author Comment

by:hmaloney
ID: 17000186
Yes, the code works in the Access DB query editor, but not when run by ASP.

The date fields are not required.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000198
ok, its ASP, alright

try the way I told u using ADO

then try this when opening your recordset

e.g.

strSQL = "SELECT * FROM PropertyDetails WHERE Internet=True AND UploadedToInternet is Null AND Active = true"

Crs.Open strSQL, xConn, adOpenDynamic, adLockPessimistic

do while not Crs.EOF

    Crs.Edit
    Crs!UploadedToInternet = Date()
    Crs!RemovedFromInternet = Null
    Crs.Update

    Crs.MoveNext
loop

Crs.close




just double check the table definitions though

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000264
urm, u dont need


Crs.Edit


just set the fields and do Crs.Update to update one record

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000310
Actually try your code like this


set Crs = Server.CreateObject("ADODB.Recordset")
strsql = "UPDATE PropertyDetails SET UploadedToInternet = #" & Date() & "#, RemovedFromInternet = Null WHERE Internet=True AND UploadedToInternet is Null AND Active = true"

'ADD THIS TO RECORDSET  , adOpenDynamic, adLockPessimistic
Crs.Open strsql, xConn, adOpenDynamic, adLockPessimistic



just ensure u open recordset as shown


What happens?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000317
values for constants are

adOpenDynamic = 2
adLockPessimistic = 2

so I guess u could try

Crs.Open strsql, xConn, 2, 2

0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:hmaloney
ID: 17000351
Okay, tried that.  Still the RemovedFromInternet field is not set to null.  It still has the date value left in it from prior to the update, but the UploadedToInternet field is changed and includes the date.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17000385
I just tried a similar update in ADO (but from MSAccess) and its fine

the fieldname is spelt correctly right?

   RemovedFromInternet

I assume so as u said it works when run in sql
And its definitely not a required field



as a test, try this, does it set to todays date? I say this to prove whether that field is actually working when updating


strsql = "UPDATE PropertyDetails SET UploadedToInternet = #" & Date() & "#, RemovedFromInternet = Date() WHERE Internet=True AND UploadedToInternet is Null AND Active = true"
0
 

Author Comment

by:hmaloney
ID: 17000424
I read on another thread that someone else had experienced that sometimes date fields would not be set to null.

I guess what I am after is someone who has come across this before, and what the solution was.

I can assure you that tie RemovedFromInternet field is definitely working... it is updated by another very similar query.
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17000582
ok, then

I can't see much wrong with your SQL

Have u tried just running this? just set the field?

UPDATE PropertyDetails SET RemovedFromInternet = Null WHERE Internet=True AND UploadedToInternet is Null AND Active = true"


Did u try the method of using a loop?

try this

open recordset as static e.g.

Crs.Open sSql, adoConn, adOpenStatic, adLockPessimistic

then check recordcount

msgbox "Number of records is " & Crs.Recordcount



0
 

Author Comment

by:hmaloney
ID: 17015878
Yes, I can do the simple set of the RemovedFromInternet field, and it works quite happily.

Yes, I did try the loop method... no difference.  It is doing one part of the update, but not the bit to set the field to null.

So.... I am preparing to give up on this as a waste of time, and add another couple of flags to the database which I will have no trouble setting on and off.

However, it would be really nice if there was someone out there who has had this problem before, who could perhaps help me to fix it without having to add more fields.

Regards,
Heather
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17015903
ok, workaround, though it may not be elegant and I don't know why u are getting the problem

the simple set works fine, right?
what if then u loop and ran two update statements

one for RemoveFromInternet to null , the other for UploadedToInternet


out of curiosity, what does

strsql = "UPDATE PropertyDetails SET RemovedFromInternet = Null, UploadedToInternet = #" & Date() & "# WHERE Internet=True AND UploadedToInternet is Null AND Active = true"


do
0
 

Author Comment

by:hmaloney
ID: 17028046
Okay, I've given you the points for your efforts.  The problem was all my fault, and it was working all along.  I was changing one ASP file, which I had actually renamed on the server, so everytime I tried something new, surprise, surprise, it made no difference.  :-(  

Sorry for wasting your time.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17029132
No probs
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now