A better way to check for IsDBNull ?

Paul D. Fox

I have this code snippet below where I'm loading all the labels based upon
the retrieved record from the DataReader. Unfortunatley, some of the values
are null, so I can't explicitly set the labels without checking first. Is
there a better method to write this than using a bunch of "If" statements?
Could it be a function? if so, how would I write it?

'Read the single record
While drLeadFeedback.Read()

If Not drLeadFeedback.IsDBNull(1) Then
lblLeadName.Text = drLeadFeedback.Item("Lead_Name")
End If
If Not drLeadFeedback.IsDBNull(2) Then
lblLeadEmail.Text = drLeadFeedback.Item("Lead_Email")
End If
If Not drLeadFeedback.IsDBNull(3) Then
lblLeadPhone.Text = drLeadFeedback.Item("Lead_Phone")
End If
If Not drLeadFeedback.IsDBNull(4) Then
lblUnderwriter.Text = drLeadFeedback.Item("Underwriter")
End If
If Not drLeadFeedback.IsDBNull(10) Then
lblCreateDate.Text = drLeadFeedback.Item("Create_Date")
End If
If Not drLeadFeedback.IsDBNull(18) Then
lblStatus.Text =
drLeadFeedback.Item("Lead_Feedback_Status")
End If
If Not drLeadFeedback.IsDBNull(19) Then
lblAssignedTo.Text = drLeadFeedback.Item("Assigned_To")
End If
If Not drLeadFeedback.IsDBNull(11) Then
lblDealName.Text = drLeadFeedback.Item("Deal_Name")
End If
If Not drLeadFeedback.IsDBNull(12) Then
lblClient.Text = drLeadFeedback.Item("Client_Name")
End If
If Not drLeadFeedback.IsDBNull(13) Then
lblCSMName.Text = drLeadFeedback.Item("CSM_Name")
End If
If Not drLeadFeedback.IsDBNull(14) Then
lblSpeedRating.Text =
drLeadFeedback.Item("Speed_Rating")
End If
If Not drLeadFeedback.IsDBNull(15) Then
lblAccuracyRating.Text =
drLeadFeedback.Item("Accuracy_Rating")
End If
If Not drLeadFeedback.IsDBNull(16) Then
lblKnowledgeRating.Text =
drLeadFeedback.Item("Knowledge_Rating")
End If
If Not drLeadFeedback.IsDBNull(17) Then
lblWorkEffortRating.Text =
drLeadFeedback.Item("Work_Effort_Rating")
End If
If Not drLeadFeedback.IsDBNull(8) Then
lblResolvedBy.Text = drLeadFeedback.Item("Resolved_By")
End If
If Not drLeadFeedback.IsDBNull(7) Then
lblResolutionDate.Text =
drLeadFeedback.Item("Resolution_Date")
End If
If Not drLeadFeedback.IsDBNull(9) Then
lblFinalResolution.Text =
drLeadFeedback.Item("Final_Resolution")
End If
If Not drLeadFeedback.IsDBNull(6) Then
lblDetail.Text = drLeadFeedback.Item("Detail")
End If
If Not drLeadFeedback.IsDBNull(5) Then
lblSubject.Text =
drLeadFeedback.Item("Lead_Feedback_Subject")
End If
If Not drLeadFeedback.IsDBNull(20) Then
lblCategory.Text =
drLeadFeedback.Item("Lead_Feedback_Category")
End If
End While

Nov 19 '05 #1

Subscribe Post Reply

3571

William F. Robertson, Jr.

There are several ways to skin this cat; here is one:

lblLeadName.Text = GetItem( drLeadFeedback, "Lead_Name" );

Shared Function GetItem( reader As IDataReader, field As String ) As String
If reader.Item( field ) <> DBNull.Value Then
Return reader.Item( field ).ToString()
End If
Return String.Empty
End Function

HTH,

bill
"Paul D. Fox" <pd*****@rcn.com> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...

I have this code snippet below where I'm loading all the labels based upon
the retrieved record from the DataReader. Unfortunatley, some of the values are null, so I can't explicitly set the labels without checking first. Is
there a better method to write this than using a bunch of "If" statements?
Could it be a function? if so, how would I write it?

'Read the single record
While drLeadFeedback.Read()

