Conversion from type 'DBNull' to type 'String' is not valid.

Question

1.00/5 (1 vote)

See more:

hai there guys, i need some help here. My friend ask me to look into his code because it got some errors. Basically what this part of the code does is when I click on the button, it automatically fetch the data from the Access table and insert it into a listview. Previously it is working, but when i play around with the data in the table, it suddenly came up with this error. Conversion from type 'DBNull' to type 'String' is not valid. I'm still fresh in VB.net so I don't know what's the problem. Here is the code segment,

VB

Private Sub btnShow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnShow.Click
        Dim li As ListViewItem
        If btnShow.Text = "Show All" Then
            LstCustomer.Items.Clear()
            Com.CommandText = "Select * from tblCustomer"
            Dr = Com.ExecuteReader
            Do While Dr.Read = True
                li = LstCustomer.Items.Add(Dr(0), 0)
                With li.SubItems
                    .Add(1).Text = Dr(1)
                    .Add(2).Text = Dr(2) 'the error is here
                    .Add(3).Text = Dr(3) 
                    .Add(3).Text = Dr(3)
                    .Add(4).Text = Dr(4)
                    .Add(5).Text = Dr(5)
                    .Add(6).Text = Dr(6)
                    .Add(7).Text = Dr(7)
                    .Add(8).Text = Dr(8)
                    .Add(9).Text = Dr(9)
                    .Add(10).Text = Dr(10)
                    'MsgBox(IsDBNull(Dr(9)))
                End With
            Loop
            Dr.Close()
            btnShow.Text = "Unshow"
        Else
            LstCustomer.Items.Clear()
            btnShow.Text = "Show All"
        End If
    End Sub

any help would be greatly appreciated~ :)

Posted 7-Dec-12 2:31am

nizam15

Updated 7-Mar-17 14:24pm

Add a Solution

3 solutions

Solution 1

It just means that the item has no value, so you need to test for DBNull before trying to add it to the ListView.

Posted 7-Dec-12 2:40am

Richard MacCutchan

Comments

nizam15 7-Dec-12 9:08am

test for DBNull??meaning??sorry i'm not that familiar with VB.

Richard MacCutchan 7-Dec-12 9:38am

Then why are you trying to fix this code? Go back to your friend and show him my comments.

nizam15 7-Dec-12 9:41am

i've showed him your comment and even he doesn't know how.

Richard MacCutchan 7-Dec-12 10:03am

How did he know to write the code that you put in the question? It's a simple if statement.

Richard MacCutchan 7-Dec-12 10:05am

He even has the code in the comment towards the end of the snippet posted in the question.

nizam15 7-Dec-12 10:11am

that's the mysteries you and i both want to know. But i think i already get enough information here. Thanks! :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Accepted Answer · 2012-12-07T04:05:00

As an extension of the answers by Richard and Kschuler, the easy way to do this is something like this.

C#

With li.SubItems
   .Add(1).Text = Dr(1)
   .Add(2).Text = If(IsDBNull(Dr(2), string.Empty, Dr(2))
   .Add(3).Text = Dr(3) 
   .Add(3).Text = Dr(3)
   .Add(4).Text = Dr(4)
   .Add(5).Text = Dr(5)
   .Add(6).Text = Dr(6)
   .Add(7).Text = Dr(7)
   .Add(8).Text = Dr(8)
   .Add(9).Text = Dr(9)
   .Add(10).Text = Dr(10)
   'MsgBox(IsDBNull(Dr(9)))
End With

Kschuler · Accepted Answer · 2012-12-07T04:00:00

Richard MacCutchan is correct. You are pulling a record from the database and for some reason there isn't data in one or more of the fields. You need to check for DBNull.Value before adding it to the ListView. Here is the MSDN documentation about DBNull.Value. It includes a sample of a function that checks for null and returns nothing instead of the DBNull.Value.

http://msdn.microsoft.com/en-us/library/system.dbnull.value.aspx[^]

You can't store a DBNull.Value in a listview. You have to convert it to something else manually.

Conversion from type 'DBNull' to type 'String' is not valid.

3 solutions

Solution 3

Solution 1

Solution 2

Add your solution here

Preview 0