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.

Realtime record count for table...

Here's a little sql 2005 script I wrote:

1. Start by running this script....

declare @.x int

select @.x = 1

while ( @.x < 75000)
begin
insert into myTesttable values (@.x)
Select @.x = @.x + 1
end

2. While the script is still running, I want to know how many records are in the table. From the same query window as the script, I have run both of the following statements.

select count(*) from mytesttable
witn (nolock)

select count(*) from mytesttable
witn (tablock)

Instead of getting the answer immediately, they run only after the original script has completed. They seem to be "blocked". How can I get a near realtime count of the number of records in this table while the script populates the table?

Thanks,

Barkingdog

Barkingdog, just open another query window and run your "select count(*) from mytesttable". You'll get the row count while the other script is running.|||

If you use GridView it only displayed after the batch execution completed.

You can use different window or change the GridView to TextView to get the result immd.

|||

Yes, opening a new window did enable to the "select count(*) ...." to run but why is this the case? (After all, I couldn't run the "Select count(*) .." from the window that invoked the original script. Seems like the orignal script "blocks" the conneciton so I need to open a new conneciton (window). But why?

TIA,

barkingdog

|||

barkingdog wrote:

Yes, opening a new window did enable to the "select count(*) ...." to run but why is this the case? (After all, I couldn't run the "Select count(*) .." from the window that invoked the original script. Seems like the orignal script "blocks" the conneciton so I need to open a new conneciton (window). But why?

TIA,

barkingdog

The query window runs the statements sequentially and does not run the next statement until the prior one has finished, thus your select count(*)... will not run until the statements in the while loop complete. It's not "blocking" the connection, it just has not completed the prior statements...

If one of the replies solved your problem, please mark them as answered...

Really need to solve this SQL problem to finish this web site.

Hi,
i created a dataSet in an ASP.Net page which:
1. Loads all fields from each database record.
2. Creates a new field using 2 of the existing fields: FullName = FirstName
+ ' ' + LastName
3. Loads only the record which has the same FullName as the variable Name
passed in the URL to this page.
My SQL is this:
SELECT *, FirstName + ' ' + LastName as FullName
FROM people
WHERE FirstName + ' ' + LastName = ?
and:
<Parameter Name="@.FullName" Value='<%# IIf((Request.QueryString("Name") <>
Nothing), Request.QueryString("Name"), "") %>'
Type="WChar"/></Parameters></MM:DataSet>
Can you please tell me how to do this?
I know i allready asked this but until now i got no solution and i need to
solve this to finish this web site and deliver it.
I hope i was able to explain better my problem.
Thank You Very Much,
Miguel
Why are you putting the Request.Querystring in a pre-render block, should it
not be in regular code with the rest of it ? IE
Value = Request.QueryString("Name")
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Miguel Dias Moura" <web001@.27NOSPAMlamps.com> wrote in message
news:%23UCksgXVEHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i created a dataSet in an ASP.Net page which:
> 1. Loads all fields from each database record.
> 2. Creates a new field using 2 of the existing fields: FullName =
FirstName
> + ' ' + LastName
> 3. Loads only the record which has the same FullName as the variable Name
> passed in the URL to this page.
> My SQL is this:
> SELECT *, FirstName + ' ' + LastName as FullName
> FROM people
> WHERE FirstName + ' ' + LastName = ?
> and:
> <Parameter Name="@.FullName" Value='<%# IIf((Request.QueryString("Name")
<>
> Nothing), Request.QueryString("Name"), "") %>'
> Type="WChar"/></Parameters></MM:DataSet>
> Can you please tell me how to do this?
> I know i allready asked this but until now i got no solution and i need to
> solve this to finish this web site and deliver it.
> I hope i was able to explain better my problem.
> Thank You Very Much,
> Miguel
>
>
|||Do you know the strange thing? I tested it and everything works fine...but
when i was testing in the Dreamweaver window where i place the SQL, i was
not getting any records...have no idea why is this...but well, at least it's
working.
Thanks for eveybody help and patiente...my code is working :-)
Thanks again for your time,
Miguel
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:OtAHCsXVEHA.3596@.tk2msftngp13.phx.gbl...
> Why are you putting the Request.Querystring in a pre-render block, should
it[vbcol=seagreen]
> not be in regular code with the rest of it ? IE
> Value = Request.QueryString("Name")
> --
> OHM ( Terry Burns )
> . . . One-Handed-Man . . .
>
> "Miguel Dias Moura" <web001@.27NOSPAMlamps.com> wrote in message
> news:%23UCksgXVEHA.2592@.TK2MSFTNGP09.phx.gbl...
> FirstName
Name[vbcol=seagreen]
IIf((Request.QueryString("Name")[vbcol=seagreen]
> <>
to
>

Really need to solve this SQL problem to finish this web site.

Hi,
i created a dataSet in an ASP.Net page which:
1. Loads all fields from each database record.
2. Creates a new field using 2 of the existing fields: FullName = FirstName
+ ' ' + LastName
3. Loads only the record which has the same FullName as the variable Name
passed in the URL to this page.
My SQL is this:
SELECT *, FirstName + ' ' + LastName as FullName
FROM people
WHERE FirstName + ' ' + LastName = ?
and:
<Parameter Name="@.FullName" Value='<%# IIf((Request.QueryString("Name") <>
Nothing), Request.QueryString("Name"), "") %>'
Type="WChar"/></Parameters></MM:DataSet>
Can you please tell me how to do this?
I know i allready asked this but until now i got no solution and i need to
solve this to finish this web site and deliver it.
I hope i was able to explain better my problem.
Thank You Very Much,
MiguelWhy are you putting the Request.Querystring in a pre-render block, should it
not be in regular code with the rest of it ? IE
Value = Request.QueryString("Name")
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Miguel Dias Moura" <web001@.27NOSPAMlamps.com> wrote in message
news:%23UCksgXVEHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i created a dataSet in an ASP.Net page which:
> 1. Loads all fields from each database record.
> 2. Creates a new field using 2 of the existing fields: FullName =
FirstName
> + ' ' + LastName
> 3. Loads only the record which has the same FullName as the variable Name
> passed in the URL to this page.
> My SQL is this:
> SELECT *, FirstName + ' ' + LastName as FullName
> FROM people
> WHERE FirstName + ' ' + LastName = ?
> and:
> <Parameter Name="@.FullName" Value='<%# IIf((Request.QueryString("Name")
<>
> Nothing), Request.QueryString("Name"), "") %>'
> Type="WChar"/></Parameters></MM:DataSet>
> Can you please tell me how to do this?
> I know i allready asked this but until now i got no solution and i need to
> solve this to finish this web site and deliver it.
> I hope i was able to explain better my problem.
> Thank You Very Much,
> Miguel
>
>|||Do you know the strange thing? I tested it and everything works fine...but
when i was testing in the Dreamweaver window where i place the SQL, i was
not getting any records...have no idea why is this...but well, at least it's
working.
Thanks for eveybody help and patiente...my code is working :-)
Thanks again for your time,
Miguel
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:OtAHCsXVEHA.3596@.tk2msftngp13.phx.gbl...
> Why are you putting the Request.Querystring in a pre-render block, should
it
> not be in regular code with the rest of it ? IE
> Value = Request.QueryString("Name")
> --
> OHM ( Terry Burns )
> . . . One-Handed-Man . . .
>
> "Miguel Dias Moura" <web001@.27NOSPAMlamps.com> wrote in message
> news:%23UCksgXVEHA.2592@.TK2MSFTNGP09.phx.gbl...
> FirstName
Name[vbcol=seagreen]
IIf((Request.QueryString("Name")[vbcol=seagreen]
> <>
to[vbcol=seagreen]
>

Really need to solve this SQL problem to finish this web site.

Hi,
i created a dataSet in an ASP.Net page which:
1. Loads all fields from each database record.
2. Creates a new field using 2 of the existing fields: FullName = FirstName
+ ' ' + LastName
3. Loads only the record which has the same FullName as the variable Name
passed in the URL to this page.
My SQL is this:
SELECT *, FirstName + ' ' + LastName as FullName
FROM people
WHERE FirstName + ' ' + LastName = ?
and:
<Parameter Name="@.FullName" Value='<%# IIf((Request.QueryString("Name") <>
Nothing), Request.QueryString("Name"), "") %>'
Type="WChar"/></Parameters></MM:DataSet>
Can you please tell me how to do this?
I know i allready asked this but until now i got no solution and i need to
solve this to finish this web site and deliver it.
I hope i was able to explain better my problem.
Thank You Very Much,
MiguelWhy are you putting the Request.Querystring in a pre-render block, should it
not be in regular code with the rest of it ? IE
Value = Request.QueryString("Name")
--
OHM ( Terry Burns )
. . . One-Handed-Man . . .
"Miguel Dias Moura" <web001@.27NOSPAMlamps.com> wrote in message
news:%23UCksgXVEHA.2592@.TK2MSFTNGP09.phx.gbl...
> Hi,
> i created a dataSet in an ASP.Net page which:
> 1. Loads all fields from each database record.
> 2. Creates a new field using 2 of the existing fields: FullName =FirstName
> + ' ' + LastName
> 3. Loads only the record which has the same FullName as the variable Name
> passed in the URL to this page.
> My SQL is this:
> SELECT *, FirstName + ' ' + LastName as FullName
> FROM people
> WHERE FirstName + ' ' + LastName = ?
> and:
> <Parameter Name="@.FullName" Value='<%# IIf((Request.QueryString("Name")
<>
> Nothing), Request.QueryString("Name"), "") %>'
> Type="WChar"/></Parameters></MM:DataSet>
> Can you please tell me how to do this?
> I know i allready asked this but until now i got no solution and i need to
> solve this to finish this web site and deliver it.
> I hope i was able to explain better my problem.
> Thank You Very Much,
> Miguel
>
>|||Do you know the strange thing? I tested it and everything works fine...but
when i was testing in the Dreamweaver window where i place the SQL, i was
not getting any records...have no idea why is this...but well, at least it's
working.
Thanks for eveybody help and patiente...my code is working :-)
Thanks again for your time,
Miguel
"One Handed Man ( OHM - Terry Burns )" <news.microsoft.com> wrote in message
news:OtAHCsXVEHA.3596@.tk2msftngp13.phx.gbl...
> Why are you putting the Request.Querystring in a pre-render block, should
it
> not be in regular code with the rest of it ? IE
> Value = Request.QueryString("Name")
> --
> OHM ( Terry Burns )
> . . . One-Handed-Man . . .
>
> "Miguel Dias Moura" <web001@.27NOSPAMlamps.com> wrote in message
> news:%23UCksgXVEHA.2592@.TK2MSFTNGP09.phx.gbl...
> > Hi,
> >
> > i created a dataSet in an ASP.Net page which:
> >
> > 1. Loads all fields from each database record.
> > 2. Creates a new field using 2 of the existing fields: FullName => FirstName
> > + ' ' + LastName
> > 3. Loads only the record which has the same FullName as the variable
Name
> > passed in the URL to this page.
> >
> > My SQL is this:
> >
> > SELECT *, FirstName + ' ' + LastName as FullName
> > FROM people
> > WHERE FirstName + ' ' + LastName = ?
> >
> > and:
> >
> > <Parameter Name="@.FullName" Value='<%#
IIf((Request.QueryString("Name")
> <>
> > Nothing), Request.QueryString("Name"), "") %>'
> > Type="WChar"/></Parameters></MM:DataSet>
> >
> > Can you please tell me how to do this?
> > I know i allready asked this but until now i got no solution and i need
to
> > solve this to finish this web site and deliver it.
> >
> > I hope i was able to explain better my problem.
> >
> > Thank You Very Much,
> > Miguel
> >
> >
> >
>

Really interesting behaviour of ADO

Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to end
and ADO reports 3 seconds from begin to end. It happens about 3 times per 2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?
Are you accounting for the time it takes to open a connection to the
database, and then get results back from SQL Server?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to
end
and ADO reports 3 seconds from begin to end. It happens about 3 times per
2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?
|||1. No I do not, I have connection open, it is just straight execute of SP.
2. There are no resultsets back to client, only OUTPUT Params, the rest 1998 cases have SP and ADO with almost the same time.
"Narayana Vyas Kondreddi" wrote:

> Are you accounting for the time it takes to open a connection to the
> database, and then get results back from SQL Server?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
> news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
> Hell all,
> I am inserting record with times when SP begins and ends
> and I log times when ADO.command.exec starts and ends
> I've noticed that there are situations where SP reports 13 ms from begin to
> end
> and ADO reports 3 seconds from begin to end. It happens about 3 times per
> 2000 tries.
> Note: There are no resultsets, only OUTPUT params.
> SO it looks to me either ADO glitch or network or what?
> Does someone had something like this?
>
>
|||Are you accounting for the time it takes to open a connection to the
database, and then get results back from SQL Server?
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to
end
and ADO reports 3 seconds from begin to end. It happens about 3 times per
2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?
|||1. No I do not, I have connection open, it is just straight execute of SP.
2. There are no resultsets back to client, only OUTPUT Params, the rest 1998 cases have SP and ADO with almost the same time.
"Narayana Vyas Kondreddi" wrote:

