Need a intelligente rotation...

Israel

Hi
I have a very small table with 4 records:

Date Name operA OperB
01/12/2014 Salam 1 2
01/12/2014 Shandi 5 3
01/12/2014 Eva 2 0
02/12/2014 Shamba 7 4
02/12/2014 Kali 1 1

Then I have four (4) textboxes and one Button:
txtDate, txtName, txtOperA, txtOperB and a btnSave
What I need?
When I click to btnsave all records should appear on thoses textboxs: name, opera and operB in other. For example, the first to appear it's "Salam" in txtName, the "1" in txtOperA and the "2" in txtOperB. After that Shandi with his own datas and so one. In conclusion, from the first record to the last one. Just when I make a filter for a specific date. That all!
See what I had tried… But not that all (I need the rotation's code):

To search date:
private void SearchDate_Click(object sender, EventArgs e)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dir1\\\app_data\data.mdb;Persist Security Info=False";
OleDbConnection sqlCon = new OleDbConnection(connectionString);
sqlCon.Open();
string commandString = "select * from operation where date='" + txtDate.Text + "'";
OleDbCommand sqlCmd = new OleDbCommand(commandString, sqlCon);
OleDbDataReader read = sqlCmd.ExecuteReader();
if (read.HasRows)
{
while (read.Read())
{
txtDate.Text = read["date"].ToString();
txtName.Text = read["A"].ToString();
txtOperA.Text = read["B"].ToString();
txtOperB.Text = read["C"].ToString();
}
}
else
{
MessageBox.Show("A record with this date " + codigoBarraConsStocks.Text + " doesnt exist. Sorry!");
}
read.Close();
sqlCon.Close();
}

With this codes its will ONLY search the record but NEVER show one by one on these texboxes.
Many thanx in advance.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Wim Sturkenboom11y ago
That's going to be a little bit more complicated. As you might know, you need to use delegates if you want to update a control on a form from another thread; you can not do so directly.

A while ago I found the below code for delegates on stackoverflow. It's a class that you can add to your code.

    // source: http://stackoverflow.com/questions/711408/best-way-to-invoke-any-cross-threaded-code/711419
    public static class ISynchronizeInvokeExtensions
    {
        public static void InvokeEx(this T @this, Action action) where T : ISynchronizeInvoke
        {
            if (@this.InvokeRequired)
            {
                @this.Invoke(action, new object[] { @this });
            }
            else
            {
                action(@this);
            }
        }
    }

Below a sample code for a form that uses the above. The form has a textbox (textBox1) and a button (btnStart).

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

namespace BackgroundworkerWithDelegate
{
    public partial class Form1 : Form
    {
        int counter = -1;
        int counterlimit = 25;
        System.Timers.Timer tmr = new System.Timers.Timer();

        public Form1()
        {
            InitializeComponent();

            tmr.Interval = 1000;
            tmr.Elapsed += tmr_Elapsed;
        }

        void tmr_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
        {
            if (counter == -1)
                return;

            tmr.Stop();
            this.InvokeEx(f => f.textBox1.Text = (++counter).ToString());
            if (counter >= counterlimit)
            {
                counter = -1;
            }
            else
            {
                tmr.Start();
            }
        }

        private void btnStart_Click(object sender, EventArgs e)
        {
            counter = 0;
            tmr.Enabled = true;
        }
    }

    // source: http://stackoverflow.com/questions/711408/best-way-to-invoke-any-cross-threaded-code/711419
    public static class ISynchronizeInvokeExtensions
    {
        public static void InvokeEx(this T @this, Action action) where T : ISynchronizeInvoke
        {
            if (@this.InvokeRequired)
            {
                @this.Invoke(action, new object[] { @this });
            }
            else
            {
                action(@this);
            }
        }
    }
}

When you click the start button, a timer will be started. When the timer fires, a counter is incremented and the value is displayed in the textbox. When the counter reaches a treshold, the process stops till you click the start button again.

Up to you to integrate this with the previous code that I posted :-)
Israel11y ago
@wim

your codes are working perfectly. But there is a big problem. Its doesnt do it ONE by ONE automatically. I think its should work with a timer (with a textbox display period of one second for each row ).
Wim Sturkenboom11y ago
Add two global variables in the beginning of your form

    public partial class Form1 : Form
    {
        DataTable tblSearchDateResults = null;
        int counter = 0;

Modify your search method

        private void btnSearchDate_Click(object sender, EventArgs e)
        {

            // if there is nothing, query the database
            if (tblSearchDateResults == null)
            {
                // reset counter
                counter = 0;

                // original code
                string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dir1\\\app_data\data.mdb;Persist Security Info=False";
                OleDbConnection sqlCon = new OleDbConnection(connectionString);
                sqlCon.Open();
                string commandString = "select * from operation where date='" + txtDate.Text + "'";
                OleDbCommand sqlCmd = new OleDbCommand(commandString, sqlCon);
                // end of original code

                // create datatable to hold results
                tblSearchDateResults = new DataTable();
                // execute query and store in datatable
                tblSearchDateResults.Load(sqlCmd.ExecuteReader());

                // close connection
                sqlCon.Close();

                // check if there were results
                if (tblSearchDateResults.Rows.Count == 0)
                {
                    // inform user
                    MessageBox.Show("A record with this date " + codigoBarraConsStocks.Text + " doesnt exist. Sorry!");
                    // 'reset'datatable
                    tblSearchDateResults = null;
                    // bail out
                    return;
                }
            } // end of if (tblSearchDateResults == null)

            // display next result
            if (counter < tblSearchDateResults.Rows.Count)
            {
                txtDate.Text = tblSearchDateResults.Rows[counter]["date"].ToString();
                txtName.Text = tblSearchDateResults.Rows[counter]["A"].ToString();
                txtOperA.Text = tblSearchDateResults.Rows[counter]["B"].ToString();
                txtOperB.Text = tblSearchDateResults.Rows[counter]["C"].ToString();

                counter++;
            }
            else
            {
                MessageBox.Show("End of records");
                // 'reset' counter and datatable
                counter=0;
                tblSearchDateResults = null;
            }
        }

This will only query the database if the datatable is null. It uses the counter to iterate through the resulting data.

Hope this helps.

I'm not familiar with Access; there probably is a way to use a different query that can give you a specific record back (something like the MySql limit clause or the MsSql Fetch/Next)