Thanks Mike, your Query works BUT then I run into another issue. When I use a Verbertim String, the Page will not compile.
I get this Exception. for every line in the page. I mean it is NOT specific to the NewsDate only Description:
An error occurred during the compilation of a resource required to service
this request. Please review the following specific error details and modify your
source code appropriately.
Compiler Error Message: CS1061: 'char' Line 26: @newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle accepting a first argument of type 'char' could be found (are you missing a
using directive or an assembly reference?)
The entire Page looks like this. The query is supposed to loop through the DB and show all News Items. Here is the Page
Still does not work. Stangely, the one that is NOT Verbertim works, BUT is not very readble.
Here is the entire Page. Any Ideas why this is Not working?
// Initialize general page variables
var NewsTitle = "";
var categoryid = "";
var NewsDate = "";
var newsText = "";
var newsPictureURL = "";
var pictureFileName = "";
var newsLink = "";
var isValid = true;
}
<h2>Recent News</h2>
@{
var db = Database.Open("StarterSite");
var stories = db.Query(qry);
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";
}
<div id="news-items">
@foreach(var newsItem in qry)
{
<h3 class="news-header">
@newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle
</h3>
<div class="news-body">
@if(!String.IsNullOrEmpty(newsItem.NewsPictureURL))
{
<img class="newsimage"
src="@Href("/news-images", newsItem.NewsPictureURL)"
alt="@newsItem.NewsTitle" />
}
<div>@newsItem.NewsText</div>
@if(!String.IsNullOrEmpty(newsItem.NewsLink))
{
<div>More details at
<a href="@newsItem.NewsLink"
target="_blank">@newsItem.NewsLink</a>
</div>
}
<br clear="all" />
</div>
}
</div>
You've tried to use the variable "qry" in the Database.Query method without having declared it or set a value for it at that point. It isn't until the next line that it even exists. And you are still passing the wrong variable into the foreach loop.
@{
var db = Database.Open("StarterSite");
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";
When I do that I get a Token Exception. The Exception is " There was an error parsing the query. [ Token line number = 2,Token line offset = 44,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 = 2,Token line offset = 44,Token in error = . ]
Source Error:
Line 30: ORDER BY Newsarticle.Newsdate DESC";
Line 31: Line 32: var stories = db.Query(qry); Line 33: }
Line 34: Line 33: }
The corrected code looks like this:
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";
var stories = db.Query(qry);
}
<div id="news-items">
@foreach(var newsItem in stories)
yousaid
Participant
811 Points
334 Posts
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 02:59 PM|LINK
Thanks Mike, your Query works BUT then I run into another issue. When I use a Verbertim String, the Page will not compile.
I get this Exception. for every line in the page. I mean it is NOT specific to the NewsDate only
Description:
An error occurred during the compilation of a resource required to service
this request. Please review the following specific error details and modify your
source code appropriately.
Compiler Error Message: CS1061: 'char'
Line 26: @newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle
accepting a first argument of type 'char' could be found (are you missing a
using directive or an assembly reference?)
The entire Page looks like this. The query is supposed to loop through the DB and show all News Items. Here is the Page
<div id="news-items"> @foreach(var newsItem in qry) { <h3 class="news-header"> @newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle </h3> <div class="news-body"> @if(!String.IsNullOrEmpty(newsItem.NewsPictureURL)) { <img class="newsimage" src="@Href("/news-images", newsItem.NewsPictureURL)" alt="@newsItem.NewsTitle" /> } <div>@newsItem.NewsText</div> @if(!String.IsNullOrEmpty(newsItem.NewsLink)) { <div>More details at <a href="@newsItem.NewsLink" target="_blank">@newsItem.NewsLink</a> </div> } <br clear="all" /> </div> } </div>Mikesdotnett...
All-Star
155599 Points
19982 Posts
Moderator
MVP
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 04:04 PM|LINK
"qry" is the variable that holds the SQL statement (a string) - not the result of the database query.
You need to do something like:
var stories = db.Query(qry); ... ... <div id="news-items"> @foreach(var newsItem in stories) { <h3 class="news-header"> @newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle </h3> ... ...Web Pages CMS | My Site | Twitter
yousaid
Participant
811 Points
334 Posts
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 07:33 PM|LINK
// Initialize general page variables var NewsTitle = ""; var categoryid = ""; var NewsDate = ""; var newsText = ""; var newsPictureURL = ""; var pictureFileName = ""; var newsLink = ""; var isValid = true; } <h2>Recent News</h2> @{ var db = Database.Open("StarterSite"); var stories = db.Query(qry); 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"; } <div id="news-items"> @foreach(var newsItem in qry) { <h3 class="news-header"> @newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle </h3> <div class="news-body"> @if(!String.IsNullOrEmpty(newsItem.NewsPictureURL)) { <img class="newsimage" src="@Href("/news-images", newsItem.NewsPictureURL)" alt="@newsItem.NewsTitle" /> } <div>@newsItem.NewsText</div> @if(!String.IsNullOrEmpty(newsItem.NewsLink)) { <div>More details at <a href="@newsItem.NewsLink" target="_blank">@newsItem.NewsLink</a> </div> } <br clear="all" /> </div> } </div>Mikesdotnett...
All-Star
155599 Points
19982 Posts
Moderator
MVP
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 09:12 PM|LINK
You've tried to use the variable "qry" in the Database.Query method without having declared it or set a value for it at that point. It isn't until the next line that it even exists. And you are still passing the wrong variable into the foreach loop.
@{
var db = Database.Open("StarterSite");
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";
var stories = db.Query(qry);
}
<div id="news-items">
@foreach(var newsItem in stories)
{
<h3 class="news-header">
@newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle
Web Pages CMS | My Site | Twitter
yousaid
Participant
811 Points
334 Posts
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 09:41 PM|LINK
Hello Mike,
Here is my corrcted code which I already tried, but it did not work. Can you show me the declarations I am missing?
Thanks
// Initialize general page variables var NewsTitle = ""; var categoryid = ""; var NewsDate = ""; var newsText = ""; var newsPictureURL = ""; var pictureFileName = ""; var newsLink = ""; var isValid = true; var qry = ""; } <h2>Recent News</h2> @{ var db = Database.Open("StarterSite"); var stories = db.Query(qry); 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"; } <div id="news-items"> @foreach(var newsItem in stories) { <h3 class="news-header"> @newsItem.NewsDate.ToShortDateString() @newsItem.NewsTitle </h3> <div class="news-body"> @if(!String.IsNullOrEmpty(newsItem.NewsPictureURL)) { <img class="newsimage" src="@Href("/news-images", newsItem.NewsPictureURL)" alt="@newsItem.NewsTitle" /> } <div>@newsItem.NewsText</div> @if(!String.IsNullOrEmpty(newsItem.NewsLink)) { <div>More details at <a href="@newsItem.NewsLink" target="_blank">@newsItem.NewsLink</a> </div> } <br clear="all" /> </div> } </div>Mikesdotnett...
All-Star
155599 Points
19982 Posts
Moderator
MVP
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 10:05 PM|LINK
Look at my previous reply, and the first bold line of code, and its position. Then compare it to the place where you have it.
Web Pages CMS | My Site | Twitter
yousaid
Participant
811 Points
334 Posts
Re: Please Help with Sql INNER JOIN
Jan 07, 2012 11:33 PM|LINK
When I do that I get a Token Exception. The Exception is "
There was an error parsing the query. [ Token line number = 2,Token line offset = 44,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 = 2,Token line offset = 44,Token in error = . ]
Source Error:
Line 30: ORDER BY Newsarticle.Newsdate DESC";
Line 31:
Line 32: var stories = db.Query(qry);
Line 33: }
Line 34: Line 33: }
The corrected code looks like this:
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"; var stories = db.Query(qry); } <div id="news-items"> @foreach(var newsItem in stories)yousaid
Participant
811 Points
334 Posts
Re: Please Help with Sql INNER JOIN
Jan 08, 2012 04:16 AM|LINK
This here Works: The problem was that there were some missing commas.
THANKS Mike
var mydb = Database.Open("Startersite"); var stories = mydb.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");