Solved

vb.net DBNull to string error in listview

Posted on 2013-11-16
7
1,181 Views
Last Modified: 2013-11-17
I am trying to correct an error that has appeared my listview: 'Cannot convert from DBNull to string'.

However, some of the fields in the db contain null values or are empty and this is causing vb to throw the error.

I would be grateful if someone could show me how to correct this or check for null or empty values. Many thanks

Sub filllistview()
        Try
            'creatconn()
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from Postings", cn)
            dr = cmd.ExecuteReader()
            While dr.Read()
                ListView1.Items.Add(dr(0).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(1))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(2))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(3))

            End While

        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        Finally
            dr.Close()
            cn.Close()
        End Try
    End Sub

Open in new window


Sub add()
        While dr.Read()
            txtname.Text = dr(0).ToString()
            txtfathername.Text = dr(1).ToString()
            txtaddress.Text = dr(2).ToString()
            txtemail.Text = dr(3).ToString()
        End While

    End Sub

Open in new window


Sub showcontectsinlistview()
        Try
            'creatconn()
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("select * from Postings where [Code]='" & ListView1.Text & "'", cn)
            dr = cmd.ExecuteReader()
            add()

        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        Finally
            dr.Close()
            cn.Close()
        End Try
    End Sub

Open in new window

0
Comment
Question by:peter-cooper
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
What line is giving you the error?

Just before that line, test for the DBNull value and just change it to an empty string or skip adding it to the listview altogether.

http://support.microsoft.com/kb/867504
0
 

Author Comment

by:peter-cooper
Comment Utility
It is in this section of code. I need a way to check for null or empty fields. Thanks

While dr.Read()
                ListView1.Items.Add(dr(0).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(1))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(2))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(3))

            End While

Open in new window

0
 

Author Comment

by:peter-cooper
Comment Utility
This is actually populating the listview with data.

Sub filllistview()
        Try
            'creatconn()
            cn.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from Postings", cn)
            dr = cmd.ExecuteReader()
            While dr.Read()

                ListView1.Items.Add(dr(0).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(1).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(2).ToString())
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(dr(3).ToString())

            End While

        Catch ex As Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        Finally
            dr.Close()
            cn.Close()
        End Try
    End Sub

Open in new window


But when I click on an entry it is not displaying the textboxes below.  What is the error. Thanks

Try
            While dr.Read()
                txtname.Text = dr(0)
                txtfathername.Text = dr(1)
                txtaddress.Text = dr(2)
                txtemail.Text = dr(3)
            End While
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

Open in new window

0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
Comment Utility
Hi Peter;

In those columns where a value could be a NULL you need to check it before using it as shown below in the code snippet in this case using the IIF function. In that function the first parameter is the boolean test, second part is what gets executed if it is true and the last is what gets executed if it is false. Note that I am using the word NULL if the column had a null in it, but in this case it can be any valid string.

ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(CStr(IIF(dr(1) IsNot DbNull.Value, dr(1), "NULL")))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(CStr(IIF(dr(2) IsNot DbNull.Value, dr(2), "NULL")))
                ListView1.Items(ListView1.Items.Count - 1).SubItems.Add(CStr(IIF(dr(3) IsNot DbNull.Value, dr(3), "NULL")))

Open in new window

0
 
LVL 11

Expert Comment

by:Technodweeb
Comment Utility
I do not see that you have an OnClick method created for the ListView control.
0
 

Author Closing Comment

by:peter-cooper
Comment Utility
Thanks fernando
0
 
LVL 62

Expert Comment

by:Fernando Soto
Comment Utility
Not a problem Peter, glad I was able to help.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now