Help with testing DBNull from a field.

  • Thread starter Thread starter Julian
  • Start date Start date
J

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
|
 
Julian said:
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?
Click to expand...

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
 
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.
 
Back
Top