Help with testing DBNull from a field.

26 Mar 2006 3:55 AM

Julian

I have the following code:

        Dim strQuery As String = "SELECT tblData.CoPayFundGrant,
tblData.M_DSS_SSA_Benefits, tblData.O_DSS_SSA_Benefits, " & _
            "tblData.Ref_MPS, tblData.Ref_LA, tblData.Ref_DCMH,
tblData.Place_Comm, tblData.Rem_Comm, tblData.Core_Case_Man,
tblData.Ref_DCMH_Case_Man " & _
            "FROM tblData;"
        Dim rsDataManip As ADODB.Recordset = cnnJET.Execute(strQuery)
        With rsDataManip
            If Not .EOF Then
                Dim NewDataValue As Object
                For Each fldData As ADODB.Field In rsDataManip.Fields
                    Select Case fldData.Value.ToString          ' ***** X
*****
                        Case "1 - Attempted-Successful"
                            NewDataValue = "Yes"
                        Case "2 - Attempted-Unsuccessful"
                            NewDataValue = "No"
                        Case Else
                            NewDataValue = System.DBNull.Value
                    End Select
                    fldData.Value = NewDataValue
                    NewDataValue = Nothing
                Next
                .MoveNext()

            End If

        End With

I had to change the line marked with "***** X *****" and add the ToString
so that I could test the field for string values and DBNull values. But I
want to test for string values and the actual DBNull value without changing
it to a string, so how can I change the code above to do this?

--

|
+-- Julian
|

+-- VB.Net 2003
Show quoteHide quote

|

26 Mar 2006 5:06 AM

Chris

Julian wrote:
Show quoteHide quote

> I have the following code:
>
>         Dim strQuery As String = "SELECT tblData.CoPayFundGrant,
> tblData.M_DSS_SSA_Benefits, tblData.O_DSS_SSA_Benefits, " & _
>             "tblData.Ref_MPS, tblData.Ref_LA, tblData.Ref_DCMH,
> tblData.Place_Comm, tblData.Rem_Comm, tblData.Core_Case_Man,
> tblData.Ref_DCMH_Case_Man " & _
>             "FROM tblData;"
>         Dim rsDataManip As ADODB.Recordset = cnnJET.Execute(strQuery)
>         With rsDataManip
>             If Not .EOF Then
>                 Dim NewDataValue As Object
>                 For Each fldData As ADODB.Field In rsDataManip.Fields
>                     Select Case fldData.Value.ToString          ' ***** X
> *****
>                         Case "1 - Attempted-Successful"
>                             NewDataValue = "Yes"
>                         Case "2 - Attempted-Unsuccessful"
>                             NewDataValue = "No"
>                         Case Else
>                             NewDataValue = System.DBNull.Value
>                     End Select
>                     fldData.Value = NewDataValue
>                     NewDataValue = Nothing
>                 Next
>                 .MoveNext()
>
>             End If
>
>         End With
>
> I had to change the line marked with "***** X *****" and add the ToString
> so that I could test the field for string values and DBNull values. But I
> want to test for string values and the actual DBNull value without changing
> it to a string, so how can I change the code above to do this?
>
>

Select Case fldData.Value
    Case is DBNull.Value
        'DBNull
    Case "1 - Attempted-Successful"
        NewDataValue = "Yes"
    Case "2 - Attempted-Unsuccessful"
        NewDataValue = "No"
    Case Else
        NewDataValue = System.DBNull.Value
End Select

26 Mar 2006 6:36 AM

Stephany Young

Because, the Type of fldData.Value os Object, you need to cast it as a
string before you can do string comparisons on it.

In my view it is best to check for DBNull first and then do whatever other
stuff you need to do. e.g.:

If fldData.Value Is DBNull.Value Then
    ...
Else
    Select Case CType(fldData.Value, String)
      Case "1 - Attempted-Successful"
        NewDataValue = "Yes"
      Case "2 - Attempted-Unsuccessful"
        NewDataValue = "No"
      Case Else
        NewDataValue = DBNull.Value
    End Select
End If

Note the use of CType(fldData.Value, String) instead of
fldData.Value.ToString. Under some circumstances the Object.ToString method
returns a value other than what is is expected whereas I have yet to
CType(<object>, String) return inappropriate values.

Show quoteHide quote

"Julian" <ad***@jdmils.com> wrote in message
news:OKbrUkIUGHA.5500@TK2MSFTNGP12.phx.gbl...
>I have the following code:
>
>        Dim strQuery As String = "SELECT tblData.CoPayFundGrant,
> tblData.M_DSS_SSA_Benefits, tblData.O_DSS_SSA_Benefits, " & _
>            "tblData.Ref_MPS, tblData.Ref_LA, tblData.Ref_DCMH,
> tblData.Place_Comm, tblData.Rem_Comm, tblData.Core_Case_Man,
> tblData.Ref_DCMH_Case_Man " & _
>            "FROM tblData;"
>        Dim rsDataManip As ADODB.Recordset = cnnJET.Execute(strQuery)
>        With rsDataManip
>            If Not .EOF Then
>                Dim NewDataValue As Object
>                For Each fldData As ADODB.Field In rsDataManip.Fields
>                    Select Case fldData.Value.ToString          ' ***** X
> *****
>                        Case "1 - Attempted-Successful"
>                            NewDataValue = "Yes"
>                        Case "2 - Attempted-Unsuccessful"
>                            NewDataValue = "No"
>                        Case Else
>                            NewDataValue = System.DBNull.Value
>                    End Select
>                    fldData.Value = NewDataValue
>                    NewDataValue = Nothing
>                Next
>                .MoveNext()
>
>            End If
>
>        End With
>
> I had to change the line marked with "***** X *****" and add the ToString
> so that I could test the field for string values and DBNull values. But I
> want to test for string values and the actual DBNull value without
> changing it to a string, so how can I change the code above to do this?
>
>
> --
> |
> +-- Julian
> |
> +-- VB.Net 2003
> |
>
>
>
>