Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull
Proper way to deal with DBNull Proper way to deal with DBNull
Proper way to deal with DBNull
Go Back  Xtreme Visual Basic Talk > > > Proper way to deal with DBNull


Reply
 
Thread Tools Display Modes
  #1  
Old 03-22-2007, 03:47 PM
SharkBait's Avatar
SharkBait SharkBait is offline
Contributor
 
Join Date: Dec 2004
Location: BC, Canada
Posts: 494
Default Proper way to deal with DBNull


Hi,

I have the following little bit of code:
Code:
While MyReader.Read SugarInfo = New CustomerInfo SugarInfo.Company = MyReader.Item("name") SugarInfo.ShipAddress1 = MyReader.Item("shipping_address_street") SugarInfo.ShipCity = MyReader.Item("shipping_address_city") SugarInfo.ShipProv = MyReader.Item("shipping_address_state") SugarInfo.ShipPostal = MyReader.Item("shipping_address_postalcode") SugarInfo.ShipCountry = MyReader.Item("shipping_address_code") lSugarAddressItems.Add(SugarInfo) End While

And when there is a NULL in the database what is the best way to handle it. Previously I would normally check to see if it is NULL before assigning it. But I would have to do that with every field I pull from the table. Is there an easier way?

Example:
Code:
if(MyReader.item("shipping_address_code") Is DBNull.Value) Then variable = "" else variable = MyReader.item("shipping_address_code") end if
__________________
SharkBait
Living on the WestCoast of Canada
My journey to blogging success: www.tyleringram.com
Reply With Quote
  #2  
Old 03-22-2007, 03:58 PM
Eduardo Lorenzo's Avatar
Eduardo Lorenzo Eduardo Lorenzo is offline
Senior Contributor
 
Join Date: Jun 2006
Location: Manila, Philippines
Posts: 846
Default

well there has been a thread here comparing methods to check for vbNull. And if memory serves me right,

If lenb(check) = 0 then

is the fastest. Something about lenb having one line less of code than len.
__________________
Pinoy ako.. ipinagmamalaki ko.
Reply With Quote
  #3  
Old 03-23-2007, 10:59 AM
SharkBait's Avatar
SharkBait SharkBait is offline
Contributor
 
Join Date: Dec 2004
Location: BC, Canada
Posts: 494
Default

I get this from the VB Documentation:

Quote:
The LenB function in earlier versions of Visual Basic returns the number of bytes in a string rather than characters. It is used primarily for converting strings in double-byte character set (DBCS) applications. All current Visual Basic strings are in Unicode, and LenB is no longer supported.
So LenB isn't supported and I guess that's why VB2005 tells me it isn't defined

Though what I was wondering if there was a way to shorting the while loop because if I have to check each field for a Null that just seems kinda inefficient. If I had 100 fields to go through, that's a lot of extra code isn't it?
__________________
SharkBait
Living on the WestCoast of Canada
My journey to blogging success: www.tyleringram.com
Reply With Quote
  #4  
Old 03-23-2007, 11:20 AM
Eduardo Lorenzo's Avatar
Eduardo Lorenzo Eduardo Lorenzo is offline
Senior Contributor
 
Join Date: Jun 2006
Location: Manila, Philippines
Posts: 846
Default

As a general rule, all fields should not be allowed to accept nulls. But in your case, it already does so we start working from there.

There are some workarounds for this without checking for null. One is

SugarInfo.ShipCountry = MyReader.Item("shipping_address_code") & " "

so nothing is null.

Another more acceptable way is to fix the null filtering in the query itself. Constructing a sql statement that selects data and rejects nulls will save you the effort of coding for checking for nulls.

So strictly speaking, for me the only "real" proper way to deal with DBNull is not to allow nulls in the table.
__________________
Pinoy ako.. ipinagmamalaki ko.

Last edited by Eduardo Lorenzo; 03-23-2007 at 11:35 AM.
Reply With Quote
  #5  
Old 03-23-2007, 07:28 PM
MKoslof's Avatar
MKoslofProper way to deal with DBNull MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

There are many, many situations in which null values can't be avoided. Sure, you always want your tables to have relevant data, but in many different database/relationship intensive applications, nulls will occur. However, as stated there are a few ways to handle this.

1) If you don't want null fields in the return handle it at the query level, but sometimes you won't know exactly what business rules are required with the present data or you might not even know (conducting joins across tables, etc)

2) Checking against DBNull.Value is the only "direct" way to check a data object against null. There are several graceful ways to do it:

A) Create your own "Nullable" DataReader class which inherits from the standard IDBDataReader. In this class you have functions that handle nullable types and return back some safe value of your liking

B) Just create a function that can be fed a database value and screen it for null, returning back something else. You can get more fancy or flexible with generics in this case.

3) The Good old if statement
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
  #6  
Old 06-12-2007, 02:26 PM
SharkBait's Avatar
SharkBait SharkBait is offline
Contributor
 
Join Date: Dec 2004
Location: BC, Canada
Posts: 494
Default

Perhaps I am doing this wrong but I am sure someone can point out what it is

Code:
sItem = CheckDBNull(myreader.item("name")) function CheckDBNull(ByVal value as String) as String if (value Is DBNull.Value) Then Return "" else Return value End Function

Does that make sense? Though it errors on DBNull cannot be converted to that of a string.

So I am not sure how I can change the above to check properly for a DBNull value
__________________
SharkBait
Living on the WestCoast of Canada
My journey to blogging success: www.tyleringram.com
Reply With Quote
  #7  
Old 06-12-2007, 03:04 PM
akrep's Avatar
akrep akrep is offline
Centurion
 
Join Date: Oct 2003
Location: Prague, CZ
Posts: 159
Default

Hi,
you have to pass value as object and then return value.ToString() if not null
akrep
Reply With Quote
  #8  
Old 06-12-2007, 03:31 PM
SharkBait's Avatar
SharkBait SharkBait is offline
Contributor
 
Join Date: Dec 2004
Location: BC, Canada
Posts: 494
Default

Ah that makes sense! Thank you
__________________
SharkBait
Living on the WestCoast of Canada
My journey to blogging success: www.tyleringram.com
Reply With Quote
  #9  
Old 06-16-2007, 07:24 PM
MKoslof's Avatar
MKoslofProper way to deal with DBNull MKoslof is offline
Cum Grano Salis

Retired Moderator
* Guru *
 
Join Date: Jul 2002
Location: Baltimore, Maryland
Posts: 14,636
Default

Or use Generics if you are using .Net 2.0 . Create functions which take type T or generic values and your function can also handle nullable types as well. You would leverage the .HasValue() property to determine if the value is null
__________________
"Artificial Intelligence is no match for natural stupidity." ~unknown
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull Proper way to deal with DBNull
Proper way to deal with DBNull
Proper way to deal with DBNull
 
Proper way to deal with DBNull
Proper way to deal with DBNull
 
-->