CODEWITHSUNDEEP
c#sqlsql-server-2008
dsumner
dsumner

Reputation: 31

SqlCommand SQL statement being executed twice

I'm creating an ASP.NET app using C# and every time I try to do an SQL INSERT using SqlCommand, the command is executed twice. I implemented what was in this post (SQL Insert query is executed twice), but it still doesn't help.

Here is the code:

protected void btn_Add_Click(object sender, EventArgs e)
{
        error.InnerHtml = " ";

        SqlConnection MyConn = new SqlConnection("Data Source=.\\SQLEXPRESS;"
            + "Initial Catalog=SIMON;"
            + "Persist Security Info=True;"
            + "User ID=username;Password=password");

        string sqlStatement = @"INSERT INTO [Network_Equipment] "
            + "([Network_Equipment_Name], [Network_Equipment_Type_ID], [IP_Address], [FQDN], [NetBIOS_Name], [Building_ID], [Description])"
            + " VALUES "
            + "(@Network_Equipment_Name, @Network_Equipment_Type_ID, @IP_Address, @FQDN, @NetBIOS_Name, @Building_ID, @Description)";

            SqlCommand sqlCmd = new SqlCommand(sqlStatement, MyConn);
            sqlCmd.Connection = MyConn;

            sqlCmd.Parameters.AddWithValue("@Network_Equipment_Name", ((in_Add_Equipment.Value == null) ? (object)DBNull.Value : (object)in_Add_Equipment.Value));
            sqlCmd.Parameters.AddWithValue("@Network_Equipment_Type_ID", ((ddl_Equipment_Type.SelectedValue == null) ? (object)DBNull.Value : (object)ddl_Equipment_Type.SelectedValue));
            sqlCmd.Parameters.AddWithValue("@IP_Address", ((in_IP_Address.Value == null) ? (object)DBNull.Value : (object)in_IP_Address.Value));
            sqlCmd.Parameters.AddWithValue("@FQDN", ((in_FQDN.Value == null) ? (object)DBNull.Value : (object)in_FQDN.Value));
            sqlCmd.Parameters.AddWithValue("@NetBIOS_Name", ((in_NetBIOS.Value == null) ? (object)DBNull.Value : (object)in_NetBIOS.Value));
            sqlCmd.Parameters.AddWithValue("@Building_ID", ((ddl_Building.SelectedValue == null) ? (object)DBNull.Value : (object)ddl_Building.SelectedValue));
            sqlCmd.Parameters.AddWithValue("@Description", ((ta_Description.Value == null) ? (object)DBNull.Value : (object)ta_Description.Value));


            try
            {
                MyConn.Open();
            }
            catch (Exception er)
            {
                error.InnerHtml = er.ToString();
                return;
            }

            try
            {
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception er)
            {
                error.InnerHtml = er.ToString();
                return;
            }

            error.InnerHtml = "Successfully added: " + in_Add_Equipment.Value;

            sqlCmd.Dispose();
            sqlCmd = null;
            MyConn.Close();

            in_Add_Equipment.Value = "";
            ddl_Equipment_Type.SelectedIndex = 0;
            in_IP_Address.Value = "";
            in_FQDN.Value = "";
            in_NetBIOS.Value = "";
            ddl_Building.SelectedIndex = 0;
            ta_Description.Value = "";

            GridView1.DataBind();
}

I've run it in Debug mode and sqlCmd.ExecuteNonQuery() is only called once, yet I have two identical entries being added.

What am I missing and/or doing wrong?

-------- EDIT --------

I used a modified version of john ryan's code, but it was still added twice.

Here is ALL the code that goes with this:

