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

 

[Israel]

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]

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.

 

[Israel]

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.

Big Sorry. I do have forgot something very important. When I said that there is no need displaying into textbox. Its should be. You know why? Because the operations are:
1) Start reading record one by one (for example, after displaying the first record into texbox. The value that appear on the textbox will be filtered on the table2. If it's founded)
2) From there this value will be saved on table3
3) If it's just doing the copy from table1 to table2. Its will not be the target that I want really.

Resume:
1) From first record until the last one. Picking up one by one and to do the filter on table2
2) After the filter. Its founded on table2. Just save the values appearing on textboxes to the table3

Please refer to my first post. Excuse me to insist to resolv to this post. I need to make it possible.
Many thanx.

 

[Skydiver]

And in that case, you do something like:

INSERT INTO tlbTest3([account], [name])
SELECT [account], [name]
FROM
    SELECT [account], [name]
    FROM tblTest1
    JOIN tblTest2
    ON tblTest1.account = tblTest2.account