Archives: FAQs

Holds Syncfusion FAQs and specific data

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 ); } }

How to show data vertically in a datagrid for a particular row

<asp:DataGrid id=’DataGrid1′ runat=’server’></asp:DataGrid> VB.NET Dim cn As SqlConnection Dim da As SqlDataAdapter 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’) da = New SqlDataAdapter(‘Select * from orders where orderid=10248’, cn) ds = New DataSet() da.Fill(ds, ‘Orders’) DataGrid1.DataSource = GoDoReShape(ds) DataGrid1.DataBind() End Sub ’Page_Load Public Function GoDoReShape(ByVal ds As DataSet) As DataSet Dim NewDs As New DataSet() NewDs.Tables.Add() ’Create Two Columns with names ‘ColumnName’ and ‘Value’ ’ColumnName -> Displays all ColumnNames ’Value -> Displays ColumnData NewDs.Tables(0).Columns.Add(‘ColumnName’) NewDs.Tables(0).Columns.Add(‘Value’) Dim dr As DataRow For Each dr In ds.Tables(0).Rows Dim dcol As System.Data.DataColumn For Each dcol In ds.Tables(0).Columns ’Declare Array Dim MyArray As String() = {dcol.ColumnName.ToString(), dr(dcol.ColumnName.ToString()).ToString()} NewDs.Tables(0).Rows.Add(MyArray) Next Next Return NewDs End Function ’GoDoReShape C# SqlConnection cn ; SqlDataAdapter da; 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’); da=new SqlDataAdapter ( ‘Select * from orders where orderid=10248′,cn); ds = new DataSet(); da.Fill (ds,’Orders’); DataGrid1.DataSource =GoDoReShape (ds); DataGrid1.DataBind (); } public DataSet GoDoReShape(DataSet ds) { DataSet NewDs=new DataSet(); NewDs.Tables.Add(); //Create Two Columns with names ‘ColumnName’ and ‘Value’ //ColumnName -> Displays all ColumnNames //Value -> Displays ColumnData NewDs.Tables[0].Columns.Add(‘ColumnName’); NewDs.Tables[0].Columns.Add(‘Value’); foreach(DataRow dr in ds.Tables [0].Rows ) { foreach(System.Data.DataColumn dcol in ds.Tables[0].Columns) { //Declare Array string[] MyArray={dcol.ColumnName.ToString(),dr[dcol.ColumnName.ToString()].ToString()}; NewDs.Tables[0].Rows.Add(MyArray); } } return NewDs; }

How to enable or disable the button in the DataGrid based on a bit value in the database

<asp:DataGrid id=’DataGrid1′ runat=’server’> <Columns> <asp:TemplateColumn HeaderText =’Product Information’ > <ItemTemplate > <asp:Button CommandName=’Status’ Text =’Status’ Runat =server Enabled =<%#CheckStatus(Convert.ToBoolean(DataBinder.Eval(Container.DataItem,’Discontinued’)))%> ID=’Button1′ > </asp:Button> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid> VB.NET Protected Function CheckStatus(ByVal prdStatus As Boolean) As Boolean If prdStatus = False Then Return True Else Return False End If End Function C# protected bool CheckStatus(bool prdStatus) { //If the Discontinued field is ‘0’ if (prdStatus==false) { return true; } //If the Discontinued field is ‘1’ else { return false; } }