Parametirized StringBuilder

I have a dynamic gridview which can be used to add new rows of two columns to specify a start date and an end date. I am trying to use a string collection to pass in the output from the textboxes to a parameterized query as shows. I am getting an incorrect syntax near 9 error. How can I make this work? I need to use the stringbuilder. Any help would be appreciated, thanks!

protected void btnSaveIterations_Click(object sender, EventArgs e)
{


    int rowIndex = 0;

    StringCollectio开发者_C百科n sc = new StringCollection();

    if (ViewState["CurrentTable"] != null)
    {

        DataTable dtCurrentTable = (DataTable)ViewState["CurrentTable"];


        if (dtCurrentTable.Rows.Count > 0)
        {

            for (int i = 1; i <= dtCurrentTable.Rows.Count; i++)
            {

                //extract the TextBox values
                TextBox box1 = (TextBox)Gridview1.Rows[rowIndex].Cells[1].FindControl("start_iteration");
                TextBox box2 = (TextBox)Gridview1.Rows[rowIndex].Cells[2].FindControl("end_iteration");

                start_date = box1.Text;
                end_date = box2.Text;

                //get the values from the TextBoxes
                //then add it to the collections with a comma "," as the delimited values

                sc.Add(proj_id + "," + start_date + "," + end_date);

                rowIndex++;

            }

            //Call the method for executing inserts

            InsertRecords(sc);
            Response.Redirect(Request.Url.ToString());

            //r.Close();
            //conn.Close();

        }
    }
}

private void InsertRecords(StringCollection sc)
{

    SqlConnection conn = new SqlConnection(GetConnectionString());
    StringBuilder sb = new StringBuilder(string.Empty);

    string[] splitItems = null;

    foreach (string item in sc)
    {
        const string sqlStatement = "INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES (@ProjectID, @StartDate, @EndDate)";

        if (item.Contains(","))
        {

            splitItems = item.Split(",".ToCharArray());
            sb.AppendFormat("{0}('{1}','{2}','{3}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2]);

        }
    }

    try
    {

        conn.Open();
        SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
        SqlCommand cmd2 = new SqlCommand(sql, conn);

        cmd.Parameters.Add("@ProjectID", SqlDbType.Int);
        cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
        cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);

        System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");

        cmd.Parameters["@ProjectID"].Value = proj_id;
        cmd.Parameters["@StartDate"].Value = Convert.ToDateTime(start_date, ci);
        cmd.Parameters["@EndDate"].Value = Convert.ToDateTime(end_date, ci);

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();

        ...
}

You seem to mix up two different things:

With the StringBuilder you set the SQL text to a string containing the parameter placeholders, followed by another string containing the values:

INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES(@ProjectID, ...)(12345, '2011-02-22', ...);

And later you set the parameters, but then you forget the single quotes around the dates - hence the 'incorrect syntax near 9' error.

Either you omit the whole SQL parameter stuff at the end and just set the values in your StringBuilder, giving

INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES (12345, '2011-02-22', ...);

or you omit the StringBuilder part and add the single quotes to your sqlStatement around the parameter placeholders:

INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES(@ProjectID, '@StartDate', '@EndDate');

You are using text from sb that is obviously incorrect sql. Correct code would be something like

...
const string sqlStatement = "INSERT INTO Iterations (ProjectID, StartDate, EndDate) VALUES (@ProjectID, @StartDate, @EndDate)";
conn.Open();
SqlCommand cmd = new SqlCommand(sqlStatement, conn);
cmd.Parameters.Add("@ProjectID", SqlDbType.Int);
cmd.Parameters.Add("@StartDate", SqlDbType.DateTime);
cmd.Parameters.Add("@EndDate", SqlDbType.DateTime);

System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");

foreach (string item in sc)
{

    if (item.Contains(","))
    {
        splitItems = item.Split(",".ToCharArray());

        cmd.Parameters["@ProjectID"].Value = splitItems[0];
        cmd.Parameters["@StartDate"].Value = Convert.ToDateTime(splitItems[1], ci);
        cmd.Parameters["@EndDate"].Value = Convert.ToDateTime(splitItems[2], ci);

        cmd.CommandType = CommandType.Text;
        cmd.ExecuteNonQuery();
    }
}
...