Conversion failed when converting datetime from character string

Question

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!

AdaTheDev · Accepted Answer · 2011-01-04 11:20:06Z

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.

David · Accepted Answer · 2011-01-04 11:22:12Z

1

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.

answered Jan 4, 2011 at 11:22

David

221k42 gold badges245 silver badges337 bronze badges

Collectives™ on Stack Overflow

Conversion failed when converting datetime from character string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related