Trouble inserting text value into datetime in SQL Server

I'm trying to update a date value from a textbox that the user enters a new date into.  I'm getting the following SQL error because apparently it's not formatted:
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The value I'm trying to enter is:
"02/29/11"

Any idea how I fix this?
string strSQL = "UPDATE tblTrayHeader SET PS_Link = '" + strUserOption + "', " +
                        "JobNumber = '" + txtJobNumber.Text.Substring(0, 5) + "', " +
                        "PckgNumber = '" + txtPackage.Text.Substring(1, 1) + "', " +
                        "DropNumber = '" + txtJobNumber.Text.Substring(2, 1) + "', " +
                        "CommQualFlag = '" + strMailType + "', " +
                        "MailDate = '" + System.Convert.ToDateTime(txtMailDate.Text) + "' " +                
                        "WHERE PS_Link LIKE ' " + lblUserOption.Text.Substring(0, 10) + "%'";

Open in new window

JT_SIROAsked:
Who is Participating?
 
AngryBinaryCommented:
First:

"MailDate = '" + System.Convert.ToDateTime(txtMailDate.Text).ToString("MM/dd/yy") + "' " +

Second:

Your update statement is vulnerable to SQL injection attack. Imagine what would happen if one of the text boxes contained an apostrophe, followed by some heinous stuff. The safer way to do it is use parameters in the SQL statement - that also simplifies data type conversion, since you wouldn't have to worry about formatting the DateTime:

 
string strSQL = "UPDATE tblTrayHeader SET PS_Link = @UserOptionNew, " +
                        "JobNumber = @JobNumber, " +
                        "PckgNumber = @Package, " +
                        "DropNumber = @DropNumber, " +
                        "CommQualFlag = @MailType, " +
                        "MailDate = @MailDate " +                
                        "WHERE PS_Link LIKE @UserOptionOld";

using (var command = new SqlCommand(strSQL, connection))
{
  command.Parameters.AddWithValue("@UserOptionOld", String.Format("{0}%", lblUserOption.Text.Substring(0, 10)));
  command.Parameters.AddWithValue("@UserOptionNew", strUserOption );
  command.Parameters.AddWithValue("@JobNumber", txtJobNumber.Text.Substring(0, 5));
  command.Parameters.AddWithValue("@Package", txtPackage.Text.Substring(1, 1));
  command.Parameters.AddWithValue("@DropNumber", txtJobNumber.Text.Substring(2, 1));
  command.Parameters.AddWithValue("@MailDate", Convert.ToDateTime(txtMailDate.Text));

  command.ExecuteNonQuery();
}

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

I suggest using the ISO date format, which almost never gets interprited incorrectly from string to datetime.

That is, yyyy-mm-dd

HTH
  David
0
 
baretreeCommented:
actually as dtodd suggests it is better to use that format but imo it is better without the dashes, just like yyyymmdd so you won't deal with any particular system configuration, mostly when accessing from windows systems
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
David ToddSenior DBACommented:
Hi,

I agree with BareTree to a point.

The YYYYMMDD formatted string will be interripted correctly despite the setting of set dateformat. However I prefer with dashes as its easier for humans to read ...

Regards
  David
0
 
nishant joshiTechnology Development ConsultantCommented:
string strSQL = "UPDATE tblTrayHeader SET PS_Link = '" + strUserOption + "', " +
                        "JobNumber = '" + txtJobNumber.Text.Substring(0, 5) + "', " +
                        "PckgNumber = '" + txtPackage.Text.Substring(1, 1) + "', " +
                        "DropNumber = '" + txtJobNumber.Text.Substring(2, 1) + "', " +
                        "CommQualFlag = '" + strMailType + "', " +
                        "MailDate = '" + DateTime.Parse(txtMailDate.Text) + "' " +                
                        "WHERE PS_Link LIKE ' " + lblUserOption.Text.Substring(0, 10) + "%'";
0
 
Gustav BrockCIOCommented:
You could get away with this simple change:

 "MailDate = '" + txtMailDate.Text + "' " +      

However, neither this nor any of the other glorius suggestions will do if you try to insert invalid dates like "02/29/11" ... 2011 is not a leap year, thus no Feb. 29th exists.

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.