none
Conversion from type 'DBNull' to type 'String' is not valid RRS feed

  • Question

  • I have tried everything I could to avoid this error to no avail.

    If Not DBNull.Value.Equals(rdr_Import("Color_Style")) Then

    m_Columns_Building_Import =  "Something"
    Else
    m_Columns_Building_Import =  "Nothing"
    End If

    If Not IsDBNull(rdr_Import("Color_Style")) Then
    m_Columns_Building_Import =  "Something"
    Else
    m_Columns_Building_Import =  "Nothing"
    End If

    If IsDBNull(rdr_Import("Color_Style")) Then
    m_Columns_Building_Import =  "Nothing"
    Else
    m_Columns_Building_Import =  "Something"
    End If

    Any ideas?

    ADawn


    ADawn


    Tuesday, December 4, 2018 4:30 PM

All replies

  • I have tried everything I could to avoid this error to no avail.

    If Not DBNull.Value.Equals(rdr_Import("Color_Style")) Then

    m_Columns_Building_Import =  "Something"
    Else
    m_Columns_Building_Import =  "Nothing"
    End If

    If Not IsDBNull(rdr_Import("Color_Style")) Then
    m_Columns_Building_Import =  "Something"
    Else
    m_Columns_Building_Import =  "Nothing"
    End If

    If IsDBNull(rdr_Import("Color_Style")) Then
    m_Columns_Building_Import =  "Nothing"
    Else
    m_Columns_Building_Import =  "Something"
    End If

    Any ideas?

    ADawn


    ADawn


    Hi,

    This error is noisy but try to use this function

     Public Function CheckForNull(ByVal fieldValue As String) As String
    
            If fieldValue.Equals(DBNull.Value) Then Return "" Else
            If fieldValue = "N/A" Then
                Return "value for N/A"
            Else
                Return ""
            End If
    End Function

    when you saving to the database ....... 

    hope this helps you


    Regards From Amr_Aly

    Tuesday, December 4, 2018 5:02 PM
  • Thanks for your reply, but I don't understand your code.

    ADawn

    Tuesday, December 4, 2018 5:25 PM
  • What is rdr_Import("Color_Style")

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, December 4, 2018 5:27 PM
    Moderator
  • rdr_Import = cmd_Import.ExecuteReader()

    ADawn


    Tuesday, December 4, 2018 5:33 PM
  • Try this too:

       If rdr_Import.IsDBNull("Color_Style") Then

          m_Columns_Building_Import =  "Nothing"

       Else

          m_Columns_Building_Import =  "Something"

       End If

    Or

       m_Columns_Building_Import = If( rdr_Import.IsDBNull("Color_Style"), "Nothing", "Something" )

     


    Tuesday, December 4, 2018 5:35 PM
  • A couple of things:

    Trying to read an Excel (XLSX) file using:

    Dim cn_Import As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + sFile_Path + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES'")
    Dim cmd_Import As System.Data.OleDb.OleDbCommand = Nothing
    Dim rdr_Import As System.Data.OleDb.OleDbDataReader = Nothing

    When I tried to read a field that is NULL I get the error and I can't figure out how to avoid the error.


    ADawn

    Tuesday, December 4, 2018 5:49 PM
  • Viorel

    Neither work. Same error message.


    ADawn

    Tuesday, December 4, 2018 6:07 PM
  • Which line generates this error? Show some of you current code.
    Tuesday, December 4, 2018 6:09 PM
  • If have a MSDN code sample which shows various ways to traverse a DataReader where in this example I get one row but by adapting to read all rows this will work.

    Public Function GetCustomerWithNullCheckes(ByVal pIdentifier As Integer) As Customer 
        mHasException = False 
    
        Dim customer As Customer = Nothing 
    
        Dim selectStatement As String = "SELECT FirstName,LastName,Address" & 
                                        ",City,State,ZipCode,JoinDate,Pin,Balance " & 
                                        "FROM dbo.Customer WHERE Id = @Id" 
    
        Try 
            Using cn = New SqlConnection(ConnectionString) 
                Using cmd = New SqlCommand() With {.Connection = cn, .CommandText = selectStatement} 
                    cmd.Parameters.AddWithValue("@Id", pIdentifier) 
                    cn.Open() 
    
                    Dim reader As SqlDataReader = cmd.ExecuteReader 
    
                    If reader.HasRows Then 
    
                        reader.Read() 
    
                        customer = CustomerBuilder(reader, pIdentifier) 
    
                    End If 
                End Using 
            End Using 
        Catch ex As Exception 
            mHasException = True 
            mLastException = ex 
        End Try 
    
        Return customer 
    
    End Function

    .

    Private Function CustomerBuilder( 
        ByVal pReader As SqlDataReader, 
        Optional ByVal pIdentifier As Integer = 0) As Customer 
     
        Dim Identifier As Integer = 0 
     
        If pIdentifier > 0 Then 
            Identifier = pIdentifier 
        Else 
            Identifier = Integer.Parse(pReader("id").ToString()) 
        End If 
     
        Return New Customer With 
    { 
        .Id = Identifier, 
        .FirstName = If(TypeOf pReader("FirstName") Is DBNull, Nothing, pReader("FirstName").ToString()), 
        .LastName = If(TypeOf pReader("Lastname") Is DBNull, Nothing, pReader("Lastname").ToString()), 
        .Address = If(TypeOf pReader("Address") Is DBNull, Nothing, pReader("Address").ToString()), 
        .City = If(TypeOf pReader("City") Is DBNull, Nothing, pReader("City").ToString()), 
        .State = If(TypeOf pReader("State") Is DBNull, Nothing, pReader("State").ToString()), 
        .ZipCode = If(TypeOf pReader("Zipcode") Is DBNull, Nothing, pReader("Zipcode").ToString()), 
        .JoinDate = If(TypeOf pReader("JoinDate") Is DBNull, Nothing, CDate(pReader("JoinDate").ToString)), 
        .Pin = If(TypeOf pReader("Pin") Is DBNull, Nothing, CInt(pReader("Pin").ToString)), 
        .Balance = If(TypeOf pReader("Balance") Is DBNull, Nothing, CDbl(pReader("Balance").ToString)) 
    } 
     
    End Function


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, December 4, 2018 6:13 PM
    Moderator
  • Here's my actual code:

    rdr_Import is the Reader

    rdr_Import(0)) 0 is the first position on an array which equals "Manufacturer"

    If Not DBNull.Value.Equals(rdr_Import(0)) Then

    m_Columns_Building_Import = ") VALUES('" & CStr(rdr_Import(m_rArray_Import(m_iArray_Import)))
    Else
    m_Columns_Building_Import = ") VALUES('" & CStr("")
    End If

    The DBNull is never caught so when it tries to set m_Columns_Building_Import that's where it fails.


    ADawn

    Tuesday, December 4, 2018 6:15 PM
  • Seems that rdr_Import(m_rArray_Import(m_iArray_Import)) is null. Check the components of this expression.

    Maybe you should write this:

       If Not rdr_Import.IsDBNull(m_rArray_Import(m_iArray_Import)) Then

          m_Columns_Building_Import = ") VALUES('" & rdr_Import(m_rArray_Import(m_iArray_Import))

       Else

          m_Columns_Building_Import = ") VALUES('"

       End If


    Tuesday, December 4, 2018 6:26 PM
  • Thanks for your reply, but I don't understand your code.

    ADawn

    It's for a "Null" Problem ...... Your database Is MS-Access right you will use this code as follow

     Private Sub Save_Click(sender As Object, e As EventArgs) Handles Save.Click
    
    
                CheckForNull("")
                - Your save code here -
    End sub

    To prevent this error ....... Or there is another solution to enforce user enter a text or numbers to your application text boxes or combo boxes by using something like that 

     If Len(Trim(txtCustomerNo.Text)) = 0 Then
                MessageBox.Show("Select Customer ID", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtCustomerNo.Focus()
                Exit Sub
            End If
            If Len(Trim(txtCustomerName.Text)) = 0 Then
                MessageBox.Show("Select Customer Name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Button1.Focus()
                Exit Sub
            End If
    
            If Val(txtTaxPer.Text) = 0 Then
                MessageBox.Show("Please enter tax percentage", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtTaxPer.Focus()
                Exit Sub
            End If
            If Val(txtTaxAmt.Text) = 0 Then
                MessageBox.Show("Please enter tax amount", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtTaxAmt.Focus()
                Exit Sub
            End If
            If Val(txtTotalPayment.Text) = 0 Then
                MessageBox.Show("Please enter total payment", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                txtTotalPayment.Focus()
                Exit Sub
            End If
            If ListView1.Items.Count = 0 Then
                MessageBox.Show("sorry no product added", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Warning)
                Exit Sub
            End If

    But i think this method succeed with apps. with not many controllers in the form but if you have many textboxes i think this is impossible to do such method ..... 

    hope it helps ............ 

     

    Regards From Amr_Aly

    Tuesday, December 4, 2018 6:26 PM
  • Use this:

      Public Function DBNullEmptyToNothing(ByRef Value As Object) As Object
        If Value Is System.DBNull.Value OrElse String.IsNullOrEmpty(Value) = True Then
          Return Nothing
        Else
          Return Value
        End If
      End Function

    Best Regard

    Xan To

    Wednesday, December 5, 2018 3:51 AM
  • Hi,

    Do you resolve the issue? If you resolve the issue,please mark the helpful as answer. it will be beneficial to other community.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 4, 2019 9:20 AM