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.

receive top 20

HI

I am trying to set up a stored procedure to retrieve to 20 messages from a queue into a table to implement a batched process. I have the following code in a stored procedure.

WAITFOR (
RECEIVE top (20) -- get batched so that we can process same listid once
message_type_name,
message_body, -- the message contents
conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.target
into @.PayloadData
), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

However, the stored procedure is only retrieving 1 message at a time from the queue. Did I miss some other setting

thanks

P

RECEIVE can only return messages on one conversation group. Normally each conversation is its own conversation group. If you sent only one message on each conversation, RECEIVE cannot get more that one message at a time, even if there are more messages in the queue.|||

Hi

so, in your blog on T-SQL RECEIVE. Fast. : Set based Processing.

How are you able to receive the message in bulk? Is it because of the way you send the message in LoadQueueReceivePerfBlog?

P|||Yes. This is also the reason why I recommend reusing dialogs in my other entry at http://blogs.msdn.com/remusrusanu/archive/2007/04/24/reusing-conversations.aspx|||

one follow up,

when I send the message using the same conversation handle, the receive top (20) statement waits until the previous batch is committed before it will start retrieving the next 20. I guess this is because of the its now part of the same conversation group and service broker need to guarantee process order?

This is what my proc looks like

BEGIN TRANSACTION

WAITFOR (

RECEIVE top (20) -- get batched so that we can process same listid once

message_type_name,

message_body, -- the message contents

conversation_handle -- the identifier of the dialog this message was received on

FROM dbo.target

into @.PayloadData

), TIMEOUT 3000 -- if the queue is empty for three second, give UPDATE and go away

-- do some processing of the records in @.PayloadData

COMMIT TRANSACTION

if "-- do some processing of the records in @.PayloadData" is taking a long time, its going to block the messages in the queue.

If I remove the begin and commit transaction block, it only wait for the 3 seconds I specified.

Question: is the transaction block necessary in the activated procedure.

thanks

Paul

Really urgent - Data field retrieve and store

Basically i make use of FormView in Web Developer Express Edition to create and store information to the database ...

what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament ....

this value would be stored within a session string in global condition that allowed any page able to access it ....

If you simply want to return the ID to use within the application, and you are using a stored procedure, do this.

Insert the record as normal in the table, then add a statement such as SELECT SCOPE_IDENETITY() as TheNewId

If your insert fails, this line never executes. If insert is OK, you get teh new ID passed back

In your code, perform an execute scaler method call on the connection, and cast the return (decimal as object) into an integer, and store in session. If the returned value is null, the insert failed

What I often create for projects is a global "Execute Scaler" method that accepts the name of a stored procedure, and a string dictionary of name/value pairs.

IN the method, create the SQL Command object, call the SQLCommandBuilder static method to get the parameters, then iterate through the dictionary to add these by name.

Call the command ExecuteScaler as mentioned and proceed as above

|||Really appreciate ur feedback men... but it looks so technical for me men... i am totally a new beginner but have to complete this project within another week time...

can u demonstrate the code (sorry for the hassle)... i kind of not sure how to achieve what u had mentioned...|||

Example SP

CREATE PROCEDURE

dbo.MyTable_Insert

(

@.Parameter1varchar(100)

)

AS

SET NOCOUNT ON

INSERT INTOMyTable (MyData)VALUES(@.Parameter1)

SELECT SCOPE_IDENTITY()asTheNewId

Example Code

int InsertRecord(string insertValue)

{

// assumes you have set cnn as a suitable connection

SqlCommand cmd =newSqlCommand("MyTable_Insert", cnn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters["@.Parameter1"].Value = insertValue;

object retObject = cmd.ExecuteScalar();

if (retObject !=null)

return (int)retObject;

else

return 0;// no new record

}