Equipment.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Equipment.aspx.cs" Inherits="SIMON.Equipment" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script language="javascript" type="text/javascript">
        // disables the button specified and sets its style to a disabled "look".
        function disableButtonOnClick(oButton, sButtonText, sCssClass) {
            oButton.disabled = true; // set button to disabled so you can't click on it.
            oButton.value = sButtonText; // change the text of the button.
            oButton.setAttribute('className', sCssClass); // IE uses className for the css property.
            oButton.setAttribute('class', sCssClass); // Firefox, Safari use class for the css property. (doesn't hurt to do both).
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
        <asp:UpdatePanel ID="UpdatePanel2" runat="server">
            <ContentTemplate>
                <div id="error" runat="server">&nbsp;</div>
                <div>
                    <fieldset style="display: inline-block; padding: 5px; -moz-border-radius: 8px; border-radius: 8px;">
                    <legend style="text-decoration: underline;">[ Add Equipment ]</legend>
                        <table cellpadding="3px" style="border: 0px; width: 300px;">
                            <tbody>
                                <tr>
                                    <td style="text-align: left;"><label for="in_Add_Equipment" style="white-space: nowrap;">Equipment Name</label></td>
                                    <td style="text-align: right;"><input id="in_Add_Equipment" runat="server" size="20" /></td>
                                    <td style="text-align: left;"><label for="ddl_Equipment_Type" style="white-space: nowrap;">Equipment Type</label></td>
                                    <td style="text-align: right;">
                                        <asp:DropDownList ID="ddl_Equipment_Type" 
                                            runat="server" DataSourceID="Network_Equipment_Type" DataTextField="Network_Equipment_Type" 
                                            DataValueField="Network_Equipment_Type_ID"></asp:DropDownList>
                                    </td>
                                </tr>
                                <tr>
                                    <td style="text-align: left;"><label for="in_IP_Address" style="white-space: nowrap;">IP Address</label></td>
                                    <td style="text-align: right;"><input id="in_IP_Address" runat="server" size="20" class="OnlyNumeric" /></td>
                                    <td style="text-align: left;"><label for="in_FQDN" title="Fully Qualified Domain Name" style="white-space: nowrap;">FQDN</label></td>
                                    <td style="text-align: right;"><input id="in_FQDN" runat="server" size="20" /></td>
                                </tr>
                                <tr>
                                    <td style="text-align: left;"><label for="in_NetBIOS" style="white-space: nowrap;">NetBIOS Name</label></td>
                                    <td style="text-align: right;"><input id="in_NetBIOS" runat="server" size="20" /></td>
                                    <td style="text-align: left;"><label for="ddl_Building" style="white-space: nowrap;">Building</label></td>
                                    <td style="text-align: right;">
                                        <asp:DropDownList ID="ddl_Building" runat="server" 
                                            DataSourceID="Buildings" DataTextField="Building_Name" 
                                            DataValueField="Building_ID"></asp:DropDownList>
                                    </td>
                                </tr>
                                <tr>
                                    <td style="text-align: left;"><label for="ta_Description" style="white-space: nowrap;">Description</label></td>
                                    <td style="text-align: right;" colspan="3"><textarea id="ta_Description" runat="server" cols="50" rows="3"></textarea></td>
                                </tr>
                                <tr>
                                    <td colspan="4" style="text-align: right;">
                                        <asp:Button ID="btn_Add" Text="Add" CssClass="ui-state-default ui-corner-all" runat="server" OnClick="btn_Add_Click" />
                                    </td>
                                </tr>
                            </tbody>
                        </table>
                    </fieldset>
                </div>
            </ContentTemplate>
        </asp:UpdatePanel>
        <p></p>
        <fieldset style="display: inline-block; padding: 5px; -moz-border-radius: 8px; border-radius: 8px;">
        <legend style="text-decoration: underline;">[ Update Equipment ]</legend>
           <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>
                    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                        ConnectionString="<%$ ConnectionStrings:SIMON-Writer %>" 
                        DeleteCommand="DELETE FROM [Network_Equipment] WHERE [Network_Equipment_ID] = @original_Network_Equipment_ID" 
                        OldValuesParameterFormatString="original_{0}" 
                        SelectCommand="SELECT * FROM [All_Network_Equipment-Simplified]" 

                        UpdateCommand="UPDATE [Network_Equipment] SET [Network_Equipment_Name] = @Network_Equipment_Name, [Network_Equipment_Type_ID] = @Network_Equipment_Type_ID, [IP_Address] = @IP_Address, [FQDN] = @FQDN, [NetBIOS_Name] = @NetBIOS_Name, [Building_ID] = @Building_ID, [Description] = @Description, [Active] = @Active WHERE [Network_Equipment_ID] = @original_Network_Equipment_ID">
                        <DeleteParameters>
                            <asp:Parameter Name="original_Network_Equipment_ID" Type="Int32" />
                        </DeleteParameters>
                        <InsertParameters>
                            <asp:Parameter Name="Network_Equipment_Name" Type="String" />
                            <asp:Parameter Name="Network_Equipment_Type_ID" Type="Int32" />
                            <asp:Parameter Name="IP_Address" Type="String" />
                            <asp:Parameter Name="FQDN" Type="String" />
                            <asp:Parameter Name="NetBIOS_Name" Type="String" />
                            <asp:Parameter Name="Building_ID" Type="Int32" />
                            <asp:Parameter Name="Description" Type="String" />
                            <asp:Parameter Name="Active" Type="Boolean" />
                        </InsertParameters>
                        <UpdateParameters>
                            <asp:Parameter Name="Network_Equipment_Name" Type="String" />
                            <asp:Parameter Name="Network_Equipment_Type_ID" Type="Int32" />
                            <asp:Parameter Name="IP_Address" Type="String" />
                            <asp:Parameter Name="FQDN" Type="String" />
                            <asp:Parameter Name="NetBIOS_Name" Type="String" />
                            <asp:Parameter Name="Building_ID" Type="Int32" />
                            <asp:Parameter Name="Description" Type="String" />
                            <asp:Parameter Name="Active" Type="Boolean" />
                            <asp:Parameter Name="original_Network_Equipment_ID" Type="Int32" />
                        </UpdateParameters>
                    </asp:SqlDataSource>
                    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
                        CellPadding="4" DataKeyNames="Network_Equipment_ID" 
                        DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None">
                        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                        <Columns>
                            <asp:BoundField DataField="Network_Equipment_ID" 
                                HeaderText="Network_Equipment_ID" ReadOnly="True" 
                                SortExpression="Network_Equipment_ID" Visible="False" />
                            <asp:BoundField DataField="Network_Equipment_Name" HeaderText="Equipment Name" SortExpression="Network_Equipment_Name" />
                            <asp:BoundField DataField="Network_Equipment_Type_ID" HeaderText="Network_Equipment_Type_ID" SortExpression="Network_Equipment_Type_ID" Visible="False" />
                            <asp:TemplateField HeaderText="Equipment Type" SortExpression="Network_Equipment_Type">
                                <EditItemTemplate>
                                    <asp:DropDownList ID="DropDownList1" runat="server" 
                                        DataSourceID="Network_Equipment_Type" DataTextField="Network_Equipment_Type" 
                                        DataValueField="Network_Equipment_Type_ID" 
                                        SelectedValue='<%# Bind("Network_Equipment_Type_ID") %>'>
                                    </asp:DropDownList>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("Network_Equipment_Type") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:BoundField DataField="IP_Address" HeaderText="IP Address" SortExpression="IP_Address" />
                            <asp:BoundField DataField="FQDN" HeaderText="FQDN" SortExpression="FQDN" />
                            <asp:BoundField DataField="NetBIOS_Name" HeaderText="NetBIOS Name" SortExpression="NetBIOS_Name" />
                            <asp:BoundField DataField="Building_ID" HeaderText="Building_ID" SortExpression="Building_ID" Visible="False" />
                            <asp:TemplateField HeaderText="Building" SortExpression="Building_Name">
                                <EditItemTemplate>
                                    <asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="Buildings" 
                                        DataTextField="Building_Name" DataValueField="Building_ID" 
                                        SelectedValue='<%# Bind("Building_ID") %>'>
                                    </asp:DropDownList>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Building_Name") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:TemplateField HeaderText="Description" SortExpression="Description">
                                <EditItemTemplate>
                                    <asp:TextBox ID="TextBox3" runat="server" Height="50px" Text='<%# Bind("Description") %>' TextMode="MultiLine" Width="200px"></asp:TextBox>
                                </EditItemTemplate>
                                <ItemTemplate>
                                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("Description") %>'></asp:Label>
                                </ItemTemplate>
                            </asp:TemplateField>
                            <asp:CheckBoxField DataField="Active" HeaderText="Active" SortExpression="Active" />
                            <asp:CommandField ShowEditButton="True" />
                        </Columns>
                        <EditRowStyle BackColor="#999999" />
                        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                        <SortedAscendingCellStyle BackColor="#E9E7E2" />
                        <SortedAscendingHeaderStyle BackColor="#506C8C" />
                        <SortedDescendingCellStyle BackColor="#FFFDF8" />
                        <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
                    </asp:GridView>
                    <br />
                    <asp:SqlDataSource ID="Network_Equipment_Type" runat="server" ConnectionString="<%$ ConnectionStrings:SIMON-Reader %>" 
                        SelectCommand="SELECT DISTINCT * FROM [Network_Equipment_Types] ORDER BY [Network_Equipment_Type]">
                    </asp:SqlDataSource>
                    <asp:SqlDataSource ID="Buildings" runat="server" ConnectionString="<%$ ConnectionStrings:SIMON-Reader %>" 
                        SelectCommand="SELECT DISTINCT * FROM [Buildings] ORDER BY [Building_Name]">
                    </asp:SqlDataSource>
                </ContentTemplate>
            </asp:UpdatePanel>
        </fieldset>
    </div>
    </form>
</body>
</html>

Equipment.aspx.cs

namespace SIMON
{
    public partial class Equipment : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                PostBackOptions optionsSubmit = new PostBackOptions(btn_Add);
                btn_Add.OnClientClick = "disableButtonOnClick(this, 'Please wait...', 'disabled_button ui-corner-all'); ";
                btn_Add.OnClientClick += ClientScript.GetPostBackEventReference(optionsSubmit);
            }
        }

        protected void btn_Add_Click(object sender, EventArgs e)
        {
            error.InnerHtml = "&nbsp;";

            SqlTransaction trans = null;
            using (SqlConnection MyConn = new SqlConnection(ConfigurationManager.ConnectionStrings["SIMON-Writer"].ToString()))
            {

                MyConn.Open();
                trans = MyConn.BeginTransaction();

                string sqlStatement = "INSERT INTO [Network_Equipment] "
                    + "([Network_Equipment_Name], [Network_Equipment_Type_ID], [IP_Address], [FQDN], [NetBIOS_Name], [Building_ID], [Description])"
                    + " VALUES "
                    + "(@Network_Equipment_Name, @Network_Equipment_Type_ID, @IP_Address, @FQDN, @NetBIOS_Name, @Building_ID, @Description)";

                using (SqlCommand sqlCmd = new SqlCommand(sqlStatement, MyConn))
                {

                    sqlCmd.Transaction = trans;
                    sqlCmd.Connection = MyConn;

                    sqlCmd.Parameters.AddWithValue("@Network_Equipment_Name", ((in_Add_Equipment.Value == null) ? (object)DBNull.Value : (object)in_Add_Equipment.Value));
                    sqlCmd.Parameters.AddWithValue("@Network_Equipment_Type_ID", ((ddl_Equipment_Type.SelectedValue == null) ? (object)DBNull.Value : (object)ddl_Equipment_Type.SelectedValue));
                    sqlCmd.Parameters.AddWithValue("@IP_Address", ((in_IP_Address.Value == null) ? (object)DBNull.Value : (object)in_IP_Address.Value));
                    sqlCmd.Parameters.AddWithValue("@FQDN", ((in_FQDN.Value == null) ? (object)DBNull.Value : (object)in_FQDN.Value));
                    sqlCmd.Parameters.AddWithValue("@NetBIOS_Name", ((in_NetBIOS.Value == null) ? (object)DBNull.Value : (object)in_NetBIOS.Value));
                    sqlCmd.Parameters.AddWithValue("@Building_ID", ((ddl_Building.SelectedValue == null) ? (object)DBNull.Value : (object)ddl_Building.SelectedValue));
                    sqlCmd.Parameters.AddWithValue("@Description", ((ta_Description.Value == null) ? (object)DBNull.Value : (object)ta_Description.Value));


                    try
                    {
                        sqlCmd.ExecuteNonQuery();
                        trans.Commit();
                    }
                    catch (Exception er)
                    {
                        error.InnerHtml = er.ToString();
                        return;
                    }
                    finally
                    {
                        MyConn.Close();

                        error.InnerHtml = "Successfully added: " + in_Add_Equipment.Value;

                        in_Add_Equipment.Value = "";
                        ddl_Equipment_Type.SelectedIndex = 0;
                        in_IP_Address.Value = "";
                        in_FQDN.Value = "";
                        in_NetBIOS.Value = "";
                        ddl_Building.SelectedIndex = 0;
                        ta_Description.Value = "";

                        GridView1.DataBind();
                    }
                }
            }
        }
    }
}

