how to sort datatable which each column contains both string and numbers

Question

0.00/5 (No votes)

See more:

hi all

i would like to sort the datagrid which each column contains both string and numbers.

i want it sorted in numeric order and make the string as the bottom ones in the datatable.

The dataview.sort=columnName+"DESC" can just let me sort it in natural order.

for example ,the quantitiy column sort as 1,10,100,1000,2,20,200,N/A. And this is not what i

want to achieve.

Does anyone have any idea about this

thanks

star

Posted 15-Aug-13 14:40pm

codingStar

Add a Solution

Comments

idenizeni 15-Aug-13 23:56pm

How are you filling your datagrid? Where is this code? When you want to sort numbers like numbers the datacolumn must be typed as numeric. If it's typed as a string then it will sort like you are seeing. As your data has both text and numbers in your column you cannot type it as a number, therefore you must do something like RedDk posted. You can do this in C# and if you post the code you use to fill the datagrid we may be able to help.

codingStar 16-Aug-13 0:31am

hi,RLH68

i really appreciate your help.
dataGrid1.MouseUp += dgDataGrid_MouseUp;
private void dgDataGrid_MouseUp(object sender, System.Windows.Forms.MouseEventArgs e)
{
if (dataGrid1.VisibleRowCount == 0) return;
DataGridHelper.SortDataGrid(sender, e);
}

dataGrid1.DataSource = table;

i have attached part of the code and i create the datatable myself and fill the table rows and columns from the value returned from webservice. I do have some columns contains both string and numbers

thanks for the answer. But the problem is i am using this helper class, i am tring to modify it to get it work. Do you know how can i achieve what i want please

