471,092 Members | 1,760 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,092 software developers and data experts.

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.

Jul 20 '05 #1
1 5003
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" <th************@hotmail.com> wrote in message
news:bu**********@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.

Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Filipe Cristóvão | last post: by
11 posts views Thread by Patrick.O.Ige | last post: by
4 posts views Thread by Tina | last post: by
8 posts views Thread by MattB | last post: by
6 posts views Thread by tshad | last post: by
6 posts views Thread by scott ocamb | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.