CODEWITHSUNDEEP
asp.net-mvc-3sql-server-2008stored-procedures
SoftwareNerd
SoftwareNerd

Reputation: 1905

Geting an error when executin the stored Procedure in my MVC3?

Hi all i have method where i pass some parameters to the stored procedure and when i execute it i get this error

[The specified cast from a materialized 'System.Int32' type to the 'System.String' type is not valid.]

this is how i am calling my storedprocedure

  public JsonResult CreatedBugs()
    {
        int year;
        int month;
        int projectid;
        year = 2012;
        month = 8;
        projectid = 16;
        var loggedbugs = db.ExecuteStoreQuery<LoggedBugs>("LoggedBugs @Year,@Month,@ProjectID", new SqlParameter("@Year",year ), new SqlParameter("@Month",  month ), new SqlParameter("@ProjectID", projectid )).ToList();
        var ClosedBugs = db.ExecuteStoreQuery<LoggedBugs>("ClosedBugs @Year,@Month,@ProjectID", new SqlParameter("@Year", year), new SqlParameter("@Month", month), new SqlParameter("@ProjectID", projectid)).ToList();
        var model = new LoggedBugs
        {
            LoggedBugsCount = loggedbugs,
            ClosedBugs = ClosedBugs
        };
        return Json(model, JsonRequestBehavior.AllowGet);
    }

and this is my StoredProcedure

      alter procedure LoggedBugs
      (
      @Year int,
      @Month int,
      @ProjectID int
      )
      as
      begin
       SELECT projectName,
    ProjectYear,
    ProjectMonth, 
      Week1, Week2, Week3, Week4, Week5
          FROM (
 Select  p.projectName,YEAR(b.CreatedDate) ProjectYear, MONTH(b.CreatedDate) ProjectMonth,
        'Week' + CAST(DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, b.CreatedDate), 0), b.CreatedDate)+1 AS VARCHAR) as [Weeks],
        b.BUGID
From    BUGS b inner join projects p on b.ProjectId = p.ProjectId
Where   DatePart(year, CreatedDate) = @Year and datepart(month, Createddate) = @Month and b.projectid = @ProjectID 
  ) p
   PIVOT (COUNT(BUGID) FOR [Weeks] IN (WEEK1, WEEK2, WEEK3, Week4, Week5)) AS pvt
 end

where am i doing wrong here

Edit1

here is my LoggedBug Class

 public class LoggedBugs
{
    public string projectName { get; set; }
    public string ProjectYear { get; set; }
    public string ProjectMonth { get; set; }
    public string Week1 { get; set; }
    public string Week2 { get; set; }
    public string Week3 { get; set; }
    public string Week4 { get; set; }
    public string Week5 { get; set; }
    public IEnumerable<LoggedBugs> LoggedBugsCount { get; set; }
    public IEnumerable<LoggedBugs> ClosedBugs { get; set; }
}

Upvotes: 0

Views: 716

Answers (2)

nuhusky2003
nuhusky2003

Reputation: 366

Change your ProjectYear and ProjectMonth properties to type int in LoggedBugs class. Your sql returns them as int, see YEAR SQL MSDN description since YEAR and MONTH sql functions both return values of type int. Your C# code expects a string, so you get a type mismatch exception.

Upvotes: 0

Eligos
Eligos

Reputation: 36

It can be problem with ProjectYear and ProjectMonth. At database side they are int, so at .NET side they would propably unboxed as System.Int32. In your entity they are string. Try convert it to varchar in your sp or change types in entity to int.

Upvotes: 1

Related Questions