CODEWITHSUNDEEP
c#.netasp.net
user904406
user904406

Reputation: 57

.net c# sql string to date conversion error

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

The database I am using is a normal sql server database. It is given to me as my assignment.

This is my query method is my webservice

 [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;
        }

    }

There is no issue with normal 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

Upvotes: 4

Views: 1986

Answers (4)

Boomer
Boomer

Reputation: 1478

Marc Gravell is absolutely right. But try this:

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

Upvotes: 1

Drakkonite
Drakkonite

Reputation: 507

As a rule of best practice using SQL date, if you have to specify date in your SQL query, always try to use ISO format (yyyy-MM-dd). In most cases it will prevent any conversion errors to appear.

In your code:

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

Kris

Upvotes: -1

Marc Gravell
Marc Gravell

Reputation: 1064114

You should handle the date-time parsing logic (in your accepted format) inside the C#, and pass that down as a parameter, i.e. 

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

and add a SqlParameter with the DateTime value you want; that way... no problems. No parsing at the DB, and no injection risk. And query-plan re-use too. Wins all round.

For example:

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
        }
    }
}

Upvotes: 7

Siva Charan
Siva Charan

Reputation: 18064

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

Replace this line 

DateTime date = Convert.ToDateTime(ddate);

To

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

Try this out...

Upvotes: 0

Related Questions