How to make a simple loop until last record

[Israel]

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 send all company’s names into one table named “Receive”

Let’s see what I do. Its works but without with loop:

Company_name Company

Andrews Company1
McSean Company2
Nokia Company2
TelCell Company1
Flex Company1

conn.Open();

SqlCommand cmd1 = conn.CreateCommand();
cmd1.CommandType = CommandType.Text;
cmd1.CommandText = "select * from Test where company = @company";
var param1 = new SqlParameter("@company", SqlDbType.NChar);


param1.Value = txtBoxCompany.Text;
{
    cmd1.Parameters.Add(param1);
    cmd1.Parameters.Add(param2);
}
cmd1.ExecuteNonQuery();

DataTable dt1 = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter(cmd1);
da1.Fill(dt1);

conn.Close();
dgvTest.DataSource = dt1;

Then after displaying the result on my datagrid its should send in block names of company on other table “Receive”

string myConnectionString;
myConnectionString = "Data Source = (LocalDB)\\MSSQLLocalDB; AttachDbFilename=C:\\Test\\WindowsFormsApplication1\\App_datas\\Accounting.mdf;Integrated Security = True;Integrated Security = True";

using (var con = new SqlConnection())
{
    con.ConnectionString = myConnectionString;
    con.Open();

    using (var cmd = new SqlCommand())
    {
        cmd.Connection = con;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = @"INSERT INTO Test (company_name, company) SELECT company_name, company FROM test where company = @company";

        cmd.Parameters.AddWithValue("@company", txtBox_company.Text);
        cmd.ExecuteNonQuery();
    }
    con.Close();
}
Console.WriteLine("Done.");

But all of these should doing as a loop one by one record until read and transfering the last record. Many thanx.

 

[jmcilhinney]

You say that you want to insert in a table named Receive but your INSERT statement is inserting into Test. It doesn't make sense to retrieve the data into your application and then do an INSERT...SELECT. If you're going to insert from another table then why retrieve the data into your app? If you have the data in your app, why not insert from there? Also, why use a loop at all?

Basically, if you want to insert directly to one table from another then you just need one call to ExecuteNonQuery containing an appropriate INSERT...SELECT statement. If you want to insert from your app then you just use a data adapter to save all the data in a single call to Update. If you choose the second option then you need to make sure that each RowState is Added, so that the data is ready to be inserted. You can do that by setting AcceptChangesOnFill to False on the data adapter before calling Fill, or you can loop through the rows of the DataTable and call SetAdded on each one.

 

[Israel]

Doesnt make sense yes. I would like just to say table "Receive".
1. I retreive that from another table because I need to filter especific datas I need.
2. I was talking about loop because I need the program making a data circle. From the first record to the last one (My big target is to see the process be automatic just by on click).

Thank you.

 

[Skydiver]

You can do something like:

INSERT INTO Receive ( ... ) SELECT ... FROM Test

(The ellipses are for the columns you are interested in.)

What will happen is the results of SELECT ... FROM Test will be inserted into Receive. And it all happens server side which is a big performance boost.

There is no need for you to loop over the contents of the "Test" table, because SELECT without any WHERE conditions will return all rows.

 

[Israel]

You can do something like:

INSERT INTO Receive ( ... ) SELECT ... FROM Test

(The ellipses are for the columns you are interested in.)

What will happen is the results of SELECT ... FROM Test will be inserted into Receive. And it all happens server side which is a big performance boost.

There is no need for you to loop over the contents of the "Test" table, because SELECT without any WHERE conditions will return all rows.

I need make this very clear please.
1. The problem here is to make the eclipse for one big reason: from one click I need automatiic readind code filtring each record.
Fro example:

On one click, its filter the first record of the column "Company1". Then its will display all names from "Company_name" belonging to "company1". But its can't refilter another time "company1". Why? Because its already be filtered. Then its will pass to the next record "company2", etc doing like that.
After to do this eclipse what will happen? its jump to the next codes for transfering the datas filtered.
Each data founded into column "company_name" after a filter its transfer at the same time.
I would like to say that my codes work perfectly but its doesnt do as I explained now.
Let's see if its start to filter "company1" its will display Andrews, telCel and Flex (These three names will be transfered to the table "Receive" at once). The next is "company2" (McSean and Nokia). But its can't filter "company1" again because he already be filtered.

Company_name Company
------------------ ------------
Andrews Company1
McSean Company2
Nokia Company2
TelCell Company1
Flex Company1

 

[Skydiver]

What do you mean by the word "eclipse"?

I'm not seeing the difference between selecting all of "Company1" values from "Test" to put into "Receive", and then putting all of "Company2" values from "Test" and putting them them into "Receive" as two iterations, as opposed to putting them all in just a single command.

Let's say we do SELECT Company_Name FROM Test WHERE Company = 'Company1'. That will yield the following:

Andrews
Nokia
TelCell
Flex

And so inserting those into your "Receive" table, that table will now contain:

Andrews
Nokia
TelCell
Flex

So in your next iteration where your do SELECT Company_Name FROM Test WHERE Company = 'Company2' to yield:

McSean
Nokia

When you insert those values into your "Recieve" table, your table will now contain:

Andrews
Nokia
TelCell
Flex
McSean
Nokia

That is the exact same result you would get if you had just done a single INSERT INTO Receive (company_name) SELECT company_name FROM Test

 

[Skydiver]

This is starting to feel like a reverse XY Problem. Instead of the normal XY problem where you say you want X but are trying to do it via Y, it sounds like you really want to do Y, and don't care about X.

 

[Israel]

This is starting to feel like a reverse XY Problem. Instead of the normal XY problem where you say you want X but are trying to do it via Y, it sounds like you really want to do Y, and don't care about X.

Okay, anyway thank you very much for your support. I do resolv it in another way.

 

[Skydiver]

Out of curiosity, how did you resolve it?

 

[NoUserHere]

It's often helpful to others troubleshooting the same issue as you when you share your solutions with other members so they too can learn from whatever resolution you came up with. It's actually unfair to ask for help and then not even bother to share your solution in the end.