[joemb]

Hi.

I want to display records position in a "navigation textbox" once they are pulled from a table, something similar to the figure below (also provided as an attachment):

C:\navigate.JPG

What I want is for the record displayed in the text boxes to be reflected in the records navigation section. When I click the next button, the position of the records in the "navigation-textbox" should be incremented accordingly to reflect the correct change, that is, move from, say, 2 of 6 to 3 of 6. But this is not happening. In the navigation textbox, the message remains constant at "1 of 6" no matter which record is being displayed in the textboxes above.

I have looked at various examples on the Internet including here: http://msdn.microsof...v=vs.71%29.aspx but I cannot seem to understand what I am not doing right.
Plus, I have noticed that my requirement can be achieved by using the in-built Visual Studio.Net Binding Navigator functionality. I have tried it and it works perfectly. But I also realize that there are times when I may need to approach the same task differently depending upon the requirement. Therefore I decided to find out how to do a "custom-built" Binding Navigator. But now I am stuck.

I have included the relevant code below:

....
....
        System.Data.SqlClient.SqlConnection con;
        DataSet ds1;
        System.Data.SqlClient.SqlDataAdapter da;
        int MaxRows = 0;
        int inc = 0;

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();

            con.ConnectionString="Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Empl.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            
            string sql = "SELECT * From tblEmployees";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
            con.Open();

            da.Fill(ds1, "Employees");
            showDataPosition();

            MaxRows = ds1.Tables["Employees"].Rows.Count;
            con.Close();

            MessageBox.Show("MS SQL database is now closed");
        }
        
        private void showRecordPosition()
        {

            DataRow dRow = ds1.Tables["Employees"].Rows[inc];

            txtEmployeeID.Text = dRow.ItemArray.GetValue(0).ToString();
            txtFirstName.Text = dRow.ItemArray.GetValue(1).ToString();
            txtLastName.Text = dRow.ItemArray.GetValue(2).ToString();
            txtJobTitle.Text = dRow.ItemArray.GetValue(3).ToString();

            int eiCount, eiPosition;
            eiPosition = 1;
            eiCount = this.BindingContext[ds1, "Employees"].Count;
            eiPosition = this.BindingContext[ds1, "Employees"].Position + 1;
            if (eiCount == 0)
            {
                txtNavigate.Text = "(There are No Records)";
            }
            else
            {
                txtNavigate.Text = eiPosition.ToString() + " of " + eiCount.ToString();
            }
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (inc != 0)
            {
                inc = 0;
                showRecordPosition();
            }
            else
            {
                MessageBox.Show("Beginning of Records");
            }
        }
....
....

What am I missing?
I am relatively new to programming and I am using VS 2005.

Thanking you in advance.

Joe

This post has been edited by joemb: 02 September 2011 - 02:51 AM

[modi123_1]

Are you using a tree control? That might be best, but then again I am having a hard time parsing what you want to do.

This post has been edited by modi123_1: 02 September 2011 - 07:00 AM

[joemb]

modi123_1, on 02 September 2011 - 07:00 AM, said:

Are you using a tree control? That might be best, but then again I am having a hard time parsing what you want to do.

Hi modi123_1,

Let me try to clarify.

Initially I created an MS SQL database (Empl.mdf) and a table (tblEmployee) within this database. Then I created a simple form of the format:

textbox1 ===> EmployeeID
textbox2 ===> FirstName
textbox3 ===> LastName
textbox4 ===> JobTitle

Then I dropped 4 buttons on this form to enable movement through the records (navigation). These are:
btnFirst, btnPrevious, btnNext and btnLast.
In this same section, I included a text box which I named txtNavigation. This should offer the following function:

When the form loads, the first record in the table is displayed within the textboxes 1,2,3 and 4.
In order to see the next button, I click on btnNext; I click btnLast to display the last record in the table, and so on.

What I want is that, while the buttons First, Previous, Next and Last enable the display (and navigation) of the records in the textboxes, txtNavigation should be displaying a message which indicates the number of the current record in the form "Record X of Y" where:

X = the position of the current record
Y = the total number of records in the table.

[modi123_1]

Okay.. if you use a dataset to have your data returned it should just be the row index and the row.count.

[joemb]

modi123_1, on 02 September 2011 - 07:35 AM, said:

Okay.. if you use a dataset to have your data returned it should just be the row index and the row.count.

Again, thank you for your willingness to help.

