Running a simple loop for displaying and saving once

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
Hi,

I have one table and two tools:

“TblCompany”

“txtAccountNumber”

“btnRun”


My table looks like this:



ID | Account | Name

1 5.6 Selling

2 3.5 Bying

3 1.4 Mobile



I wrote some codes that make a normal and simple filter. The target filter is one the column “Account”.

Then why I’am doing to resolv it? I put each ID number inside of my codes and copy/paste the same block of codes to filter the next ID number to make the operation. But there is a problem? This table is growing up every day and its becoming little bit havy when processing. Then how can I do to make a one way loop until the latest record of my table displaying one by one the next account’s number that will be saved later and so on.

Please see my codes:

C#:
//Filtering Accounting Number: 5.6

string connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flex\\Accounting.mdf;Integrated Security = True";
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
   string commandString = "select top 1 * from tblTest1 where ID='" + “5.6” + "' order by ID DESC";
                SqlCommand sqlCmd = new SqlCommand(commandString, conn);
                SqlDataReader read = sqlCmd.ExecuteReader();

                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        txtAccount.Text = read["Account"].ToString();
                        txtName.Text = read["Name"].ToString();
                    }
                }
                else
                {
                }
                read.Close();
                conn.Close();


conn = new SqlConnection"Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flex\\Accounting.mdf;Integrated Security = True";);
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;

            SqlParameter account = new SqlParameter("@account", SqlDbType.Varchar);
            SqlParameter name = new SqlParameter("@name", SqlDbType.Varchar);

            comm.Parameters.Add(account);
            comm.Parameters.Add(name);

            account.Value = txtAccount.Text;
            name.Value = txtName.Text;

            comm.Connection = conn;

            comm.CommandText = "insert into tblTest2 ([account],[name])values(@account,@name)";

            {
               
                {
                    try
                    {
                        comm.ExecuteNonQuery();
                    }

                    finally
                    {
                        conn.Close();
                    }

//Filtering Account number: 3.5

string connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flex\\Accounting.mdf;Integrated Security = True";
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
   string commandString = "select top 1 * from tblTest1 where ID='" + “3.5” + "' order by ID DESC";
                SqlCommand sqlCmd = new SqlCommand(commandString, conn);
                SqlDataReader read = sqlCmd.ExecuteReader();

                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        txtAccount.Text = read["Account"].ToString();
                        txtName.Text = read["Name"].ToString();
                    }
                }
                else
                {
                }
                read.Close();
                conn.Close();


