Row is repeated while inserting data

Question

I was trying to populate a table with dates for a whole year except Sundays. Following is my code.

for (int i = 0; i < 365; i++)
        {

            if ( b <= 6)
            {
                cmd.Parameters["@Shift"].Value = "S" + b.ToString();
            }
            b++;
            if (b > 6)
                b = 1;


            if (date.DayOfWeek != DayOfWeek.Sunday)
            {
                cmd.Parameters["@Date"].Value = date.ToString("d");
            }
            date = date.NextDay();
            sqlConn.Open();
            cmd.ExecuteNonQuery();
            sqlConn.Close();
        }

I'm using the extension method from http://fluentdatetime.codeplex.com/.

The problem is, when the rows are inserted, everything is correct except the date before a Sunday is repeated.

For example,

    4/28/2011 < Thu 
    4/29/2011 < Fri
    4/30/2011 < Sat 
    4/30/2011 < Sat
    5/2/2011  < Mon

Where did I go wrong?

PS. When I try to figure out with breakpoints and soon as the pointer reaches sqlConn.Open(), Visual Studio 2010 says "No Source Available".

`if (date.DayOfWeek != DayOfWeek.Sunday)` presumably you shouldn't execute the `insert` at all. Your execute is outside the `if` block. This is a very inefficient way of populating a calendar table BTW. — Martin Smith, Apr 24 '11 at 17:17
Thanks for the suggestion. Please let me know if there are better ways. I'm still a learner =) — Ye Myat Aung, Apr 24 '11 at 17:21
I'd do it in one statement rather than 365 calls to the database using one of the techniques [from here](http://stackoverflow.com/questions/10819/sql-auxiliary-table-of-numbers/2663232#2663232). That generates numbers rather than dates but it is easy to generate dates from these numbers using `DATEADD(DAY,N-1,'20110101')` — Martin Smith, Apr 24 '11 at 17:38

Bala R · Accepted Answer · 2011-04-24T17:26:36.877

1

You'll have to wrap your insert logic into your if-block.

Try this

if (date.DayOfWeek != DayOfWeek.Sunday)
{
    cmd.Parameters["@Date"].Value = date.ToString("d");

    sqlConn.Open();
    cmd.ExecuteNonQuery();
    sqlConn.Close();
 }
 date = date.NextDay();

edited Apr 24 '11 at 17:26

answered Apr 24 '11 at 17:16

Bala R

107,317
23
199
210

With `date.NextDay()` inside your `if`, how will `date` ever progress past Sunday? – Gabe Apr 24 '11 at 17:23
@Gabe & @Bala Thanks. The date problem is solved but now, the other column "Shift" seems not correct anymore. Some shifts are missing randomly after every loop. – Ye Myat Aung Apr 24 '11 at 17:32

Row is repeated while inserting data

1 Answers1