Counting number of unread messages in TABLE is problematic RSS

• • Reply
  

  jameskent

  0 Points

  2 Posts

  Counting number of unread messages in TABLE is problematic

  May 15, 2012 01:51 PM|LINK

  I have made a helper class, made up of:

   

  @helper RetrievePhotoWithName(int userid)
  {
      var database = Database.Open("SC");
      var name = database.QuerySingle("select FirstName, LastName, ProfilePicture from UserProfile where UserId = @0", userid);
      var notifications =  database.Query("SELECT COUNT(*) as 'counter' FROM Notifications WHERE UserID = @0 AND [Read] = @1", userid, false);
   
      var DisplayName = "";
      if(notifications["counter"] < 1)
      {
          DisplayName = name["FirstName"] + " " + name["LastName"];
      }
      else
      {
          DisplayName = name["FirstName"] + ", you have " + notifications["counter"] + " new messages.";
      }
      <a href="@Href("~/Home")" title="My Account"><img src="@Href("~/Shared/Assets/Images/" + name["ProfilePicture"] + ".png")" id="MiniProfilePicture" />&nbsp;@DisplayName</a>
   
      database.Close();
  }

   

  And in the Default.cshtml page, I am outputting the results like this:

  @JTS.RetrievePhotoWithName(WebSecurity.CurrentUserId)

   

  What I'm trying to do, is get the number of unread messages in the Notifications table and if the count is less than 1, output the users name, if it's equal to 1 or greater, output a message like "you have N new messages"

   

  But it's giving me errors, mainly stuff like "cannot convert type dynamic to type int" etc. What gives?

  • Edited by jameskent on May 15, 2012 01:51 PM
  • Edited by jameskent on May 15, 2012 01:51 PM
• • Reply
  

  GmGregori

  Contributor

  5470 Points

  737 Posts

  Re: Counting number of unread messages in TABLE is problematic

  May 15, 2012 02:26 PM|LINK

  Try modifying like follows your code:

  @helper RetrievePhotoWithName(int userid)
  {
      var database = Database.Open("SC");
      var name = database.QuerySingle("select FirstName, LastName, ProfilePicture from UserProfile where UserId = @0", userid);
      var notifications =  database.QueryValue("SELECT COUNT(*) FROM Notifications WHERE UserID = @0 AND [Read] = @1", userid, false);
    
      var DisplayName = "";
      if(notifications < 1)
      {
          DisplayName = name["FirstName"] + " " + name["LastName"];
      }
      else
      {
          DisplayName = name["FirstName"] + ", you have " + notifications.ToString() + " new messages.";
      }
      <a href="@Href("~/Home")" title="My Account"><img src="@Href("~/Shared/Assets/Images/" + name["ProfilePicture"] + ".png")" id="MiniProfilePicture" />&nbsp;@DisplayName</a>
    
      database.Close();
  }

• • Reply
  

  jameskent

  0 Points

  2 Posts

  Re: Counting number of unread messages in TABLE is problematic

  May 15, 2012 06:24 PM|LINK

  That was the code that I had originally written before but unfortunately it doesn't work. It just outputs my first and last name. I don't understand why this is not working. Any ideas what it could be?

   

  I keep going over and over this in my head and trying every possible thing I can think of but nothing has helped so far.

• • Reply
  

  GmGregori

  Contributor

  5470 Points

  737 Posts

  Re: Counting number of unread messages in TABLE is problematic

  May 15, 2012 11:38 PM|LINK

  I don't understand why you have this result.

  I have tested the helper with a database like yours and it's ok for me.

  Try with a simpler version of the helper, like this one:

  @helper RetrievePhotoWithName(int userid)
  {
      var database = Database.Open("SC");
      var notifications =  database.QueryValue("SELECT COUNT(*) FROM Notifications WHERE UserID = @0 AND [Read] = @1", userid, false);
     
      <p>@notifications</p>
  }

  Note that I have used QueryValue for the query instead of Query.

• • Reply
  

  Mikesdotnett...

  All-Star

  154955 Points

  19872 Posts

  Moderator

  MVP

  Re: Counting number of unread messages in TABLE is problematic

  May 16, 2012 05:32 AM|LINK

  jameskent

  Any ideas what it could be?

  The other problem is your alias:

  SELECT COUNT(*) as 'counter' ...

  Remove the apostrophes around "counter":

  SELECT COUNT(*) as counter ...

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Marked as answer by Dino He - MSFT on May 22, 2012 07:28 AM