asked on

Conversion from type 'DBNull' to type 'Boolean' is not valid-SQL to ASP

I'm getting this error:
Conversion from type 'DBNull' to type 'Boolean' is not valid.

Exception Details: System.InvalidCastException: Conversion from type 'DBNull' to type 'Boolean' is not valid.

As you can see, the fields that I'm trying to access are null, and I don't know how to handle null values in these fields. How do I deal with this?

<div id="page" style="text-align: left">
            <p style="text-align: left" id="P1" runat="server">
                </p>
            <p>
                <table style="width: 100%; height: 100%">
                    <tr>
                        <td style="width: 100px; height: 288px;">
                            <span><strong><asp:DetailsView ID="DetailsView2" runat="server" AutoGenerateRows="False"
                                DataSourceID="Client_Name" Height="26px"
                                Width="353px" BorderColor="Transparent" BorderStyle="None" CellPadding="0" >
                                <Fields>
                                    <asp:BoundField DataField="Patient Name" ReadOnly="True" 
                                        SortExpression="Patient Name" HeaderText="Patient Name"/>
                                </Fields>
                            </asp:DetailsView>

                            </strong>
                            </span><span><span><asp:DetailsView ID="DetailsView1" runat="server"
                                AutoGenerateRows="False" DataSourceID="qry_Enrollment" Height="50px" Width="568px" DefaultMode="Edit">
                                <Fields>
												<asp:boundfield DataField="Patient_Id" HeaderText="Patient_Id" SortExpression="Patient_Id">
																<ControlStyle BorderStyle="None" Width="0px" />
												</asp:boundfield>
												<asp:boundfield DataField="DetailID" HeaderText="DetailID" SortExpression="DetailID">
																<ControlStyle BorderStyle="None" Width="0px" />
												</asp:boundfield>
												
												<asp:templatefield HeaderText="Package Billed" 
                                                    SortExpression="Prenatal_Package_Billed">
																<EditItemTemplate>
                                                                     <asp:CheckBox ID="CheckBox100" runat="server" Checked='<%# Bind("Prenatal_Package_Billed") %>' />
    															</EditItemTemplate>
													</asp:templatefield>
													
													
												<asp:templatefield HeaderText="Delivery Billed" 
                                                    SortExpression="Prenatal_Delivery_Billed">
																<EditItemTemplate>
                                                                    <asp:CheckBox ID="CheckBox101" runat="server" Checked='<%# Bind("Prenatal_Delivery_Billed") %>' />
																</EditItemTemplate>
													</asp:templatefield>													
													
																								
												<asp:templatefield ShowHeader="False">
																<EditItemTemplate>
																				<asp:Button ID="Button1" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />&nbsp;
																</EditItemTemplate>
																<ItemTemplate>
																				<asp:Button ID="Button1" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" />
																</ItemTemplate>
												</asp:templatefield>
								</Fields>
                            </asp:DetailsView>

                                </span></span><asp:SqlDataSource id="qry_Enrollment" runat="server" ConnectionString="<%$ ConnectionStrings:MIHP_MaternalConnectionString %>" SelectCommand="SELECT Patient_Id, DetailID, Prenatal_Package_Billed, Prenatal_Delivery_Billed FROM Detail WHERE (Patient_Id = @Patient_Id) AND (DetailID = @DetailID)" UpdateCommand='UPDATE Detail SET Prenatal_Package_Billed = @Prenatal_Package_Billed,&#13;&#10;Prenatal_Delivery_Billed = @Prenatal_Delivery_Billed WHERE (Patient_ID = @Patient_Id) and (DetailID = @DetailID)'>


<SelectParameters>
                                   <asp:QueryStringParameter Name="Patient_Id" QueryStringField="Patient_Id" />
    <asp:QueryStringParameter Name="DetailID" QueryStringField="DetailId" />
                               </SelectParameters>
                                 <UpdateParameters>
                                     <asp:Parameter Name="Prenatal_Package_Billed" />
                                     <asp:Parameter Name="Prenatal_Delivery_Billed" />
                                    <asp:ControlParameter Name="Patient_ID" ControlID="DetailsView1" PropertyName="SelectedValue" />
                                     <asp:Parameter Name="DetailID" />
                                  </UpdateParameters>
                           </asp:SqlDataSource>

                            <asp:SqlDataSource ID="Client_Name" runat="server" ConnectionString="<%$ ConnectionStrings:MIHP_MaternalConnectionString %>"
                                SelectCommand="SELECT (Upper(Patient_Last_Name)+', '+ Upper(Patient_First_Name)) as [Patient Name]&#13;&#10;&#13;&#10;FROM VADER.Chorus_Reporting.dbo.Patient_Personal AS Patient_Personal_1 &#13;&#10;WHERE (Patient_Id = @Patient_Id)">
                                <SelectParameters>
                                    <asp:QueryStringParameter Name="Patient_Id" QueryStringField="Patient_Id" />
                                </SelectParameters>
                            </asp:SqlDataSource>

Open in new window

ASKER CERTIFIED SOLUTION

meetingexpectations

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

IntercareSupport

ASKER

Thanks! That was a much simpler solution. I did have to make a few adjustments. Here they are...

SELECT Patient_Id, DetailID, CASE Prenatal_Package_Billed WHEN 1 THEN 1 ELSE 0 END AS Prenatal_Package_Billed, CASE Prenatal_Delivery_Billed WHEN 1 THEN 1 ELSE 0 END AS Prenatal_Delivery_Billed FROM Detail WHERE (Patient_Id = @Patient_Id) AND (DetailID = @DetailID)

Thanks again.