473,320 Members | 1,961 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,320 software developers and data experts.

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 = "@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.
Nov 18 '05 #1
2 1247
Did you figure this out?

--
Regards,
Alvin Bruney [ASP.NET MVP]
Got tidbits? Get it here...
http://tinyurl.com/3he3b
"Martin" <th************@hotmail.com> wrote in message
news:72**************************@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.

Nov 18 '05 #2
Hi Alvin

Yes. Thanks for asking. Forgot OUTPUT for @Sec_ProgUser_Gen

"Alvin Bruney" <vapor at steaming post office> wrote in message news:<OF**************@TK2MSFTNGP12.phx.gbl>...
Did you figure this out?

--
Regards,
Alvin Bruney [ASP.NET MVP]
Got tidbits? Get it here...
http://tinyurl.com/3he3b
"Martin" <th************@hotmail.com> wrote in message
news:72**************************@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.

Nov 18 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

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...
8
by: fredda054 | last post by:
Hi everybody ! I have a little repeater/hyperlink issue I'm not sure how to solve. I use a repeater to list subjects available at a specific school. The datasource of this repeater is a...
1
by: Martin Feuersteiner | last post by:
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...
3
by: Mike | last post by:
Hello, I have develop an apsnet page using vb language. I'm currently retriving data from the database. I have two fields that have null values. I get the following error Cast from type...
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...
4
by: Alexander Widera | last post by:
Hello, i'm using an asp:Reader and an ObjectDataSource. With this I get some strings and integer-values... but some of the intergervalues could be a <DBNull>-value. In the repeater is a...
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...
3
by: Sep410 | last post by:
Hi all, Please help me in this code: txtLastNameFM.Text = IIf(dgFamilyMember.CurrentRow.Cells(3).Equals(System.DBNull.Value), "", dgFamilyMember.CurrentRow.Cells(3).Value) this is an error I...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

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.