public static class DataGridHelper
{
static bool isAscending;

///

/// Sort data according to DataTable
///

/// <param name="sender"></param>
/// <param name="e"></param>
public static void SortDataGrid(object sender, System.Windows.Forms.MouseEventArgs e)
{

DataGrid.HitTestInfo hitTest;
DataTable dataTable;
DataView dataView;
string columnName;
DataGrid dataGrid;
// Use only left mouse button clicks.
if (e.Button == MouseButtons.Left)
{
// Set dataGrid equal to the object that called this event handler.
dataGrid = (DataGrid)sender;
// Perform a hit test to determine where the mousedown event occured.
hitTest = dataGrid.HitTest(e.X, e.Y);
// If the MouseDown event occured on a column header,
// then perform the sorting operation.
if (hitTest.Type == DataGrid.HitTestType.ColumnHeader)
{
// Get the DataTable associated with this datagrid.

dataTable = (DataTable)dataGrid.DataSource;
// Get the DataView associated with the DataTable.
dataView = dataTable.DefaultView;

columnName = "";
// Get the name of the column that was clicked.
if (dataGrid.TableStyles.Count != 0)
{
foreach (DataGridTableStyle item in dataGrid.TableStyles)
{
if (item.MappingName == dataTable.TableName)
{
columnName = item.GridColumnStyles[hitTest.Column].MappingName;
}
}

}
else
{
columnName = dataTable.Columns[hitTest.Column].ColumnName;
}
// If the sort property of the DataView is already the current
// column name, sort that column in descending order.
// Otherwise, sort on the column name.
if (String.IsNullOrEmpty(dataView.Sort))
{
dataView.Sort = columnName + " DESC";
}
else if (dataView.Sort == columnName + " ASC")
{
dataView.Sort = columnName + " DESC";
}
else
dataView.Sort = columnName + " ASC";
}
}
}

thanks a lot

star

idenizeni 16-Aug-13 1:08am

You don't need to fix this in the helper class, that will just complicate your problem. You said...

"dataGrid1.DataSource = table;"

... where is the code where you fill the above table with data?

codingStar 16-Aug-13 1:20am

hi,RLH68

This part of code fill the table i mentioned. It's just a normal table and has been filled based on the return json string from webservice.

thanks

peter

var jsonString = ws.getAllItemsInLot(UserInformation.UserWarehouse, lotNumber);

Dictionary<string, string=""> receivedObject = JsonConvert.DeserializeObject<dictionary<string, string="">>(jsonString);

if (receivedObject["itemsArrayJsonObject"] == "[]" || String.IsNullOrEmpty(receivedObject["itemsArrayJsonObject"]))
{
ClearMenuItem_Click();
Cursor.Current = Cursors.Default;
throw new ItemNotFoundException("No lot found!");
}

List<dictionary<string, string="">> locationAndItems = JsonConvert.DeserializeObject<List<dictionary<string, string="">>>(receivedObject["itemsArrayJsonObject"]);

DataTable table = new DataTable();
table.TableName = "LotEnquiryTable";

table.Columns.Add("PrioDte");
table.Columns.Add("Location");
table.Columns.Add("ItemNo");
table.Columns.Add("Qty");

table.Columns.Add("NetAvail");
table.Columns.Add("BUoM");
table.Columns.Add("CW");
table.Columns.Add("Container");
table.Columns.Add("IncludedIn");
table.Columns.Add("Sts");

foreach (var item in locationAndItems)
{
DataRow row = table.NewRow();
var numberOfDecimals = Convert.ToInt16(item["MMDCCD"]);

row["PrioDte"] = item["MLPRDT"].ConvertToDateTimeString(UserInformation.ServerDateFormat, UserInformation.DeviceDateFormat);
row["Location"] = item["MLWHSL"];
row["ItemNo"] = item["MMITNO"];
row["Qty"] = decimal.Round(item["MLSTQT"].ConvertToDecimal(), numberOfDecimals).ToString();

decimal itemLQT = item["MLSTQT"].ConvertToDecimal() - item["MLALQT"].ConvertToDecimal() * item["MLALOC"].ConvertToDecimal();
row["NetAvail"] = decimal.Round(itemLQT, numberOfDecimals).ToString();

row["BUoM"] = item["MMUNMS"];
if (item.Keys.Contains("MLCAWE"))
{
row["CW"] = decimal.Round(item["MLCAWE"].ConvertToDecimal(), 2).ToString();

}
else
row["CW"] = "N/A";
row["Container"] = item["MLCAMU"];

if (item.Keys.Contains("MLOPAN"))
{
row["IncludedIn"] = item["MLOPAN"];
}
else
row["IncludedIn"] = "N/A";
row["Sts"] = item["MLSTAS"];
table.Rows.Add(row);
}

dataGrid1.DataSource = table;

codingStar 16-Aug-13 1:23am

As you can see, some of the colums has to be defined as string since it could be something like "ASZ00112" ,"1234675","N/A".

thanks

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RedDk · Answer 1 · 2013-08-15T14:54:00

Solution 1

Here is one solution but it's strictly TSQL ("datatable"?) and c#4.0 would be the frontend:

USE [cpqaAnswers]
GO
CREATE TABLE [cpqa].[tbl_CS_zeropadSolution003]
(
	P_Id int,
		amount varchar(50)
)
INSERT INTO [cpqa].[tbl_CS_zeropadSolution003]
	VALUES(1, '2340'),
	(2, '4568'),
	(3, '10000')
	
SELECT * FROM [cpqa].[tbl_CS_zeropadSolution003] -- trouble with "ints" as "chars"
SELECT * FROM [cpqa].[tbl_CS_zeropadSolution003] ORDER BY [amount] -- not right (sic)

How many places is the question ... arbitrarily pick a number, here six (6) ... but it doesn't really matter ... the objectionable sort too:

SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_CS_zeropadSolution003]
/*
 result:

 P_Id	zeropadded_amount
    1	           002340
    2	           004568
    3	           010000
*/

Here's the good sort:

SELECT [P_Id], LEFT(REPLICATE('0', 6), 6 - LEN([amount])) + [amount] AS [zeropadded_amount] FROM [cpqa].[tbl_CS_zeropadSolution003] ORDER BY [zeropadded_amount] DESC
/*
 result:

 P_Id	zeropadded_amount
 ~~~~~~~~~~~~~~~~~~~~~~~~
    3	           010000
    2	           004568
    1	           002340
*/

Posted 15-Aug-13 14:54pm

RedDk

Comments