-------- EDIT 2 --------

I've tracked down what is causing the "double execute" of the SQL statement to this piece of code:

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            PostBackOptions optionsSubmit = new PostBackOptions(btn_Add);
            btn_Add.OnClientClick = "disableButtonOnClick(this, 'Please wait...', 'disabled_button ui-corner-all'); ";
            btn_Add.OnClientClick = ClientScript.GetPostBackEventReference(optionsSubmit);
        }
    }

When I comment it out, the information is added once, but the button is not disabled like I want. Disabling the button will prevent people from being impatient and clicking the button multiple times.

Suggestions?

Here's what I get:

aspx

<asp:Button ID="btn_Add" Text="Add" CssClass="ui-state-default ui-corner-all" runat="server" OnClick="btn_Add_Click" />

aspx.cs

if (!Page.IsPostBack)
{
    PostBackOptions optionsSubmit = new PostBackOptions(btn_Add);
    btn_Add.OnClientClick = "disableButtonOnClick(this, 'Please wait...', 'disabled_button ui-corner-all'); ";
    btn_Add.OnClientClick += ClientScript.GetPostBackEventReference(optionsSubmit);
}

executes the SQL statement twice

aspx

<asp:Button ID="btn_Add" Text="Add" CssClass="ui-state-default ui-corner-all" runat="server" />

