Strange DBNULL Error - Please Help!

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
Webapplication and store them in sessions.
Like This:

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

prm5 = cmd1.CreateParameter
With prm5
..ParameterName = "@dotnet.itags.org.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. @dotnet.itags.org.Sec_ProgUser_Gen is "1110011",
@dotnet.itags.org.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_ProgClient_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 @dotnet.itags.org.Sec_ProgUser_XXX are created in the stored procedure with a
statement like this:
SET @dotnet.itags.org.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 = @dotnet.itags.org.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.Did you figure this out?

--
Regards,
Alvin Bruney [ASP.NET MVP]
Got tidbits? Get it here...
http://tinyurl.com/3he3b
"Martin" <theintrepidfox@.hotmail.com> wrote in message
news:7217238c.0401221018.3b36790a@.posting.google.c om...
> 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
> 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_ProgClient_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 Alvin

Yes. Thanks for asking. Forgot OUTPUT for @.Sec_ProgUser_Gen

"Alvin Bruney" <vapor at steaming post office> wrote in message news:<OFZOOxs4DHA.1504@.TK2MSFTNGP12.phx.gbl>...
> Did you figure this out?
> --
> Regards,
> Alvin Bruney [ASP.NET MVP]
> Got tidbits? Get it here...
> http://tinyurl.com/3he3b
> "Martin" <theintrepidfox@.hotmail.com> wrote in message
> news:7217238c.0401221018.3b36790a@.posting.google.c om...
> > 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
> > 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_ProgClient_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.