Live Chat Icon For mobile
Live Chat Icon

How to edit a record using DataGrid

Platform: ASP.NET| Category: DataGrid

<asp:datagrid id='DataGrid1' runat='server' DataKeyField='OrderID'  
	OnUpdateCommand='DataGrid1_Update' OnEditCommand='DataGrid1_Edit' OnCancelCommand='DataGrid1_Cancel'>
<Columns>
	<asp:EditCommandColumn EditText='Edit' CancelText='Cancel' UpdateText='Update' />
</Columns>
</asp:datagrid>
<asp:Label id='lblError' style='Z-INDEX: 101; LEFT: 16px; POSITION: absolute; TOP: 160px' runat='server'
	Visible='False' ForeColor='Red'></asp:Label>

VB.NET


Dim mycn As SqlConnection
Dim myda As SqlDataAdapter
Dim ds As DataSet
Dim strConn, strSQL As String

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
	strConn = 'server=localhost;uid=sa;database=northwind;pwd=;'
	If Not Page.IsPostBack Then
	            BindGrid()
	End If
End Sub ’Page_Load

Sub BindGrid()
  	mycn = New SqlConnection(strConn)
	strSQL = 'Select * from [Order Details] where orderid=10249'
	myda = New SqlDataAdapter(strSQL, mycn)
	ds = New DataSet
	myda.Fill(ds, 'Table')
	DataGrid1.DataSource = ds
	DataGrid1.DataBind()
End Sub ’BindGrid

Public Sub DataGrid1_Cancel(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs)
	DataGrid1.EditItemIndex = -1
	BindGrid()
End Sub ’DataGrid1_Cancel

Public Sub DataGrid1_Edit(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs)
	DataGrid1.EditItemIndex = CInt(e.Item.ItemIndex)
	BindGrid()
End Sub ’DataGrid1_Edit

Public Sub DataGrid1_Update(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs)
	Dim unitprice As String = CType(e.Item.Cells(3).Controls(0), TextBox).Text
	Dim quantity As String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
	Dim discount As String = CType(e.Item.Cells(5).Controls(0), TextBox).Text
	Dim orderid As Integer = CInt(DataGrid1.DataKeys(CInt(e.Item.ItemIndex)))
	Dim productid As String = CType(e.Item.Cells(2).Controls(0), TextBox).Text

	Try
		Dim updateCmd As String = 'UPDATE [Order Details] SET  UnitPrice = @UnitPrice,' + 'Quantity = @Quantity, Discount = @Discount  where OrderId =@OrderId  and ProductId=@ProductId'
	            Dim cn As New SqlConnection(strConn)
	            Dim myCommand As New SqlCommand(updateCmd, cn)
	            myCommand.Parameters.Add(New SqlParameter('@UnitPrice', Convert.ToDecimal(unitprice)))
	            myCommand.Parameters.Add(New SqlParameter('@Quantity', Convert.ToInt16(quantity)))
	            myCommand.Parameters.Add(New SqlParameter('@Discount', Convert.ToInt16(discount)))
	            myCommand.Parameters.Add(New SqlParameter('@OrderId', orderid))
	            myCommand.Parameters.Add(New SqlParameter('@ProductId', Convert.ToInt16(productid)))
	            cn.Open()
	            myCommand.ExecuteNonQuery()
	            DataGrid1.EditItemIndex = -1
	            BindGrid()
	Catch ex As Exception
	            lblError.Visible = True
	            lblError.Text = ex.Message
	End Try
End Sub ’DataGrid1_Update

C#


SqlConnection mycn;
SqlDataAdapter myda;
DataSet ds;
string strConn,strSQL;

private void Page_Load(object sender, System.EventArgs e)
{
	strConn ='server=localhost;uid=sa;database=northwind;pwd=;';
	if (!Page.IsPostBack )
	{
		BindGrid();
	}
}

void BindGrid()
{
	mycn = new SqlConnection(strConn);
	strSQL = 'Select * from [Order Details] where orderid=10249'  ;
	myda = new SqlDataAdapter (strSQL, mycn);
	ds= new DataSet ();
	myda.Fill (ds,'Table');
	DataGrid1.DataSource =ds;
	DataGrid1.DataBind (); 
}

public void DataGrid1_Cancel(Object sender, DataGridCommandEventArgs e)
{
	DataGrid1.EditItemIndex = -1;
	BindGrid();
}

public void DataGrid1_Edit(Object sender, DataGridCommandEventArgs e)
{
	DataGrid1.EditItemIndex = (int)e.Item.ItemIndex;			 
	BindGrid(); 
}

public void DataGrid1_Update(Object sender, DataGridCommandEventArgs e)
{
	string unitprice =((TextBox)e.Item.Cells[3].Controls[0] ).Text  ;
	string quantity =((TextBox)e.Item.Cells[4].Controls[0] ).Text  ;
	string discount=((TextBox)e.Item.Cells[5].Controls[0] ).Text  ;
	int orderid = (int)DataGrid1.DataKeys[(int)e.Item.ItemIndex];
	string productid= ((TextBox)e.Item.Cells[2].Controls[0] ).Text  ;
	try
	{
		string updateCmd = 'UPDATE [Order Details] SET  UnitPrice = @UnitPrice,'
		+ 'Quantity = @Quantity, Discount = @Discount  where OrderId =@OrderId  and ProductId=@ProductId';
		SqlConnection cn = new SqlConnection (strConn);
		SqlCommand myCommand = new SqlCommand(updateCmd, cn);
		myCommand.Parameters.Add(new SqlParameter('@UnitPrice', Convert.ToDecimal(unitprice )));
		myCommand.Parameters.Add(new SqlParameter('@Quantity', Convert.ToInt16 (quantity  ) ));
		myCommand.Parameters.Add(new SqlParameter('@Discount', Convert.ToInt16 ( discount   )));
		myCommand.Parameters.Add(new SqlParameter('@OrderId',  orderid));
		myCommand.Parameters.Add(new SqlParameter('@ProductId', Convert.ToInt16 ( productid)));
		cn.Open ();
		myCommand.ExecuteNonQuery ();
		DataGrid1.EditItemIndex = -1;
		BindGrid();
	}
	catch(Exception ex)
	{
		lblError.Visible =true;
		lblError.Text =(ex.Message );
	}
}

Share with

Share on twitter
Share on facebook
Share on linkedin