CODEWITHSUNDEEP
c#sql-server-2005
Moh
Moh

Reputation: 2087

Conversion failed when converting datetime from character string

I try reading data from SQL Server 2005 and filling it in a TableAdapter (also tried to user DataReader) but I keep getting this exception. The thing is that I get this error on some systems, that is I run my application peacfully on a system but on another system I get this exception.

The Code is:

public DataSetRef GetReportPumpControl(PumpInfo pump, DateTime start, DateTime end)
    {
        if (!OpenConnection())
            return null;

        m_Command.CommandText = "SELECT ref_dig_pumpcontrol, ref_energy, ref_datetime FROM [molisoftSchema].[Refresh] WHERE ref_pump_id = " + pump.ID + " AND ref_datetime BETWEEN '" + start + "' AND '" + end + "' ORDER BY ref_datetime ASC";

        SqlDataAdapter adapter = new SqlDataAdapter(m_Command);
        DataSetRef ds = new DataSetRef();
        adapter.Fill(ds, "RefreshPC");

        return ds;

        /*m_Reader = m_Command.ExecuteReader();

        LinkedList<PumpControlInfo> returnValue = new LinkedList<PumpControlInfo>();
        while (m_Reader.Read())
        {
            PumpControlInfo tempControl = new PumpControlInfo();
            tempControl.DateTime = (DateTime)m_Reader["ref_datetime"];
            tempControl.Energy = (double)m_Reader["ref_energy"];
            tempControl.PumpControl = (bool)m_Reader["ref_dig_pumpcontrol"];

            returnValue.AddLast(tempControl);
        }
        m_Reader.Close();
        return returnValue.ToArray<PumpControlInfo>();*/
    }

Please help me with this matter. Thanks in advance!

Upvotes: 0

Views: 1513

Answers (2)

David
David

Reputation: 218818

You should set your query values as parameters rather than just using string concatenation (and with it implicit conversion). Try:

m_Command.CommandText = "SELECT ref_dig_pumpcontrol, ref_energy, ref_datetime FROM [molisoftSchema].[Refresh] WHERE ref_pump_id = @id AND ref_datetime BETWEEN @start AND @end ORDER BY ref_datetime ASC";
m_Command.Parameters.AddWithValue("@id", pump.ID);
m_Command.Parameters.AddWithValue("@start", start);
m_Command.Parameters.AddWithValue("@end", end);

Aside from being better practice overall, this will use the conversion properties in place in parameters which are better designed for this sort of thing than implicit inline string conversion.

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147224

Kill 2 birds with 1 stone and parameterise your SQL (will guard you against SQL injection attacks):

m_Command.CommandText = "SELECT ref_dig_pumpcontrol, ref_energy, ref_datetime FROM [molisoftSchema].[Refresh] WHERE ref_pump_id = @pumpid AND ref_datetime BETWEEN @StartDate AND @EndDate ORDER BY ref_datetime ASC";

m_Command.Parameters.AddWithValue("@pumpid", pump.ID);
m_Command.Parameters.AddWithValue("@StartDate", start);
m_Command.Parameters.AddWithValue("@EndDate", end);

The problem was because the .NET start and end DateTime values are being serialised to a string to "hard code" into the SQL you were generating. However, this string representation could not be translated back into a valid datetime in SQL (you'd need to use a SAFE date format like yyyy-MM-ddTHH:mm:ss - so explicitly format those DateTimes before appending into the SQL statement). However, parameterisation is the far better solution which gets round these kinds of problems.

Upvotes: 6

Related Questions