Argument matching parameter 'text' cannot convert from 'DBNull' to 'String'

Question

Trying to import records from an Access database file and display the records in a listview (in Visual Basic). It works 50% of the time, but seems to crash stating:

    An unhandled exception of type 'System.Reflection.AmbiguousMatchException' occurred in Microsoft.VisualBasic.dll

    Additional information: Overload resolution failed because no Public 'Add' can be called with these arguments:

'Public Overrides Function Add(text As String) As System.Windows.Forms.ListViewItem':

    Argument matching parameter 'text' cannot convert from 'DBNull' to 'String'.

'Public Overrides Function Add(value As System.Windows.Forms.ListViewItem) As System.Windows.Forms.ListViewItem':

    Argument matching parameter 'value' cannot convert from 'DBNull' to 'ListViewItem'.

The code in question is the following:

    ds.Clear()
        LVResults.Items.Clear()
        con.ConnectionString = dbProvider & dbSource
        con.Open()                                          'Open connection to the database
        sqlstatement = "SELECT * FROM records WHERE checkoutdate is NULL"
        da = New OleDb.OleDbDataAdapter(sqlstatement, con)
        da.Fill(ds, "allmembers")                           'Fill the data adapter
        con.Close()
        Dim recordCount, x As Short
        recordCount = 0
        x = 0
        recordCount = ds.Tables("allmembers").Rows.Count
        With ds.Tables("allmembers")
            Do Until x = recordCount
                LVResults.Items.Add(.Rows(x).Item(0))
                LVResults.Items(x).SubItems.Add(.Rows(x).Item(1))
                LVResults.Items(x).SubItems.Add(.Rows(x).Item(2))
                LVResults.Items(x).SubItems.Add(.Rows(x).Item(3))
                LVResults.Items(x).SubItems.Add(.Rows(x).Item(4))
                x = x + 1
            Loop
        End With

Knowing me it's something really obvious but do appreciate the help :) The most annoying this is that it works sometimes, but others it'll throw up the error.

The data it imports are integers and strings btw.

ConnorL
– ConnorL

2013-12-07 16:20:56 +00:00
Commented Dec 7, 2013 at 16:20 — ConnorL
– ConnorL, Commented Dec 7, 2013 at 16:20

Chris Rolliston · Accepted Answer · 2013-12-07 16:39:10Z

1

The error message is pretty self-explanatory to be honest - some of the fields contain null values, which VB won't auto-convert to empty strings or zeros. One solution would be to edit the SQL statement to explicitly avoid returning nulls:

SELECT IIf(Surname Is Null, '', Surname), IIf(Forename Is Null, '', Forename),
  IIf(SomeIntField Is Null, 0, SomeIntField) FROM records WHERE CheckoutDate Is Null

answered Dec 7, 2013 at 16:39

Chris Rolliston

4,8181 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ConnorL Over a year ago

But none of the fields in the access database are empty, the only NULL field is checkoutdate.

Chris Rolliston Over a year ago

@user2366605 - you aren't returning checkoutdate however, you're filtering for where it isn't null. How are you determing none of the other columns have any null values?

Chris Rolliston Over a year ago

@user2366605 - also, are you saying you've tried my suggested approach and still get the error?

Collectives™ on Stack Overflow

Argument matching parameter 'text' cannot convert from 'DBNull' to 'String'

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related