I am using the following code to insert StartDate(s) and EndDate(s) into my Iterations table. I am displaying the output in my textbox like this: dd/MM/yyyy, but am writing to my db as MM/dd/yyyy, because of the datetime data type - hence I'm using:
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");
sc.Add(proj_id + "," + Convert.ToDateTime(box1.Text, ci) + "," + Convert.ToDateTime(box2.Text, ci));
I'm pretty sure this code worked just fine on localhost, but when I uploaded it to a server, I am getting the error: Insert error: the conversion of a varchar datatype to a datetime data type resulted in an out-of-range value. Please help! thanks!
Here's the full code:
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";
if (item.Contains(","))
{
splitItems = item.Split(",".ToCharArray());
sb.AppendFormat("{0}('{1}','{2}','{3}'); ", sqlStatement, splitItems[0], splitItems[1], splitItems[2]);
}
}
string sql = "INSERT INTO ProjectIterationMember (ProjectIterationID, MemberID) SELECT ProjectIterationID AS pro_it_id, @member_id FROM Iterations WHERE ProjectID = '" + proj_id + "'";
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), conn);
SqlCommand cmd2 = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
SqlParameter memberParameter = new SqlParameter("@member_id", SqlDbType.Int);
cmd2.Parameters.Add(memberParameter);
cmd2.CommandType = CommandType.Text;
cmd2.Prepare();
memberParameter.Value = project_manager.SelectedValue;
cmd2.ExecuteNonQuery();
for (int i = 0; i < project_members.Items.Count; ++i)
{
if (project_members.Items[i].Selected)
{
memberParameter.Value = project_members.Items[i].Value;
cmd2.ExecuteNonQuery();
}
}
//Display a popup which indicates that the record was successfully inserted
Page.ClientScript.RegisterClientScriptBlock(typeof(Page), "Script", "alert('New iterations were successfully added!');", true);
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Insert Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
conn.Close();
}
}
protected void btnSaveIterations_Click(object sender, EventArgs e)
{
int rowIndex = 0;
StringCollection 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");
System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-GB");
//get the values from the TextBoxes
//then add it to the collections with a comma "," as the delimited values
sc.Add(proj_id + "," + Convert.ToDateTime(box1.Text, ci) + "," + Convert.ToDateTime(box2.Text, ci));
rowIndex++;
}
//Call the method for executing inserts
InsertRecords(sc);
Response.Redirect(Request.Url.ToString());
//r.Close();
//conn.Close();
}
}
}