DBNull values

N

Nickels

Member
Joined
May 8, 2003
Messages
7
Location
England
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):

Code: 
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 dont want to put a default date in to get around the problem).

Can anyone help?

Nickels
 
Last edited by a moderator:
When I read a date field into a dataset I do so like this:

Code: 
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

Code: 
m_Dated = IIf(IsDBNull(m_drContract.Item("dated")), Now(), m_drContract.Item("dated"))
 
Thats 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 isnt a problem, its overwriting it with todays date.

If you try what you said the other way around...
Code: 
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 doesnt like it.

Does that make any sense?
 
Code: 
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
 
I have never seen the behavior youre talking about, Nickels.

I know that Now() is a holdover from VB6. Maybe try "... = DateTime.Now" instead? If that doesnt 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 doesnt matter if your database column allows nulls or not since youre assigning a date. If you wanted to assign System.DBNull.Value to the column, then youd have to make sure the column allows nulls.

-ner
 
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 cant 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 dont understand why it lets me edit values which arent null but not those that are. Seems a bit random.

Any ideas?
 
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?
 
I havent 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 dont 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 havent had any problems from that side of things.
 
I cant 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. Ive noticed what youre 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 cant see any reason why you couldnt put a valid date value in the date column unless that was your join column (which I would find hard to believe since its a date). Youre 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 Im not sure what the problem might be.

Id suggest using the call stack when your program gets the exception (when setting the column to DateTime.Now). Also, search microsofts support forum - this might be a known issue or just something you cant do.

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

Did you find a way around this error?

-={Brian Kedersha}=- :confused:

Nickels said:
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):

Code: 
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 dont want to put a default date in to get around the problem).

Can anyone help?

Nickels
Click to expand...
 
Back
Top