Below is part of the code which I suppose, should achieve my requirement"

        int eiCount, eiPosition;
	eiCount = this.BindingContext[ds1, "Employees"].Count;
	eiPosition = this.BindingContext[ds1, "Employees"].Position + 1;
	if (eiCount == 0)
	{
	    txtNavigate.Text = "(There are No Records)";
	}
	else
	{
	    txtNavigate.Text = eiPosition.ToString() + " of " + eiCount.ToString();
	}

What do I need to change here?

[modi123_1]

I don't get it. You have the count and the position variables.. so use them!

Example:

Int32 eiCount, eiPosition;
Console.Write(String.Format("{0} of {1}", eiCount, eiPosition));

[joemb]

modi123_1, on 02 September 2011 - 08:01 AM, said:

I don't get it. You have the count and the position variables.. so use them!

Example:

Int32 eiCount, eiPosition;
Console.Write(String.Format("{0} of {1}", eiCount, eiPosition));

Sure modi123_1, I actually used the variable.
Indeed the txtNavigation displays 1 of 6 at form load and that is all!
The dilemma I am facing is, the "{0} of {1}" remains static. It does not change consistent with the navigation and the changing of the records in the textboxes. It indicate 1 of 6 where it should be 2 of 6, 3 of 6, 4 of 6... It is not changing and I am lost as to what I should do right.

[modi123_1]

Okay.. so your navigation works, right? I mean you click next and the textbox data changes to the next right thing? Well poll your information there.

Maybe I am assuming you have too much working..

[joemb]

modi123_1, on 02 September 2011 - 08:41 AM, said:

Okay.. so your navigation works, right? I mean you click next and the textbox data changes to the next right thing? Well poll your information there.

Maybe I am assuming you have too much working..

Within the text boxes:

• textbox1 for EmployeeID
  
• textbox2 for FirstName
  
• textbox3 for LastName
  
• textbox4 for JobTitle

everything works as expected. Records are displayed correctly when I click the navigation buttons (btnNext, btnLast etc.) But the text box (txtNavigate) where one should see which position the displayed record actually is within the table, this is where the problem lies. The information displayed here does not change. It remains at "1 of 6". I am wondering, how should ensure that it changes to reflect the correct position of the record?

Below is the entire code. Please if you have the time, look at it and advise accordingly.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace connect2Sql
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        System.Data.SqlClient.SqlConnection con;
        DataSet ds1;
        System.Data.SqlClient.SqlDataAdapter da;
        int MaxRows = 0;
        int inc = 0;

        private void Form1_Load(object sender, EventArgs e)
        {
            con = new System.Data.SqlClient.SqlConnection();
            ds1 = new DataSet();

            con.ConnectionString="Data Source=.\\SQLEXPRESS;AttachDbFilename=C:\\Empl.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
            
            string sql = "SELECT * From tblEmployees";
            da = new System.Data.SqlClient.SqlDataAdapter(sql, con);
            con.Open();

            da.Fill(ds1, "Employees");
            showDataPosition();

            MaxRows = ds1.Tables["Employees"].Rows.Count;
            con.Close();

            MessageBox.Show("MS SQL database is now closed");
        }
        
        private void showDataPosition()
        {

            DataRow dRow = ds1.Tables["Employees"].Rows[inc];

            txtEmployeeID.Text = dRow.ItemArray.GetValue(0).ToString();
            txtFirstName.Text = dRow.ItemArray.GetValue(1).ToString();
            txtLastName.Text = dRow.ItemArray.GetValue(2).ToString();
            txtJobTitle.Text = dRow.ItemArray.GetValue(3).ToString();

            int eiCount, eiPosition;
            eiCount = this.BindingContext[ds1, "Employees"].Count;
            eiPosition = this.BindingContext[ds1, "Employees"].Position + 1;
            if (eiCount == 0)
            {
                txtNavigate.Text = "(There are No Records)";
            }
            else
            {
                txtNavigate.Text = eiPosition.ToString() + " of " + eiCount.ToString();
            }
        }

        private void btnFirst_Click(object sender, EventArgs e)
        {
            if (inc != 0)
            {
                inc = 0;
                showDataPosition();
            }
            else
            {
                MessageBox.Show("Beginning of Records");
            }
        }

        private void btnPrevious_Click(object sender, EventArgs e)
        {
            if (inc > 0)
            {
                inc--;
                showDataPosition();
            }
            else
            {
                MessageBox.Show("First Record");
            }
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc++;
                showDataPosition();
            }
            else
            {
                MessageBox.Show("No more rows");
            }
        }

        private void btnLast_Click(object sender, EventArgs e)
        {
            if (inc != MaxRows - 1)
            {
                inc = MaxRows - 1;
                showDataPosition();
            }
            else
            {
                MessageBox.Show("Final Record");
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            DataRow dRow = ds1.Tables["Employees"].NewRow();

            dRow[1] = txtFirstName.Text;
            dRow[2] = txtLastName.Text;
            dRow[3] = txtJobTitle.Text;

            ds1.Tables["Workers"].Rows.Add(dRow);

            MaxRows = MaxRows + 1;
            inc = MaxRows - 1;

            da.Update(ds1, "Employees");

            MessageBox.Show("New Record Added");
        }

        private void btnDelete_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            ds1.Tables["Employees"].Rows[inc].Delete();
            MaxRows--;
            inc = 0;
            showDataPosition();

            da.Update(ds1, "Employees");

            MessageBox.Show("Record Successfully Deleted");
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlCommandBuilder cb;
            cb = new System.Data.SqlClient.SqlCommandBuilder(da);

            DataRow dRow2 = ds1.Tables["Employees"].Rows[inc];

            dRow2[1] = txtFirstName.Text;
            dRow2[2] = txtLastName.Text;
            dRow2[3] = txtJobTitle.Text;

            da.Update(ds1, "Employees");

            MessageBox.Show("Record Updated");
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
             txtEmployeeID.Text = txtFirstName.Text = txtLastName.Text = txtJobTitle.Text = "";
        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }
    }
}

