One neat trick is to create your own DataReader class that implements
IDataReader. Yeah, it's some typing to do it once, but when you're done you
can use the resulting class in all of you database access code and never
worry about nulls again.
Public Class SafeDataReader
Implements IDataReader
Private mDataReader As IDataReader
Public Sub New(ByVal DataReader As IDataReader)
mDataReader = DataReader
End Sub
....And a whole LOT of GetXXX methods go here...
End Class
In it, you can write Get methods like this:
Public Function GetString(ByVal i As Integer) As String Implements
IDataReader.GetString
If mDataReader.IsDBNull(i) Then
Return ""
Else
Return mDataReader.GetString(i)
End If
End Function
Even if you don't want to do that, the if statement in the above function
could easily be modified for your example:
If MyReader.IsDBNull(3) Then
M = ""
Else
M = MyReader.GetString(3)
End If
However, if you do a one-time fix like this then you won't be protected when
a null crops up in other fields like FName or LName at some later date.
BTW: I don't want to steal credit for this idea; I found it in "Visual Basic
..NET Business Objects" by Rockford Lhotka.
"Woody Splawn" <wo***@splawns.com> wrote in message
news:Oj**************@TK2MSFTNGP10.phx.gbl...
I have some code that looks like this:
Dim SSN, LName, FName, M As String
mySqlConnection = New SqlConnection(myConnectionString)
Dim sql_Command As New SqlCommand( _
"Select SSN, LName, FName, M from Students WHERE (SSN = " + " '" +
ProposedValue + "')", _
mySqlConnection)
Try
mySqlConnection.Open()
Dim myReader As SqlDataReader =
sql_Command.ExecuteReader(CommandBehavior.CloseCon nection)
While myReader.Read()
SSN = myReader.GetString(0) ' The first field in the
answer set
LName = myReader.GetString(1) 'The second field in the
answer set
FName = myReader.GetString(2) 'The third field in the
answer set
'M = myReader.GetString(3) 'The fourth field in the answer
set
End While
myReader.Close()
sql_Command.Connection.Close()
The problem is that the field caled M (middle initial) is sometimes null.
When it is the code above fails. How can I write the code so that if the
middle initial field is blank or null the code won't crash?