Convert DBNull to blank string in Access DB
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 5 of 5

Thread: Convert DBNull to blank string in Access DB

  1. #1
    Join Date
    May 2010
    Posts
    1

    Convert DBNull to blank string in Access DB

    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:

    Code:
      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
    Last edited by HanneSThEGreaT; May 19th, 2010 at 08:09 AM.

  2. #2
    Join Date
    Jan 2006
    Location
    Chicago, IL
    Posts
    14,948

    Re: Convert DBNull to blank string in Access DB

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

    Code:
            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:

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  3. #3
    Join Date
    Jan 2007
    Posts
    55

    Re: Convert DBNull to blank string in Access DB

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

  4. #4
    DataMiser is offline Super Moderator Power Poster
    Join Date
    Jul 2008
    Location
    WV
    Posts
    4,813

    Re: Convert DBNull to blank string in Access DB

    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.

    Code:
    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.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,090

    Re: Convert DBNull to blank string in Access DB

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center