This post has been edited by joemb: 02 September 2011 - 08:59 AM

[modi123_1]

Have you slapped a break point in your 'show data position' method and checked out what is happening? Make sure it's being called?

I assume "inc" is increment, right?

Why isn't eiPosition just increment?

[joemb]

modi123_1, on 02 September 2011 - 09:04 AM, said:

Have you slapped a break point in your 'show data position' method and checked out what is happening? Make sure it's being called?

I assume "inc" is increment, right?

Why isn't eiPosition just increment?

modi123_1, you have hinted on incrementing.
This is what I have done (see code snippet below):

I have changed "Position + 1;" to "Position++;" and then the position in txtNavigate stopped being static. But it now started from 0 instead of 1, showing "0 of 6" instead of "1 of 6" and ending at "5 of 6". So...
I added "Position++;" to the "else" statement. This seems to have solved the problem because now counting starts from 1 and the navigation works as expected.

    private void showDataPosition()

    {
     .............
     .............
       int eiCount, eiPosition;
       eiCount = this.BindingContext[ds1, "Employees"].Count;
       eiPosition = this.BindingContext[ds1, "Employees"].Position++;

       if (eiCount == 0)
       {
          txtNavigate.Text = "(There are No Records)";
       }
       else
       {
          eiPosition++
          txtNavigate.Text = eiPosition.ToString() + " of " + eiCount.ToString();
       }
    }

modi123_1, on 02 September 2011 - 09:04 AM, said:

Have you slapped a break point in your 'show data position' method and checked out what is happening? Make sure it's being called?

I assume "inc" is increment, right?

Why isn't eiPosition just increment?

modi123_1, you have hinted on incrementing.
This is what I have done (see code snippet below):

I have changed "Position + 1;" to "Position++;" and then the position in txtNavigate stopped being static. But it now started from 0 instead of 1, showing "0 of 6" instead of "1 of 6" and ending at "5 of 6". So...
I added "Position++;" to the "else" statement. This seems to have solved the problem because now counting starts from 1 and the navigation works as expected.

    private void showDataPosition()

    {
     .............
     .............
       int eiCount, eiPosition;
       eiCount = this.BindingContext[ds1, "Employees"].Count;
       eiPosition = this.BindingContext[ds1, "Employees"].Position++;

       if (eiCount == 0)
       {
          txtNavigate.Text = "(There are No Records)";
       }
       else
       {
          eiPosition++
          txtNavigate.Text = eiPosition.ToString() + " of " + eiCount.ToString();
       }
    }

Sorry about the double-posting.

[modi123_1]

ah.. okay. Good job on finding your solution!

[joemb]

modi123_1, on 02 September 2011 - 09:56 AM, said:

ah.. okay. Good job on finding your solution!

Thanks modi123_1 for sticking with me through it all.
Am grateful.

Kind Regards.