If Not drLeadFeedback.IsDBNull(1) Then
lblLeadName.Text = drLeadFeedback.Item("Lead_Name")
End If
If Not drLeadFeedback.IsDBNull(2) Then
lblLeadEmail.Text = drLeadFeedback.Item("Lead_Email")
End If
If Not drLeadFeedback.IsDBNull(3) Then
lblLeadPhone.Text = drLeadFeedback.Item("Lead_Phone")
End If
If Not drLeadFeedback.IsDBNull(4) Then
lblUnderwriter.Text = drLeadFeedback.Item("Underwriter") End If
If Not drLeadFeedback.IsDBNull(10) Then
lblCreateDate.Text = drLeadFeedback.Item("Create_Date") End If
If Not drLeadFeedback.IsDBNull(18) Then
lblStatus.Text =
drLeadFeedback.Item("Lead_Feedback_Status")
End If
If Not drLeadFeedback.IsDBNull(19) Then
lblAssignedTo.Text = drLeadFeedback.Item("Assigned_To") End If
If Not drLeadFeedback.IsDBNull(11) Then
lblDealName.Text = drLeadFeedback.Item("Deal_Name")
End If
If Not drLeadFeedback.IsDBNull(12) Then
lblClient.Text = drLeadFeedback.Item("Client_Name")
End If
If Not drLeadFeedback.IsDBNull(13) Then
lblCSMName.Text = drLeadFeedback.Item("CSM_Name")
End If
If Not drLeadFeedback.IsDBNull(14) Then
lblSpeedRating.Text =
drLeadFeedback.Item("Speed_Rating")
End If
If Not drLeadFeedback.IsDBNull(15) Then
lblAccuracyRating.Text =
drLeadFeedback.Item("Accuracy_Rating")
End If
If Not drLeadFeedback.IsDBNull(16) Then
lblKnowledgeRating.Text =
drLeadFeedback.Item("Knowledge_Rating")
End If
If Not drLeadFeedback.IsDBNull(17) Then
lblWorkEffortRating.Text =
drLeadFeedback.Item("Work_Effort_Rating")
End If
If Not drLeadFeedback.IsDBNull(8) Then
lblResolvedBy.Text = drLeadFeedback.Item("Resolved_By") End If
If Not drLeadFeedback.IsDBNull(7) Then
lblResolutionDate.Text =
drLeadFeedback.Item("Resolution_Date")
End If
If Not drLeadFeedback.IsDBNull(9) Then
lblFinalResolution.Text =
drLeadFeedback.Item("Final_Resolution")
End If
If Not drLeadFeedback.IsDBNull(6) Then
lblDetail.Text = drLeadFeedback.Item("Detail")
End If
If Not drLeadFeedback.IsDBNull(5) Then
lblSubject.Text =
drLeadFeedback.Item("Lead_Feedback_Subject")
End If
If Not drLeadFeedback.IsDBNull(20) Then
lblCategory.Text =
drLeadFeedback.Item("Lead_Feedback_Category")
End If
End While

Nov 19 '05 #2

Bruce Barker

refactoring 101, would say to build a function:

lblLeadName.Text =
DRHelper.GetTextValue(drLeadFeedback,"Lead_Name")

where (air code)

public class DRHelper
{
static public string GetTextValue (IDataReader dr, string colName)
{
int colnum = dr.GetOrdinal(colName);
if (dr.IsDBNull(colnum))
return "";
else
return dr[colnum].ToString();
}
}

-- bruce (sqlwork.com)
"Paul D. Fox" <pd*****@rcn.com> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...

I have this code snippet below where I'm loading all the labels based upon
the retrieved record from the DataReader. Unfortunatley, some of the
values are null, so I can't explicitly set the labels without checking
first. Is there a better method to write this than using a bunch of "If"
statements? Could it be a function? if so, how would I write it?

'Read the single record
While drLeadFeedback.Read()

