?
Solved

check table row prior to insert database record

Posted on 2011-05-13
1
Medium Priority
?
250 Views
Last Modified: 2012-06-21
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:

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

 <!-- Panel displaying buildings new starts here-->
 <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">&nbsp;
                   <div class="requiredfield">* indicates required fields </div> </td>
                         
                   </tr>
                         <tr>
                   <td valign="top" align="left">&nbsp;<div class="requiredfield"> Building Code *</div></td>
                    <td valign="top" align="left">&nbsp;<div class="requiredfield">Building Name *</div> </td>
                   </tr>
                   
                         <tr>
                   
                    <td valign="top" align="left">&nbsp;
<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">&nbsp;
                    <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">&nbsp;
<asp:Button ID="btn_maint_building_add"
                       runat="server" Text="Add Building" onclick="btn_maint_building_add_Click" /></td>
                    <td valign="top" align="left">&nbsp;
                    <asp:Button ID="btn_maint_building_cancel" runat="server"
                        CausesValidation="False" onclick="btn_maint_building_cancel_Click"
                        Text="Cancel" />
                    </td>
                   </tr>
                 
      
                   </table>                                   
</asp:Panel>

<!-- Panel displaying buildings new ends here-->      
---------------

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

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

<!-- Panel displaying floors new starts here-->      

                   <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">&nbsp; </td>
                         
                   </tr>
                         <tr>
                   <td valign="top" align="left">&nbsp; Building Code</td>
                    <td valign="top" align="left">&nbsp;Floor Name </td>
                   </tr>
                   
                         <tr>
                   
                    <td valign="top" align="left">&nbsp;
<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>&nbsp;&nbsp;
                    <asp:Label ID="lbl_maint_buildingCode_to_add_newfloor" runat="server" Text=" " BackColor="Yellow"></asp:Label></td><td valign="top" align="left">&nbsp;
                    <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">&nbsp;
<asp:Button ID="btn_maint_floor_add" runat="server" Text="Add Floor" onclick="btn_maint_floor_add_Click" /></td>
                    <td valign="top" align="left">&nbsp;
                    <asp:Button ID="btn_maint_floor_cancel" runat="server"
                        CausesValidation="False" onclick="btn_maint_floor_cancel_Click"
                       
                       
                        Text="Cancel" />
                    </td>
                   </tr>
                   </table>
             </asp:Panel>
                   
                   <!-- Panel displaying floors new starts here-->      

                   
                    <!-- Panel displaying floors starts here-->  
     <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">&nbsp; </td>    
                   </tr>
                   <tr>
                   <td valign="top" align="left">&nbsp;   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">&nbsp; </td>    
                   </tr>
                   
                   
                   </table>
     
     </asp:Panel>
    <!-- Panel displaying floors ends here-->  
         --------------------

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.
0
Comment
Question by:Codeaddict7423
1 Comment
 
LVL 12

Accepted Solution

by:
starlite551 earned 2000 total points
ID: 35759630
Well the solution is simple :
1. First of all don`t write direct codes in the button's click events.. Create methods for doing so.
2. Create a method that will check for a building if it exists and return a corresponding boolean value to justify that.The return type of the method will be bool.
3. If the building exists i.e If the method returns true then call Method to add floors to that building.

You need to first check the table named [CodeTable_Building] for existing Building_Code and then only add a floor to the table named [CodeTable_Floor] with Building_Id,Building_Code and Floor_Name values. Here is the optimized code :
 
protected void btn_maint_building_add_Click(object sender, EventArgs e)
{
	AddBuilding();            
}

protected void lnkbtn_maint_floor_add_Click(object sender, EventArgs e)
{    
	pnl_maint_floor_new.Visible=true;
}

protected void btn_maint_floor_add_Click(object sender, EventArgs e)
{
        if(CheckExistingBuilding(lbl_maint_buildingCode_to_add_newfloor.Text)==true)
        {
		AddFloor();
	}
	else
	{
		Response.Write("<script>alert('Building does not exists!')</script>");
	}
}

protected void AddBuilding()
{
	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 AddFloor()
{

	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");

}

protected bool CheckExistingBuilding(string building_code)
{
	int count = 0;
	bool result = false;

	SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DetentionDWLConnectionString"].ConnectionString);
        SqlCommand cmd = Conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        Conn.Open();

        cmd.CommandText = @"Select Count(*) From [CodeTable_Building] Where [Building_Code]=@Building_Code";
        cmd.Parameters.AddWithValue("@Building_Code", building_code);

	count = int.Parse(cm.ExecuteScalar().ToString()); 
        Conn.Close(); //Close the connection with database
	if(count>0)
	{
		result = true;
	}

	return result;
}

Open in new window

0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month16 days, 14 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question