Please Help with Sql INNER JOIN RSS

• • Reply
  

  yousaid

  Participant

  811 Points

  334 Posts

  Please Help with Sql INNER JOIN

  Jan 06, 2012 09:14 PM|LINK

  This Sql statement  here throws an exception, BUT for it's worth, I can see anything wrong with it
  The exception is :
  Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near '.'. on this line
  Line 10:   var stories = db.Query("SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText "

  Here is the SQL
  

  var stories = db.Query("SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText "
              +   "NewsArticle.NewsPictureUrl, NewsArticle.Newslink "
              +   "NewsArticle.IsVisible, UserProfile.Membername, UserProfile.Firstname "
              +   "UserProfile.Lastname, NewsCategory.CategoryName "
              +   "FROM  NewsArticle INNER JOIN "
              +    "ON UserProfile.UserId = Newsarticle.UserId INNER JOIN "
              +     "ON Newscategory.CategoryId - Newsarticle.CategoryId "
              +    "WHERE Newsarticle.Isvisible = 1 "
              +    "ORDER BY Newsarticle.Newsdate DESC");

  
  
  
  
  

• • Reply
  

  p3tar

  Participant

  1526 Points

  238 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 06, 2012 09:16 PM|LINK

  For start, there are missing commas

  var stories = db.Query("SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText, "
              +   "NewsArticle.NewsPictureUrl, NewsArticle.Newslink, "
              +   "NewsArticle.IsVisible, UserProfile.Membername, UserProfile.Firstname, "
              +   "UserProfile.Lastname, NewsCategory.CategoryName "
              +   "FROM  NewsArticle INNER JOIN "
              +    "ON UserProfile.UserId = Newsarticle.UserId INNER JOIN "
              +     "ON Newscategory.CategoryId - Newsarticle.CategoryId "
              +    "WHERE Newsarticle.Isvisible = 1 "
              +    "ORDER BY Newsarticle.Newsdate DESC");

  "I'm just trying to give back to the community..."
  If I helped please mark my posts as "Answer". Cheers.
• • Reply
  

  yousaid

  Participant

  811 Points

  334 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 06, 2012 09:27 PM|LINK

  Thanks, BUT that did not work either,

  The New exception is now:
  Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'ON'.
  On this Line
  
  Line 10:  var stories = db.Query("SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText, "
  
  

  Incorrect syntax near the keyword 'ON'.

  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 'ON'.
  
  Source Error:
  
  

• • Reply
  

  p3tar

  Participant

  1526 Points

  238 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 06, 2012 09:45 PM|LINK

  Try this:

  var stories = db.Query("SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText, "
              +   "NewsArticle.NewsPictureUrl, NewsArticle.Newslink, "
              +   "NewsArticle.IsVisible, UserProfile.Membername, UserProfile.Firstname, "
              +   "UserProfile.Lastname, NewsCategory.CategoryName "
              +   "FROM  NewsArticle INNER JOIN UserProfile"
              +    "ON UserProfile.UserId = NewsArticle.UserId INNER JOIN Newscategory"
              +     "ON Newscategory.CategoryId = NewsArticle.CategoryId "
              +    "WHERE NewsArticle.Isvisible = 1 "
              +    "ORDER BY NewsArticle.Newsdate DESC");

  Also check for typo errors... column name "Isvisible" = "IsVisible"?...

  "I'm just trying to give back to the community..."
  If I helped please mark my posts as "Answer". Cheers.
• • Reply
  

  yousaid

  Participant

  811 Points

  334 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 06, 2012 10:52 PM|LINK

  Thanks again, BUT still it did NOT work. BUT the error is now different.  I have fixed the typo, but still
  The error now is:

  There was an error parsing the query. [ Token line number = 1,Token line
  offset = 317,Token in error = . ]

  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.SqlServerCe.SqlCeException: There was an error parsing the
  query. [ Token line number = 1,Token line offset = 317,Token in error = .
  ]
  
  Source Error:

  Line 8: @{ Line 9: var db = Database.Open("StarterSite"); Line 10: var stories = db.Query("SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText, " Line 11: + "NewsArticle.NewsPictureUrl, NewsArticle.Newslink, " Line 12: + "NewsArticle.IsVisible, UserProfile.Membername, UserProfile.Firstname, "

• • Reply
  

  ThumbDog

  Member

  74 Points

  28 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 07, 2012 02:31 AM|LINK

  Hi,

   

  Try putting a space at the end of the .... INNER JOIN UserProfile line and the ... INNER JOIN NewsCategory line.

   

  Cheers 

  • Marked as answer by yousaid on Jan 07, 2012 03:58 AM
• • Reply
  

  sunilgurjar

  Contributor

  2252 Points

  478 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 07, 2012 03:09 AM|LINK

  try this
  SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle, NewsArticle.NewsText "
              +   " NewsArticle.NewsPictureUrl, NewsArticle.Newslink "
              +   " NewsArticle.IsVisible, UserProfile.Membername, UserProfile.Firstname "
              +   " UserProfile.Lastname, NewsCategory.CategoryName "
              +   " FROM  NewsArticle INNER JOIN UserProfile "
              +    " ON UserProfile.UserId = Newsarticle.UserId INNER JOIN Newscategory "
              +     " ON Newscategory.CategoryId = Newsarticle.CategoryId "
              +    " WHERE Newsarticle.Isvisible = 1 "
              +    " ORDER BY Newsarticle.Newsdate DESC

  "mark as an answer" if it helps you
  read my blog
  http://sunilgurjar.blogspot.com/
• • Reply
  

  yousaid

  Participant

  811 Points

  334 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 07, 2012 03:59 AM|LINK

  ThumbDog

  Try putting a space at the end of the .... INNER JOIN UserProfile line and the ... INNER JOIN NewsCategory line.

   

  GREAT Catch !!

  Thanks much,

  yousaid

• • Reply
  

  yousaid

  Participant

  811 Points

  334 Posts

  Re: Please Help with Sql INNER JOIN

  Jan 07, 2012 04:01 AM|LINK

  Thanks, very much, but this throws same exception.

  The problem was that, I was missing spaces as Thumbdog indicated !!
  thanks again.
  yousaid

• • Reply
  

  Mikesdotnett...

  All-Star

  154818 Points

  19853 Posts

  Moderator

  MVP

  Re: Please Help with Sql INNER JOIN

  Jan 07, 2012 07:12 AM|LINK

  For readability, you are much better off using the verbatim string literal:

  A verbatim string literal consists of an @ character followed by a double-quote character, zero or more characters, and a closing double-quote character.... A verbatim string literal may span multiple lines.

  var qry = @"SELECT NewsArticle.NewsId, NewsArticle.NewsDate, NewsArticle.NewsTitle,
            NewsArticle.NewsText NewsArticle.NewsPictureUrl, NewsArticle.Newslink  NewsArticle.IsVisible,
            UserProfile.Membername, UserProfile.Firstname  UserProfile.Lastname, NewsCategory.CategoryName
            FROM  NewsArticle
            INNER JOIN UserProfile  ON UserProfile.UserId = Newsarticle.UserId
            INNER JOIN Newscategory  ON Newscategory.CategoryId = Newsarticle.CategoryId
            WHERE Newsarticle.Isvisible = 1
            ORDER BY Newsarticle.Newsdate DESC";

  Regards, Mike
  Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter
  • Edited by Mikesdotnetting on Jan 07, 2012 07:13 AM
  • Edited by Mikesdotnetting on Jan 07, 2012 07:13 AM