none
Access to listview error.

    Question

  • I have a prog that works fine until I need a to display DB into a Listview.

     Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
            Dim connection As OleDbConnection
            Dim command As OleDbCommand
            Dim data_reader As OleDbDataReader
            setupListView()
            connection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=F:\All VB Stuff\All Access DB\DB7.mdb ")
            connection.Open()
            command = New OleDbCommand("SELECT * FROM Table1", connection)
            data_reader = command.ExecuteReader
            ListView1.GridLines = True
            If data_reader.HasRows Then
                While data_reader.Read
                    Dim newitem As New ListViewItem()
                    newitem.Text = data_reader.GetValue(0)
                    newitem.SubItems.Add(data_reader.GetValue(1))
                    newitem.SubItems.Add(data_reader.GetValue(2))
                    'newitem.SubItems.Add(data_reader.GetValue(3))
                    'newitem.SubItems.Add(data_reader.GetValue(4))
                    ListView1.Items.Add(newitem)
                End While
            End If
        End Sub

    Then I get this error message.

    System.Reflection.AmbiguousMatchException: 'Overload resolution failed because no Public 'Add' can be called with these arguments:
        'Public Function Add(item As System.Windows.Forms.ListViewItem.ListViewSubItem) As System.Windows.Forms.ListViewItem.ListViewSubItem':
            Argument matching parameter 'item' cannot convert from 'DBNull' to 'ListViewSubItem'.
        'Public Function Add(text As String) As System.Windows.Forms.ListViewItem.ListViewSubItem':
            Argument matching parameter 'text' cannot convert from 'DBNull' to 'String'.'

    Please can anyone point out my error,

    Thanks.


    Wednesday, January 03, 2018 10:09 PM

Answers

  •  That would be partially because you need to convert the data Object to a String type.  Turn on Option Strict and you will be warned of that before you can even run/debug the application.  Notice the use of the .ToString method in the example below...

            newitem.Text = data_reader.GetValue(0).ToString
            newitem.SubItems.Add(data_reader.GetValue(1).ToString)
            newitem.SubItems.Add(data_reader.GetValue(2).ToString)


     However,  the exception also says that it can not convert 'DBNull' to a 'ListViewSubItem' which indicates you have a Null value in the database.  This means you would need to test each Object to see if it is Null before trying to use it.  Try it like this and see what happens...

            If data_reader.HasRows Then
                While data_reader.Read
    
                    Dim Obj As Object = data_reader.GetValue(0)
                    If Obj IsNot Nothing Then
                        Dim newitem As New ListViewItem(Obj.ToString)
    
                        Obj = data_reader.GetValue(1)
                        newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                        Obj = data_reader.GetValue(2)
                        newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                        ListView1.Items.Add(newitem)
                    End If
    
                End While
            End If
     

     This will add the row into the ListView even if the first value (0) is nothing,  the above example will not.

            If data_reader.HasRows Then
                While data_reader.Read
    
                    Dim Obj As Object = data_reader.GetValue(0)
                    Dim newitem As New ListViewItem(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                    Obj = data_reader.GetValue(1)
                    newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                    Obj = data_reader.GetValue(2)
                    newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                    ListView1.Items.Add(newitem)
    
                End While
            End If
    


    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Wednesday, January 03, 2018 11:46 PM
    • Marked as answer by mikki18 Wednesday, January 03, 2018 11:57 PM
    Wednesday, January 03, 2018 11:38 PM

All replies

  •  That would be partially because you need to convert the data Object to a String type.  Turn on Option Strict and you will be warned of that before you can even run/debug the application.  Notice the use of the .ToString method in the example below...

            newitem.Text = data_reader.GetValue(0).ToString
            newitem.SubItems.Add(data_reader.GetValue(1).ToString)
            newitem.SubItems.Add(data_reader.GetValue(2).ToString)


     However,  the exception also says that it can not convert 'DBNull' to a 'ListViewSubItem' which indicates you have a Null value in the database.  This means you would need to test each Object to see if it is Null before trying to use it.  Try it like this and see what happens...

            If data_reader.HasRows Then
                While data_reader.Read
    
                    Dim Obj As Object = data_reader.GetValue(0)
                    If Obj IsNot Nothing Then
                        Dim newitem As New ListViewItem(Obj.ToString)
    
                        Obj = data_reader.GetValue(1)
                        newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                        Obj = data_reader.GetValue(2)
                        newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                        ListView1.Items.Add(newitem)
                    End If
    
                End While
            End If
     

     This will add the row into the ListView even if the first value (0) is nothing,  the above example will not.

            If data_reader.HasRows Then
                While data_reader.Read
    
                    Dim Obj As Object = data_reader.GetValue(0)
                    Dim newitem As New ListViewItem(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                    Obj = data_reader.GetValue(1)
                    newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                    Obj = data_reader.GetValue(2)
                    newitem.SubItems.Add(If(Obj IsNot Nothing, Obj.ToString, ""))
    
                    ListView1.Items.Add(newitem)
    
                End While
            End If
    


    If you say it can`t be done then i`ll try it

    • Edited by IronRazerz Wednesday, January 03, 2018 11:46 PM
    • Marked as answer by mikki18 Wednesday, January 03, 2018 11:57 PM
    Wednesday, January 03, 2018 11:38 PM
  • Thanks a lot IronRazerz that got it going fine.
    Wednesday, January 03, 2018 11:59 PM