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
>
>

DBNULL Error - SQL Server2000/ASP.NET

Dear Group

I'm having a very weird problem. Any hints are greatly appreciated.

I'm returning two values from a MS SQL Server 2000 stored procedure to my
ASP.NET Webapplication and store them in sessions.
Like This:

prm4 = cmd1.CreateParameter
With prm4
..ParameterName = "@.Sec_ProgUser_Gen"
..SqlDbType = SqlDbType.VarChar
..Size = 10
..Direction = ParameterDirection.Output
End With

prm5 = cmd1.CreateParameter
With prm5
..ParameterName = "@.Sec_ProgUser_Key"
..SqlDbType = SqlDbType.VarChar
..Size = 10
..Direction = ParameterDirection.Output
End With
...
cmd1.ExecuteNonQuery()
...
Session("Sec_ProgUser_Gen") = prm4.Value
Session("Sec_ProgUser_Key") = prm5.Value

Both output parameters are declared as varchar(10) within the stored
procedure. If I run the stored procedure in SQL Analyzer, I'm getting a
string value for each of them. E.g. @.Sec_ProgUser_Gen is "1110011",
@.Sec_ProgUser_Key = "1100".

Now the strange thing happens if I try to run the following code:

Sub MyTest()
Dim MyString1 As String
Dim MyString2 As String
MyString1 = CStr(Session("Sec_ProgUser_Key"))
...
MyString2 = CStr(Session("Sec_ProgUser_Gen"))
End Sub

It fails in line 'MyString2 = CStr(Session("Sec_ProgUser_Gen"))' with Cast
from type 'DBNull' to type 'String' is not valid.

I don't understand this. They are both the same, the only difference is the
length of the string. Help!

Additional Information:
The values for @.Sec_ProgUser_XXX are created in the stored procedure with a
statement like this:
SET @.Sec_ProgUser_Key = (SELECT Convert(varchar(1),Key_CanCreateKey) +
Convert(varchar(1),Key_CanCreateTransaction) +
Convert(varchar(1),Key_CanView) + Convert(varchar(1),Key_CanDelete) FROM
i2b_proguser_securityprofile WHERE SecurityProfileID = @.SecurityProfileID)

The datatype of the source columns used to be bit then changed them to
Integer as I thought this might cause the problem. (Although it shouldn't as
the values get converted to varchar without a problem in the stored
procedure. No fields contain NULL values, only 1 or 0.Hi Everyone

Found the problem. Strange that I haven't seen it earlier.
Dim str1 As String = "EXEC sp_ValidatePermissions @.ProgClientID,
@.ProgUserID, @.Sec_ProgClient_Mod OUTPUT, @.Sec_ProgUser_Gen OUTPUT,
@.Sec_ProgUser_Key OUTPUT"

Forgot OUTPUT for @.Sec_ProgUser_Gen

"Martin Feuersteiner" <theintrepidfox@.hotmail.com> wrote in message
news:bup5nf$5dl$1@.titan.btinternet.com...
> Dear Group
> I'm having a very weird problem. Any hints are greatly appreciated.
> I'm returning two values from a MS SQL Server 2000 stored procedure to my
> ASP.NET Webapplication and store them in sessions.
> Like This:
> prm4 = cmd1.CreateParameter
> With prm4
> .ParameterName = "@.Sec_ProgUser_Gen"
> .SqlDbType = SqlDbType.VarChar
> .Size = 10
> .Direction = ParameterDirection.Output
> End With
> prm5 = cmd1.CreateParameter
> With prm5
> .ParameterName = "@.Sec_ProgUser_Key"
> .SqlDbType = SqlDbType.VarChar
> .Size = 10
> .Direction = ParameterDirection.Output
> End With
> ...
> cmd1.ExecuteNonQuery()
> ...
> Session("Sec_ProgUser_Gen") = prm4.Value
> Session("Sec_ProgUser_Key") = prm5.Value
> Both output parameters are declared as varchar(10) within the stored
> procedure. If I run the stored procedure in SQL Analyzer, I'm getting a
> string value for each of them. E.g. @.Sec_ProgUser_Gen is "1110011",
> @.Sec_ProgUser_Key = "1100".
> Now the strange thing happens if I try to run the following code:
> Sub MyTest()
> Dim MyString1 As String
> Dim MyString2 As String
> MyString1 = CStr(Session("Sec_ProgUser_Key"))
> ...
> MyString2 = CStr(Session("Sec_ProgUser_Gen"))
> End Sub
> It fails in line 'MyString2 = CStr(Session("Sec_ProgUser_Gen"))' with Cast
> from type 'DBNull' to type 'String' is not valid.
> I don't understand this. They are both the same, the only difference is
the
> length of the string. Help!
> Additional Information:
> The values for @.Sec_ProgUser_XXX are created in the stored procedure with
a
> statement like this:
> SET @.Sec_ProgUser_Key = (SELECT Convert(varchar(1),Key_CanCreateKey) +
> Convert(varchar(1),Key_CanCreateTransaction) +
> Convert(varchar(1),Key_CanView) + Convert(varchar(1),Key_CanDelete) FROM
> i2b_proguser_securityprofile WHERE SecurityProfileID = @.SecurityProfileID)
> The datatype of the source columns used to be bit then changed them to
> Integer as I thought this might cause the problem. (Although it shouldn't
as
> the values get converted to varchar without a problem in the stored
> procedure. No fields contain NULL values, only 1 or 0.

DBNull Error

Am getting errors on this syntax:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.myColumn = DBNull.Value

Value of type 'System.DBNull' cannot be converted to type 'String'

Any ideas? Just want to set the myColumn to NULL.

Thanks

Nevermind.

Row.myColumn = Nothing

DBNULL

I found a bug, if a textbox of a record row has dbnull value, all of following rows will hide the textbox, even they have non dbnull value. My current workaround is use ISNULL(col,'') AS col in query.

Is this by design or a bug?

If this is reproducible, it is a bug. I have not seen it, though. Which version and build of Reporting Services are you using?|||I am using the reportviewer in vs2005 beta2 to view local report.|||

We are not seeing this in current builds. I believe that it is resolved.

dbnull

Is there an easy way in Reporting service to handle dbull in dataset?

I am using the local mode of the new reportviewer control, when I feed the dataset to the report, if there are some dbull column, an exception will be thrown.

To those string column, I can set NULLVALUE property to empty, but to other datatype, like integer, datetime, money, what should I do?

ThanksDBNull is translated to a unitialized object in the report. You can use IsNothing, i.e. IsNothing(Fields!Foo.Value).|||Hey, you know what, it is very strange.

If I use BindingDatasource.datasource = ds.tables(0), (BindDatasource is bound to ReportViewer), I will have exception if any field is null.

But if I use
BindingDatasource.datasource = ds
BindingDatasource.datamember = "Table1"

I don't get exception any more.

Don't why, but it works like charm.

Wei|||

If this solves your problem, please mark the question answered. Thanks. :)

|||In addition to =IsNothing(Fields!Foo.Value), you could use the IS keyword: (Fields!Foo.Value is Nothing)

-- Robert