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.