codingStar 15-Aug-13 21:25pm

thanks for the answer. But the problem is i am using this helper class, i am tring to modify it to get it work. Do you know how can i achieve what i want please

public static class DataGridHelper
{
static bool isAscending;

///

/// Sort data according to DataTable
///

/// <param name="sender"></param>
/// <param name="e"></param>
public static void SortDataGrid(object sender, System.Windows.Forms.MouseEventArgs e)
{

DataGrid.HitTestInfo hitTest;
DataTable dataTable;
DataView dataView;
string columnName;
DataGrid dataGrid;
// Use only left mouse button clicks.
if (e.Button == MouseButtons.Left)
{
// Set dataGrid equal to the object that called this event handler.
dataGrid = (DataGrid)sender;
// Perform a hit test to determine where the mousedown event occured.
hitTest = dataGrid.HitTest(e.X, e.Y);
// If the MouseDown event occured on a column header,
// then perform the sorting operation.
if (hitTest.Type == DataGrid.HitTestType.ColumnHeader)
{
// Get the DataTable associated with this datagrid.

dataTable = (DataTable)dataGrid.DataSource;
// Get the DataView associated with the DataTable.
dataView = dataTable.DefaultView;

columnName = "";
// Get the name of the column that was clicked.
if (dataGrid.TableStyles.Count != 0)
{
foreach (DataGridTableStyle item in dataGrid.TableStyles)
{
if (item.MappingName == dataTable.TableName)
{
columnName = item.GridColumnStyles[hitTest.Column].MappingName;
}
}

}
else
{
columnName = dataTable.Columns[hitTest.Column].ColumnName;
}
// If the sort property of the DataView is already the current
// column name, sort that column in descending order.
// Otherwise, sort on the column name.
if (String.IsNullOrEmpty(dataView.Sort))
{
dataView.Sort = columnName + " DESC";
}
else if (dataView.Sort == columnName + " ASC")
{
dataView.Sort = columnName + " DESC";
}
else
dataView.Sort = columnName + " ASC";
}
}
}

idenizeni · Answer 2 · 2013-08-15T20:27:00

C#

// let's say you have mixed text and numbers in the ItemNo column
// when you create your table, add another column that will be used to sort for this column
table.Columns.Add("SortKeyItemNo");

C#

// set the value for the sort key in your foreach loop when you populate your table
row["SortKeyItemNo"] = MakeSortKey(item["ItemNo"]);

C#

// routine to create sort key
string MakeSortKey(string value)
{
    string key = value; // initialize key to incoming value
    Int32 num = 0;
    bool result = Int32.TryParse(value, out num);
    if (result)
    {
        // override incoming value with padding value
        // prefixes leading zeros to number, until the number contains 6 characters           
        key = num.ToString().PadLeft(6, '0');
    }
    return key;
}

I was wrong, you will have to modify the datagrid helper class. I was thinking web form not win form, my mistake.

C#

// in the datagrid helper class, add this line above the code after you set the columnName variable but before you set the dataView.Sort property.
if(table.Columns.Contains("SortKeyItemNo"))
{
    columnName = "SortKey" + columnName;
}

Place it in the datagrid helper class, where the [...place here...] text is...

C#

columnName = ""; 
// Get the name of the column that was clicked. 
if (dataGrid.TableStyles.Count != 0) 
{ 
    foreach (DataGridTableStyle item in dataGrid.TableStyles) 
    { 
        if (item.MappingName == dataTable.TableName) 
        { 
            columnName = item.GridColumnStyles[hitTest.Column].MappingName; 
        } 
    } 
} 
else 
{ 
    columnName = dataTable.Columns[hitTest.Column].ColumnName; 
}

[...place here...]

// If the sort property of the DataView is already the current 
// column name, sort that column in descending order. 
// Otherwise, sort on the column name. 
if (String.IsNullOrEmpty(dataView.Sort)) 
{ 
    dataView.Sort = columnName + " DESC"; 
} 
else if (dataView.Sort == columnName + " ASC")
{
    dataView.Sort = columnName + " DESC"; 
} 
else dataView.Sort = columnName + " ASC";