Sorting problem: Conversion from String(varchar) to Int32

[Alex Ayzin]

Hi,

I have a column in my WinGrid, that's being populated with numeric data, but
it's of String datatype(business rule requires to have these numbers as
varchars in DB). On top of that, I need to be able to sort on that
particular column. But because column contains character data(even though it
looks like digits), the sorting is all out of whack, e.g, 1, 11, 12,
13.....2, 21, 22, so on, you got the picture.I'm using the typed dataset as
a datasource for the grid, so I changed the column's datatype in the
datasource to Int32. Additionaly, I cast the column when I retrieve it in
Proc.(SELECT CONVERT(INT,ColumnName....). But it doesn't work, I mean it's
getting sorted in exactly the same way it did before.
I also tried to explicitly cast it before setting the grid's datasource:
For iRow = 0 To dsTyped.Tables("TableName").Rows.Count - 1

If Not dsTyped.Tables("TableName").Rows(iRow)(ColumnName) Is
GetType(System.Int32) ThendsTyped.Tables("TableName").Rows(iRow)(ColumnName)
= CType(dsTyped.Tables("TableName").Rows(iRow)(ColumnName), System.Int32)

End If

Next

Please advise or tell me what I'm doing wrong.

Thanks, Alex Ayzin

 

[Puneet Taneja]

Dear Alex,

I have tried the following select query....
Select discounttype, stor_id, lowqty, highqty, discount,
Convert(int, PTColumn) From Discounts

This is a table of the Pubs database with PTColumn as a
new varchar column added.

I have tried opening the dataset (through SQLConnection,
SQLAdapter....) and then bound it to the datagrid control.
Following is the code snippet...

System.Data.DataSet ds = new System.Data.DataSet();

System.Data.SqlClient.SqlConnection sqlConn = new
System.Data.SqlClient.SqlConnection("Data
Source=MACNAME;Initial Catalog=Pubs;User
Id=sa;Password=;");

sqlConn.Open();

System.Data.SqlClient.SqlDataAdapter sqlAdp = new
System.Data.SqlClient.SqlDataAdapter("Select
discounttype, stor_id, lowqty, highqty, discount, Convert
(int, PTColumn) From Discounts", sqlConn);

sqlAdp.Fill(ds);

this.dataGrid1.DataSource = ds;

I have tried this out and it works fine. Also in case if
you are using typed dataset, ensure that in the InitClass
() function the column constructor is called with the
datatype set to typeof(int).

Hope this may resolve your problem.

Regards,
Puneet Taneja

 

[CJ Taylor]

If the Typed dataset is set to string for that column, then it will override
whatever you send into it.

So, even though your converting it in two different ways, the final result
in the typed dataset is that it is defined as a string...

So...

the value is converted to string which is why your sort doesn't work.

Fix...

change your typed dataset.

-CJ

 

[Alex Ayzin]

OK, thanks a lot, guys for all of your replies. Here's what I've done and
it's working great:

1. Cast numeric text inside the procedure;
2. In the typed dataset, change the datatype of the column in question from
String to Int(it's gotta be Int, not Integer, otherwise exception is
thrown, type mismatch).
3. Setup SortIndicator(I'm using Infragistics WinGrid).

That's it. Works great.
Thanks again,
--Alex Ayzin