none
DBNull Conversion Exception RRS feed

  • Question

  • I have this plug-in routine for an audit trail for a table that contains what some dimwits consider critical data.  

    All this routine does is dump all the data from specific columns from a specific table into a specific column in the audit table.  The routine is called when a table record is loaded to a form for display (showing what the data was at time of access by the user) and the called again when the user saves any changes they might have made to the record data (showing what the data is at the time the user leaves the form.

    There are several problems for me associated with this.  I really do not fully understand the use of CType(), I have read up on it and all I can do is scratch my head and just do whatever I am able to make work.  However, the primary problem is that I am getting the message below and am unable to load the column with the accumulated data from the input table.

    I understand the error message telling me that I am trying to convert null data to a string.  I also understand that some of the columns that are supplying the data are indeed null (they frequently have no data).  Additionally, if all the columns from the table supplying the data contain data then this routine will execute without failing.  What I have concluded from all of that is that the way I have this setup is completely wrong and that something about my use of CType() is blowing this routine out of the water.

    So can anyone explain to me what is wrong and what is required to make it work?  Please speak slowly, I appear to not be easily understanding things today.

            Public Sub AuditChangeRequest()
                If mnuMain.TblChangeMasterTableAdapter.FillByChangeID(mnuMain._MasterBase_1_1DataSet.tblChangeMaster, glbintChangeID) = Nothing Then
                    Dim strMessage As String = vbNewLine & "AUDIT FAILED!!!!" & vbNewLine & "Contact System Administrator."
                    Dim ErrorMessage As New nspMasterBaseOperations.ErrorMessage
                    MessageBox.Show(String.Format("{0}{1}", ErrorMessage.Error1003(glbstrErrorMessage), strMessage))
                    glbfrmWindowForm.Close()
                    glbfrmWindowForm2.Show()
                Else
                    mnuMain.TblAuditTableAdapter.Fill(mnuMain._MasterBase_1_1DataSet.tblAudit)
                    'Create Change Request record
                    Dim ChangeRow As _MasterBase_1_1DataSet.tblAuditRow
                    ChangeRow = mnuMain._MasterBase_1_1DataSet.tblAudit.NewtblAuditRow
                    ChangeRow.intChangeID = glbintChangeID
                    ChangeRow.intEmployeeID = glbintEmployeeID
                    ChangeRow.dteTime = CDate(CStr(DateAndTime.Now))
                    ChangeRow.chrBaseObject = glbstrObject
                    ChangeRow.chrChange = CStr("Change Request Changes:") & vbNewLine & CStr(CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView).Item("chrTitle")) &
                            " " & CStr(CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView).Item("chrRevision")) &
                            vbNewLine & CStr("Change: ") & CStr(CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView).Item("chrChangeMade")) &
                            vbNewLine & CStr("Reason: ") & CStr(CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView).Item("chrChangeReason")) &
                            vbNewLine & CStr("Result: ") & CStr(CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView).Item("chrChangeResult"))
                    mnuMain._MasterBase_1_1DataSet.tblAudit.Rows.Add(ChangeRow)
                    mnuMain.tblAuditBindingSource.EndEdit()
                    mnuMain.TblAuditTableAdapter.Update(mnuMain._MasterBase_1_1DataSet)
                End If
            End Sub



    gwboolean


    • Edited by gwboolean Friday, July 6, 2018 6:10 PM
    Friday, July 6, 2018 6:08 PM

Answers

  • You'll need to test each value to see if it is null before trying to insert the string.

    CType casts (changes/converts) one data type into another.  It is smart enough to handle a lot of different type conversions but this one (dbnull to string) is not handled on purpose (prevents data errors).

    I would suggest that you first perform the conversion of the bindingsource current object to a datarowview one time:

    Dim drv As DataRowView = CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView)

    This will simplify the rest of the code that needs access to the datarowview.

    Now you can test each necessary field to see if it is null before building the string:

    Dim drvTitle As String = String.Empty
    Dim drvRevision As String = String.Empty
    Dim drvChangeMade As String = String.Empty
    'etc... create variables for each field
    
    If Not IsDbNull(drv.Item("chrTitle")) Then
       drvTitle = drv.Item("chrTitle").ToString()
    End If
    ' repeat for each other field
    
    'finally, create concatenated string using string variables

    If the underlying datarow comes from a strongly-typed data table (a data table in a DataSet created in the designer or from a database using the IDE) then you can cast drv.Row into that table row:

    Dim theRow As TableNameTableRow = CType(drv.Row, TableNameTableRow)

    Where TableNameTableRow is the name of the strongly typed data table row.  Doing it this way, there will be an IsNull property for each nullable field.  This just gives you a different way to test for the null value.  It isn't strictly necessary though as you can use the IsDbNull() method to check the object returned from the datarowview.item property.

    -EDIT-

    Note that you might also want to set the initial value of the string variables to something like "[NULL]" instead of String.Empty, depending on how you want the null values to appear in the final string you are building.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"



    Friday, July 6, 2018 7:42 PM
    Moderator

