Archives: FAQs

Holds Syncfusion FAQs and specific data

Is there a way to show more than one datafield in a column when using a datagrid?

Yes. Use a TemplateColumn in a DataGrid <asp:DataGrid id=’DataGrid1′ runat=server AutoGenerateColumns=False> <Columns> <asp:TemplateColumn HeaderText=’Field’> <ItemTemplate> <%#DataBinder.Eval(Container.DataItem, ‘Field1’).ToString()%> – <%#DataBinder.Eval(Container.DataItem, ‘Field2’).ToString()%> </ItemTemplate> </asp:TemplateColumn> </Columns> </asp:DataGrid>

How to edit data in the DataGrid using a TemplateColumn

<asp:DataGrid id=’DataGrid1′ AutoGenerateColumns=False OnCancelCommand =’CancelCmd’ OnEditCommand =’EditCmd’ OnUpdateCommand =’UpdateCmd’ DataKeyField=’Employeeid’ runat=’server’> <Columns> <asp:TemplateColumn HeaderText =’Employee Information’> <ItemTemplate > <asp:label ID=’lblLastName’ Runat=server text=<%#DataBinder.Eval(Container.DataItem, ‘Lastname’)%>></asp:Label> , <asp:label ID=’lblFirstName’ Runat=server text=<%#DataBinder.Eval(Container.DataItem, ‘Firstname’)%>></asp:Label> <br> <asp:label ID=’lblTitle’ Runat=server text=<%#DataBinder.Eval(Container.DataItem, ‘Title’)%>> </ItemTemplate> <EditItemTemplate > <asp:TextBox runat=server ID=’txtLastName’ Text=<%#DataBinder.Eval(Container.DataItem, ‘lastname’)%>></asp:TextBox> <asp:RequiredFieldValidator id=’rqdfldLastName’ Display =’Static’ Runat =server ErrorMessage =’*’ ControlToValidate =’txtLastName’> <asp:TextBox runat=server ID=’txtFirstName’ text=<%#DataBinder.Eval(Container.DataItem, ‘Firstname’)%>></asp:TextBox> <asp:RequiredFieldValidator id=’rqdfldFirstName’ Display =’Static’ Runat =server ErrorMessage =’*’ ControlToValidate =’txtFirstName’> <asp:TextBox runat=server ID=’txtTitle’ Text=<%#DataBinder.Eval(Container.DataItem, ‘title’)%>></asp:TextBox> </EditItemTemplate> </asp:TemplateColumn> <asp:EditCommandColumn ButtonType=’PushButton’ CancelText=’Cancel’ EditText=’Edit’ UpdateText=’Update’></asp:EditCommandColumn> </Columns> </asp:DataGrid> VB.NET Dim sqlStmt As String Dim conString As String Dim cn As SqlConnection Dim cmd As SqlCommand 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 If Not Page.IsPostBack Then BindGrid() End If End Sub ’Page_Load Sub BindGrid() ’Bind the Datagrid with dataSet End Sub ’BindGrid Protected Sub EditCmd(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = CInt(e.Item.ItemIndex) BindGrid() End Sub ’EditCmd Public Sub CancelCmd(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = -1 BindGrid() End Sub ’CancelCmd Protected Sub UpdateCmd(ByVal sender As Object, ByVal e As DataGridCommandEventArgs) If Page.IsValid Then sqlStmt = ‘UPDATE Employees SET LastName = @LastName, FirstName=@FirstName,Title=@Title where EmployeeId = @EmployeeId’ conString = ‘server=localhost;database=Northwind;uid=sa;pwd=;’ cn = New SqlConnection(conString) cmd = New SqlCommand(sqlStmt, cn) Dim LastName, FirstName, Title As String LastName = CType(e.Item.FindControl(‘txtLastName’), TextBox).Text FirstName = CType(e.Item.FindControl(‘txtFirstName’), TextBox).Text Title = CType(e.Item.FindControl(‘txtTitle’), TextBox).Text Dim EmployeeId As Integer = CInt(DataGrid1.DataKeys(CInt(e.Item.ItemIndex))) cmd.Parameters.Add(New SqlParameter(‘@LastName’, LastName)) cmd.Parameters.Add(New SqlParameter(‘@FirstName’, FirstName)) cmd.Parameters.Add(New SqlParameter(‘@Title’, Title)) cmd.Parameters.Add(New SqlParameter(‘@EmployeeId’, EmployeeId)) Try cn.Open() cmd.ExecuteNonQuery() DataGrid1.EditItemIndex = -1 Catch ex As Exception Response.Write(ex.Message.ToString()) End Try BindGrid() End If End Sub ’UpdateCmd C# private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here if (!Page.IsPostBack ) { BindGrid(); } } void BindGrid() { //Bind the DataGrid with dataset } protected void EditCmd(object sender, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = (int)e.Item.ItemIndex; BindGrid(); } public void CancelCmd(object sender, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; BindGrid(); } string sqlStmt; string conString; SqlConnection cn; SqlCommand cmd; protected void UpdateCmd(object sender, DataGridCommandEventArgs e) { if (Page.IsValid) { sqlStmt = ‘UPDATE Employees SET LastName = @LastName, FirstName=@FirstName,Title=@Title where EmployeeId = @EmployeeId’; conString = ‘server=localhost;database=Northwind;uid=sa;pwd=;’; cn = new SqlConnection(conString); cmd = new SqlCommand(sqlStmt, cn); string LastName , FirstName , Title; LastName = ((TextBox )e.Item.FindControl (‘txtLastName’)).Text ; FirstName = ((TextBox )e.Item.FindControl (‘txtFirstName’)).Text ; Title = ((TextBox )e.Item.FindControl (‘txtTitle’)).Text ; int EmployeeId =(int) DataGrid1.DataKeys[(int)e.Item.ItemIndex]; cmd.Parameters.Add(new SqlParameter(‘@LastName’, LastName)); cmd.Parameters.Add(new SqlParameter(‘@FirstName’,FirstName )); cmd.Parameters.Add(new SqlParameter(‘@Title’, Title )); cmd.Parameters.Add(new SqlParameter(‘@EmployeeId’,EmployeeId )); try { cn.Open(); cmd.ExecuteNonQuery(); DataGrid1.EditItemIndex = -1; } catch(Exception ex) { Response.Write (ex.Message.ToString () ); } BindGrid(); } }

