convert funtion

27 Oct 2006 11:01 PM

gurvar

Hi I try this funation in SQL2K It works fine.
But I'm getting the following error in my ASPX Page
There was no problem with following statement when talking to SQL7.
Please advice. & Thanks

Select CONVERT(varchar(20),SUM(CONVERT(numeric(20),F.Fee_Amount_VC))) 'Fee
Sum' FROM Bpc_T S,Fee_T F WHERE (Active_Bpc_CH = 'Yes') AND
(F.Fee_Recurring_CH = 'Yes') AND (F.Series_ID IS NULL OR F.Series_ID = '0')
AND (S.Bpc_ID = F.Bpc_ID);An Error has occured:
System.Data.SqlClient.SqlException: Error converting data type varchar to
numeric. at System.Data.SqlClient.SqlDataReader.Read() at
WebApplication1.wfrmRRevenue.Bpc_RSum() in
C:\Inetpub\wwwroot\WebApplication1\wfrmRRevenue.aspx.vb:line 975

28 Oct 2006 5:43 AM

Cor Ligthert [MVP]

Gurvar,

Did you try it in your Query analyzer already, it seems more an SQL problem
than a dotNet problem.

dotNet is not using VarChar

Cor

Show quote

"gurvar" <gur***@discussions.microsoft.com> schreef in bericht
news:08CD1DB5-E69E-45DF-8678-B6A7A5CE8F3F@microsoft.com...
> Hi I try this funation in SQL2K It works fine.
> But I'm getting the following error in my ASPX Page
> There was no problem with following statement when talking to SQL7.
> Please advice. & Thanks
>
> Select CONVERT(varchar(20),SUM(CONVERT(numeric(20),F.Fee_Amount_VC))) 'Fee
> Sum' FROM Bpc_T S,Fee_T F WHERE (Active_Bpc_CH = 'Yes') AND
> (F.Fee_Recurring_CH = 'Yes') AND (F.Series_ID IS NULL OR F.Series_ID =
> '0')
> AND (S.Bpc_ID = F.Bpc_ID);An Error has occured:
> System.Data.SqlClient.SqlException: Error converting data type varchar to
> numeric. at System.Data.SqlClient.SqlDataReader.Read() at
> WebApplication1.wfrmRRevenue.Bpc_RSum() in
> C:\Inetpub\wwwroot\WebApplication1\wfrmRRevenue.aspx.vb:line 975
>

28 Oct 2006 1:10 PM

Otis Mukinfus

On Fri, 27 Oct 2006 16:01:01 -0700, gurvar <gur***@discussions.microsoft.com>
wrote:

>Hi I try this funation in SQL2K It works fine.
>But I'm getting the following error in my ASPX Page
>There was no problem with following statement when talking to SQL7.
>Please advice. & Thanks
>
>Select CONVERT(varchar(20),SUM(CONVERT(numeric(20),F.Fee_Amount_VC))) 'Fee
>Sum' FROM Bpc_T S,Fee_T F WHERE (Active_Bpc_CH = 'Yes') AND
>(F.Fee_Recurring_CH = 'Yes') AND (F.Series_ID IS NULL OR F.Series_ID = '0')
>AND (S.Bpc_ID = F.Bpc_ID);An Error has occured:
>System.Data.SqlClient.SqlException: Error converting data type varchar to
>numeric. at System.Data.SqlClient.SqlDataReader.Read() at
>WebApplication1.wfrmRRevenue.Bpc_RSum() in
>C:\Inetpub\wwwroot\WebApplication1\wfrmRRevenue.aspx.vb:line 975

This error means that the column you are trying to contains non-numeric data.
View the data in the column and see what is wrong with the data. It probably
contains Alpha characters instead of numeric.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

30 Oct 2006 4:56 AM

gurvar

It works fine in Querry Analyser. I get the expected SUM.
There are no Non numeric as such, I checked the data in the columns.

Is space an issue? if so how can I trim the values, or use suitable
Numeric() function with precisions?

Thanks for your feed back.

Show quote

"Otis Mukinfus" wrote:

> On Fri, 27 Oct 2006 16:01:01 -0700, gurvar <gur***@discussions.microsoft.com>
> wrote:
>
> >Hi I try this funation in SQL2K It works fine.
> >But I'm getting the following error in my ASPX Page
> >There was no problem with following statement when talking to SQL7.
> >Please advice. & Thanks
> >
> >Select CONVERT(varchar(20),SUM(CONVERT(numeric(20),F.Fee_Amount_VC))) 'Fee
> >Sum' FROM Bpc_T S,Fee_T F WHERE (Active_Bpc_CH = 'Yes') AND
> >(F.Fee_Recurring_CH = 'Yes') AND (F.Series_ID IS NULL OR F.Series_ID = '0')
> >AND (S.Bpc_ID = F.Bpc_ID);An Error has occured:
> >System.Data.SqlClient.SqlException: Error converting data type varchar to
> >numeric. at System.Data.SqlClient.SqlDataReader.Read() at
> >WebApplication1.wfrmRRevenue.Bpc_RSum() in
> >C:\Inetpub\wwwroot\WebApplication1\wfrmRRevenue.aspx.vb:line 975
>
> This error means that the column you are trying to contains non-numeric data.
> View the data in the column and see what is wrong with the data. It probably
> contains Alpha characters instead of numeric.
>
> Good luck with your project,
>
> Otis Mukinfus
> http://www.arltex.com
> http://www.tomchilders.com
>