If Not drLeadFeedback.IsDBNull(1) Then
lblLeadName.Text = drLeadFeedback.Item("Lead_Name")
End If
If Not drLeadFeedback.IsDBNull(2) Then
lblLeadEmail.Text = drLeadFeedback.Item("Lead_Email")
End If
If Not drLeadFeedback.IsDBNull(3) Then
lblLeadPhone.Text = drLeadFeedback.Item("Lead_Phone")
End If
If Not drLeadFeedback.IsDBNull(4) Then
lblUnderwriter.Text =
drLeadFeedback.Item("Underwriter")
End If
If Not drLeadFeedback.IsDBNull(10) Then
lblCreateDate.Text = drLeadFeedback.Item("Create_Date")
End If
If Not drLeadFeedback.IsDBNull(18) Then
lblStatus.Text =
drLeadFeedback.Item("Lead_Feedback_Status")
End If
If Not drLeadFeedback.IsDBNull(19) Then
lblAssignedTo.Text = drLeadFeedback.Item("Assigned_To")
End If
If Not drLeadFeedback.IsDBNull(11) Then
lblDealName.Text = drLeadFeedback.Item("Deal_Name")
End If
If Not drLeadFeedback.IsDBNull(12) Then
lblClient.Text = drLeadFeedback.Item("Client_Name")
End If
If Not drLeadFeedback.IsDBNull(13) Then
lblCSMName.Text = drLeadFeedback.Item("CSM_Name")
End If
If Not drLeadFeedback.IsDBNull(14) Then
lblSpeedRating.Text =
drLeadFeedback.Item("Speed_Rating")
End If
If Not drLeadFeedback.IsDBNull(15) Then
lblAccuracyRating.Text =
drLeadFeedback.Item("Accuracy_Rating")
End If
If Not drLeadFeedback.IsDBNull(16) Then
lblKnowledgeRating.Text =
drLeadFeedback.Item("Knowledge_Rating")
End If
If Not drLeadFeedback.IsDBNull(17) Then
lblWorkEffortRating.Text =
drLeadFeedback.Item("Work_Effort_Rating")
End If
If Not drLeadFeedback.IsDBNull(8) Then
lblResolvedBy.Text = drLeadFeedback.Item("Resolved_By")
End If
If Not drLeadFeedback.IsDBNull(7) Then
lblResolutionDate.Text =
drLeadFeedback.Item("Resolution_Date")
End If
If Not drLeadFeedback.IsDBNull(9) Then
lblFinalResolution.Text =
drLeadFeedback.Item("Final_Resolution")
End If
If Not drLeadFeedback.IsDBNull(6) Then
lblDetail.Text = drLeadFeedback.Item("Detail")
End If
If Not drLeadFeedback.IsDBNull(5) Then
lblSubject.Text =
drLeadFeedback.Item("Lead_Feedback_Subject")
End If
If Not drLeadFeedback.IsDBNull(20) Then
lblCategory.Text =
drLeadFeedback.Item("Lead_Feedback_Category")
End If
End While

Nov 19 '05 #3

Paul D. Fox

Thanks Guy's, that worked much better...

Paul
"William F. Robertson, Jr." <th****@nameht.org> wrote in message
news:e%****************@TK2MSFTNGP09.phx.gbl...

There are several ways to skin this cat; here is one:

lblLeadName.Text = GetItem( drLeadFeedback, "Lead_Name" );

Shared Function GetItem( reader As IDataReader, field As String ) As
String
If reader.Item( field ) <> DBNull.Value Then
Return reader.Item( field ).ToString()
End If
Return String.Empty
End Function

HTH,

bill
"Paul D. Fox" <pd*****@rcn.com> wrote in message
news:uj**************@TK2MSFTNGP12.phx.gbl...
I have this code snippet below where I'm loading all the labels based
upon
the retrieved record from the DataReader. Unfortunatley, some of the

values
are null, so I can't explicitly set the labels without checking first.
Is
there a better method to write this than using a bunch of "If"
statements?
Could it be a function? if so, how would I write it?

'Read the single record
While drLeadFeedback.Read()

If Not drLeadFeedback.IsDBNull(1) Then
lblLeadName.Text = drLeadFeedback.Item("Lead_Name")
End If
If Not drLeadFeedback.IsDBNull(2) Then
lblLeadEmail.Text = drLeadFeedback.Item("Lead_Email")
End If
If Not drLeadFeedback.IsDBNull(3) Then
lblLeadPhone.Text = drLeadFeedback.Item("Lead_Phone")
End If
If Not drLeadFeedback.IsDBNull(4) Then
lblUnderwriter.Text =

drLeadFeedback.Item("Underwriter")
End If
If Not drLeadFeedback.IsDBNull(10) Then
lblCreateDate.Text =

drLeadFeedback.Item("Create_Date")
End If
If Not drLeadFeedback.IsDBNull(18) Then
lblStatus.Text =
drLeadFeedback.Item("Lead_Feedback_Status")
End If
If Not drLeadFeedback.IsDBNull(19) Then
lblAssignedTo.Text =

