We help IT Professionals succeed at work.

DBNull Value when Using SqlCEDataReader in VB.net

comsult
comsult asked
on
3,130 Views
Last Modified: 2013-11-26
In a VB.net application using a SQLCE database we are using a SqlCeDataReader to "read" values from the DB. The values are then sent to a webservice.

The problem occurs when we use an IF to check whether a specific DB column is null or not.  If it is Null, we get the following error :

The column at the specified ordinal (2) contains DBNull value which can not be represented as a built-in .NET system type. Use SqlTypes or IsDBNull property.

The following is our code snippet :

   While rdr.Read()
                item.ItemID = rdr.GetString(0)
                item.Title = rdr.GetString(1)

                If Not rdr.GetString(2) = "" Then
                    item.SubTitle = rdr.GetString(2)
                Else
                    item.SubTitle = deletedFields.Add(rdr.GetString(2))
                End If
            End While

How can the IF be written to remove the error ? Thank you.
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
               If Not rdr.GetString(2) = DBNull.Value Then

now, you might consider writing the SQL so you don't get a NULL value at alll...

Author

Commented:
Thank you for such a quick response.

Due to the nature of the webservice, there will be times when the majority of the data is "null". We did think of putting false for example when we had a null, but that negatively affects the end user experience.

The line you provided gives the following error :

Value of type System.DBNull can't be converted to String.

We tried adding .tostring but then it went back to the original error.
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
sorry...
               If Not rdr[2]= DBNull.Value Then
Top Expert 2007

Commented:
This should work:
if (rdr(2) != DBNull.Value)

Author

Commented:
Thanks, but it doesn't like that :

Operator 'Not' is not defined for type 'System.Data.SqlServerCe.SqlCeDataReader'.

Author

Commented:
Thanks but it doesn't like that one either.

On the != it is underlined with the error Identifier Expected.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Sorry, still getting Operator 'Not' is not defined for type 'System.Data.SqlServerCe.SqlCeDataReader'.

If I change the square brackets to round we then get :

Operator '=' is not defined for types 'Object' and 'System.DBNull'
Top Expert 2007
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2007

Commented:
It looks like a special function was created for this:
if isdbnull(rdr[2]) then
   handle the null
else
  handle non-null.

Author

Commented:
Thank you both, that gave me enough to get it.

The final line is as follows and I'll be splitting the points. Appreciate the help.

If (rdr(2) Is DBNull.Value) Then

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.