Home  »  Articles  »  ASP.Net  »

Edit and Update one record in sql server database using asp.net and c#.net

 Posted By : Manoranjan SahooPosted Date : 02/11/2010 08:53:28 AM Category : ASP.Net Points : 5 (Rs 2.00)

Hello Friends,

Here i am going to put the simple example for Edit and Update one record in sql server database using ASP.Net and C#.Net.

See, here i have one simple table in my database which has following structure.

Table Name : Table1

Fields :

itemid as int

itemaname as varchar(50)

company as varchar(50)

Now we have to create design to fetch the data and edit that based on item id.

Make the design as below :

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        Item ID : <asp:TextBox ID="txtId" runat="server"></asp:TextBox>
        <asp:Button ID="btnEdit" runat="server" Text="Edit" onclick="btnEdit_Click" />
        <asp:Panel ID="Panel1" Visible ="false" runat="server">
         Item ID : <asp:TextBox ID="txtItemID" ReadOnly ="true"  runat="server"></asp:TextBox> <br /><br />
           Item Name : <asp:TextBox ID="txtItemName" runat="server"></asp:TextBox><br /><br />
           Company : <asp:TextBox ID="txtCompany" runat="server"></asp:TextBox><br /><br />
            <asp:Button ID="btnUpdate" runat="server" Text="Update"
                onclick="btnUpdate_Click" />
        </asp:Panel>
        <asp:Label ID="lblerror" ForeColor ="Red" runat="server" Text="Label"></asp:Label>
    </div>
    </form>
</body>
</html>

 

Now time to fetch the data from database. When we click on the edit button it will fetch the data from database. Put below code in codebehind window of the page.

Code To  Fetch data :

    protected void btnEdit_Click(object sender, EventArgs e)
    {
        string sql = "Select itemid,itemname,company from Table1 where itemid=" + txtId.Text;
        if (cn.State == ConnectionState.Closed) { cn.Open(); }
        SqlDataAdapter sda = new SqlDataAdapter(sql, cn);
        DataSet ds = new DataSet();
        sda.Fill(ds, "ItemDetails");
        if (ds.Tables[0].Rows.Count > 0)
        {
            DataRow dtr;
            int i = 0;
            while (i < ds.Tables[0].Rows.Count)
            {
                dtr = ds.Tables[0].Rows[i];
                txtItemID.Text = dtr[0].ToString();
                txtItemName.Text = dtr[1].ToString();
                txtCompany.Text = dtr[2].ToString();
                Panel1.Visible = true;
                txtId.Visible = false;
                btnEdit.Visible = false;
                i++;
            }
        }

    }

Now the time to update the data after editing. So we have to write code on btnUpdate's click event.

Code to Update :

    protected void btnUpdate_Click(object sender, EventArgs e)
    {
        lblerror.Text = "";
        if (txtItemID.Text != "" && txtItemName.Text != "")
        {
            string sql = "update Table1 set itemname='" + txtItemName.Text + "', Company='" + txtCompany.Text + "' where itemid=" + txtItemID.Text;
            int rowupdated = UpdatedCommand(sql);
            Panel1.Visible = false;
            txtItemID.Text = "";
            txtItemName.Text = "";
            txtCompany.Text = "";
        }
        else
        {
            lblerror.Text = "Item ID and Item Name can not be blank";
        }
    }


    public int UpdatedCommand(string strupdatequery)
    {
        int intreturn = -4;
        SqlTransaction transsql=null;
        try
        {
            SqlCommand cmdupdatecommand = new SqlCommand();
            transsql = null;
            if (cn.State != ConnectionState.Open)
            {
                cn.Open();

            }
            transsql = cn.BeginTransaction();
            cmdupdatecommand.CommandTimeout = 0;
            cmdupdatecommand.CommandText = strupdatequery;
            cmdupdatecommand.Connection = cn;
            cmdupdatecommand.Transaction = transsql;
            intreturn = cmdupdatecommand.ExecuteNonQuery();
            transsql.Commit();
            if (cn.State != ConnectionState.Closed)
            {
                cn.Close();

            }
            return intreturn;

        }
        catch (Exception exp)
        {
            transsql.Rollback();
            strupdatequery = exp.Message;
            return -3;
        }
        finally
        {
            if (cn.State != ConnectionState.Closed)
            {
                cn.Close();

            }
        }
    }

 

Here i used simple updatecommand method to handle sql updatation. if there is any error occurs then it will rollback all the changes.

Thank You.

Responses
No responses found for this thread. Be the first to respond this thread.
Post Reply
Login to post Response.