drLeadFeedback.Item("Assigned_To")
End If
If Not drLeadFeedback.IsDBNull(11) Then
lblDealName.Text = drLeadFeedback.Item("Deal_Name")
End If
If Not drLeadFeedback.IsDBNull(12) Then
lblClient.Text = drLeadFeedback.Item("Client_Name")
End If
If Not drLeadFeedback.IsDBNull(13) Then
lblCSMName.Text = drLeadFeedback.Item("CSM_Name")
End If
If Not drLeadFeedback.IsDBNull(14) Then
lblSpeedRating.Text =
drLeadFeedback.Item("Speed_Rating")
End If
If Not drLeadFeedback.IsDBNull(15) Then
lblAccuracyRating.Text =
drLeadFeedback.Item("Accuracy_Rating")
End If
If Not drLeadFeedback.IsDBNull(16) Then
lblKnowledgeRating.Text =
drLeadFeedback.Item("Knowledge_Rating")
End If
If Not drLeadFeedback.IsDBNull(17) Then
lblWorkEffortRating.Text =
drLeadFeedback.Item("Work_Effort_Rating")
End If
If Not drLeadFeedback.IsDBNull(8) Then
lblResolvedBy.Text =

drLeadFeedback.Item("Resolved_By")
End If
If Not drLeadFeedback.IsDBNull(7) Then
lblResolutionDate.Text =
drLeadFeedback.Item("Resolution_Date")
End If
If Not drLeadFeedback.IsDBNull(9) Then
lblFinalResolution.Text =
drLeadFeedback.Item("Final_Resolution")
End If
If Not drLeadFeedback.IsDBNull(6) Then
lblDetail.Text = drLeadFeedback.Item("Detail")
End If
If Not drLeadFeedback.IsDBNull(5) Then
lblSubject.Text =
drLeadFeedback.Item("Lead_Feedback_Subject")
End If
If Not drLeadFeedback.IsDBNull(20) Then
lblCategory.Text =
drLeadFeedback.Item("Lead_Feedback_Category")
End If
End While

Nov 19 '05 #4

by: Wolfgang Kaml | last post by:

Hello All, I have been working on this for almost a week now and I haven't anything up my sleeves anymore that I could test in addition or change.... Since I am not sure, if this is a Windows...

ASP / Active Server Pages

ADODB/IsDBNull Problem

by: Jim | last post by:

I am having a problem with the IsDBNull function. When it checks what type of value is contained in the parameter that is passed to it, it does not successfully determine that it has a DBNull...

.NET Framework

Check if item is selected in listbox

by: martin | last post by:

Hi, I am looking for a method to check if an item (any item) is selected in a particular listbox before deleteing it from my database, however I keep getting errors, The code I have is ...

ASP.NET

Better Code than This......

by: Larry Smith | last post by:

If Not dtrMyDataReader.IsDBNull(dtrMyDataReader.GetOrdinal("Customer")) Then strCustomer = dtrMyDataReader("Customer").ToString() End If Where "Customer" is a string data type.

ASP.NET

IsDBNull

by: sck10 | last post by:

Hello (converting from vb to c#), Is there a way to test the value of null of a particular field of a dataset from SQL Server? I am going through a dataset and trying to determine if the value...

ASP.NET

Generic helper function to check for dbnull value for any type?

by: John Dalberg | last post by:

..NET 2.0. Is there a generic single function to check for a dbnull regardless of data type? John Dalberg

C# / C Sharp

Make Address Look Better

by: pvong | last post by:

VB.Net I'm pulling Data from a DB and creating a letter by filling the Labels. It works perfectly, but if Addr2 and Addr3 are blank, it leaves a Blank space in that area. Is there a way to...

ASP.NET

DBNull check for ADODB.recordset

by: =?Utf-8?B?TWlrZSBPS0M=?= | last post by:

VB 2005 All developers face this issue; so I'm sure Microsoft was a solution for it. What is the built in method to check DBNull in a recordset field? NOT a typed dataset. I don't want to...

.NET Framework

check if field is empty/nothing/null????

by: cj2 | last post by:

if myodbcreader.hasrows then if myodbcreader("code").trim = "" or myodbcreader("reas").trim = "aVAL" then do something endif endif Is this the appropriate way to test if the code field is...

Visual Basic .NET

Easy Steps to Fix "Canon Printer Won't Connect to WiFi Network"

by: taylorcarr | last post by:

A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...

General

How to turn on java script in a villaon keypad mobile phone

by: Charles Arthur | last post by:

How do i turn on java script on a villaon, callus and itel keypad mobile phone

Java

Basic Javascript concepts

by: aa123db | last post by:

Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...

Javascript

Batch import of multiple excel files into the database

by: ryjfgjl | last post by:

If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...

Data Management

Is that possible of reading the .csv file in column wise and the column have different lengths ?

by: Sonnysonu | last post by:

This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

C / C++

How to build RAID in BIOS?

by: Hystou | last post by:

There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

Computer Hardware

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...

General

Changing the language in Windows 10

by: Hystou | last post by:

Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

Windows Server

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

Online Marketing

A better way to check for IsDBNull ?

Similar topics