aspx.cs

if (!Page.IsPostBack)
{
    PostBackOptions optionsSubmit = new PostBackOptions(btn_Add);
    btn_Add.OnClientClick = "disableButtonOnClick(this, 'Please wait...', 'disabled_button ui-corner-all'); ";
    btn_Add.OnClientClick += ClientScript.GetPostBackEventReference(optionsSubmit);
}

briefly changes "btn_Add" in the desired way, but doesn't execute the code for "btn_Add_Click"

aspx

<asp:Button ID="btn_Add" Text="Add" CssClass="ui-state-default ui-corner-all" runat="server" OnClick="btn_Add_Click" />

aspx.cs

if (!Page.IsPostBack)
{
    PostBackOptions optionsSubmit = new PostBackOptions(btn_Add);
    btn_Add.OnClientClick = "disableButtonOnClick(this, 'Please wait...', 'disabled_button ui-corner-all'); ";
    //btn_Add.OnClientClick += ClientScript.GetPostBackEventReference(optionsSubmit);
}

changes "btn_Add" in the desired way, doesn't execute the code for "btn_Add_Click", and refreshes the entire page to Equipment.aspx (normally Equipment.aspx is loaded into a div through a jquery .load event)

Upvotes: 3

Views: 4946

Answers (4)

JustBeingHelpful
JustBeingHelpful

