Running a simple loop for displaying and saving once

[Israel]

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:

//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();
                    }

 

[Skydiver]

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

INSERT INTO tlbTest2([account], [name])
SELECT [account], [name]
FROM tlbTest1

 

[Skydiver]

This thread feels almost like your other thread from a few months ago:

How to make a simple loop until last record

Hi, I would like to do a Loop with these codes. But I need to explain very well what I try to do. On my form I have a Datagrid, one TextBox and a Button I have a table name “Test”with a 2 columns (Company_name and Company). This table have 5 records After displaying each “Company” its should...

csharpforums.net

 

[Israel]

This thread feels almost like your other thread from a few months ago:

How to make a simple loop until last record

Hi, I would like to do a Loop with these codes. But I need to explain very well what I try to do. On my form I have a Datagrid, one TextBox and a Button I have a table name “Test”with a 2 columns (Company_name and Company). This table have 5 records After displaying each “Company” its should...

csharpforums.net

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.

 

[Israel]

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

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]

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]

Ok, Thanx. But next couple of hours I will show you what I wrote as code to make my understanding more clear.

 

[Israel]

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).

//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

 

[Skydiver]

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]

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.

 

[NoUserHere]

Why are you using your UI as part of you data model?

There was an implied question there, in case you missed it @Israel

 

[Israel]

There was an implied question there, in case you missed it @Israel

Ok, I am wrong yes. That why I need a solution. But no one are interested to help correcting my codes.

 

[Skydiver]

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]

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:

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]

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.