Sue Hernandez's SharePoint Blog

SharePoint and Related Stuff

Conversion failed when converting date and/or time from character string

When attempting to write a SQL Query to insert into a database, I was using something like the following statement below:

string sql = "INSERT INTO PurchaseRequest (ProjectGroupNumber, OrderType, DateRequested) ";
sql += " VALUES ('?', '?', '?')";

SqlCommand cmd = new SqlCommand(sql, conn);


cmd.Parameters.Add(new SqlParameter("@ProjectGroupNumber", _purchaseRequest.GeneralInformation.ProjectGroupNumber));
cmd.Parameters.Add(new SqlParameter("@OrderType", _purchaseRequest.GeneralInformation.OrderType));
cmd.Parameters.Add(new SqlParameter("@DateRequested", _purchaseRequest.GeneralInformation.DateRequested.Value.ToString()));

I would always get the following:

Could Not Insert into DB: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.

I found the solution to be use named parameters

string sql = "INSERT INTO PurchaseRequest (ProjectGroupNumber, OrderType, DateRequested) ";
sql += " VALUES (@ProjectGroupNumber, @OrderType, @DateRequested)";

SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add(new SqlParameter("@ProjectGroupNumber", _purchaseRequest.GeneralInformation.ProjectGroupNumber));
cmd.Parameters.Add(new SqlParameter("@OrderType", _purchaseRequest.GeneralInformation.OrderType));
cmd.Parameters.Add(new SqlParameter("@DateRequested", _purchaseRequest.GeneralInformation.DateRequested.Value));
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: