asked on

DBNULL Error

I need to get rid of a DBNull erro that pops up when my sql code is run. Basically in the follwoing sql it fails if there is no bitPresent for the selected dtAttendance in the database. The date will be there but if the bitPresent is not marked the page fails and calls up a DBNull error. How do I make it return nothing or not error out.

select max(Enlisted) as 'Enlisted', max(WOOfficer) as 'WOOfficer', max(Officer) as 'Officer', max(Total) as 'Total' " _
& "from (Select Count(p.strssn)as Total, " _
& "SUM(CASE WHEN s.sidstrPAY_GR in ('E1', 'E2', 'E3', 'E4', 'E5', 'E6', 'E7', 'E8', 'E9') THEN 1 ELSE 0 END) as Enlisted, " _
& "SUM(CASE WHEN s.sidstrPAY_GR in ('WO1', 'W2', 'W3', 'W4', 'W5') THEN 1 ELSE 0 END) as WOOfficer, " _
& "SUM(CASE WHEN s.sidstrPAY_GR in ('O1', 'O2', 'O3', 'O4', 'O5', 'O6', 'O7', 'O8') THEN 1 ELSE 0 END) as Officer " _
& "from tblAssignedPersonnel as p INNER JOIN cms.dbo.tblSidpers as s on s.sidstrSSN_SM = p.strSSN Where bitpresent = 1 " _
& "and dtAttendance = '" & PickDate & "' and IntUIcId In (Select intUicId from tblUIC where inttaskforceID = " & taskForceId & " " _
& "and strUic = '" & UicId & "') " _

Guy Hengel [angelIII / a3]

as you don't return the bitpresent column, but just use it in the query, your query won't error on that column itself?

can you show the relevant code and the line it errors out?
I must assume so far that you error out on a empty reader ...

kdeutsch

ASKER

Here is the error it gives me, the code points to my lblEnlisted.text.rows.(0)(0). From this point I am assuming since it counts popel and there are no people present this is the reason it is erroring out. When I put attendance to the bitPresent it works just fine.

Cast from type 'DBNull' to type 'String' is not valid.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.]
Microsoft.VisualBasic.CompilerServices.StringType.FromObject(Object Value) +616
SADDotNet1.DailyPersonnelSummary.ddlDate_SelectedIndexChanged(Object sender, EventArgs e) in R:\Inetpub\wwwroot\SADDotNet1\Reports\DailyPersonnelSummary.aspx.vb:141
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +108
System.Web.UI.WebControls.DropDownList.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +26
System.Web.UI.Page.RaiseChangedEvents() +115
System.Web.UI.Page.ProcessRequestMain() +1099

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

kdeutsch

ASKER

Sorry, I should have posted all mydatatable rows along with original post. lblenlisted.text is the first row in my data table. The solution up above corrects my mistake in the sql code. Thanks