How to hide a column in a Datagrid if AutoGenerateColumns is set to True?
<asp:DataGrid id=’DataGrid1′ onItemDataBound=’ItemDB’ runat=’server’></asp:DataGrid> VB.NET protected Sub ItemDB (ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) e.Item.Cells(0).Visible = False End Sub C# protected void ItemDB (object sender, System.Web.UI.WebControls.DataGridItemEventArgs e) { e.Item.Cells[0].Visible = false; }
How can I display the field as a link in the DataGrid? If may or may not be stored in the ‘http://’ format.
<asp:DataGrid id=’DataGrid1′ AutoGenerateColumns=’False’ runat=’server’> <Columns> <asp:TemplateColumn HeaderText=’Link’> <ItemTemplate> <asp:HyperLink Runat =server NavigateUrl =’<%#GetURL(DataBinder.Eval(Container.DataItem, ‘RegionDescription’).ToString())%>’ ID=’Hyperlink1′> <%#DataBinder.Eval(Container.DataItem, ‘RegionDescription’)%> </asp:HyperLink> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> VB.NET Protected Function GetURL(ByVal fldval As String) As String If fldval.IndexOf(‘http://’, 0, fldval.Length) = 0 Then Return fldval Else Return ‘http://’ + fldval End If End Function ’GetURL C# protected string GetURL (string fldval ) { if (fldval.IndexOf ( ‘http://’ , 0, fldval.Length ) ==0) { return fldval; } else { return ‘http://’ + fldval; } }
How to use a LinkButton in a DataGrid
<asp:DataGrid id=’DataGrid1′ OnItemCommand=’ItemCmd’ runat=’server’> <Columns> <asp:ButtonColumn DataTextField=’ProductID’ CommandName=’Show’ HeaderText=’Productid’ ButtonType=’LinkButton’ Text=’Click’></asp:ButtonColumn> </Columns> </asp:DataGrid> VB.NET Protected Sub ItemCmd(source As Object, e As System.Web.UI.WebControls.DataGridCommandEventArgs) If e.CommandName.ToString() = ‘Show’ Then Response.Write(e.Item.Cells(5).Text) End If End Sub ’ItemCmd C# protected void ItemCmd(Object source, System.Web.UI.WebControls.DataGridCommandEventArgs e ) { if (e.CommandName.ToString () == ‘Show’) { Response.Write( e.Item.Cells[5].Text ); } }
How to delete a record using DataGrid
<asp:datagrid id=’DataGrid1′ runat=’server’ DataKeyField=’Regionid’ OnDeleteCommand=’DataGrid1_Delete’ OnEditCommand=’DataGrid1_Edit’ OnCancelCommand=’DataGrid1_Cancel’> <Columns> <asp:ButtonColumn Text=’Delete’ CommandName=’Delete’ /> </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(sender As Object, e As System.EventArgs) 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 Region’ myda = New SqlDataAdapter(strSQL, mycn) ds = New DataSet() myda.Fill(ds, ‘Table’) DataGrid1.DataSource = ds DataGrid1.DataBind() End Sub ’BindGrid Public Sub DataGrid1_Cancel(sender As [Object], e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = – 1 BindGrid() End Sub ’DataGrid1_Cancel Public Sub DataGrid1_Edit(sender As [Object], e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = CInt(e.Item.ItemIndex) BindGrid() End Sub ’DataGrid1_Edit Public Sub DataGrid1_Delete(sender As [Object], e As DataGridCommandEventArgs) Dim orderid As Integer = CInt(DataGrid1.DataKeys(CInt(e.Item.ItemIndex))) Dim deleteCmd As [String] = ‘DELETE from Region where Regionid = @Regionid ‘ Dim cn As New SqlConnection(strConn) Dim myCommand As New SqlCommand(deleteCmd, cn) myCommand.Parameters.Add(New SqlParameter(‘@Regionid’, SqlDbType.Int)) myCommand.Parameters(‘@Regionid’).Value = DataGrid1.DataKeys(CInt(e.Item.ItemIndex)) myCommand.Connection.Open() Try myCommand.ExecuteNonQuery() Catch lblError.Text = ‘ERROR: Could not delete record’ End Try myCommand.Connection.Close() BindGrid() End Sub ’DataGrid1_Delete 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 Region’ ; 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_Delete(Object sender, DataGridCommandEventArgs e) { int orderid=(int) DataGrid1.DataKeys[(int)e.Item.ItemIndex];; String deleteCmd = ‘DELETE from Region where Regionid = @Regionid ‘; SqlConnection cn = new SqlConnection (strConn); SqlCommand myCommand = new SqlCommand(deleteCmd, cn); myCommand.Parameters.Add(new SqlParameter(‘@Regionid’, SqlDbType.Int )); myCommand.Parameters[‘@Regionid’].Value = DataGrid1.DataKeys[(int)e.Item.ItemIndex]; myCommand.Connection.Open(); try { myCommand.ExecuteNonQuery(); } catch (SqlException) { lblError.Text = ‘ERROR: Could not delete record’; } myCommand.Connection.Close(); BindGrid(); }
How to edit a record using 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 ); } }