CODEWITHSUNDEEP
sqlvb.netms-access
ConnorL
ConnorL

Reputation: 227

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

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.

Upvotes: 1

Views: 2000

Answers (1)

Chris Rolliston
Chris Rolliston

Reputation: 4808

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

Upvotes: 1

Related Questions