Solved

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

Posted on 2006-06-28
16
262 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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
 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

636 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