DBNull values

[Nickels]

Hi,

 

I am trying to programmatically update a datetime field in a dataset with the current date and time (this line occurs within a for...next loop, with intLoop being the counter):

 

dsResults.Tables("Queries").Rows(intLoop).Item("DateResolved") = Now()

If the field already has a date in it, it works no problem. If, however, the field does not yet have a recorded value and is therefore null (which it often is) I get error number 13: Operator is not valid for type DBNull and type Date.

 

The datatable ("Queries") is populated from a table in a SQL Server 2000 database. The column in question ("DateResolved")is a DateTime field which is set to allow null values. It is used to record the date when an enquiry has been resolved and should not contain a date until this happens (I don't want to put a default date in to get around the problem).

 

Can anyone help?

 

Nickels

Edited May 8, 2003 by Nickels

[hog]

When I read a date field into a dataset I do so like this:

 

m_Dated = IIf(IsDBNull(m_drContract.Item("dated")), Nothing, m_drContract.Item("dated"))

 

this works for me and has not caused any problems as yet. However if you do not want it to assign Nothing do the following

 

m_Dated = IIf(IsDBNull(m_drContract.Item("dated")), Now(), m_drContract.Item("dated"))

[Nickels]

That's great for reading data from the dataset into a variable as in your example, but I am trying to do the opposite and write to the dataset. Checking whether the existing 'Date Resolved' in the dataset is Null or not isn't a problem, it's overwriting it with todays date.

 

If you try what you said the other way around...

m_drContract.Item("dated")) = Now()

...and m_drContract.Item("dated")) is currently DBNull, does the 'dated' field get updated or does it break like mine?

 

It seems that because the expression m_drContract.Item("dated")) equates to DBNull what the code is effectively saying is...

 

A DBNull thing = A Date thing

 

...and it doesn't like it.

 

Does that make any sense?

[hog]

m_drContract.Item("dated")) = Now()

 

results in no error and the date is written correctly to the database.

 

The field in the database is set to Indexed=No and Required=No

[Nerseus]

I have never seen the behavior you're talking about, Nickels.

 

I know that Now() is a holdover from VB6. Maybe try "... = DateTime.Now" instead? If that doesn't work, maybe you could post your code with a SQL snippet for the CREATE TABLE so we can test it. I assign DateTime.Now to SQL Server DateTime variables all the time.

 

Also, it doesn't matter if your database column allows nulls or not since you're assigning a date. If you wanted to assign System.DBNull.Value to the column, then you'd have to make sure the column allows nulls.

 

-ner

[Nickels]

Both, thanks for your replies - they have helped and I am now looking at my problem differently.

 

The difficulty I am having seems to be related to the data binding I have in place rather than the specific line of code at which I am getting the error.

 

My dataset has two datatables ('Accounts' and 'Queries') and a datarelation (One to many, 'Accounts.AccountID' to 'Queries.AccountID'). On my form I have a bunch of textboxes bound to an account record. They all work fine. I then have a datagrid bound to the datarelation (so it lists all related queries for the account), and a bunch of other textboxes also bound to the datarelation. This means that the user can click on one of the queries in the datagrid and the textboxes automatically change to show the selected record. This all works perfectly - including data updates - until I try and amend any values which are currently null. If I do it programmatically I get the error I mentioned in my first post, if I do it manually by typing into one of the bound textboxes the app goes bonkers and I can't get focus away from the textbox, click any buttons or do anything.

 

If the problem is because I am binding to the datarelation rather than directly to the 'Queries' datatable, I don't understand why it lets me edit values which aren't null but not those that are. Seems a bit random.

 

Any ideas?

[APaule]

a) Does the error appear in new records or also in existing ones you are editing?

 

b) Does the error appear exactly at that line you posted first or at updating the record? - And if so, do you have true/false columns in your Query-table?

[Nickels]

I haven't tried it on new records, just existing ones. I expect the problem would still occur with new records though.

 

The error occurs as soon as you try and overwrite any null value in the local dataset, either programmatically or via manual data-entry using the bound textboxes. Things don't get as far as posting the changes back to the database.

 

In terms of True/False fields yes, there are some in the table. However, my database update function checks for Boolean fields and converts them to a int16 value compatible with the SQL Server bit datatype and I haven't had any problems from that side of things.

[Nerseus]

I can't really help with your problem, but I can offer some insight (maybe). First, the "lockup" problem you experience when modifying the null values to something else in a textbox is usually caused by untrapped errors. I've noticed what you're seeing when I have bound controls that try to write invalid data to the dataset. With some 3rd party controls, in some scenarios, there is no exception being thrown, no messagebox, and no JIT runtime error - it just "sticks" focus on the textbox.

 

But, this problem will go away (hopefully!) once the other error is corrected. I can't see any reason why you couldn't put a valid date value in the date column unless that was your join column (which I would find hard to believe since it's a date). You're probably right in that it has something to do with the binding to a relationship instead of a table or a DataView. I have no experience there so I'm not sure what the problem might be.

 

I'd suggest using the call stack when your program gets the exception (when setting the column to DateTime.Now). Also, search microsoft's support forum - this might be a known issue or just something you can't do.

 

-Nerseus

[bkedersha]

Re: Operator is not valid for type 'DBNull' and type 'Date'.

 

Did you find a way around this error?

 

-={Brian Kedersha}=- :confused:

 

Hi,

 

I am trying to programmatically update a datetime field in a dataset with the current date and time (this line occurs within a for...next loop, with intLoop being the counter):

 

dsResults.Tables("Queries").Rows(intLoop).Item("DateResolved") = Now()

If the field already has a date in it, it works no problem. If, however, the field does not yet have a recorded value and is therefore null (which it often is) I get error number 13: Operator is not valid for type DBNull and type Date.

 

The datatable ("Queries") is populated from a table in a SQL Server 2000 database. The column in question ("DateResolved")is a DateTime field which is set to allow null values. It is used to record the date when an enquiry has been resolved and should not contain a date until this happens (I don't want to put a default date in to get around the problem).

 

Can anyone help?

 

Nickels