Solved

vb.net DBNull to string error in listview

Posted on 2013-11-16
7
1,289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:Gregory Miller
ID: 39653238
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
ID: 39653564
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
ID: 39653609
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
Get Actionable Data from Your Monitoring Solution

Your communication platform is only as good as the relevance of the information you send. Ensure your alerts get to the right people every time with actionable responses. Create escalation rules that ensure everyone follows the process and nothing is left to chance.

 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 39653648
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:Gregory Miller
ID: 39653652
I do not see that you have an OnClick method created for the ListView control.
0
 

Author Closing Comment

by:peter-cooper
ID: 39654243
Thanks fernando
0
 
LVL 63

Expert Comment

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

Featured Post

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

717 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