> Are you accounting for the time it takes to open a connection to the
> database, and then get results back from SQL Server?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
> news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
> Hell all,
> I am inserting record with times when SP begins and ends
> and I log times when ADO.command.exec starts and ends
> I've noticed that there are situations where SP reports 13 ms from begin to
> end
> and ADO reports 3 seconds from begin to end. It happens about 3 times per
> 2000 tries.
> Note: There are no resultsets, only OUTPUT params.
> SO it looks to me either ADO glitch or network or what?
> Does someone had something like this?
>
>

Really interesting behaviour of ADO

Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to
end
and ADO reports 3 seconds from begin to end. It happens about 3 times per 20
00 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?Are you accounting for the time it takes to open a connection to the
database, and then get results back from SQL Server?
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in message
news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
Hell all,
I am inserting record with times when SP begins and ends
and I log times when ADO.command.exec starts and ends
I've noticed that there are situations where SP reports 13 ms from begin to
end
and ADO reports 3 seconds from begin to end. It happens about 3 times per
2000 tries.
Note: There are no resultsets, only OUTPUT params.
SO it looks to me either ADO glitch or network or what?
Does someone had something like this?|||1. No I do not, I have connection open, it is just straight execute of SP.
2. There are no resultsets back to client, only OUTPUT Params, the rest 199
8 cases have SP and ADO with almost the same time.
"Narayana Vyas Kondreddi" wrote:

> Are you accounting for the time it takes to open a connection to the
> database, and then get results back from SQL Server?
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> Is .NET important for a database professional?
> http://vyaskn.tripod.com/poll.htm
>
> "Ruslan Osmonov" <Ruslan Osmonov@.discussions.microsoft.com> wrote in messa
ge
> news:C6C63E47-DD24-449E-9885-68663277B034@.microsoft.com...
> Hell all,
> I am inserting record with times when SP begins and ends
> and I log times when ADO.command.exec starts and ends
> I've noticed that there are situations where SP reports 13 ms from begin t
o
> end
> and ADO reports 3 seconds from begin to end. It happens about 3 times per
> 2000 tries.
> Note: There are no resultsets, only OUTPUT params.
> SO it looks to me either ADO glitch or network or what?
> Does someone had something like this?
>
>