Reputation: 18980

Few suggestions...

  • connection strings should be stored in your web.config file.

  • it's okay to catch your exceptions for each step along the way, but use a "using" statement. That way you don't have to worry about disposing your object.

  • I would recommend adding a Global.asax file to your project and catching application errors there. In your catch statements, you could place a line and say "throw new Exception.." and pass in your specific exception so it's easy to catch and you can describe the error. Then I would recommend setting up the Logging application block in the Enterprise Library 5.0. Having a global "error" variable like you have its probably not a good idea. If you want to easily learn Enterprise Library, download the Enterprise Library Labs examples.

  • I wouldn't use SQL commands directly in your code. I would use stored procedures, and have the SQL be done on the SQL server itself. If you have variables, set up parameters on the stored procedure. Stored procedures are more secure for your web application. It might even be a good idea to use the Database Application block from Microsoft's Enterprise Library. They have all of this code for you, and you just need to import the library and know the commands. They handle all of the performance implications for you.

  • if using a using statement, no need to close the connection or dispose. I removed that in the code.

  • if you throw an exception, no need for the "return" statement... the next block up the stack with a catch statement (or finally statement) that qualifies will catch it. If there are none, then the Application_Error in Global.asax will catch it. Make sure you handle unhandled exceptions properly in the .NET framework. Microsoft broke the .NET 3.5 unhandled exception architecture, so you either have to add your own module, or switch back to the 1.1 architecture. Or upgrade to .NET 4.0.

  • Actual Question: Your click event might be getting called twice because you're using a handler AND you have the "onclick" event attribute specified in your <asp:button source tag. In VB, the word "Handles" would follow the event method. In C#, the handler could be in a different spot, with different types of syntax. It will get called twice if you have both there.

Here's #1: <asp:Button ID="btn_Add" Text="Add" CssClass="ui-state-default ui-corner-all" runat="server" OnClick="btn_Add_Click" />

Here's #2: btn_Add.OnClientClick = ClientScript.GetPostBackEventReference(optionsSubmit);

If you have the onclick attribute on your control, then you don't need to add special code (like OnClientClick or event handlers). Just use 1 or the other.

