asked on

check table row prior to insert database record

Hello,

I have a maintenance page that allows users to insert buildings to a database table. In this same page, I have a textbox to allow users to add floors to a building.

However, I am attempting to write code that checks for the existence of buildings (perhaps by looking at a ddl control or a database table) so that users cannot add floors to a non-existence building. in other words, so that each floor has to have an associated building.

My aspx code follows:

-------------


<asp:Panel ID="pnl_maint_building_new" runat="server" BackColor="#dedede">

<table width="100%" cellpadding="0" cellspacing="0" border="0">
              <tr>
              <td colspan="2" valign="top" align="center"> 
<div class="requiredfield">* indicates required fields </div> </td>

              </tr>
                         <tr>
              <td valign="top" align="left"> <div class="requiredfield"> Building Code *</div></td>
              <td valign="top" align="left"> <div class="requiredfield">Building Name *</div> </td>
              </tr>

                         <tr>

              <td valign="top" align="left"> 
<asp:TextBox ID="txt_maint_buidling_code_add" runat="server"></asp:TextBox>
<br />
<asp:RequiredFieldValidator ID="RFV_building_code_add" runat="server" ErrorMessage="Must Enter Building Code" ControlToValidate="txt_maint_buidling_code_add"></asp:RequiredFieldValidator>
</td>
<td valign="top" align="left"> 
<asp:TextBox ID="txt_maint_building_name_add" runat="server"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="RFV_building_name_add" runat="server" ErrorMessage="Must Enter Building Name" ControlToValidate="txt_maint_building_name_add"></asp:RequiredFieldValidator>

</td>
              </tr>

              <tr>
              <td valign="top" align="left"> 
<asp:Button ID="btn_maint_building_add"
runat="server" Text="Add Building" onclick="btn_maint_building_add_Click" /></td>
              <td valign="top" align="left"> 
<asp:Button ID="btn_maint_building_cancel" runat="server"
CausesValidation="False" onclick="btn_maint_building_cancel_Click"
Text="Cancel" />
</td>
              </tr>


              </table>
</asp:Panel>


---------------

My codebehind to add building follows:

---------------
protected void btn_maint_building_add_Click(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DetentionDWLConnectionString"].ConnectionString);
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
Conn.Open();

cmd.CommandText = @"INSERT INTO [CodeTable_Building] ([Building_Code], [Building_Name]) VALUES (@Building_Code, @Building_Name)";
cmd.Parameters.AddWithValue("@Building_Code", txt_maint_buidling_code_add.Text);
cmd.Parameters.AddWithValue("@Building_Name", txt_maint_building_name_add.Text);

cmd.ExecuteNonQuery();
Conn.Close(); //Close the connection with database

//fv_building.DataBind();
//ddl_maint_building.DataBind();

txt_maint_buidling_code_add.Text = " ";
txt_maint_building_name_add.Text = " ";

// Response.Redirect("mainteannce_page_02.aspx");
// Bind_gv_maint_building();
gv_maint_building_add.DataBind();

gv_maint_building_add.Visible = false;

GridView1.DataBind();
GridView1.Visible = true;
pnl_maint_building_nested.Visible = true;

Response.Redirect("maintenance_page04.aspx");

}

protected void lnkbtn_maint_floor_add_Click(object sender, EventArgs e)
{

pnl_maint_floor_new.Visible=true;

}

-----------------------

The aspx code for adding floors follows

-----------------------



<asp:Panel ID="pnl_maint_floor_new" runat="server" BackColor="#deedde">

<table width="100%" cellpadding="0" cellspacing="0" border="0">
              <tr>
              <td colspan="2" valign="top" align="left">  </td>

              </tr>
                         <tr>
              <td valign="top" align="left">  Building Code</td>
              <td valign="top" align="left"> Floor Name </td>
              </tr>

                         <tr>

              <td valign="top" align="left"> 
<asp:DropDownList ID="ddl_maint_bldg" runat="server" AutoPostBack="True"
onselectedindexchanged="ddl_maint_bldg_SelectedIndexChanged"
DataSourceID="ds_maint_building_to_add_floor" DataTextField="Building_Code"
DataValueField="Building_ID">
<asp:ListItem Text="--Select Building--"></asp:ListItem>
</asp:DropDownList>

<asp:SqlDataSource ID="ds_maint_building_to_add_floor" runat="server"
ConnectionString="<%$ ConnectionStrings:DetentionDWLConnectionString %>"
SelectCommand="SELECT [Building_ID], [Building_Code] FROM [CodeTable_Building] ORDER BY [Building_Code]">
</asp:SqlDataSource>

<br />
<asp:Label ID="lbl_maint_buildingID_to_add_newfloor" runat="server" Text=" " BackColor="blue"></asp:Label>  
<asp:Label ID="lbl_maint_buildingCode_to_add_newfloor" runat="server" Text=" " BackColor="Yellow"></asp:Label></td><td valign="top" align="left"> 
<asp:TextBox ID="txt_maint_floor_add" runat="server"
ontextchanged="txt_maint_floor_add_TextChanged"></asp:TextBox><br />
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Enter Floor Name" ControlToValidate="txt_maint_floor_add"></asp:RequiredFieldValidator></td></tr><tr>
              <td valign="top" align="left"> 
<asp:Button ID="btn_maint_floor_add" runat="server" Text="Add Floor" onclick="btn_maint_floor_add_Click" /></td>
              <td valign="top" align="left"> 