All replies

  • You'll need to test each value to see if it is null before trying to insert the string.

    CType casts (changes/converts) one data type into another.  It is smart enough to handle a lot of different type conversions but this one (dbnull to string) is not handled on purpose (prevents data errors).

    I would suggest that you first perform the conversion of the bindingsource current object to a datarowview one time:

    Dim drv As DataRowView = CType(frmChangeRequest.tblChangeMasterBindingSource.Current, DataRowView)

    This will simplify the rest of the code that needs access to the datarowview.

    Now you can test each necessary field to see if it is null before building the string:

    Dim drvTitle As String = String.Empty
    Dim drvRevision As String = String.Empty
    Dim drvChangeMade As String = String.Empty
    'etc... create variables for each field
    
    If Not IsDbNull(drv.Item("chrTitle")) Then
       drvTitle = drv.Item("chrTitle").ToString()
    End If
    ' repeat for each other field
    
    'finally, create concatenated string using string variables

    If the underlying datarow comes from a strongly-typed data table (a data table in a DataSet created in the designer or from a database using the IDE) then you can cast drv.Row into that table row:

    Dim theRow As TableNameTableRow = CType(drv.Row, TableNameTableRow)

    Where TableNameTableRow is the name of the strongly typed data table row.  Doing it this way, there will be an IsNull property for each nullable field.  This just gives you a different way to test for the null value.  It isn't strictly necessary though as you can use the IsDbNull() method to check the object returned from the datarowview.item property.

    -EDIT-

    Note that you might also want to set the initial value of the string variables to something like "[NULL]" instead of String.Empty, depending on how you want the null values to appear in the final string you are building.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"



    Friday, July 6, 2018 7:42 PM
    Moderator
  • Oh man, that is so painful!!!!  Any one of the columns that goes into accumulated data column might not contain any data.  That means I have to set this up for each one of them.  

    I was hoping that all that would be required is go back to the dataset and change some property in the table adapter or go back to the database and change some property in the table.

    Are you sure that I am stuck with, what seems to me to be, such an archaic way to deal with this?


    gwboolean

    Friday, July 6, 2018 8:12 PM
  • Oh man, that is so painful!!!!  Any one of the columns that goes into accumulated data column might not contain any data.  That means I have to set this up for each one of them.  

    I was hoping that all that would be required is go back to the dataset and change some property in the table adapter or go back to the database and change some property in the table.

    Are you sure that I am stuck with, what seems to me to be, such an archaic way to deal with this?


    gwboolean

    If ALL of the fields were a string type (nvarchar etc) then you could configure the dataset to return empty strings on null values.  That might save you a few checks for any columns that are text types, but everything else will require the check for dbnull.  You could write a single loop that goes through every field and checks for null and returns an array of string output... that would be a little more concise even if it was converting fields you don't actually care about.

    -EDIT-

    Actually, looking at your field names again, maybe they are all text fields.  If that is the case, open the dataset designer for this dataset, select the field in the table and go to the properties grid.  Go to the NullValue property in the properties grid and set it to (Empty).  That might be an easier fix if you don't mind having empty strings in the output.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Friday, July 6, 2018 9:08 PM
    Moderator
  • OK, I got over the fact that I was not provided with a magic button to fix my problem and worked through it.  Here is what I ended up with:

            Public Sub TestColumn()
                If IsDBNull(drv.Item("chrTitle")) Then
                    drvTitle = drv.Item("chrTitle").ToString()
                Else
                    drvTitle = CStr(CType(mnuMain.tblChangeMasterBindingSource.Current, DataRowView).Item("chrTitle"))
                End If
                If IsDBNull(drv.Item("chrRevision")) Then
                    drvRevision = drv.Item("chrRevision").ToString()
                Else
                    drvRevision = CStr(CType(mnuMain.tblChangeMasterBindingSource.Current, DataRowView).Item("chrRevision"))
                End If
                If IsDBNull(drv.Item("chrChangeMade")) Then
                    drvChangeMade = drv.Item("chrChangeMade").ToString()
                Else
                    drvChangeMade = CStr(CType(mnuMain.tblChangeMasterBindingSource.Current, DataRowView).Item("chrChangeMade"))
                End If
                If IsDBNull(drv.Item("chrChangeReason")) Then
                    drvChangeReason = drv.Item("chrChangeReason").ToString()
                Else
                    drvChangeReason = CStr(CType(mnuMain.tblChangeMasterBindingSource.Current, DataRowView).Item("chrChangeReason"))
                End If
                If IsDBNull(drv.Item("chrChangeResult")) Then
                    drvChangeResult = drv.Item("chrChangeResult").ToString()
                Else
                    drvChangeResult = CStr(CType(mnuMain.tblChangeMasterBindingSource.Current, DataRowView).Item("chrChangeResult"))
                End If
                _AccumulatedData = "Change Request Data: " & drvTitle & " " & drvRevision & vbNewLine & "Changes Made: " &
                                   drvChangeMade & vbNewLine & "Reason for Changes: " & drvChangeReason & vbNewLine & "Results of Changes: " & drvChangeResult
            End Sub

    I have only tested it a little bit, but it looks like it gets the job done.

    Thanks Reed!


    gwboolean

    Friday, July 6, 2018 9:23 PM
  • What is the output when the value is null?

    It looks like both sides of the If-Then-Else return the same value, just with the ToString on the null part...

    Maybe ToString is all you needed on each item... (in which has I sincerely apologize for putting you through hoops)


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, July 6, 2018 9:33 PM
    Moderator
  • Actually, all of the fields involved are varchar.  In the dataset I have the Null Value property set to Empty for all of these fields.  The other options were Nothing or Throw exception.  It threw an exception anyway.

    Since the Dataset is not cooperating what you provided works very well.

    Thanks.


    gwboolean

    Friday, July 6, 2018 9:40 PM
  • Not fully understanding the process, that was what I thought was needed.

    I already knew that if one of those fields contained no data then a null exception would occur.  That being the case, I thought ToString was just to replace null.  Anyway, it works like a champ and without the magic button, I can't see any way through except by the hoops.


    gwboolean

    Friday, July 6, 2018 9:44 PM