System.IndexOutOfRangeException

[Lusiphur]

I'll preface this by giving a bit of an overview of what I'm trying for here.

I'm trying to create a quasi-blog. Something that I can "post" journal type entries directly to a page in my website but without all the luggage that comes with the readily available blog engines and without viewer reply capability.

Essentially it's just going to be an interface where I can enter a post which gets stored on a database and a dynamically loading page that shows the most recent post with the option to navigate to prior posts (and back forward again).

Here's the thing... I can get my dynamic page to show the 'current' or most recent post, I can get it to navigate backwards through posts... but when I try to navigate forward again I get System.IndexOutOfRangeException: There is no row at position 0.

the only difference between the two functions of the page is that the "Previous Post" button is geared to utilize the code:
currentMessageID -= 1;
and the "Next Post" button instead does += 1. currentMessageID is, of course, the MessageID variable that determines which message number I'm attempting to get from the database in my SELECT statement.

Pertinent pieces of the code are below... any help is appreciated.

public partial class blogged : System.Web.UI.Page
{
    bool firstLoader = true;
    int testMessageID = 0;
    int lastMessageID = 0;
    int firstMessageID = 0;
    int currentMessageID = 0;
    protected void Page_Load(object sender, EventArgs e)
    {
        getFirstMessageID(true);
        getLastMessageID(true);
        if (firstLoader == true)
        {
            currentMessageID = lastMessageID;
        }
        loadMessage();
        firstLoader = false;
    }

    private void getLastMessageID(bool first) // Obtain the current highest message ID from the list
    {
        string adaptString = "";
        if (first == true)
        {
            adaptString = @"SELECT TOP (1) mID, isDeleted FROM blMessages order by mID Desc";
        }
        else
        {
            adaptString = @"SELECT mID, isDeleted FROM blMessages WHERE mID ='" + (testMessageID - 1) + "'";
        }
        blConn connector = new blConn();
        string connStr = @connector.cString;
        SqlConnection latestConn = new SqlConnection(@connStr);
        SqlDataAdapter latestAdapt = new SqlDataAdapter(adaptString, latestConn);
        SqlCommandBuilder latestBuilder = new SqlCommandBuilder(latestAdapt);
        DataSet latestSet = new DataSet();
        latestAdapt.Fill(latestSet, "lastID");
        DataRow latestRow = latestSet.Tables["lastID"].Rows[0];
        if (latestRow["isDeleted"].ToString() == "True")
        {
            testMessageID = Convert.ToInt16(latestRow["mID"].ToString());
            latestConn.Close();
            getLastMessageID(false);
        }
        else
        {
            lastMessageID = Convert.ToInt16(latestRow["mID"].ToString());
            latestConn.Close();
        }
    }
    private void getFirstMessageID(bool first) // Obtain the current lowest message ID from the list
    {
        string adaptString = "";
        if (first == true)
        {
            adaptString = @"SELECT TOP (1) mID, isDeleted FROM blMessages";
        }
        else
        {
            adaptString = @"SELECT mID, isDeleted FROM blMessages WHERE mID ='" + (testMessageID + 1) + "'";
        }
        blConn connector = new blConn();
        string connStr = @connector.cString;
        SqlConnection firstConn = new SqlConnection(@connStr);
        SqlDataAdapter firstAdapt = new SqlDataAdapter(adaptString, firstConn);
        SqlCommandBuilder firstBuilder = new SqlCommandBuilder(firstAdapt);
        DataSet firstSet = new DataSet();
        firstAdapt.Fill(firstSet, "firstID");
        DataRow firstRow = firstSet.Tables["firstID"].Rows[0];
        if (firstRow["isDeleted"].ToString() == "True")
        {
            testMessageID = Convert.ToInt16(firstRow["mID"].ToString());
            firstConn.Close();
            getFirstMessageID(false);
        }
        else
        {
            lastMessageID = Convert.ToInt16(firstRow["mID"].ToString());
            firstConn.Close();
        }
    }
    private void loadMessage() // Load the currently selected message to the screen
    {
        blConn connector = new blConn();
        string connStr = @connector.cString;
        SqlConnection loadMsgConn = new SqlConnection(@connStr);
        SqlDataAdapter loadMsgAdapt = new SqlDataAdapter("SELECT mID, mDate, mSubject, mBody, isDeleted FROM blMessages WHERE mID='" + currentMessageID + "'", loadMsgConn);
        SqlCommandBuilder loadMsgBuilder = new SqlCommandBuilder(loadMsgAdapt);
        DataSet loadMsgSet = new DataSet();
        loadMsgAdapt.Fill(loadMsgSet, "currentMsg");
        loadMsgConn.Close();
        DataRow loadMsgRow = loadMsgSet.Tables["currentMsg"].Rows[0];
        if (loadMsgRow["isDeleted"].ToString() == "True")
        {
            if ((currentMessageID + 1) > lastMessageID)
            {
                currentMessageID -= 1;
                loadMessage();
            }
            else
            {
                currentMessageID += 1;
                loadMessage();
            }
        }
        else
        {
            msgSubject.Text = (loadMsgRow["mSubject"].ToString());
            msgDateTime.Text = (loadMsgRow["mDate"].ToString());
            msgBody.Text = (loadMsgRow["mBody"].ToString());
            currentMessageID = Convert.ToInt16(loadMsgRow["mID"].ToString());
            if (currentMessageID < lastMessageID)
            {
                fwdButton.Visible = true;
            }
            else
            {
                fwdButton.Visible = false;
            }
            if (currentMessageID > firstMessageID)
            {
                backButton.Visible = true;
            }
            else
            {
                backButton.Visible = false;
            }
        }
        loadMsgSet.Clear();
    }
    protected void fwdButton_Click(object sender, EventArgs e)
    {
        currentMessageID += 1;
        loadMessage();
    }
    protected void backButton_Click(object sender, EventArgs e)
    {
        currentMessageID -= 1;
        loadMessage();
    }
}

 

