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.CloseConnection)

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?

"Woody Splawn" <(E-Mail Removed)> schrieb
> 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.CloseConnection)
>
> 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?

Which value do you want to have in M when the database value is Null?
Nothing? A zero-length String? A certain string like "(Null)"?



--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html

M = IIf(IsDBNull(dr.GetString(3)), _
String.Empty, dr(3))

"Woody Splawn" <(E-Mail Removed)> wrote in message
news:Oj$(E-Mail Removed)...
> 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.CloseConnection)
>
> 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?
>
>
>

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" <(E-Mail Removed)> wrote in message
news:Oj$(E-Mail Removed)...
> 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.CloseConnection)
>
> 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?
>
>
>

"Adam J. Schaff" <(E-Mail Removed)> schrieb
> 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.

Only a remark:
Disallowing Null values - if possible - in a database is the better
approach. When designing the database, one should wonder whether there is a
reason to distinguish between a Null value and e.g. a zero-length string.
Usually there isn't. If Null values are converted to zero-length strings
anyway, storing Null values does not make sense.


--
Armin

http://www.plig.net/nnq/nquote.html
http://www.netmeister.org/news/learn2quote.html