using foreach and multiple insert rows?

Question

I'm a newbie from C# and I'm currently using C# vs 2013 and MS access database..I'm trying to do the mulitple inserts and at the same time trying to attempt foreach.. I have 2 tables in access

first table

EID  ------ FirstName
10175-- random names
10176-- random names
10177-- random names
10178 --random names
10179 --random names
10180 --random names

2nd table

index--- EID-----Date(index is autonumber type)
1-------10175----10/10/2014
2-------10175----10/11/2014
3-------10175----10/12/2014
4-------10175----10/13/2014
5-------10175----10/14/2014
6-------10175----10/15/2014
7-------10175----10/16/2014
8-------10175----10/17/2014
9-------10175----10/18/2014
10------10175----10/10/2014

what I wanted to happen was when I click a button I want to insert 10 record dates on 2nd table FOR EACH EID on the first table..here's my code for the loop 10 records for 10175

        connection.Open();
        OleDbCommand command = new OleDbCommand();
        command.Connection = connection;
        int ctr = 0;
        int counter;
        counter = int.Parse(TimeIntxt.Text);//I just use textbox for test i want this to be autogenerate based on the number of EID on first table 
        String counter2;
        for (ctr = 0; ctr < 10; ctr++)
        {
            counter++;
            counter2 = dateTimePicker1.Value.AddDays(ctr + 1).ToString();

            command10.CommandText = "insert into EmployeeData (EID,DateIn) values('" + counter + "','" + counter2 + "')";
            command10.ExecuteNonQuery();
        }

        MessageBox.Show("successfully created");
        connection.Close();

Very much thankful for those who will help me..I'm sorry if my english is not really fluent Y.Y

It sounds like you will want to use an OleDbDataReader to loop through the rows in the first table. See the related question here for an example. — Gord Thompson
– Gord Thompson, Commented Oct 10, 2014 at 15:24

Sherman · Accepted Answer · 2014-10-10 15:34:10Z

2

connection.Open()
OleDBCommand command = new OleDbCommand();
command.Connection = connection;
command.CommandText = "SELECT EID From Table";

using (OleDbDataReader dr = command.ExecuteReader())
{
    while (dr.read())
    {
        //new connection
        for(var i = 0;i < 10;i++)
        {  
            //insert (int)dr["EID"] into 2nd table
        }

    }
}

edited Oct 10, 2014 at 15:34

answered Oct 10, 2014 at 15:25

Sherman

8535 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Raymart Calinao AsthreA Over a year ago

why is it that SqlDataReader in my code that you give is still on red lines below i know that means an error did i miss something there?

Raymart Calinao AsthreA Over a year ago

tried to changed SqlDataReader with OleDbDataReader and dr.read() becomes red this time

Raymart Calinao AsthreA Over a year ago

by the way how do i use using block?

Sherman Over a year ago

You really need to look at the documentation for the OleDbDataReader object and find the appropriate methods to use. I was really just giving you a general idea on how to structure your code to accomplish what you wanted, not write the code for you.

Collectives™ on Stack Overflow

using foreach and multiple insert rows?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related