Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 266
  • Last Modified:

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

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
hmaloney
Asked:
hmaloney
  • 11
  • 5
1 Solution
 
rockiroadsCommented:
Does your table allow null fields?

0
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
hmaloneyAuthor Commented:
Yes, the code works in the Access DB query editor, but not when run by ASP.

The date fields are not required.
0
 
rockiroadsCommented:
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
 
rockiroadsCommented:
urm, u dont need


Crs.Edit


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

0
 
rockiroadsCommented:
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
 
rockiroadsCommented:
values for constants are

adOpenDynamic = 2
adLockPessimistic = 2

so I guess u could try

Crs.Open strsql, xConn, 2, 2

0
 
hmaloneyAuthor Commented:
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
 
rockiroadsCommented:
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
 
hmaloneyAuthor Commented:
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
 
rockiroadsCommented:
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
 
hmaloneyAuthor Commented:
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
 
rockiroadsCommented:
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
 
hmaloneyAuthor Commented:
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
 
rockiroadsCommented:
No probs
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now