How to highlight the Column that is sorted in a DataGrid

<asp:DataGrid id=’DataGrid1′ OnItemDataBound =’ItemDB’ AllowSorting=’True’ OnSortCommand=’SortData’ runat=’server’></asp:DataGrid> VB.NET 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 If Not Page.IsPostBack Then BindDataGrid(‘ProductId’) End If End Sub Protected Sub SortData(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridSortCommandEventArgs) BindDataGrid(e.SortExpression.ToString()) End Sub Sub BindDataGrid(ByVal sortfield As String) ViewState(‘SortExpression’) = sortfield ’Fill the DataSet Dim dv As DataView = ds.Tables(0).DefaultView dv.Sort = sortfield DataGrid1.DataSource = dv DataGrid1.DataBind() End Sub Protected Sub ItemDB(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Dim dv As DataView = DataGrid1.DataSource Dim dc As DataColumnCollection = dv.Table.Columns If e.Item.ItemType = ListItemType.Header Then If ViewState(‘SortExpression’) <> ” Then e.Item.Cells(dc.IndexOf(dc(ViewState(‘SortExpression’)))).BackColor = Color.BlueViolet e.Item.Cells(dc.IndexOf(dc(ViewState(‘SortExpression’)))).ForeColor = Color.AntiqueWhite End If End If End Sub C# private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here if(! Page.IsPostBack ) { BindDataGrid(‘ProductId’); } } protected void SortData(Object source, System.Web.UI.WebControls.DataGridSortCommandEventArgs e ) { BindDataGrid(e.SortExpression.ToString()); } void BindDataGrid(string sortfield) { ViewState[‘SortExpression’] = sortfield; //Fill the dataset DataView dv = ds.Tables[0].DefaultView; dv.Sort = sortfield; DataGrid1.DataSource = dv; DataGrid1.DataBind(); } protected void ItemDB(object sender ,System.Web.UI.WebControls.DataGridItemEventArgs e ) { DataView dv =(DataView) DataGrid1.DataSource; DataColumnCollection dc = dv.Table.Columns; if (e.Item.ItemType == ListItemType.Header ) { if (ViewState[‘SortExpression’].ToString()!= ”) { e.Item.Cells[dc.IndexOf( dc[ViewState[‘SortExpression’].ToString ()])].BackColor = Color.BlueViolet; e.Item.Cells[dc.IndexOf(dc[ViewState[‘SortExpression’].ToString ()])].ForeColor = Color.AntiqueWhite; } } }

How to use a dropdownlist in a DataGrid

Step 1: Display the Data in the Datagrid Initially the Datagrid is populated with the records in the db The field Discontinued is of data type bit in the db i.e 0/1. To display it as yes/no in the Datagrid a helper function ShowVal(…) is called Step 2: To Edit the Datagrid , When the edit button is clicked it should display field Discontinued as Yes/No To update the record the user should get a choice to Select Yes/No using dropdownlist By default the dropdownlist should be set to the value in the database So the DataSource property of the dropdownlist is set to BindTheDiscontinued() and OnPreRender property does the task of setting the value from the db to the dropdownlist <asp:DataGrid id=’DataGrid1′ AutoGenerateColumns=’False’ DataKeyField=’ProductID’ OnUpdateCommand=’DataGrid1_Update’ OnEditCommand=’DataGrid1_Edit’ OnCancelCommand=’DataGrid1_Cancel’ runat=’server’> <Columns> <asp:TemplateColumn HeaderText=’Discontinued’> <ItemTemplate> <asp:Label ID=’lblDiscontinued’ Text=’<%#ShowVal(Convert.ToBoolean( DataBinder.Eval(Container.DataItem, ‘Discontinued’).ToString()) )%>’ Runat=’server’ /> </ItemTemplate> <EditItemTemplate> <asp:Label runat=’server’ id=’lblProductID’ Visible=’False’ Text=’<%# DataBinder.Eval(Container.DataItem, ‘ProductId’) %>’/> <asp:Label ID=’lblEditDiscontinued’ Text=’<%#ShowVal(Convert.ToBoolean(DataBinder.Eval(Container.DataItem, ‘Discontinued’).ToString() ))%>’ Runat=’server’ /> <asp:DropDownList id=’ddlDiscontinued’ DataSource='<%# BindTheDiscontinued() %>’ OnPreRender=’SetDropDownIndex’ DataTextField=’Discontinued’ DataValueField=’Discontinued’ runat=’server’ /> </EditItemTemplate> </asp:TemplateColumn> <asp:EditCommandColumn EditText=’Edit’ CancelText=’Cancel’ UpdateText=’Update’ ItemStyle-Width=’100px’ HeaderText=’Commands’ /> </Columns> </asp:DataGrid> Code Behind VB.NET Dim strDiscontinued As String Dim obj As GetData Dim strSql As String Dim strConn As String Dim ds As DataSet Dim dr As SqlDataReader 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 strConn = ‘server=localhost;uid=sa;pwd=;database=northwind’ If Not Page.IsPostBack Then BindGrid() End If End Sub ’Page_Load ’To Bind the DataGrid Sub BindGrid() obj = New GetData strSql = ‘Select productid, discontinued from Products’ ds = obj.GetDataFromTable(strSql, strConn) DataGrid1.DataSource = ds DataGrid1.DataBind() End Sub ’BindGrid ’To display Yes/No for True/False Protected Function ShowVal(ByVal blnval As Boolean) As String If blnval = True Then Return ‘Yes’ Else Return ‘No’ End If End Function ’ShowVal ’Bind the Data to the dropdownlist in the EditTemplate Protected Function BindTheDiscontinued() As SqlDataReader obj = New GetData strSql = ‘SELECT distinct ’Discontinued’ =’ strSql += ‘ CASE ‘ strSql += ‘ WHEN Discontinued = 1 Then ’Yes’’ strSql += ‘ ELSE ’No’’ strSql += ‘ END ‘ strSql += ‘ From Products ‘ dr = obj.GetSingleDataUsingReader(strSql, strConn) Return dr End Function ’BindTheDiscontinued ’Set the Text of the Dropdownlist to the field value in Database Protected Sub SetDropDownIndex(ByVal sender As [Object], ByVal e As System.EventArgs) Dim ed As DropDownList ed = CType(sender, DropDownList) ed.SelectedIndex = ed.Items.IndexOf(ed.Items.FindByText(strDiscontinued)) End Sub ’SetDropDownIndex ’For Edit Update Cancel Public Sub DataGrid1_Edit(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs) strDiscontinued = CType(e.Item.FindControl(‘lblDiscontinued’), Label).Text DataGrid1.EditItemIndex = CInt(e.Item.ItemIndex) BindGrid() End Sub ’DataGrid1_Edit Public Sub DataGrid1_Update(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs) Dim TempList As DropDownList Dim TempValue As [String] TempList = CType(e.Item.FindControl(‘ddlDiscontinued’), DropDownList) TempValue = TempList.SelectedItem.Value ’Place update code here Response.Write(TempValue) DataGrid1.EditItemIndex = -1 BindGrid() End Sub ’DataGrid1_Update Public Sub DataGrid1_Cancel(ByVal sender As [Object], ByVal e As DataGridCommandEventArgs) DataGrid1.EditItemIndex = -1 BindGrid() End Sub ’DataGrid1_Cancel ’Functions used in Class GetData.cs Dim mycn As SqlConnection Dim myda As SqlDataAdapter Dim mycmd As SqlCommand Dim ds As DataSet Dim strConn As String Dim myReader As SqlDataReader Public Function GetDataFromTable(ByVal strSQL As String, ByVal strConnString As String) As DataSet Try strConn = strConnString mycn = New SqlConnection(strConn) myda = New SqlDataAdapter(strSQL, mycn) ds = New DataSet myda.Fill(ds, ‘Table’) Return ds Catch ex As Exception Throw New Exception(ex.Message.ToString()) Finally mycn.Close() End Try End Function ’GetDataFromTable Public Function GetSingleDataUsingReader(ByVal strSQL As String, ByVal strConnString As String) As SqlDataReader Try strConn = strConnString mycn = New SqlConnection(strConn) mycmd = New SqlCommand(strSQL, mycn) mycn.Open() myReader = mycmd.ExecuteReader(CommandBehavior.CloseConnection) Return myReader Catch ex As Exception Throw New Exception(ex.Message.ToString()) Finally End Try ’mycn.Close (); End Function ’GetSingleDataUsingReader C# string strDiscontinued; GetData obj; string strSql; string strConn; DataSet ds; SqlDataReader dr; private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here strConn =’server=localhost;uid=sa;pwd=;database=northwind’; if (!Page.IsPostBack ) { BindGrid(); } } //To Bind the DataGrid void BindGrid() { obj=new GetData (); strSql = ‘Select productid, discontinued from Products’; ds=obj.GetDataFromTable (strSql ,strConn); DataGrid1.DataSource =ds; DataGrid1.DataBind (); } //To display Yes/No for True/False protected string ShowVal(bool blnval) { if (blnval==true) { return ‘Yes’; } else { return ‘No’; } } //Bind the Data to the dropdownlist in the EditTemplate protected SqlDataReader BindTheDiscontinued() { obj=new GetData (); strSql =’SELECT distinct ’Discontinued’ =’ ; strSql+=’ CASE ‘; strSql+=’ WHEN Discontinued = 1 Then ’Yes’’ ; strSql+=’ ELSE ’No’’ ; strSql+=’ END ‘ ; strSql+=’ From Products ‘; dr=obj.GetSingleDataUsingReader (strSql ,strConn); return dr; } //Set the Text of the Dropdownlist to the field value in Database protected void SetDropDownIndex(Object sender ,System.EventArgs e ) { DropDownList ed ; ed = (DropDownList) sender; ed.SelectedIndex = ed.Items.IndexOf(ed.Items.FindByText(strDiscontinued)); } //For Edit Update Cancel public void DataGrid1_Edit(Object sender, DataGridCommandEventArgs e) { strDiscontinued = ((Label )e.Item.FindControl(‘lblDiscontinued’)).Text; DataGrid1.EditItemIndex = (int)e.Item.ItemIndex; BindGrid(); } public void DataGrid1_Update(Object sender, DataGridCommandEventArgs e) { DropDownList TempList ; String TempValue ; TempList = (DropDownList) e.Item.FindControl(‘ddlDiscontinued’); TempValue = TempList.SelectedItem.Value; //Place update code here Response.Write (TempValue); DataGrid1.EditItemIndex = -1; BindGrid(); } public void DataGrid1_Cancel(Object sender, DataGridCommandEventArgs e) { DataGrid1.EditItemIndex = -1; BindGrid(); } //Functions used in Class GetData.cs SqlConnection mycn; SqlDataAdapter myda; SqlCommand mycmd; DataSet ds; String strConn; SqlDataReader myReader; public DataSet GetDataFromTable(string strSQL ,string strConnString) { try { strConn=strConnString; mycn = new SqlConnection(strConn); myda = new SqlDataAdapter (strSQL, mycn); ds= new DataSet (); myda.Fill (ds,’Table’); return ds; } catch(Exception ex) { throw new Exception (ex.Message.ToString ()); } finally { mycn.Close (); } } public SqlDataReader GetSingleDataUsingReader(string strSQL ,string strConnString) { try { strConn=strConnString; mycn = new SqlConnection(strConn); mycmd = new SqlCommand (strSQL, mycn); mycn.Open (); myReader=mycmd.ExecuteReader(CommandBehavior.CloseConnection ); return myReader; } catch(Exception ex) { throw new Exception (ex.Message.ToString ()); } finally { //mycn.Close (); } }