I'm fairly new to ASP.NET and as a self-chosen assignment I am to create a simple blog. It's very nearly finished, but I've run into a problem. As you can see from the code I am first using a query to return the amount of blog entries from my SQL server database. A for loop then reads titles, dates and text content from all the rows and creates a html div which it loads them into. Works great except for one thing: ExecuteScalar() only reads the first row, which will return the same value for each iteration causing all the blog entries to be the same.
I need a way for the reader to move on with each iteration. I've considered making a SQLbulkcopy into another table which I can then delete the top row from in the for loop, but this seems like it would slow down the page a fair amount if it's to be done on each Page_Load.
string SQLreadTitle = "Select BLOG.BlogTitle from BLOG order by BlogID DESC";
string SQLreadDate = "Select BLOG.BlogDate from BLOG order by BlogID DESC";
string SQLreadText = "Select BLOG.BlogText from BLOG order by BlogID DESC";
string SQLcountIDs = "Select count(BlogID) from BLOG";
protected void Page_Load(object sender, EventArgs e)
{
int i;
SqlConnection con = new SqlConnection(cnString);
con.Open();
SqlCommand countIDs = new SqlCommand(SQLcountIDs, con);
int count = Convert.ToInt32(countIDs.ExecuteScalar());
for (i = 0; i < count; i++)
{
SqlCommand readTitle = new SqlCommand(SQLreadTitle, con);
string titleString = readTitle.ExecuteScalar().ToString();
SqlCommand readDate = new SqlCommand(SQLreadDate, con);
string dateString = readDate.ExecuteScalar().ToString();
SqlCommand readText = new SqlCommand(SQLreadText, con);
string textString = readText.ExecuteScalar().ToString();
System.Web.UI.HtmlControls.HtmlGenericControl dynDiv =
new System.Web.UI.HtmlControls.HtmlGenericControl("DIV");
dynDiv.ID = "BlogPost";
dynDiv.InnerHtml = "<div id=\"BlogTitle\">" +
titleString + "</div><br /><div id=\"BlogDate\">"
+ dateString + "</div><br /><br /><div id=\"BlogText\">"
+ textString + "</div>";
Label1.Controls.Add(dynDiv);
}
con.Close();
}
Thanks in advance!
You are making life far too hard on yourself. You should have a single select statement then read each of the rows that is returned.
string SQL = "Select BLOG.BlogTitle, BLOG.BlogDate, BLOG.BlogText from BLOG order by BlogID DESC";
// Positions of the columns you are reading
const int TITLE_ORDINAL = 0;
const int DATE_ORDINAL = 1;
const int TEXT_ORDINAL = 2;
protected void Page_Load(object sender, EventArgs e)
{
using (var con = new SqlConnection(cnString))
{
con.Open();
try
{
using (var oCommand = new SqlCommand(SQL, con))
{
using (var oReader = oCommand.ExecuteReader())
{
while (oReader.Read())
{
var dynDiv = new System.Web.UI.HtmlControls.HtmlGenericControl("DIV");
dynDiv.ID = "BlogPost";
dynDiv.InnerHtml = "<div id=\"BlogTitle\">" +
oReader.GetString(TITLE_ORDINAL) + "</div><br /><div id=\"BlogDate\">"
+ oReader.GetString(DATE_ORDINAL) + "</div><br /><br /><div id=\"BlogText\">"
+ oReader.GetString(TEXT_ORDINAL) + "</div>";
Label1.Controls.Add(dynDiv);
}
}
}
} finally {
con.Close();
}
}
}