Good luck to you!

        error.InnerHtml = "&nbsp;";

        string connStr = "Data Source=.\\SQLEXPRESS;"
            + "Initial Catalog=SIMON;"
            + "Persist Security Info=True;"
            + "User ID=username;Password=password");

        using (SqlConnection MyConn = new SqlConnection(connStr))
        {
            string sqlStatement = @"INSERT INTO [Network_Equipment] "
                + "([Network_Equipment_Name], [Network_Equipment_Type_ID], [IP_Address], [FQDN], [NetBIOS_Name], [Building_ID], [Description])"
                + " VALUES "
                + "(@Network_Equipment_Name, @Network_Equipment_Type_ID, @IP_Address, @FQDN, @NetBIOS_Name, @Building_ID, @Description)";

            SqlCommand sqlCmd = new SqlCommand(sqlStatement, MyConn);
            sqlCmd.Connection = MyConn;

            sqlCmd.Parameters.AddWithValue("@Network_Equipment_Name", ((in_Add_Equipment.Value == null) ? (object)DBNull.Value : (object)in_Add_Equipment.Value));
            sqlCmd.Parameters.AddWithValue("@Network_Equipment_Type_ID", ((ddl_Equipment_Type.SelectedValue == null) ? (object)DBNull.Value : (object)ddl_Equipment_Type.SelectedValue));
            sqlCmd.Parameters.AddWithValue("@IP_Address", ((in_IP_Address.Value == null) ? (object)DBNull.Value : (object)in_IP_Address.Value));
            sqlCmd.Parameters.AddWithValue("@FQDN", ((in_FQDN.Value == null) ? (object)DBNull.Value : (object)in_FQDN.Value));
            sqlCmd.Parameters.AddWithValue("@NetBIOS_Name", ((in_NetBIOS.Value == null) ? (object)DBNull.Value : (object)in_NetBIOS.Value));
            sqlCmd.Parameters.AddWithValue("@Building_ID", ((ddl_Building.SelectedValue == null) ? (object)DBNull.Value : (object)ddl_Building.SelectedValue));
            sqlCmd.Parameters.AddWithValue("@Description", ((ta_Description.Value == null) ? (object)DBNull.Value : (object)ta_Description.Value));

            try
            {
                MyConn.Open();
            }
            catch (Exception er)
            {
                //error.InnerHtml = er.ToString();
                //return;
                throw new Exception(...
            }

            try
            {
                sqlCmd.ExecuteNonQuery();
            }
            catch (Exception er)
            {
                //error.InnerHtml = er.ToString();
                //return;
                throw new Exception(...
            }

            error.InnerHtml = "Successfully added: " + in_Add_Equipment.Value;
        }

        in_Add_Equipment.Value = "";
        ddl_Equipment_Type.SelectedIndex = 0;
        in_IP_Address.Value = "";
        in_FQDN.Value = "";
        in_NetBIOS.Value = "";
        ddl_Building.SelectedIndex = 0;
        ta_Description.Value = "";

        GridView1.DataBind();

Upvotes: 3

Sumo
Sumo

Reputation: 4112

Aside from their being too much code in one function, not using using statements, etc., the only reason that this event handler function would be called more than once is if it was getting wired up more than once. Check your AutoEventWireUp setting, the code-in-front for a definition of OnClick on the button, and an explicit event wire-up in your code-behind (would probably look like btn_Add.Click += btn_Add_Click;.

Upvotes: 0

yapingchen
yapingchen

Reputation: 856

remove the "return" which in the two catches

and

add the finally statement for each try

and move the 

 sqlCmd.Dispose();             sqlCmd = null;             MyConn.Close();

into the finally

when you debug ,you can see the insert statement whether is right. then look the database.

Upvotes: 1

BizApps
BizApps

Reputation: 6130

@Jesus Ramos is Correct.

Do something like this.

private _constring = new SqlConnection("Data Source=.\\SQLEXPRESS;"
        + "Initial Catalog=SIMON;"
        + "Persist Security Info=True;"
        + "User ID=username;Password=password");


protected void btn_Add_Click(object sender, EventArgs e)
   {

        SqlTransaction trans = null;
        using (var con = new  SqlConnection(_constring))
        {
            con.Open();
            trans = con.BeginTransaction();
            using (

                 string sqlStatement ="//SqlStuff";

                var  sqlCmd = new  SqlCommand(sqlStatement,con))
            {
                 sqlCmd.Transaction = trans;
                 sqlCmd.Connection = con;

                try
                {


                    //sqlcmd.Parameters Stuff


                    sqlCmd.ExecuteNonQuery();
                    trans.Commit();

                   //SUCCESS
                }
               catch (Exception er)
               {
               error.InnerHtml = er.ToString();
               return;
                }
                finally
                {
                    con.Close();
                }

            }
        }
    }

Regards

Upvotes: 0

Related Questions