 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / General / July 2008Null date in access db
I have a legacy app that was generated with the old vb6 data app wizard. It uses an Access 97 db to display a master-detail view of two tables, and allows editing of the detail record. using ado and data binding to textboxes. It was modified by someone, years ago, to do the specific task for which it was designed. I now want to clear a date in the database but clearing the textbox bound to it throws an error when "rec.UpdateBatch adAffectAll" is called. Is there a value I can put in the textbox that will set the db date field to null? >I have a legacy app that was generated with the old vb6 data app wizard. > [quoted text clipped - 8 lines] > Is there a value I can put in the textbox that will set the db date field > to null? Well, I thoroughly detest data-binding and never use it except for VERY simple demos (proof of concept type of thing). But my guess is that since a TextBox has no concept of a null, you're going to have to write some code. With data-binding, this could be more difficult (precisely why I don't like it) because you need to check if the textbox contains nothing (i.e., a 0-length string). Of course, stating what error your actually getting might help solve this too. Just telling us "it throws an error" really doesn't tell us anything.  SignatureMike Microsoft MVP Visual Basic >>I have a legacy app that was generated with the old vb6 data app >>wizard. [quoted text clipped - 22 lines] > this too. Just telling us "it throws an error" really doesn't tell us > anything. Right. There error is: Run-time error 80040e21 Multiple-step operation generated errors. Check each status value. The relevant code for processing editing/updating is as follows. load: set textbox.datasource = rec edit: textbox.locked = false update: rec.UpdateBatch adAffectAll <-- error thrown here textbox.locked = true One interesting note: On a record with a blank date, if I click 'edit' then click 'update' (without puttin in a date) I do not get the error. It could be that the rec.updateBatch knows that the date textbox did not change. But if I put in a date then clear it out and hit update I get the error. Sure, I can remove the databinding and manually manage the database, but I'm suprised that putting nulls into the database through databinding was not considered when this stuff was designed. PS - I'm gald to see I'm not the only one adverse to databinding. ;) >>>I have a legacy app that was generated with the old vb6 data app >>>wizard. [quoted text clipped - 54 lines] > > PS - I'm gald to see I'm not the only one adverse to databinding. ;) You might change the offending column in the db to allow nulls. > You might change the offending column in the db to allow nulls. The db field is a date and allow nulls is not an option. I could, however, change it to a text field. hmmmmm.... >> You might change the offending column in the db to allow nulls. > > The db field is a date and allow nulls is not an option. Yes it is; Access simply doesn't call it "allow nulls." Look for the "Required" property and set it to No. (It's probably already set to that, though. I doubt this is actually your problem.) >>> You might change the offending column in the db to allow nulls. >> [quoted text clipped - 3 lines] > "Required" property and set it to No. (It's probably already set to > that, though. I doubt this is actually your problem.) It is set to Required: No, so that's not it. >>>> You might change the offending column in the db to allow nulls. >>> [quoted text clipped - 5 lines] > > It is set to Required: No, so that's not it. Yeah, I didn't think so. Glad to see you found a solution. And data binding still sucks. As MikeB suggested, change the field to allow nulls, but few applications don't behave well with nulls. Another choice is to use a StdDataFormat object. In MSDN, type "Format Objects" in MSDN Index and read both "Format Objects" and "Format Objects Tutorial". StdDataFormat object basically allows you to convert the data back and forth between the DB and the bound controls. Another choice is to define the recordset variable WithEvents. If you are using the Data Environment designer, you can also add the events by viewing the code. Check the following events in MSDN: WillChangeField WillChangeRecord WillChangeRecordset You can check and modify the fields as necessary, however, your changes will cause reentrancy by triggering further events, so you have to use flags to ignore these extra events. Using StdDataFormat is easier in your case. Using Will events above works with recordsets that are not bound to controls. > As MikeB suggested, change the field to allow nulls, but few > applications don't behave well with nulls. Another choice is to use a [quoted text clipped - 16 lines] > easier in your case. Using Will events above works with recordsets > that are not bound to controls. Success! In the IDE I set the DataFormat property of the textbox to Date. I can now blank out the date and the appropriate null value is written to the database. Now that I dig deep in the re-formatted part of my brain I remember using Format Objects with data binding to get 'in-between' the database and the bound object. Here the 'defualt' date format handled the null-problem just fine. Thanks expvb. kpg |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.