SQL insert command only working once in a loop

Question

This is s for loop and it will go to the times and will put the time column as true. This works for the first time, but when the time increases by 0.5, it stays false. The for loop is working as i tried a MessageBox.Show("" + Time1 + ""); inside the for loop.

for (double Time = time_began_5; Time < time_finished_5; Time = Time + 0.5)
        {
            string Time1 = Time.ToString("0.00");


            try
            {
                SqlConnection cn = new SqlConnection("Data Source=.\\SqlExpress;Initial Catalog=AllensCroft;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework;");

                cn.Open();
                SqlCommand Command = new SqlCommand("INSERT INTO Slots ([Date],[RoomID],[" + Time1 + "]) Values (@date,@room,1)", cn);
                Command.Parameters.AddWithValue("date", date);
                Command.Parameters.AddWithValue("room", rooms_combo.SelectedValue);

                Command.ExecuteNonQuery();


                try
                {
                    cn.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

        }

Here is what the database looks like, the first true field works, but when it loops to another time, it remains false, I think it may be due to the fact that if I have an existing row with that date (date is primary key), i cannot update that row, so i might need to have an IF the row exists, update, else create a new row.

slots database

as a parameter in the method:private void SaveSlots(DateTime date) — Hamoudy
– Hamoudy, Commented Mar 13, 2013 at 21:32

rs. · Accepted Answer · 2013-03-13 23:45:49Z

3

Try this, you don't have to open connection in each loop, create your sql statement first looping through each value and then do insert using one statement

private string CreateInsertStatement(double time_began_5, double time_finished_5)
{
   string sql = "INSERT INTO Slots ([Date],[RoomID],";
   string valuesql = " Values (@date,@room,";
   for (double Time = time_began_5; Time < time_finished_5; Time = Time + 0.5)
   {
    string Time1 = Time.ToString("0.00");
    sql+ = "[" + Time1 + "],";
    valuesql+ = "1,";
   }
   sql = sql.TrimEnd(',') + ") ";
   valuesql = valuesql.TrimEnd(',') + ") ";

   return sql + valuesql;
}

private string CreateUpdateStatement(double time_began_5, double time_finished_5)
{
    string sql = "UPDATE Slots SET ";
    string wheresql = " WHERE [Date] = @date AND [RoomID] = @room";
    for (double Time = time_began_5; Time < time_finished_5; Time = Time + 0.5)
    {
        string Time1 = Time.ToString("0.00");
        sql+ = "[" + Time1 + "] = 1,";        
    }
    sql = sql.TrimEnd(','); 
    return sql + wheresql;
}

Then in you actual insert code:

try
{
    SqlConnection cn = new SqlConnection("Data Source=.\\SqlExpress;Initial Catalog=AllensCroft;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework;");

    cn.Open();
    SqlCommand Command;
    //check if row exists 
    Command = new SqlCommand("select count(*) from Slots WHERE [Date] = @date AND [RoomID] = @room", cn);
    Command.Parameters.AddWithValue("date", date);
    Command.Parameters.AddWithValue("room", rooms_combo.SelectedValue);

    var cnt = Command.ExecuteScalar();
    if(cnt!=null)
    {
        string sqlstr = ""
        if(Int32.Parse(cnt.ToString()) > 0)
        {
            sqlstr = CreateUpdateStatement(time_began_5,time_finished_5);
        }
        else if(Int32.Parse(cnt.ToString()) == 0)
        {
            sqlstr = CreateInsertStatement(time_began_5,time_finished_5);
        }
        Command = new SqlCommand(sqlstr, cn);
        Command.Parameters.AddWithValue("date", date);
        Command.Parameters.AddWithValue("room", rooms_combo.SelectedValue);
        Command.ExecuteNonQuery();
    }
    try
    {
        cn.Close();
    }
    catch (Exception e)
    {
        Console.WriteLine(e.ToString());
    }
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
}

edited Mar 13, 2013 at 23:45

answered Mar 13, 2013 at 21:44

rs.

27.6k13 gold badges71 silver badges93 bronze badges

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

5 Comments

Hamoudy Over a year ago

thanks for making my code more efficient, but it will only do one column to true, it only inserts a new row but does not update

rs. Over a year ago

this will do all columns based on time_finished_5 value you use? Can you debug and check what value it is returning from CreateInsertStatement function and post here?

Hamoudy Over a year ago

this works the first time for all of the times in the loop, thanks, but when i try to do the same procedure, again same date different times, it doesn't update any more, should we try merge?

rs. Over a year ago

Yes you can try using merge or what you can do is first check database if that room and date row exists based on that return insert or update statement string. You may have to write another function that will return Update SQL.

Hamoudy Over a year ago

yes it worked, thanks a lot, i used convert.toint32 instead of parse because VS was throwing an error and i put another equals sign in Int32.Parse(cnt) = 0, but thanks a lot for your time, i have marked it as an answer

Steve · Accepted Answer · 2013-03-13 21:52:37Z

3

You are doing an insert. In every loop you insert a new row and set the value true only for the column which name is equal to the current value of the variable Time1.

Not having a value for the other columns they probably default to false. (bit columns I suppose)

If you want a default to true for every column perhaps it is better to change the database schema adding the default for every time column, otherwise you need a long list of parameters

EDIT: If your logic dictates that you need only one row per date and set every time column to true if you enter the situation above then you can move this logic in the database using a stored procedure:

CREATE PROCEDURE InsertOrUpdateSlotFromCode(@dt smalldatetime, @roomID int)
AS
BEGIN
    DECLARE @cnt INT
    SELECT  @cnt = COUNT(*) from Slots WHERE [Date] = @dt
    if @cnt = 0 
        INSERT INTO Slots ([Date],[RoomID], <here all the time fields> VALUES (@dt, @roomID, 1, ....)
    else
        UPDATE Slots SET [09.00] = 1, ..... WHERE [Date] = @dt
    End 
END

then your code call the sp

using(SqlConnection cn = new SqlConnection(.........))
{
    cn.Open();
    SqlCommand Command = new SqlCommand("InsertOrUpdateSlotFromCode", cn);
    Command.CommandType = CommandType.StoredProcedure;
    Command.Parameters.AddWithValue("date", date);
    Command.Parameters.AddWithValue("room", rooms_combo.SelectedValue);
    Command.ExecuteNonQuery();
}

Of course now you can completely get rid of the loop

edited Mar 13, 2013 at 21:52

answered Mar 13, 2013 at 21:32

Steve

217k22 gold badges242 silver badges296 bronze badges

4 Comments

Hamoudy Over a year ago

no, i wanted the for loop to change them to true itself, the default should be false, but when the for loop runs, only the first time gets changed to true. How would i update the row if a row already exists?

Abe Miessler Over a year ago

@Hamoudy, You would have to include some logic to see if a row already existed for that key and do an insert/update depending on what you find. A better approach would be to construct all of the values you want to insert, and then insert them. What you are talking about is essentially executing 1 insert operation and at least 12 updates (the number of columns in your screen shot) for every row you want to populate. If your DBA gets wind of you doing this he/she will likely skin you alive.

rs. Over a year ago

@steve, he is calculating column names dynamically, not sure how he can do that using your stored procedure.

Hamoudy Over a year ago

yes, rs is correct, i was researching this, could we use merge here?

Collectives™ on Stack Overflow

SQL insert command only working once in a loop

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related