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(busine ss 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,Col umnName....). 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").Ro ws.Count - 1

If Not dsTyped.Tables( "TableName").Ro ws(iRow)(Column Name) Is
GetType(System. Int32) ThendsTyped.Tab les("TableName" ).Rows(iRow)(Co lumnName)
= CType(dsTyped.T ables("TableNam e").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.Dat aSet ds = new System.Data.Dat aSet();

System.Data.Sql Client.SqlConne ction sqlConn = new
System.Data.Sql Client.SqlConne ction("Data
Source=MACNAME; Initial Catalog=Pubs;Us er
Id=sa;Password= ;");

sqlConn.Open();

System.Data.Sql Client.SqlDataA dapter sqlAdp = new
System.Data.Sql Client.SqlDataA dapter("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

-----Original Message-----
Hi,

I have a column in my WinGrid, that's being populated with numeric data, butit's of String datatype(busine ss rule requires to have these numbers asvarchars in DB). On top of that, I need to be able to sort on thatparticular column. But because column contains character data(even though itlooks 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 asa datasource for the grid, so I changed the column's datatype in thedatasource to Int32. Additionaly, I cast the column when I retrieve it inProc.(SELECT CONVERT(INT,Col umnName....). But it doesn't work, I mean it'sgetting 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").Ro ws.Count - 1
If Not dsTyped.Tables( "TableName").Ro ws(iRow) (ColumnName) IsGetType(System .Int32) ThendsTyped.Tab les ("TableName").R ows(iRow)(Colum nName)= CType(dsTyped.T ables("TableNam e").Rows(iRo w) (ColumnName), System.Int32)
End If

Next

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

Thanks, Alex Ayzin
.

[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" <Al********@ver izon.net> wrote in message
news:eX******** ******@TK2MSFTN GP12.phx.gbl...

Hi,

I have a column in my WinGrid, that's being populated with numeric data, but it's of String datatype(busine ss 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,Col umnName....). 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").Ro ws.Count - 1

If Not dsTyped.Tables( "TableName").Ro ws(iRow)(Column Name) Is
GetType(System. Int32) ThendsTyped.Tab les("TableName" ).Rows(iRow)(Co lumnName) = CType(dsTyped.T ables("TableNam e").Rows(iRow)( ColumnName), System.Int32)

End If

Next

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

Thanks, Alex Ayzin

[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
"CJ Taylor" <no****@blowgoa ts.com> wrote in message
news:vt******** ****@corp.super news.com...

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" <Al********@ver izon.net> wrote in message
news:eX******** ******@TK2MSFTN GP12.phx.gbl...

Hi,

I have a column in my WinGrid, that's being populated with numeric data, but

it's of String datatype(busine ss 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,Col umnName....). 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").Ro ws.Count - 1

If Not dsTyped.Tables( "TableName").Ro ws(iRow)(Column Name) Is
GetType(System. Int32)

ThendsTyped.Tab les("TableName" ).Rows(iRow)(Co lumnName)

= CType(dsTyped.T ables("TableNam e").Rows(iRow)( ColumnName), System.Int32)
End If

Next

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

Thanks, Alex Ayzin