?
Solved

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

Posted on 2006-06-28
16
Medium Priority
?
264 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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 …

719 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