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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

David ToddSenior Database AdministratorCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.