<NULL> in DataSource

  • Thread starter Thread starter Bob Day
  • Start date Start date
B

Bob Day

Using VS 2003, VB.net and SQL MSDE...

This is driving me nuts!

I would love to find a simple way to translate any column with a <NULL>
value that is a datatype string to a string.empty (i.e. ""). Is there any
command that says "To make life easy, automatically translate any <NULL> in
a STRING datatype column to String.Empty" when it is FILLed from a
DataSource?

The answer I suspect is no.

I have posted many posts to these boards on this subject, and have not
gotten an answer that is feasible.. Since VB.net fails when you try to FILL
a column that has a <NULL> value, you either have to 1) confirm it is not
<NULL> for each column before you FILL it to a DataSet or 2) prevent <NULL>
from ever getting in your datasource to begin with. Both of these take an
extraordinary amount of code, which could easily break if field names
change, etc.

1) confirm each column is not null before you FILL. This works, but
requires a lot of code (for each column read).
Basically, check each column with the .IsColumn1Null, and FILL it only if
false, but this would be a lot of code required for every column.

2) One suggestion:
"You can use isnull in the sql select statement. It
returns what you want if the cell is null. i.e., isnull
(inCareOf, '')"
I use the Component Designer/Query Designer, which makes use of the above
very difficult. You manually have to change each column name, defeating the
power of the Query Designer to make change quickly in the future. Any other
way of doing this?

3) Another suggestion:
"Public Sub StringNullHelper(arg As Object) As String
If IsDbNull(arg) Then Return String.Empty
Return Cstr(arg)
End Sub"
The problem here is this assumes you already have arg to pass to this
method. The Fill command fails on a column that has a <NULL> value, so you
never get arg to use this method.

4) Using Sever Explorer, Table Designer, designing each column with a
default value of ('') for string data type should avoid a null when the row
is added to the DataSource and updated with a column that has no value in
it. This does not work as documented at all, and is a bug that should be
resolved.

This is really causing a lot of problems, and I cannot believe there is not
a relatively simply way to do it.

There has to be an easier way!!!

Any ideas?

Thanks!
Bob Day
 
Bob:

YOu don't have to confirm that a Column isn't null before you select it in a
Da.Fill statement. I can say that I have an app that gets well over 10,000
queries a day from the web alone and every one of them allows null. Da.Fill
doesn't fail just because there are nulls.

From the other NG posts, Null objects aren't the same as Null datatypes.
IsNull works from everywhere, even query designer.

..NEt makes a lot of things 'harder' than VB6, they even have this 'Option
Strict' thing and don't allow variants. All of which are needed
improvements. I'm guessing from the tone of all of this that Stored
Procedures are out of the question, and commandbuilders are as well.

The whole point of 3) probably isnt' valid b/c Null values in the DB don't
have any effect whatsoever in select commands. Perhaps, if you are filling
a strongly typed dataset that doesn't allow nulls........but if the dataset
doesn't allow them, then why does the database? If this isn't the case,
then you don't have a problem.

I'm not trying to be aggressive, but you are making some claims, as you did
in the other forum that simply aren't true. Your whole point on 4 stems
from the fact, based on your other posts, that you set column values to
Nothing, and expect the default column value to pop in there. DataTables,
DataColumns et al are reference types!!!!!! You keep thinking that setting
it to nothing will give you a default which is true if you are referencing a
VALUE type, but you aren't. Therein is the crux of the problem.

I know you are probably frustrated b/c this is a big difference between VB6
and .NET, and there are many people here to help you. But before you assign
'bug' status to things, well, that should be the last explanation in
general...particularly in this case.

If you'd like to post your SELECT code and what you are trying to do, we can
fix it. However my friend, before understanding the distinction between
Value and Refernce Types and how setting them to Nothing affects
them....this problem won't be resolved.

I'll be up for a while, and I'll gladly take a look at your code and fix it
if you'd like (e-mail address removed) (minus the nospamplease).

Bill
 
It doesn't work that way for me. With a datatable (designed in Data Table
Designer) and a DataSet (from a Data Adapter made in component designer),
where both the DataSet and DataSource set to allow nulls, it will fail on
the section below in the Component Designer generated code with the error
"cast from type DBNull to type STRING is not valid". The datatype for this
column is string. I guess if I made all columns object datatype I could get
around this, but that would be silly.

I would appreciate any help. I am at the end of my rope on this issue.

Bob Day

Public Property fld_Extension_Digits As String
Get
Try
Return
CType(Me(Me.tablePhones.fld_Extension_DigitsColumn),String)
'''''FAILS ON ABOVE LINE *****************
Catch e As InvalidCastException
Throw New StrongTypingException("Cannot get value
because it is DBNull.", e)
End Try
End Get
Set
Me(Me.tablePhones.fld_Extension_DigitsColumn) = value
End Set
End Property
 
Back
Top