<asp:Button ID="btn_maint_floor_cancel" runat="server"
CausesValidation="False" onclick="btn_maint_floor_cancel_Click"

Text="Cancel" />
</td>
              </tr>
              </table>
       </asp:Panel>

              


              
<asp:Panel ID="pnl_maint_floor_add" runat="server" BackColor="#dedede">
       <table width="100%" cellpadding="0" cellspacing="0" border="0">
              <tr>
              <td valign="top" align="left">  </td>
              </tr>
              <tr>
              <td valign="top" align="left">  gridview below is for displaying floors

<asp:GridView ID="gv_maint_floor" runat="server" AutoGenerateColumns="False"
CellPadding="4" DataKeyNames="Floor_ID" DataSourceID="ds_maint_floors"
Font-Names="Arial" Font-Size="Small" ForeColor="#333333" GridLines="None"
Height="54px" onselectedindexchanged="gv_maint_floor_SelectedIndexChanged"
Width="460px" AllowSorting="True">
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<Columns>
<asp:CommandField ShowEditButton="True" ShowSelectButton="True" />
<asp:BoundField DataField="Floor_ID" HeaderText="Floor_ID"
SortExpression="Floor_ID" InsertVisible="False" ReadOnly="True" />
<asp:BoundField DataField="Building_ID" HeaderText="Building_ID"
SortExpression="Building_ID" />
<asp:BoundField DataField="Building_Code" HeaderText="Building_Code"
SortExpression="Building_Code" />
<asp:BoundField DataField="Floor_Name" HeaderText="Floor_Name"
SortExpression="Floor_Name" />
</Columns>
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="ds_maint_floors" runat="server"
ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:DetentionDWLConnectionString %>"
DeleteCommand="DELETE FROM [CodeTable_Floor] WHERE [Floor_ID] = @original_Floor_ID AND (([Building_ID] = @original_Building_ID) OR ([Building_ID] IS NULL AND @original_Building_ID IS NULL)) AND (([Floor_Name] = @original_Floor_Name) OR ([Floor_Name] IS NULL AND @original_Floor_Name IS NULL))"
InsertCommand="INSERT INTO [CodeTable_Floor] ([Building_ID], [Floor_Name]) VALUES (@Building_ID, @Floor_Name)"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT CodeTable_Floor.Floor_ID, CodeTable_Floor.Building_ID, CodeTable_Building.Building_Code, CodeTable_Floor.Floor_Name FROM CodeTable_Floor INNER JOIN CodeTable_Building ON CodeTable_Floor.Building_ID = CodeTable_Building.Building_ID"

UpdateCommand="UPDATE [CodeTable_Floor] SET [Building_ID] = @Building_ID, [Floor_Name] = @Floor_Name WHERE [Floor_ID] = @original_Floor_ID AND (([Building_ID] = @original_Building_ID) OR ([Building_ID] IS NULL AND @original_Building_ID IS NULL)) AND (([Floor_Name] = @original_Floor_Name) OR ([Floor_Name] IS NULL AND @original_Floor_Name IS NULL))">
<DeleteParameters>
<asp:Parameter Name="original_Floor_ID" Type="Int32" />
<asp:Parameter Name="original_Building_ID" Type="Int32" />
<asp:Parameter Name="original_Floor_Name" Type="String" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="Building_ID" Type="Int32" />
<asp:Parameter Name="Floor_Name" Type="String" />
<asp:Parameter Name="original_Floor_ID" Type="Int32" />
<asp:Parameter Name="original_Building_ID" Type="Int32" />
<asp:Parameter Name="original_Floor_Name" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="Building_ID" Type="Int32" />
<asp:Parameter Name="Floor_Name" Type="String" />
</InsertParameters>
</asp:SqlDataSource>

</td>
              </tr>
              <tr>
              <td valign="top" align="left">  </td>
              </tr>


              </table>

</asp:Panel>

--------------------

The codebehind to insert floors follows:
---------------------------

protected void btn_maint_floor_add_Click(object sender, EventArgs e)
{
SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DetentionDWLConnectionString"].ConnectionString);
SqlCommand cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.Text;
Conn.Open();

cmd.CommandText = @"INSERT INTO [CodeTable_Floor] ([Building_ID], [Building_Code], [Floor_Name]) VALUES (@Building_ID, @Building_Code, @Floor_Name)";
cmd.Parameters.AddWithValue("@Building_ID", lbl_maint_buildingID_to_add_newfloor.Text);
cmd.Parameters.AddWithValue("@Building_Code", lbl_maint_buildingCode_to_add_newfloor.Text);
cmd.Parameters.AddWithValue("@Floor_Name", txt_maint_floor_add.Text);

cmd.ExecuteNonQuery();
Conn.Close(); //Close the connection with database

//fv_building.DataBind();
//ddl_maint_building.DataBind();

txt_maint_buidling_code_add.Text = " ";
txt_maint_building_name_add.Text = " ";

// Response.Redirect("mainteannce_page_02.aspx");
// Bind_gv_maint_building();
gv_maint_building_add.DataBind();

gv_maint_building_add.Visible = false;

GridView1.DataBind();
GridView1.Visible = true;
pnl_maint_building_nested.Visible = true;

Response.Redirect("maintenance_page04.aspx");

}

---------------------

ANY help with this logic would be very much appreciated.

ASKER CERTIFIED SOLUTION

starlite551

membership

This solution is only available to members.

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

Start Free Trial