473,371 Members | 1,753 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,371 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 5067
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

19
by: jim | last post by:
This line of code returns error 13, cast from 'DBNull' to type 'String' is not valid If IsDBNull(Clinics.Clinics.Item(A).Workphone) The <other code End I Clinics.Clinics is a dataset that...
5
by: DraguVaso | last post by:
Hi, Something I don't understand about a Typed DataSet: When a value in the DataSet is DBNull, it throws this error: "Cannot get value because it is DBNull". But aren't Typed DataSets...
2
by: Filipe Cristóvão | last post by:
Hi, I know that this is a newbie's question, but I need to know how i do to know if a field in a table is DBNull. If I try to write (with response.write) the field, it gives me an error: "Cast...
11
by: Patrick.O.Ige | last post by:
When i try and use this (Where Unit is a column in my Table):- If Unit Is DBNull.Value Then Return "1" Else Return "2" End If I always have 2 returned! Even when Unit is NULL! I want a...
4
by: Tina | last post by:
I have instantiated an insertRow for a dataset. I now want to make the GLCode DBNull. I have tried: insertRow.GLCode = Convert.dbnull insertRow.GLCode = Convert.dbnull(insertRow.GLCode) and...
8
by: MattB | last post by:
Hello. I have a vb.net (asp.net) application that uses ado.net datasets. At one point, I need to check a text field in a DataTable to see if there's any text in it before performing text operations...
6
by: tshad | last post by:
I have a value coming from my Object (or it could also be from a SqlDbReader) where I need to test for DBNull and 0. I tried to do it in one call: if (not (newPosition.ReportsTo is...
19
by: Dave | last post by:
If Iwant to check if dataset1.SelectQuery1.column1 == System.DBNull.Value. How do I do this? What I wrote above will give an error. -- L. A. Jones
6
by: scott ocamb | last post by:
Hello, I have a function that expects the following parms. How can I handle null values. For example, if FirstName comes through as null, how can i propogate that null value to the stored...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

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.