Sorting problem: Conversion from String(varchar) to Int32

**Puneet Taneja** · Jul 21 '05, 11:13 AM

Sorting problem: Conversion from String(varchar) to Int32

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
[color=blue]
>-----Original Message-----
>Hi,
>
>I have a column in my WinGrid, that's being populated[/color]
with numeric data, but[color=blue]
>it's of String datatype(busine ss rule requires to have[/color]
these numbers as[color=blue]
>varchars in DB). On top of that, I need to be able to[/color]
sort on that[color=blue]
>particular column. But because column contains character[/color]
data(even though it[color=blue]
>looks like digits), the sorting is all out of whack,[/color]
e.g, 1, 11, 12,[color=blue]
>13.....2, 21, 22, so on, you got the picture.I'm using[/color]
the typed dataset as[color=blue]
>a datasource for the grid, so I changed the column's[/color]
datatype in the[color=blue]
>datasource to Int32. Additionaly, I cast the column when[/color]
I retrieve it in[color=blue]
>Proc.(SELECT CONVERT(INT,Col umnName....). But it doesn't[/color]
work, I mean it's[color=blue]
>getting sorted in exactly the same way it did before.
>I also tried to explicitly cast it before setting the[/color]
grid's datasource:[color=blue]
>For iRow = 0 To dsTyped.Tables( "TableName").Ro ws.Count -[/color]
1[color=blue]
>
>If Not dsTyped.Tables( "TableName").Ro ws(iRow)[/color]
(ColumnName) Is[color=blue]
>GetType(System .Int32) ThendsTyped.Tab les[/color]
("TableName").R ows(iRow)(Colum nName)[color=blue]
>= CType(dsTyped.T ables("TableNam e").Rows(iRo w)[/color]
(ColumnName), System.Int32)[color=blue]
>
>End If
>
>Next
>
>Please advise or tell me what I'm doing wrong.
>
>Thanks, Alex Ayzin
>
>
>.
>[/color]

**CJ Taylor** · Jul 21 '05, 11:13 AM

Re: Sorting problem: Conversion from String(varchar) to Int32

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" <Alex.Ayzin@ver izon.net> wrote in message
news:eXjoKg4wDH A.1272@TK2MSFTN GP12.phx.gbl...[color=blue]
> Hi,
>
> I have a column in my WinGrid, that's being populated with numeric data,[/color]
but[color=blue]
> 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[/color]
it[color=blue]
> 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[/color]
as[color=blue]
> 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)[/color]
ThendsTyped.Tab les("TableName" ).Rows(iRow)(Co lumnName)[color=blue]
> = 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
>
>[/color]

**Alex Ayzin** · Jul 21 '05, 11:14 AM

Re: Sorting problem: Conversion from String(varchar) to Int32

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" <nospam@blowgoa ts.com> wrote in message
news:vtu66ln28p v350@corp.super news.com...[color=blue]
> If the Typed dataset is set to string for that column, then it will[/color]
override[color=blue]
> 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" <Alex.Ayzin@ver izon.net> wrote in message
> news:eXjoKg4wDH A.1272@TK2MSFTN GP12.phx.gbl...[color=green]
> > Hi,
> >
> > I have a column in my WinGrid, that's being populated with numeric data,[/color]
> but[color=green]
> > 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[/color][/color]
though[color=blue]
> it[color=green]
> > 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[/color]
> as[color=green]
> > 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[/color][/color]
in[color=blue][color=green]
> > Proc.(SELECT CONVERT(INT,Col umnName....). But it doesn't work, I mean[/color][/color]
it's[color=blue][color=green]
> > 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)[/color]
> ThendsTyped.Tab les("TableName" ).Rows(iRow)(Co lumnName)[color=green]
> > = CType(dsTyped.T ables("TableNam e").Rows(iRow)( ColumnName),[/color][/color]
System.Int32)[color=blue][color=green]
> >
> > End If
> >
> > Next
> >
> > Please advise or tell me what I'm doing wrong.
> >
> > Thanks, Alex Ayzin
> >
> >[/color]
>
>[/color]

Sorting problem: Conversion from String(varchar) to Int32

Sorting problem: Conversion from String(varchar) to Int32

Comment

Comment

Comment