conn = new SqlConnection"Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flex\\Accounting.mdf;Integrated Security = True";);
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;

            SqlParameter account = new SqlParameter("@account", SqlDbType.Varchar);
            SqlParameter name = new SqlParameter("@name", SqlDbType.Varchar);

            comm.Parameters.Add(account);
            comm.Parameters.Add(name);

            account.Value = txtAccount.Text;
            name.Value = txtName.Text;

            comm.Connection = conn;

            comm.CommandText = "insert into tblTest2 ([account],[name])values(@account,@name)";

            {
               
                {
                    try
                    {
                        comm.ExecuteNonQuery();
                    }

                    finally
                    {
                        conn.Close();
                    }
//Filtering 1.4

string connectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flex\\Accounting.mdf;Integrated Security = True";
                SqlConnection conn = new SqlConnection(connectionString);
                conn.Open();
   string commandString = "select top 1 * from tblTest1 where ID='" + “1.4” + "' order by ID DESC";
                SqlCommand sqlCmd = new SqlCommand(commandString, conn);
                SqlDataReader read = sqlCmd.ExecuteReader();

                if (read.HasRows)
                {
                    while (read.Read())
                    {
                        txtAccount.Text = read["Account"].ToString();
                        txtName.Text = read["Name"].ToString();
                    }
                }
                else
                {
                }
                read.Close();
                conn.Close();



conn = new SqlConnection"Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flex\\Accounting.mdf;Integrated Security = True";);
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;

            SqlParameter account = new SqlParameter("@account", SqlDbType.Varchar);
            SqlParameter name = new SqlParameter("@name", SqlDbType.Varchar);

            comm.Parameters.Add(account);
            comm.Parameters.Add(name);

            account.Value = txtAccount.Text;
            name.Value = txtName.Text;

            comm.Connection = conn;

            comm.CommandText = "insert into tblTest2 ([account],[name])values(@account,@name)";

            {
               
                {
                    try
                    {
                        comm.ExecuteNonQuery();
                    }

                    finally
                    {
                        conn.Close();
                    }
 
Last edited:

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
Why are you hard coding the IDs into your code? Doing a SELECT ID FROM tlbTest1 will give you all the IDs. With that you can do just call a helper method passing in the ID.

But then the brings up the question: Why are you pulling the account number and account name from tblTest1, putting that into two textboxes, and then turning around inserting the account number and name into tblTest2? Why no simply insert directly into tlbTest2 values that you get from tlbTest1? Something like
SQL:
INSERT INTO tlbTest2([account], [name])
SELECT [account], [name]
FROM tlbTest1
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
This thread feels almost like your other thread from a few months ago:
 

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
This thread feels almost like your other thread from a few months ago:
Yes, It's really looks like other thread from a few months ago. Its mine! I do try to resolv it in another way. But no solution. That why I do post another one that I am still struggling with until NOW.
 
Last edited:

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
Why are you hard coding the IDs into your code? Doing a SELECT ID FROM tlbTest1 will give you all the IDs. With that you can do just call a helper method passing in the ID.

But then the brings up the question: Why are you pulling the account number and account name from tblTest1, putting that into two textboxes, and then turning around inserting the account number and name into tblTest2? Why no simply insert directly into tlbTest2 values that you get from tlbTest1? Something like
SQL:
INSERT INTO tlbTest2([account], [name])
SELECT [account], [name]
FROM tlbTest1
It's a good question. But my problem is I need to use two separated table (1 and 2). The first one displaying record one by one from the first until the last one. While Displaying each one and saving each data into table2. That why you see two textboxes from table1. It's very important this solution. I need to learn or to know how to resolv it please. It's capital for me. Thanx!
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
Is the display of each record being copied over from table 1 to table 2 what is important? Or is it that by the end of the process, the display shows the last record that was processed?

I ask because as your code in post #1 currently stands, all the user will ever see is the last record despite the fact that you've updated the textboxes to several different values. The reason for this is because if all that code above is executed the Windows message pump never get a chance to repaint the windows until your code is done running. (And please whatever you do, don't do the VB6 hack of calling DoEvents(). You open yourself up to hard to reproduce, hard to debug re-entrancy problems if you do.)
 

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
Ok, Thanx. But next couple of hours I will show you what I wrote as code to make my understanding more clear.
 

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
What I wrote is here. Even it's a big mess its works as a loop (starting from the first datagrid's row until the latest one). Just to let's understand what I need to do.
I have two buttons (1 and 2).
C#:
//Into the Button1:
private void button1_Click(object sender, EventArgs e)
        {      
            if (dgvDiario.Rows.Count >= 0)
            {
                int i = dgvDiario.CurrentRow.Index + 0;
                if (i >= -1 && i < dgvDiario.Rows.Count)
                    dgvDiario.CurrentCell = dgvDiario.Rows[i].Cells[0];

                DataGridViewRow row = this.dgvDiario.Rows[this.dgvDiario.SelectedCells[0].RowIndex + 1];
                if (row != null)
                {
                    txtDebit.Text = row.Cells["debit"].Value.ToString();
                    txtCredit.Text = row.Cells["credit"].Value.ToString();

                    if (dgvDiario.Rows.Count >= 0)
                    {
                        int i1 = dgvDiario.CurrentRow.Index + 0;
                        if (i1 >= -1 && i1 < dgvDiario.Rows.Count)
                            dgvDiario.CurrentCell = dgvDiario.Rows[i1].Cells[0];
                    }

                    if (dgvDiario.Rows.Count >= 1)
                    {
                        int i2 = dgvDiario.CurrentRow.Index + 1;
                        if (i2 >= -1 && i2 < dgvDiario.Rows.Count)
                            dgvDiario.CurrentCell = dgvDiario.Rows[i2].Cells[0];
                       
                        button2.PerformClick(); //Before to pass to the next row its save then so one.

                        if (dgvDiario.Rows.Count >= 0)
                        {
                            int i3 = dgvDiario.CurrentRow.Index + 0;
                            if (i3 >= -1 && i3 < dgvDiario.Rows.Count)
                                dgvDiario.CurrentCell = dgvDiario.Rows[i3].Cells[0];

                            DataGridViewRow row1 = this.dgvDiario.Rows[this.dgvDiario.SelectedCells[0].RowIndex + 1];
                            if (row1 != null)
                            {
                                txtDebit.Text = row1.Cells["debit"].Value.ToString();
                                txtCredit.Text = row1.Cells["credit"].Value.ToString();
                                if (dgvDiario.Rows.Count >= 0)
                                {
                                    int i4 = dgvDiario.CurrentRow.Index + 0;
                                    if (i4 >= -1 && i4 < dgvDiario.Rows.Count)
                                        dgvDiario.CurrentCell = dgvDiario.Rows[i4].Cells[0];
                                }

                                if (dgvDiario.Rows.Count >= 1)
                                {
                                    int i5 = dgvDiario.CurrentRow.Index + 1;
                                    if (i5 >= -1 && i5 < dgvDiario.Rows.Count)
                                        dgvDiario.CurrentCell = dgvDiario.Rows[i5].Cells[0];
                                }
                            }
                           
        //Into the Button2:                              
                           
        private void button2_Click(object sender, EventArgs e)
        {
            conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flexit_GovFAA\\Forcas_Armadas_Angolanas\\App_Datas\\FlexAccounting.mdf;Integrated Security = True");
            comm = new SqlCommand();
            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlCommand comm1 = new SqlCommand();
            comm1.Connection = conn;

            SqlParameter debit = new SqlParameter("@debit", SqlDbType.VarChar);
            comm1.Parameters.Add(debit);
            debit.Value = txtDebit.Text;
            comm1.Connection = conn;
            comm1.CommandText = "insert into testRazao ([debit])values(@debit)";

            {
                //if (MessageBox.Show("Tens certeza de guardar os dados?", "Janela de gravação", MessageBoxButtons.YesNo) == DialogResult.Yes)
                {
                    try
                    {
                        comm1.ExecuteNonQuery();
                    }

                    finally
                    {
                        //dgvDiario.CurrentCell = dgvDiario.Rows[dgvDiario.Rows.Count - 1].Cells[0];
                        conn.Close();
                        button1.PerformClick();
                    }
//Unfortunaly after doing all the job its gives this error message pointing on this line:
//txtDebit.Text = row.Cells["debit"].Value.ToString();

//Error message:
//Additional information:
//Reference object not defined for one instance of object
 
Last edited:

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
But all you are doing in button1 is manually setting each row as the current row, updating the textboxes, and manually invoking the click event on button2. Then in button2 you are saving the current row. WTF!?!?!?!

If you need to save all the rows of the datagrid, just save the rows to the database. Why are you using your UI as part of you data model?
 

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
But all you are doing in button1 is manually setting each row as the current row, updating the textboxes, and manually invoking the click event on button2. Then in button2 you are saving the current row. WTF!?!?!?!

If you need to save all the rows of the datagrid, just save the rows to the database. Why are you using your UI as part of you data model?
Okay, Thank you.
 

Sheepings

Senior Programmer
Joined
Sep 5, 2018
Messages
844
Location
UK
Programming Experience
10+

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
We have tried correcting not only your code, but your entire approach in your other threads. You just reject it and continue along with your current approach. We are at an impasse.
 

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
Fine. But maybe I do explain wrong myself.

What I wrote is here. Even it's a big mess its works as a loop (starting from the first datagrid's row until the latest one). Just to let's understand what I need to do. Even like that these should be correctly professionally.

Using two separated table (1 and 2). The first one displaying record one by one from the first until the last one. While Displaying each one and saving each data into table2.

Sorry doesnt make sense. Just try to understand what really I need to do:
C#:
private void button1_Click(object sender, EventArgs e) 
{ 
if (dgvDiario.Rows.Count >= 0) 
{ 
int i = dgvDiario.CurrentRow.Index + 0; 
if (i >= -1 && i < dgvDiario.Rows.Count) 
dgvDiario.CurrentCell = dgvDiario.Rows[i].Cells[0]; 
DataGridViewRow row = this.dgvDiario.Rows[this.dgvDiario.SelectedCells[0].RowIndex + 1]; 
if (row != null) 
{ 
txtDebit.Text = row.Cells["debit"].Value.ToString(); 
txtCredit.Text = row.Cells["credit"].Value.ToString(); 
if (dgvDiario.Rows.Count >= 0) 
{ 
int i1 = dgvDiario.CurrentRow.Index + 0; 
if (i1 >= -1 && i1 < dgvDiario.Rows.Count) 
dgvDiario.CurrentCell = dgvDiario.Rows[i1].Cells[0]; 
} 
if (dgvDiario.Rows.Count >= 1) 
{ 
int i2 = dgvDiario.CurrentRow.Index + 1; 
if (i2 >= -1 && i2 < dgvDiario.Rows.Count) 
dgvDiario.CurrentCell = dgvDiario.Rows[i2].Cells[0]; 
button2.PerformClick(); //Before to pass to the next row its save then so one. 
if (dgvDiario.Rows.Count >= 0) 
{ 
int i3 = dgvDiario.CurrentRow.Index + 0; 
if (i3 >= -1 && i3 < dgvDiario.Rows.Count) 
dgvDiario.CurrentCell = dgvDiario.Rows[i3].Cells[0]; 
DataGridViewRow row1 = this.dgvDiario.Rows[this.dgvDiario.SelectedCells[0].RowIndex + 1]; 
if (row1 != null) 
{ 
txtDebit.Text = row1.Cells["debit"].Value.ToString(); 
txtCredit.Text = row1.Cells["credit"].Value.ToString(); 
if (dgvDiario.Rows.Count >= 0) 
{ 
int i4 = dgvDiario.CurrentRow.Index + 0; 
if (i4 >= -1 && i4 < dgvDiario.Rows.Count) 
dgvDiario.CurrentCell = dgvDiario.Rows[i4].Cells[0]; 
} 
if (dgvDiario.Rows.Count >= 1) 
{ 
int i5 = dgvDiario.CurrentRow.Index + 1; 
if (i5 >= -1 && i5 < dgvDiario.Rows.Count) 
dgvDiario.CurrentCell = dgvDiario.Rows[i5].Cells[0]; 
} 
} 
//Into the Button2: 
private void button2_Click(object sender, EventArgs e) 
{ 
conn = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Flexit_GovFAA\\Forcas_Armadas_Angolanas\\App_Datas\\FlexAccounting.mdf;Integrated Security = True"); 
if (conn.State != ConnectionState.Open) 
conn.Open(); 
SqlCommand comm1 = new SqlCommand(); 
comm1.Connection = conn; 
SqlParameter debit = new SqlParameter("@debit", SqlDbType.VarChar); 
comm1.Parameters.Add(debit); 
debit.Value = txtDebit.Text; 
comm1.Connection = conn; 
comm1.CommandText = "insert into testRazao ([debit])values(@debit)"; 
{ 
//if (MessageBox.Show("Tens certeza de guardar os dados?", "Janela de gravação", MessageBoxButtons.YesNo) == DialogResult.Yes) 
{ 
try 
{ 
comm1.ExecuteNonQuery(); 
} 
finally 
{ 
//dgvDiario.CurrentCell = dgvDiario.Rows[dgvDiario.Rows.Count - 1].Cells[0]; 
conn.Close(); 
button1.PerformClick(); 
}
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
Let me ask again. Why does each one need to be displayed in the textbox? Everything will go by so quickly that in the end, the only thing the user will see in the textbox is the last thing that was saved. So as a cleaner alternative, why not save everything in one go, and then just update the textbox with the last item.
 

Israel

Member
Joined
Jan 10, 2020
Messages
23
Programming Experience
Beginner
Let me ask again. Why does each one need to be displayed in the textbox? Everything will go by so quickly that in the end, the only thing the user will see in the textbox is the last thing that was saved. So as a cleaner alternative, why not save everything in one go, and then just update the textbox with the last item.
Very good question. With this question I am glad that you are trying to understand me now. Yes, let's forgot the displaying into textbox. Then how to do rightly the rest of codes?
 

Skydiver

Well-known member
Joined
Apr 6, 2019
Messages
922
Location
Virginia Beach, VA
Programming Experience
10+
See my post #2 above. That's the right way to copy the data to the other table. You can then query the table to get the last row and update the UI.
 
Top Bottom