0

I have done this before but this time it's just not working.

All I am trying to do is COUNT() the number of unread messages there are in a table.

Table def:

UserId(int), From(int), Type(nvarchar), Message(nvarchar), Read(bit)

The Read and UserId columns are what I"m trying to check against. I have tried the following:

 database.QuerySingle("SELECT COUNT(*) AS NumberOfNotifications FROM Notifications WHERE UserId = @0 AND Read = CONVERT(bit,0)", userid);

I have also tried many other versions but it's always the same result. I always get the following error:

Server Error in '/' Application.

Incorrect syntax near the keyword 'Read'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'Read'.

What I am trying to do, is this: "GET NUMBER OF UNREAD MESSAGES IN NOTIFICATIONSTABLE WHERE USERID = @0 AND READ = FALSE"

Any help at all is appreciated!

Thank you

UPDATED

Below is full code of the helper I am experiencing problems with. Now that I have got the error fixed up, I don't know why it's not displaying the "You have new notifications message" when i sign in - there are 4 rows in the corresponding table.

@helper RetrievePhotoWithName(int userid)
{
    var database = Database.Open("DUDE");
    var name = database.QuerySingle("select FirstName, LastName, ProfilePicture from UserProfile where UserId = @0", userid);
    var notifications =  database.QuerySingle("SELECT COUNT(*) AS NumberOfNotifications FROM Notifications WHERE UserId = @0 AND [Read] = @1", userid, false);

    var DisplayName = "";
    if(notifications["NumberOfNotifications"] < 1)
    {
        DisplayName = name["FirstName"] + " " + name["LastName"];
    }
    else
    {
        DisplayName = name["FirstName"] + ", you have " + notifications["NumberOfNotifications"] + " 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();
}
shane
  • 229
  • 3
  • 13

2 Answers2

4

try to put square brackets surrounding the read ===> [Read]

Hilmi
  • 3,411
  • 6
  • 27
  • 55
  • read is a keyword so you have to put [] to tell the SQL that this is a column name not a keyword ... – Hilmi May 15 '12 at 09:46
  • Omg, I totally overlooked that. Thank you! But it still doesn't work. (But atleast there are no errors now.) I have updated my question with full (not muh) code. Please have a bit of a look if you can :) – shane May 15 '12 at 10:00
  • is the returned value 'notifications' integer ? – Hilmi May 15 '12 at 11:11
  • Yep. Should be. But i have no way of seeing that I know of because when I try to output the value of notifications, nothing appears. – shane May 15 '12 at 12:29
  • When it enters the iff statement, it always displays just the name of the person who is signed in, which only happens if there are no unread notifications - but there are. – shane May 15 '12 at 12:30
  • Sir, do you know how to debug your code? if yes {just please tell me if of its exact value and datatype }: if no {tell me so i can provide you with the instructions} – Hilmi May 15 '12 at 13:04
  • The data type, as stated *in my question* is _bit_ :-) Thank you, @user1225246 – shane May 15 '12 at 13:18
1

Your error states that READ is a keyword. It's interpretting that a a command, or such like, and not a field name.

Indifferent versions of SQL you can enclose field names in different markers to prevent this confusion. For example SQL Server would use [Read], MySQL uses backticks such as `Read`.

MatBailie
  • 83,401
  • 18
  • 103
  • 137
  • Thank you @Dems. That is so weird, because I've never had to enclose Boolean column names in anything before. I dunno.. But thanks :) – shane May 15 '12 at 09:46
  • 1
    @shane - It's not that the field is `BOOLEAN` it's that you've chosen a field name that means something in SQL. Imagine the confusion if you had a table called `SELECT` with a single field called `FROM`. You'd have `SELECT FROM FROM SELECT`. But in SQL Server you could do `SELECT [FROM] FROM [SELECT]`. My *personal* recommendation is just to avoid using table and field names that are reserved keywords. – MatBailie May 15 '12 at 10:38