System.Byte[] displaying in Datagrid column

Question

All columns are displaying correctly in the DataGrid except for one. I ran this sql query in MySqlWorkbench and it works so there is nothing wrong with that:

  string query = @"SELECT c.*,cc.CountryName ,(Select UserName FROM users WHERE User_ID = c.CreatedByUser) AS CreatedBy,
                      (select GROUP_CONCAT(AreaDescription) from pxpcountycodes where countyid=c.id) as AreaDescription, 
                      (select GROUP_CONCAT(cmr.AccountCode)) as Member  " +
                    " FROM counties c " +
                    " LEFT JOIN Countries cc ON c.CountryID = cc.ID " +
                    " LEFT JOIN PXPCountyCodes PXPc on c.ID = PXPc.CountyID" +
                    " LEFT JOIN customer cmr ON PXPc.MemberID = cmr.ID  " +
                    " WHERE c.Company_ID = ?companyID ";

And the code for the DataGrid:

<asp:DataGrid runat="server" CssClass="tblResults" OnItemDataBound="dgList_ItemCreated" AllowSorting="true" OnSortCommand="dgList_Sort" ID="dgList" DataKeyField="ID" AutoGenerateColumns="false">
                <HeaderStyle CssClass="tblResultsHeader" />
                <AlternatingItemStyle BackColor="#EEEEEE" />
                <Columns>
                    <asp:BoundColumn DataField="CountyName" HeaderText="County Name/PostCode" SortExpression="c.CountyName" ></asp:BoundColumn>
                    <asp:BoundColumn DataField="Description" HeaderText="Description" SortExpression="c.Description"></asp:BoundColumn>   
                    <asp:BoundColumn DataField="Member" HeaderText="Member" SortExpression="Member"/> 
                    <asp:BoundColumn DataField="CountryName" HeaderText="Country" SortExpression="cc.CountryName"></asp:BoundColumn>                   
                    <asp:BoundColumn DataField="CountyPostCode" HeaderText="County/PostCode" SortExpression="c.CountyPostCode"></asp:BoundColumn>   
                    </Columns>
            </asp:DataGrid>

The problem is with the Member column. This column displays a list of companies, so for example the column should display CONWAY,NGWCLARE. Some columns are trying to display around 5 companies so is the length causing a problem?

Code behind Grid:

DataSet ds = Lookups.County.GetAllCounties(Company.Current.CompanyID, ddlSearchBy.SelectedItem.Value, txtSearchBy.Text, IsActive, rbl_both.Checked, OrderBy, false, -1, "", 0);

        if (ds.Tables[0].Rows.Count > 0)
        {
            dgList.DataSource = ds.Tables[0];
            dgList.DataBind();
        }

Can you post the code behind code? How you are binding the DataGrid? — Rahul Singh
– Rahul Singh, Commented Dec 14, 2015 at 10:36
@RahulSingh I added the code to my question. Yes I am binding the DataGrid. All the columns except the Member are all displaying — user123456789
– user123456789, Commented Dec 14, 2015 at 10:53
Is there really the need of SELECT before GROUP_CONCAT(cmr.AccountCode)? — sachin
– sachin, Commented Dec 14, 2015 at 11:13
@user123456789 Did you inspect the content of ds.Tables[0] in debug mode? — sachin
– sachin, Commented Dec 14, 2015 at 11:26

sachin · Accepted Answer · 2015-12-14 12:10:26Z

1

Try this query. CASTing GROUP_CONCAT's output AS CHAR should fix the issue.

 string query = @"SELECT c.*,cc.CountryName ,(Select UserName FROM users WHERE User_ID = c.CreatedByUser) AS CreatedBy,
                      (select GROUP_CONCAT(AreaDescription) from pxpcountycodes where countyid=c.id) as AreaDescription, 
                      CAST(GROUP_CONCAT(cmr.AccountCode) As CHAR) as Member  " +
                    " FROM counties c " +
                    " LEFT JOIN Countries cc ON c.CountryID = cc.ID " +
                    " LEFT JOIN PXPCountyCodes PXPc on c.ID = PXPc.CountyID" +
                    " LEFT JOIN customer cmr ON PXPc.MemberID = cmr.ID  " +
                    " WHERE c.Company_ID = ?companyID ";

answered Dec 14, 2015 at 12:10

sachin

2,36114 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

System.Byte[] displaying in Datagrid column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related