DBNULL is giving me a headach!

Could someone please help with this:
I'm trying to connect to a sql server 2000 that to web service. When i run
the service i get:
System.InvalidCastException: Specified cast is not valid.
at wsStudents.Service1.GetStudent(String studentlastname) in
C:\Inetpub\wwwroot\wsStudents\Service1.asmx.vb:line 82
and a throws a similar error (Cast from type 'DBNull' to type 'String' is
not valid) when i try to consume it. This code accepts one input and one
output paramer. The value returned from the output paramter seems be Nothing
"
!!
Here's the code:
<WebMethod()> _
Public Function GetStudent(ByVal studentlastname As String) As String
Dim dsResult As New DataSet
Dim strSQL As String
Dim result_outParam As String
'strSQL = "SELECT @.StuFee FROM dbo.Student WHERE
dbo.Student.LastName = @.StuLName and dbo.Student.FirstName =@.StuFName"
strSQL = "SELECT @.StuFee FROM dbo.Student WHERE dbo.Student.LastName
= @.StuLName"
Dim cmd As New SqlCommand(strSQL, conn)
'Add the first input paramter
cmd.Parameters.Add("@.StuLName", SqlDbType.VarChar)
cmd.Parameters("@.StuLName").Direction = ParameterDirection.Input
cmd.Parameters("@.StuLName").Value = studentlastname
'Add the output paramter
cmd.Parameters.Add("@.StuFee", SqlDbType.VarChar, 50)
cmd.Parameters("@.StuFee").Direction = ParameterDirection.Output
If cmd.Connection.State <> ConnectionState.Open Then
conn.Open()
End If
cmd.ExecuteNonQuery()
result_outParam = cmd.Parameters("@.StuFee").Value
Return result_outParam
conn.Close()
I tried to rewrite the last code like this:
result_outParam =
System.DBNull.Value.ToString(cmd.Parameters("@.StuFee").Value)
but no luck
UKok, that is not possible if the value returned is DBNull.
result_outParam = cmd.Parameters("@.StuFee").Value == DBNull.Value ?
string.Empty : cmd.Parameters("@.StuFee").Value.ToString();
This is the long way :-)
HTH, Jens Suessmeyer.|||Thanks Jens
This suggested code i assume is in C# (mine was in vb .net). I'm not
familiar with using "?" symbol. Can you please translate this into VB .net.
Thanks. Execuse my ignorance.
Is this suggested code one line or two lines:
result_outParam = cmd.Parameters("@.StuFee").Value == DBNull.Value ?
string.Empty : cmd.Parameters("@.StuFee").Value.ToString()
UK
"Jens" wrote:

> ok, that is not possible if the value returned is DBNull.
> result_outParam = cmd.Parameters("@.StuFee").Value == DBNull.Value ?
> string.Empty : cmd.Parameters("@.StuFee").Value.ToString();
> This is the long way :-)
> HTH, Jens Suessmeyer.
>|||Nab (Nab@.discussions.microsoft.com) writes:
> This suggested code i assume is in C# (mine was in vb .net). I'm not
> familiar with using "?" symbol. Can you please translate this into VB
> .net.
?: is the C# equivalent of the Iif function in Visual Basic. That is
condition ? true-return : false-return
So Jens's example would read:
result_outParam = Iif(cmd.Parameters("@.StuFee").Value == DBNull.Value, _
string.Empty, _
cmd.Parameters("@.StuFee").Value.ToString())
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks. i'll try this and let you know. What I don't understand is that
sometime ago i wrote similar code but never had to to deal with an issue lik
e
this.
I had deleted SQL Servre 2000 since and re-installed it again but never
installed any service packs this time. Could this have something to do with
it?
"Erland Sommarskog" wrote:

> Nab (Nab@.discussions.microsoft.com) writes:
> ?: is the C# equivalent of the Iif function in Visual Basic. That is
> condition ? true-return : false-return
> So Jens's example would read:
> result_outParam = Iif(cmd.Parameters("@.StuFee").Value == DBNull.Value,
_
> string.Empty, _
> cmd.Parameters("@.StuFee").Value.ToString())
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||
"Erland Sommarskog" wrote:

