.net c # sql string to date error error

Question

I am having a convserion error for a database using SQL in visual studio.

The database used is a regular sql server database. This is given to me as my task.

This is my request method - this is my web service

[WebMethod] public bool search(string ddate, string dairport, string aairport, string seat) { int seat2 = Convert.ToInt32(seat); DateTime date = Convert.ToDateTime(ddate); String query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = '24/09/2011'"; using (SqlConnection connect = new SqlConnection(conn)) { SqlCommand cmd = new SqlCommand(query1, connect); connect.Open(); SqlDataReader result = cmd.ExecuteReader(); try { if (result.Read()) { return true; } finally { result.Close(); connect.Close(); connect.Dispose(); } return false; } } 

No problem with regular queries like:

 "SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + dairport + "'"; 

Error:

 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.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.HasMoreRows() at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) at System.Data.SqlClient.SqlDataReader.Read() at AirportService.AirportServices.search(String ddate, String dairport, String aairport, String seat) in C:\Users\XXXXX\Documents\Visual Studio 2010\Projects\WebService2\AirportService\AirportServices.asmx.cs:line 47 

Answer 1

You must process the date parsing logic (in your accepted format) inside C # and pass this value as a parameter, i.e.

 String query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = @departureDate" 

and add SqlParameter with the desired DateTime value; that way ... no problem. Lack of analysis in the database and lack of risk of injection. And reuse of the request plan. Winning in a circle.

For instance:

 DateTime when = DateTime.Parse(ddate); // better to use ParseExact and formally state the format you are using const string query1 = "SELECT * FROM Flight_Schedule S WHERE S.departure_date = @departureDate"; using (SqlConnection connect = new SqlConnection(conn)) { using (SqlCommand cmd = new SqlCommand(query1, connect)) { cmd.Parameters.AddWithValue("departureDate", when); connect.Open(); using (SqlDataReader result = cmd.ExecuteReader()) { ... etc } } } 

Answer 2

Mark Gravell is absolutely right. But try the following:

 "SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + dairport.ToString("yyyyMMdd") + "'"; 

Answer 3

Your error says "Conversion error while converting date and / or time from character string."

Replace this line

 DateTime date = Convert.ToDateTime(ddate); 

For

 IFormatProvider theCultureInfo = new System.Globalization.CultureInfo("en-GB", true); DateTime theDateTime = DateTime.ParseExact(ddate, "mm-dd-yyyy", theCultureInfo); 

Try it...

Answer 4

As a rule, best practice using SQL dates, if you need to specify a date in your SQL query, always try to use the ISO format (yyyy-MM-dd). In most cases, this will prevent conversion errors from occurring.

In your code:

 "SELECT * FROM Flight_Schedule S WHERE S.origin_airport_code = '" + ddate.ToString("yyyy-MM-dd") + "'"; 

Kris