Convert DBNull to blank string in Access DB

[kckrns]

Hi everyone,

I'm a new member to this site, and I have been searching for a solution to this problem.. Although I have found variations in the problem, nothing has seemed to work for me. Just started programming last year.

I am trying to populate text boxes in a form based on information from an Access Database. Everything is working, except when there are empty field values; the program then crashes because a DBNull value is returned. I set up an if statement to check the index as the rows are read for DBNull values, the problem is How do I actually change the value in the field from DBNull to a blank string ("") ?? Everything I try gives me an error, most often saying something about setting up an object. The problem happens within my if statement of the do loop...I'm thinking maybe I need to set up parameters ? Or an actual edit session within a datarow variable? Sorry, I'm pretty lost on this one.

Here is a snippet of my code, any help our references would be much appreciated! Thanks so much:

  tableConn.Open()
        Dim vendorPopulation As String = "select * from TestProject"

        Dim oleDbCommand As OleDbCommand = New OleDbCommand(vendorPopulation, tableConn)
        Dim oleDbDataReader As OleDbDataReader = oleDbCommand.ExecuteReader()
      
        da = New OleDbDataAdapter(vendorPopulation, tableConn)
        da.Fill(rs, "Tests")
      
        cur_rowindex = cboTestProject.SelectedIndex()

        Dim count As Integer
        count = oleDbDataReader.FieldCount 'gets the total amount of fields
        Dim index As Integer = 0 'index for the "For" loop
        Dim ctr As Integer = 0
        Dim blank as string = ""      

        Do While oleDbDataReader.Read

                If ctr = cur_rowindex Then
                For index = 0 To count - 1
                    If oleDbDataReader.IsDBNull(index) = True Then
                        'This is the problem area!
                          blank = rs.Tables("Test").Rows(cur_rowindex).Item(index).ToString()
                    End If
                Next index

                txtProjID.Text = oleDbDataReader(0) 'could also make whatever is selected
                txtVenNo.Text = oleDbDataReader(1)
                              etc......
            End If
            ctr = ctr + 1
        Loop
        tableConn.Close()

Thanks again

[dglienna]

Please use code tags, like this:
Also, change the IF statement, and use.

        Dim count As Integer
        count = oleDbDataReader.FieldCount 'gets the total amount of fields
        Dim index As Integer = 0 'index for the "For" loop
        Dim ctr As Integer = 0
        Dim blank(count-1) as string      

        Do While oleDbDataReader.Read

                If ctr = cur_rowindex Then
                For index = 0 To count - 1
                    If oleDbDataReader.IsDBNull(index) = False Then
              blank(index) =rs.Tables("Test").Rows(cur_rowindex).Item(index).ToString()                      
                   Else
                          blank(index) = "" 
                    End If
                Next index
                txtProjID.Text = blank(0) 'could also make whatever is selected
                txtVenNo.Text = blank(1)
                              etc......
            End If
            ctr = ctr + 1
        Loop
        tableConn.Close()

You can test with something like this:

        MsgBox(String.Format("{0}; {1}; {2}; {3}; {4}", blank(0), blank(1), blank(2), blank(3), blank(4)))

[swaroopbhave]

Set Default value of field to ''(2 single quotes).this avoids dbnull even if no input is given

[DataMiser]

When you are reading data from a database or other source into a string and that data may be null you can get around the issues by simply appending an empty string to the data during the assignment.

MyVar =DataField &""

The code above does not alter a string in any way but will convert a null to an empty string. works well and is very fast.

[HanneSThEGreaT]

kckrns, please make use of [CODE] tags when posting code. It is explained here :

http://www.codeguru.com/forum/showthread.php?t=403073

Thank you for understanding,

Hannes