[Lusiphur]

Never mind folks... it was just a matter of changing the variables at the beginning to static variables and it worked fine.

 

[jmeckley]

if you are using static varaibles this won't work if multiple people use the webpage. There are also alot of red flags in the code.

1. you are not disposing of dispoable resources. ie: the database connection. for more information research IDisposable, the 'using' keywork and try/catch/finally blocks.
2. you are using injected sql statements rather than parameterized queries.
3. you are relying on static variables to track what entry to display. there are much better ways of accomplishing this. the least of which is putting the current page/id in the the user's session. but even then I wouldn't do that. I would pass the id back and forth with each request.
4. you are assuming there are no gaps with the ids in the database. ie:id = 1,2,3,4. what if there is a problem when entering an entry? the ids may increment: 1,2,4 (omitting 3 because there was an error trying to enter that record). This would occur if you are using an identity column as the primary key. a better approach would be to execute 3 queries.
1. get the current record
2. get the lowest id that is greater than the current records id [tt]select min(id) from table where id > @current[/tt]. This will be used to create the next button.
3. get the highest id that is less than the current records id [tt]select max(id) from table where id < @current[/tt]. This will be used to create the previous button.
with these 3 queries you can display the current entry, build a prevous link and build a next link. you can then remove the static field.

here is a crude example. i'm sure there are syntax problems, it's off the top of my head.

void page_load(...0
{
   var settings = ConfigurationManager.ConnectionStrings["key from web.config"];
   var id = int.Parse(Request.QueryString["id"]);
   var previous = 0;
   var next = 0;
   DataRow current;

   using(var connection = DbProviderFactories.CreateFactory(settings.Provider).CreateConnection())
      connection.ConnectionString = settings.ConnectionString;
      connection.Open();
   {
      using(var command = connection.CreateCommand();
      {
         command.CommandText = "select [fields] from [table] where id = @id";
         var parameter = command.CreateParameter();
         parameter.Name = "id";
         parameter.Value = id;
         command.AddParameter(parameter);

         var table = new DataTable();
         table.Load(command.ExecuteReader());
         current = table.Rows[0];
      }

      using(var command = connection.CreateCommand();
      {
         command.CommandText = "select min(id) from [table] where id > @id";
         var parameter = command.CreateParameter();
         parameter.Name = "id";
         parameter.Value = id;
         command.AddParameter(parameter);

         var result = command.ExecuteScalar();
         if(result != DbNull.Value)
         {
             next = (int)result;
         }
      }

      using(var command = connection.CreateCommand();
      {
         command.CommandText = "select max(id) from [table] where id < @id";
         var parameter = command.CreateParameter();
         parameter.Name = "id";
         parameter.Value = id;
         command.AddParameter(parameter);

         var result = command.ExecuteScalar();
         if(result != DbNull.Value)
         {
             previous = (int)result;
         }
      }
   }

   //use current to display the current record.
   //use next to build the url for next link/button. if next = 0 do not create a hyper link (last record)
   //use previous to build the url for previous link/button. if previous = 0 do not create a hyper link (first record)
}

you can also batch sql statements into a single command to reduce remote calls to the database. that would look like this

using(var command = connection.CreateCommand())
{
   command.CommandText = @"
select * from table where id = @id;
select max(id) from table where id < @id;
select min(id) from table where id >@id;
";
   var parameter = command.CreateParameter();
   parameter.Name = "id";
   parameter.Value = id;
   command.AddParameter(parameter);

   using(var reader = command.ExecuteReader())
   {
      table.Load(reader);
      var previous = reader.NextResult().Read()[0];
      var next = reader.NextResult().Read()[0];
   }
}

again no gaurantees on the syntax.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[Lusiphur]

Jason,

Thanks for your reply.

I'll have to look into the issue of static variables affecting differently in the event of multiple simultaneous users.

As I'm using C# for my ASP.Net coding language I'm not sure the same rules apply on the disposal of unused items or the overall syntax in comparison to what you've provided. Additionally, I was somewhat under the impression that the codebehind of the page would more or less run in it's own session-like instance for each browser it is brought up in (including variables and such).

Any suggestions?

 

[jmeckley]

As I'm using C# for my ASP.Net coding language I'm not sure the same rules apply on the disposal of unused items or the overall syntax in comparison to what you've provided.

why would you assume the syntax has any bearing on how the compiler interprets the BCL? do you think IDisposable works differently in VB vs C# vs IronRuby? Think of it this way, do you use a fork differently if you are eating american vs mexican vs french food? No, the fork is a utensil, it may have a different name depending on the name, but a fork is used the same regardless.

btw: what I provided above is C#.

Additionally, I was somewhat under the impression that the codebehind of the page would more or less run in it's own session-like instance for each browser it is brought up in (including variables and .such)

this is true for instance members. however static members are associated to the type, not the instance. static references are shared among all instances.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259