> Nab (Nab@.discussions.microsoft.com) writes:
> ?: is the C# equivalent of the Iif function in Visual Basic. That is
> condition ? true-return : false-return
> So Jens's example would read:
> result_outParam = Iif(cmd.Parameters("@.StuFee").Value == DBNull.Value,
_
> string.Empty, _
> cmd.Parameters("@.StuFee").Value.ToString())
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||In addition to what others have posted...
I encounter this often enough that I created a function that I call whenever
I retrieve a value from the database in VB. It saves the time of having to
write an IF statement every time you need to retrieve a value from the
database.
After you have the function your code would read:
result_outParam = nvl(cmd.Parameters("@.StuFee").Value)
Public Shared Function nvl(ByVal strValue As Object) As String
'***************************************
********************************
' Public method
' returns empty strign in place of DB nulls
' prevents errors caused by type discrepencies
'***************************************
********************************
If strValue Is DBNull.Value Then
Return ""
Else
Return strValue
End If
End Function
"Nab" <Nab@.discussions.microsoft.com> wrote in message
news:AC678A4A-BE76-4884-AA65-8D1BFB3697CB@.microsoft.com...
> Could someone please help with this:
>
> I'm trying to connect to a sql server 2000 that to web service. When i
run
> the service i get:
> System.InvalidCastException: Specified cast is not valid.
> at wsStudents.Service1.GetStudent(String studentlastname) in
> C:\Inetpub\wwwroot\wsStudents\Service1.asmx.vb:line 82
> and a throws a similar error (Cast from type 'DBNull' to type 'String' is
> not valid) when i try to consume it. This code accepts one input and one
> output paramer. The value returned from the output paramter seems be
Nothing"
> !!
> Here's the code:
> <WebMethod()> _
> Public Function GetStudent(ByVal studentlastname As String) As String
> Dim dsResult As New DataSet
> Dim strSQL As String
> Dim result_outParam As String
> 'strSQL = "SELECT @.StuFee FROM dbo.Student WHERE
> dbo.Student.LastName = @.StuLName and dbo.Student.FirstName =@.StuFName"
> strSQL = "SELECT @.StuFee FROM dbo.Student WHERE
dbo.Student.LastName
> = @.StuLName"
> Dim cmd As New SqlCommand(strSQL, conn)
> 'Add the first input paramter
> cmd.Parameters.Add("@.StuLName", SqlDbType.VarChar)
> cmd.Parameters("@.StuLName").Direction = ParameterDirection.Input
> cmd.Parameters("@.StuLName").Value = studentlastname
> 'Add the output paramter
> cmd.Parameters.Add("@.StuFee", SqlDbType.VarChar, 50)
> cmd.Parameters("@.StuFee").Direction = ParameterDirection.Output
> If cmd.Connection.State <> ConnectionState.Open Then
> conn.Open()
> End If
> cmd.ExecuteNonQuery()
> result_outParam = cmd.Parameters("@.StuFee").Value
> Return result_outParam
> conn.Close()
> I tried to rewrite the last code like this:
> result_outParam =
> System.DBNull.Value.ToString(cmd.Parameters("@.StuFee").Value)
> but no luck
> --
> UK|||Nab (Nab@.discussions.microsoft.com) writes:
> Thanks. i'll try this and let you know. What I don't understand is that
> sometime ago i wrote similar code but never had to to deal with an issue
> like this.
> I had deleted SQL Servre 2000 since and re-installed it again but never
> installed any service packs this time. Could this have something to do
> with it?
The installation of SQL 2000 cannot affect ADO .Net or VB .Net, as they
are separate products. Maybe you didn't have to deal with any NULL values
the first time?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for all your replies. Jim's function-based code seems to have worked
better for me!
Cheers.
--
UK
"Jim Underwood" wrote:

> In addition to what others have posted...
> I encounter this often enough that I created a function that I call whenev
er
> I retrieve a value from the database in VB. It saves the time of having t
o
> write an IF statement every time you need to retrieve a value from the
> database.
> After you have the function your code would read:
> result_outParam = nvl(cmd.Parameters("@.StuFee").Value)
> Public Shared Function nvl(ByVal strValue As Object) As String
> '***************************************
********************************
> ' Public method
> ' returns empty strign in place of DB nulls
> ' prevents errors caused by type discrepencies
> '***************************************
********************************
> If strValue Is DBNull.Value Then
> Return ""
> Else
> Return strValue
> End If
> End Function
>
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:AC678A4A-BE76-4884-AA65-8D1BFB3697CB@.microsoft.com...
> run
> Nothing"
> dbo.Student.LastName
>
>|||Apologies. In fact, all suggested solutions worked fine now. Thanks to all.
--
UK
"Jim Underwood" wrote:

> In addition to what others have posted...
> I encounter this often enough that I created a function that I call whenev
er
> I retrieve a value from the database in VB. It saves the time of having t
o
> write an IF statement every time you need to retrieve a value from the
> database.
> After you have the function your code would read:
> result_outParam = nvl(cmd.Parameters("@.StuFee").Value)
> Public Shared Function nvl(ByVal strValue As Object) As String
> '***************************************
********************************
> ' Public method
> ' returns empty strign in place of DB nulls
> ' prevents errors caused by type discrepencies
> '***************************************
********************************
> If strValue Is DBNull.Value Then
> Return ""
> Else
> Return strValue
> End If
> End Function
>
> "Nab" <Nab@.discussions.microsoft.com> wrote in message
> news:AC678A4A-BE76-4884-AA65-8D1BFB3697CB@.microsoft.com...
> run
> Nothing"
> dbo.Student.LastName
>
>