By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,234 Members | 1,908 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,234 IT Pros & Developers. It's quick & easy.

Help with testing DBNull from a field.

P: n/a
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
|


Mar 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Julian wrote:
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
Mar 26 '06 #2

P: n/a
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.
"Julian" <ad***@jdmils.com> wrote in message
news:OK**************@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
|

Mar 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.