DBNULL Error - SQL Server2000/ASP.NET

[Martin Feuersteiner]

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.CreatePara meter
With prm4
..ParameterName = "@Sec_ProgUser_ Gen"
..SqlDbType = SqlDbType.VarCh ar
..Size = 10
..Direction = ParameterDirect ion.Output
End With

prm5 = cmd1.CreatePara meter
With prm5
..ParameterName = "@Sec_ProgUser_ Key"
..SqlDbType = SqlDbType.VarCh ar
..Size = 10
..Direction = ParameterDirect ion.Output
End With
....
cmd1.ExecuteNon Query()
....
Session("Sec_Pr ogUser_Gen") = prm4.Value
Session("Sec_Pr ogUser_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_G en is "1110011",
@Sec_ProgUser_K ey = "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("S ec_ProgUser_Key "))
....
MyString2 = CStr(Session("S ec_ProgUser_Gen "))
End Sub

It fails in line 'MyString2 = CStr(Session("S ec_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_X XX are created in the stored procedure with a
statement like this:
SET @Sec_ProgUser_K ey = (SELECT Convert(varchar (1),Key_CanCrea teKey) +
Convert(varchar (1),Key_CanCrea teTransaction) +
Convert(varchar (1),Key_CanView ) + Convert(varchar (1),Key_CanDele te) FROM
i2b_proguser_se curityprofile WHERE SecurityProfile ID = @SecurityProfil eID)

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.

[Martin Feuersteiner]

Hi Everyone

Found the problem. Strange that I haven't seen it earlier.
Dim str1 As String = "EXEC sp_ValidatePerm issions @ProgClientID,
@ProgUserID, @Sec_ProgClient _Mod OUTPUT, @Sec_ProgUser_G en OUTPUT,
@Sec_ProgUser_K ey OUTPUT"

Forgot OUTPUT for @Sec_ProgUser_G en

"Martin Feuersteiner" <th************ @hotmail.com> wrote in message
news:bu******** **@titan.btinte rnet.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.CreatePara meter
With prm4
.ParameterName = "@Sec_ProgUser_ Gen"
.SqlDbType = SqlDbType.VarCh ar
.Size = 10
.Direction = ParameterDirect ion.Output
End With

prm5 = cmd1.CreatePara meter
With prm5
.ParameterName = "@Sec_ProgUser_ Key"
.SqlDbType = SqlDbType.VarCh ar
.Size = 10
.Direction = ParameterDirect ion.Output
End With
...
cmd1.ExecuteNon Query()
...
Session("Sec_Pr ogUser_Gen") = prm4.Value
Session("Sec_Pr ogUser_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_G en is "1110011",
@Sec_ProgUser_K ey = "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("S ec_ProgUser_Key "))
...
MyString2 = CStr(Session("S ec_ProgUser_Gen "))
End Sub

It fails in line 'MyString2 = CStr(Session("S ec_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_X XX are created in the stored procedure with a statement like this:
SET @Sec_ProgUser_K ey = (SELECT Convert(varchar (1),Key_CanCrea teKey) +
Convert(varchar (1),Key_CanCrea teTransaction) +
Convert(varchar (1),Key_CanView ) + Convert(varchar (1),Key_CanDele te) FROM
i2b_proguser_se curityprofile WHERE SecurityProfile ID = @SecurityProfil eID)

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.