Navigating to the Next Record 2

[hspage]

Greetings,

I have the following code for a button on a Windows form where I am attempting to navigate to the next record. It works, but only if the ID field (of the SQL Server 2005 table) is 1 larger than the previous number. If a record was deleted, however, then the ID field of the next record would not be in sequence. For instance, if the ID field of the current record is 2, then the code below will look for a record with an ID field of 3. If it can't find it, then no record is displayed. [Note: the ID field of the current record is displayed in a text box on the form - during the Load event].

My question is how do I get around this? I'm assuming there is a better way to navigate to the next record than what I am doing, but I'm not sure how to accomplish this.

Any suggestions?

Thanks in advance!

Code:
private void btnNext_Click(object sender, EventArgs e)
{
int intNewID = Convert.ToInt32(this.txtID.Text) + 1;

SqlConnection conn = new SqlConnection("Data Source=HOME-PC\\SQLEXPRESS;Initial Catalog=Contacts;Integrated Security=True");

SqlDataAdapter myAdapter = new SqlDataAdapter("SELECT * FROM tblContacts WHERE ID = " + intNewID, conn);

DataSet dsContacts = new DataSet();

myAdapter.Fill(dsContacts);

this.txtID.Text = dsContacts.Tables[0].Rows[0].ItemArray[0].ToString();
this.txtFirst.Text = dsContacts.Tables[0].Rows[0].ItemArray[1].ToString();
this.txtLast.Text = dsContacts.Tables[0].Rows[0].ItemArray[2].ToString();
this.txtStreet.Text = dsContacts.Tables[0].Rows[0].ItemArray[3].ToString();
this.txtCity.Text = dsContacts.Tables[0].Rows[0].ItemArray[4].ToString();
this.cboState.Text = dsContacts.Tables[0].Rows[0].ItemArray[5].ToString();
this.txtZip.Text = dsContacts.Tables[0].Rows[0].ItemArray[6].ToString();

myAdapter.Dispose();

conn.Close();

...
...

}

 

[JurkMonkey]

Really?

http://www.ubuntu.com/

 

[jmeckley]

1st. use the using keyword to wrap your conncetions and commands. this prevents issues if an exception is thrown.
2nd. use parameters.

private int currentId = 0;

public void NextButton_Click(object sender, EventArgs e)
{
   DataSet result = new DataSet();
   using(SqlConnection connection = new SqlConnection())
   using(SqlCommand command = new SqlCommand())
   {
      connection.Open();
      command.Connection = connection;
      command.CommandText = "select * from [table] where [id] = (select min(id) from [table] where [id] > @id)";
      command.Parameters.Add("id", SqlDbType.Integer).Value = currentId;
      result.Load(command.ExecuteReader());
   }

   DataRow row = dsContacts.Tables[0].Rows[0];
   currentId = (int)row["id"];
   txtID.Text = currentId.ToString();
   txtFirst.Text = row["FirstName"];
   txtLast.Text = row["LastName"]; 
   txtStreet.Text = row["Street"];
   txtCity.Text = row["City"];
   cboState.Text = row["State"];
   this.txtZip.Text = row["ZipCode"];
}

using will automatically close/dispose the connection. it also creates a try/finally block around the code, so it's safe from exceptions prematurely exiting the code.

I would also highly recommend refactoring the ado.net code into a seperate object.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JurkMonkey]

jmeckley - you missed the point I think.

Generally when you are trying to work with data - you start by getting a list of the data. You don't guess at the indexes.

Start by getting the indexes using SQL and dumping it into a DataTable using the advice provided by Jason. Something like:

"SELECT ID, firstname, lastname FROM tblContacts"

Then for each record you return you can navigate via the ID field. Also - don't use ItemArray[0] ever because if you find a need to change your SQL statement your entire app can be screwed. Use row["ID"] and row["firstname"] to get the data you are expecting.

You may also want to look at the BindingNavigator that is available in .Net 2.0

http://www.ubuntu.com/

 

[hspage]

JMeckley,

I tried running your code but I receive the following error:

"No overload for method 'Load' takes '1' arguments"

This occurs on the following line of code:
result.Load(command.ExecuteReader());

Any ideas?

Thanks!

 

[jmeckley]

yeah, the syntax is wrong. use the help (F1, msdn, google) to figure out the correct overload. It may be a different method you need to use, can't remembe off the top of my head.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[JurkMonkey]

I also recommend you do some research on Guids

http://www.ubuntu.com/