Receiving system error when retrieving database record with null value

I have some VB.NET code to retrieve data from an SQL Server database and display it. The code is as follows:

-------------------------------

sw_calendar = calendarAdapter.GetEventByID(cid)

If sw_calendar.Rows.Count > 0Then

lblStartDateText.Text = sw_calendar(0).eventStartDate

lblEndDateText.Text = sw_calendar(0).eventEndDate

lblTitleText.Text = sw_calendar(0).title

lblLocationText.Text = sw_calendar(0).location

lblDescriptionText.Text = sw_calendar(0).description

Else

lblStartDateText.Text ="*** Not Found ***"

lblEndDateText.Text ="*** Not Found ***"

lblTitleText.Text ="*** Not Found ***"

lblLocationText.Text ="*** Not Found ***"

lblDescriptionText.Text ="*** Not Found ***"

EndIf

-------------------------------

If all of the fields in the database has values, everything works ok. However, if the title, location or description fields have a null value, I receive the following error message:

Unable to cast object of type 'System.DBNull' to type 'System.String'.

I've tried a bunch of different things such as:

Adding ".ToString" to the database field,Seeing if the value is null: If sw_calendar(0).description = system.DBnull.value...

...but either I get syntax errors in the code, or if the syntax is ok, I still get the above error message.

Can anyone help me with the code required to trap the nullwithin the code example I've provided? I'm sure there are other, and better, ways to code this, but for now I'd really like to get it working as is, and then optimize the code once the application is working (...can you tell I have a tight deadline)

Thanks,

Brad

Check forDBNullin VB.NET, with optional specification of type, so itconverts null to the appropriate value (e.g., "" for string, 0 fornumbers).

Good luck.

|||

Something like:

sw_calendar = calendarAdapter.GetEventByID(cid)If sw_calendar.Rows.Count > 0Then IF NOT IsDBNull(sw_calendar(0).eventStartDate)Then lblStartDateText.Text = sw_calendar(0).eventStartDate ELSEblStartDateText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).eventEndDate)Then lblEndDateText.Text = sw_calendar(0).eventEndDate ELSE lblEndDateText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).title) THEN lblTitleText.Text = sw_calendar(0).title ELSE lblTitleText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).location) THEN lblLocationText.Text = sw_calendar(0).location ELSE lblLocationText.Text ="*** Not Found ***" END IF IF NOT IsDBNull(sw_calendar(0).description) THEN lblDescriptionText.Text = sw_calendar(0).description ELSE lblDescriptionText.Text ="*** Not Found ***" END IFGood luck.

|||

Hi,

I tried addingIF NOT IsDBNull... but I still get the same error message. To make sure the problem is what I think it is, I changed the value of the description field in the database to a single space. After doing this, page renders fine. When I delete the space, the error returns. So, there is still a problem evaluating sw_calendar(0).location within the IsDBNull function.

Any ideas?

|||

I just tried:

If sw_calendar(0).description.Length >= 1Then

lblDescriptionText.Text = sw_calendar(0).description

Else

lblDescriptionText.Text =" "

EndIf

and itstill generates theUnable to cast object of type 'System.DBNull' to type 'System.String'. error message!

|||

The problem isnt with IsDbNull. The problem is with the strongly typed data row you are using. If you try to do IsDbNull(sw_calendar(0).description), sw_calendar tries to convert the description to a string and then pass that value to IsDBNull. However, sincesw_calendar(0).description is DBNull, it will always throw this error before DbNull ever gets it.

This, in my opinion, has crippled the Strongly Typed DataSets that are created with the TableAdapters.

The workaround is not to try to get the description withsw_calendar(0).description. Instead, usesw_calendar(0)("description"). Its not strongly typed, but at least it wont crash your app.

|||

