Edit/Update a Chosen Database Record

Introduction:

This code samples shows how to 

• Select the Row in the DataGrid for Update/Edit.
• Populate the data for the selected row in the Textboxes on form to Update/Edit.

To begin with Code:

Drag Drop the DataGrid and the Relevant Textboxes (txtRegionID, txtRegionDescription) on the webform

<asp:DataGrid id="DataGrid1" OnItemCommand="ItemCommand" style="Z-INDEX: 101; LEFT: 15px; POSITION: absolute; TOP: 23px"runat="server"
CssClass="tx-tbl-Gr-lml">
<Columns>
<asp:ButtonColumn Text="Edit" ButtonType="PushButton" CommandName="Edit">
</asp:ButtonColumn>
</Columns>
</asp:DataGrid>
....

Namespaces used

• System.Data.SqlClient/ System.Data.OleDb
  Code for System.Data.SqlClient

C#

SqlConnection cn;
SqlDataAdapter da ;
SqlCommand cmd ;
string strsql ;
DataSet ds ;
private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
cn = new SqlConnection("Server=localhost;uid=sa;pwd=;database=northwind;");
if(!Page.IsPostBack)
{
//Code to Bind the data to the Datagrid
BindData();
}
}
void BindData()
{
DataGrid1.DataSource = GetData("Select * from Region");
DataGrid1.DataBind();
}
DataSet GetData(string strSql)
{
da = new SqlDataAdapter(strSql, cn);
ds = new DataSet();
da.Fill(ds);
return ds;
}
protected void ItemCommand(Object
ource,System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
if (e.CommandName == "Edit")
{
//'Fill the Textboxes with relevant data
FillTheData(e.Item.Cells[1].Text, e.Item.Cells[2].Text);
lblMessage.Text="";
}
}
void FillTheData(string RegionID,string RegionDescription)
{
txtRegionID.Text = RegionID;
txtRegionDescription.Text = RegionDescription;
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
try
{
strsql = "Update Region set RegionDescription=@RegionDescription <mailto:RegionDescription=@RegionDescription> where RegionId=@RegionId <mailto:RegionId=@RegionId>";
cmd = new SqlCommand(strsql, cn);
cmd.Parameters.Add(new SqlParameter("@RegionId", SqlDbType.Int));
cmd.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NVarChar, 40));
cmd.Parameters["@RegionId"].Value = Convert.ToInt32(txtRegionID.Text);
cmd.Parameters["@RegionDescription"].Value = txtRegionDescription.Text;
cn.Open();
cmd.ExecuteNonQuery();
BindData();
lblMessage.Text = "Updated Successfully";
}
catch (Exception ex)
{
lblMessage.Text = ex.Message;
lblMessage.ForeColor = Color.Red;
}
finally
{
cn.Close();
}
}

VB.NET

Dim cn As SqlConnection
Dim da As SqlDataAdapter
Dim cmd As SqlCommand
Dim strsql As String
Dim ds As DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
cn = New SqlConnection("Server=localhost;uid=sa;pwd=;database=northwind;")
If Not Page.IsPostBack Then
BindData()
End If
End Sub
Sub BindData()
DataGrid1.DataSource = GetData("Select * from Region")
DataGrid1.DataBind()
End Sub
Function GetData(ByVal strSql As String) As DataSet
da = New SqlDataAdapter(strSql, cn)
ds = New DataSet
da.Fill(ds)
Return ds
End Function
Protected Sub ItemCommand(ByVal source As Object, _
ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs)
If e.CommandName = "Edit" Then
'Fill the Textboxes with relevant data
FillTheData(e.Item.Cells(1).Text, e.Item.Cells(2).Text)
End If
End Sub
Sub FillTheData(ByVal RegionID As String, ByVal RegionDescription As String)txtRegionID.Text = RegionID
txtRegionDescription.Text = RegionDescription
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Trystrsql = "Update Region set RegionDescription=@RegionDescription
<mailto:RegionDescription=@RegionDescription> where RegionId=@RegionId <mailto:RegionId=@RegionId>"
cmd = New SqlCommand(strsql, cn)
cmd.Parameters.Add(New SqlParameter("@RegionId", SqlDbType.Int))cmd.Parameters.Add(New SqlParameter("@RegionDescription", SqlDbType.NVarChar, 40))
cmd.Parameters("@RegionId").Value = Convert.ToInt32(txtRegionID.Text)
cmd.Parameters("@RegionDescription").Value = txtRegionDescription.Text)
cn.Open()
cmd.ExecuteNonQuery()
BindData()
lblMessage.Text = "Updated Successfully"
Catch ex As Exception
lblMessage.Text = ex.Message
lblMessage.ForeColor = Color.Red
Finally
cn.Close()
End Try
End Sub

The User Interface before Update

The User Interface after Update