Can't insert data

Question

I can't find mistake why my data didn't inserted in data base. I use sql server. DB is 3 field: 1) id (int) 2) test1(varchar(50)) 3) test2(varchar(50)). I try insert data from textbox and date.

private void SaveToDB()
    {
        string strSql = "";
        string[] tos = txtTo.Text.Split(';');
        for (int i = 0; i < tos.Length; i++)
        {
            strSql += "INSERT INTO test (test1, test2) VALUES ('" + txtContent.Text.Trim() + "','" + DateTime.Now.ToString() + "');";
        }
        using (SqlConnection connection = new SqlConnection(Common.ConnetionString))
        {
            connection.Open();
            SqlTransaction tran = connection.BeginTransaction();
            try
            {
                SqlCommand cmd = new SqlCommand(strSql, connection, tran);
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();  
            }
            finally
            {
                connection.Close();
                Response.Redirect("messagelist.aspx?flag=2");
            }
        }
    }

But how I should change code, that parameter work in loop

Answer 1

Your loop should probably encompass the entire block. Also, parameterize your query so you aren't left wide open to injection. Just a suggestion, but I would also use BEGIN TRANSACTION in your sql statement opposed to SqlTransaction tran, it will handle the rollback for you and clean up your code a bit.

string[] tos = txtTo.Text.Split(';');
for (int i = 0; i < tos.Length; i++)
{
    string strSql = "INSERT INTO test (test1, test2) VALUES (@Content, @DateTime);";
    using (SqlConnection connection = new SqlConnection(Common.ConnetionString))
    {
        connection.Open();
        SqlTransaction tran = connection.BeginTransaction();
        try
        {
            using (SqlCommand cmd = new SqlCommand(strSql, connection, tran))
            {
                cmd.Parameters.AddWithValue("@Content", txtContent.Text.Trim());
                cmd.Parameters.AddWithValue("@DateTime", DateTime.Now.ToString());
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
        }
        catch (Exception e)
        {
            tran.Rollback();  
        }
        finally
        {
            connection.Close();
            Response.Redirect("messagelist.aspx?flag=2");
        }
    }
}