One option would be to convert the values of NULLs to '' in case of strings and 0 or something similar in case of integers. You can achieve this in your query itself. There is a function call ISNULL in SQL. Basically you can use this function like ISNULL ( <column name> , '' ). This will replace the null values in the column to '' ( blank which is a legal string BLOCKED EXPRESSION. You can write select isnull ( description , '' ) as description. Then you are rest assured that the query itself will give you the valid string values instead of null and you having to bother to convert those nulls to blank string or something similar.

You can use this type of query when you are not sured about the values contained in the column, I mean in case the column may contain nulls also.

Hope this will help.

Received error code 0x80040e57

I've check out that this error code says: A literal value in the command overflowed the range of the type of the associated column.

I received the error message when inserting large block data into a image column in SQL Server 2000. As I know, image support max 2G data to store. I use the C code function below to insert the data:

DBCreateParamBinary (hStmt, "", DB_PARAM_INPUT,&(pMonitorDBData->SampDat),NUM_SAMPLES_SETDBMONITOR*sizeof(float64) );

in which, the NUM_SAMPLES_SETDBMONITOR*sizeof(float64) stands for the data length by byte. and ,&(pMonitorDBData->SampDat) stands for the pointer address of the data block. (pls note: NUM_SAMPLES_SETDBMONITOR*sizeof(float64) = 4096*8=32768 bytes)

In addation, I've tried to use certain numbers instead of "NUM_SAMPLES_SETDBMONITOR*sizeof(float64)", and I found I could only set this value up to 8003 bytes. If 8003 above, this error message would be received.

What should I do with this issue? Any help?
Thanks a million!

I believe that you want to construct your table such that your images are out of row to avoid the 8k row item limit.

http://msdn2.microsoft.com/en-us/library/ms173530.aspx

Hope this helps,

John Gordon (MSFT)

Received error code 0x80040e57

I've check out that this error code says: A literal value in the command overflowed the range of the type of the associated column.

I received the error message when inserting large block data into a image column in SQL Server 2000. As I know, image support max 2G data to store. I use the C code function below to insert the data:

DBCreateParamBinary (hStmt, "", DB_PARAM_INPUT,&(pMonitorDBData->SampDat),NUM_SAMPLES_SETDBMONITOR*sizeof(float64) );

in which, the NUM_SAMPLES_SETDBMONITOR*sizeof(float64) stands for the data length by byte. and ,&(pMonitorDBData->SampDat) stands for the pointer address of the data block. (pls note: NUM_SAMPLES_SETDBMONITOR*sizeof(float64) = 4096*8=32768 bytes)

In addation, I've tried to use certain numbers instead of "NUM_SAMPLES_SETDBMONITOR*sizeof(float64)", and I found I could only set this value up to 8003 bytes. If 8003 above, this error message would be received.

What should I do with this issue? Any help?
Thanks a million!

I believe that you want to construct your table such that your images are out of row to avoid the 8k row item limit.

http://msdn2.microsoft.com/en-us/library/ms173530.aspx

Hope this helps,

John Gordon (MSFT)

Really easy stored proc question

I suppose it's not possible to return a varchar value from a stored proc? I keep getting a conversion error.
How can I return a varchar value, then?
Thanks.. sorry for the stupid question. I did some searching on Google and didn't find much.In the procedure - RAISERROR('your_text', <level int>, <state int>), in your front-end code - interrogate errors collection.|||Originally posted by MDesigner
I suppose it's not possible to return a varchar value from a stored proc? I keep getting a conversion error.

How can I return a varchar value, then?

Thanks.. sorry for the stupid question. I did some searching on Google and didn't find much.

Sounds like you want to use an output parameter in your stored proc

Search SQL Server help on 'output parameters'|||That's right...return can only return an int...use return only to determine the success or failure of a sproc...

use output variable to return values...like:

CREATE PROC mySproc99 @.inputParam varchar(10), @.outputParam varchar(255) OUTPUT
AS|||Originally posted by rdjabarov
In the procedure - RAISERROR('your_text', <level int>, <state int>), in your front-end code - interrogate errors collection.

Hey .. what are you talking about here !!!:confused: :confused: :confused:

READTEXT and return values

First question :

How can I set the return value of READTEXT to a variable of type nvarchar.

Second question :

I have a table t1 with a ntext column n1.

The ntext column has has words separated by empty space.
Each word can be assumed to be of size <= 255 characters.

How can I extract all the keywords in the ntext column to a table t2 with a column word nvarchar(255).

Assume that the text in column n1 is big enough so that it cannot be cast into a nvarchar or any other simpler type.

Any help on this is greatly appreciated.
Please do provide a sample code.

Alok.Have you referred to books online for READTEXT topoic.