M
Martin
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.
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.