locked
Specified cast is not valid RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello, I have the following code:

    if (dataTable.Columns.Count > 1)
                    {
                        strXML = ChartFileNameandPath(chartType) + @"?dataXML=<graph caption='Report ; Summary' subcaption='" + ReportName(reportType) + "' xAxisName='Line Number' yAxisName='Pounds made' rotatelabels='1' slantlabels='1' showNames='1' showvalues='0' numberPrefix='$' exportenabled='1' exportAtClient='1' exportHandler='fcExporter1' exportFormats='PNG=Save as PNG' >";
                        
                        //Getting the Subtotal of PoundsMade based on the Line Number column
                        //C# linq query
                        if (reportType == ReportEnums.Clientsdata) {
                            var query = from row in dataTable.AsEnumerable()
                                        group row by row.Field<string>("ClientName") into grp
                                        orderby grp.Key
                                        select new {
                                            Clientname = grp.Key,
                                            TotalAmountSpent = grp.Sum(r => r.Field<int>("TotalAmountSpent")) //error here, specified cast is not valid
                                        };

                            foreach (var grp in query) {
                                strXML = strXML + "<set name='" + grp.Clientname.ToString() + "' value='" + grp.TotalAmountSpent.ToString() + "'/>";
                            }
                        }

    I get a error "Specified cast is not valid"

    Part of my store procedure where I'm grabbing it from is:

    SELECT  ClientName,
		Mem_Name,
		Mem_Address,
		Mem_city,
		Mem_state,
		Sum(mv.AmountSpent) as TotalAmountSpent,
		(Sum(mv.AmountSpent)/ (CAST(s.TotalBusinessSales as decimal)) * 100)  as PercentAmountSpent

    My question is: Am I getting an error because of TotalAmountSpent is an alias?

    Thanks,

    N

    Tuesday, October 7, 2014 6:57 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    TotalAmountSpent = grp.Sum(r => r.Field<int>("TotalAmountSpent")) //error here, specified cast is not valid }; foreach (var grp in query)
    ....
    I get a error "Specified cast is not valid"

    Hello Nonabona,

    Which Kind of Exception are you getting, a SQL or an InvalidCast-Exception? What data type do the column "AmountSpent" have? Amount of money is in common a decimal (money) and in your code you try to get it as an integer value; that could cause "cast is not valid" error.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Nonabona Wednesday, October 8, 2014 5:58 PM
    Wednesday, October 8, 2014 6:43 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    what are the distinct type of values in TotalBusinessSales? Check the data once.

    --Prashanth

    Tuesday, October 7, 2014 7:05 PM
    Answerer
  • Question
    Sign in to vote
    0
    Sign in to vote 
    ALTER PROCEDURE usp_Clienttransaction (@FromDate Varchar (30), @ToDate Varchar (30), @Active int)
AS
SELECT  ClientName,
		Mem_Name,
		Mem_Address,
		Mem_city,
		Mem_state,
		Sum(mv.AmountSpent) as TotalAmountSpent,
		(Sum(mv.AmountSpent)/ (CAST(s.TotalBusinessSales as decimal)) * 100)  as PercentAmountSpent

FROM UnitedDiningClub.dbo.UDC_Member

JOIN UnitedDiningClub.dbo.UDC_MemberVisits mv
ON UDC_Member.Mem_ID = mv.MemberId
JOIN UnitedDiningClub.dbo.UDClub_Client c
ON c.ClientId = mv.RestaurantId
INNER JOIN   
		( SELECT RestaurantId, sum(AmountSpent) as TotalBusinessSales
		FROM UnitedDiningClub.dbo.UDC_MemberVisits 
		GROUP BY RestaurantId) s
		ON s.RestaurantId = c.ClientId

GROUP by ClientName, Mem_Name, Mem_Address , Mem_city , Mem_state, s.TotalBusinessSales
ORDER BY ClientName
    Thats the full procedure.
    Its coming from a sub query. Would an alias effect the results in c#?
    • Edited by Nonabona Tuesday, October 7, 2014 7:11 PM
    Tuesday, October 7, 2014 7:10 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    TotalAmountSpent = grp.Sum(r => r.Field<int>("TotalAmountSpent")) //error here, specified cast is not valid }; foreach (var grp in query)
    ....
    I get a error "Specified cast is not valid"

    Hello Nonabona,

    Which Kind of Exception are you getting, a SQL or an InvalidCast-Exception? What data type do the column "AmountSpent" have? Amount of money is in common a decimal (money) and in your code you try to get it as an integer value; that could cause "cast is not valid" error.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Nonabona Wednesday, October 8, 2014 5:58 PM
    Wednesday, October 8, 2014 6:43 AM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Hi Nonabona,

    I don’t think you get the error because TotalAmountSpent is an alias.

    I think you get the error because the real data type of TotalAmountSpent is not int. In your dataset it could be one of the data types below:

    • Int16
    • Int32
    • Int64
    • SByte
    • Single
    • UInt16
    • UInt32
    • UInt64

    So please debug and confirm the data type of column TotalAmountSpent in your dataset first.

    If the data type is Int64, you can use the following code:

    TotalAmountSpent = grp.Sum(r => r.Field< Int64>("TotalAmountSpent"))

    If you have any question, please feel free to let me know.

    Regards,
    Donghui Li

    Wednesday, October 8, 2014 9:04 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Thanks guys, it was different datatypes.
     From decimal to int.
    • Edited by Nonabona Wednesday, October 8, 2014 5:56 PM
    Wednesday, October 8, 2014 5:55 PM