Solved

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

Posted on 2006-06-28
16
260 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
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 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

821 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