How to fix The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.'

Question

I try to insert my data into SQL Server but the query cannot run due to an error

System.Data.SqlClient.SqlException: 'The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

I'm using c# to code.

txtPlateNo.Text = vehicresult.results[0].plate.ToString();
lblDate.Text = DateTime.Now.ToShortDateString();
lblTime.Text = DateTime.Now.ToShortTimeString();

//string parkDate = lblDate.Text;
//DateTime newDate = Convert.ToString(parkDate.ToString("MMddyyyy"));
//string parkTime = lblTime.Text;
//DateTime newTime = Convert.ToDateTime(parkTime);

//label10.Text = newDate;
//string newDate2 = label10.Text.ToString();
//string newTime2 = newTime.ToString();

SqlConnection conn = new SqlConnection(new DBConnection().ConnectionString);
if (conn.State == System.Data.ConnectionState.Closed)
    conn.Open();
SqlDataReader dr;

SqlCommand cmd = new SqlCommand("SELECT * FROM pmsVehicleRecord WHERE clVehicPlateNo='" + txtPlateNo.Text + "'", conn);

dr = cmd.ExecuteReader();

if (dr.HasRows)
{
    while (dr.Read())
    {
        lblFullName.Text = dr["clFullName"].ToString();
        lblUsername.Text = dr["clUsername"].ToString();
        lblAddress.Text = dr["clAddress"].ToString();
        lblPaymentMethod.Text = dr["clPaymentMethod"].ToString();
    }

    dr.Close();
    SqlCommand cms = new SqlCommand("SELECT * FROM pmsParkingRecord WHERE parkVehicPlateNo ='" + txtPlateNo + "' AND parkDate='" + lblDate + "'", conn);
    dr = cms.ExecuteReader();

    if (dr.HasRows)
    {
        dr.Close();
        SqlCommand cmt = new SqlCommand("UPDATE pmsParkingRecord SET parkExitTime='" + DateTime.Now.ToShortTimeString() + "'");
        cmt.Connection = conn;
        if (conn.State == System.Data.ConnectionState.Closed)
            conn.Open();
        SqlDataReader ds = cmt.ExecuteReader();
    }
    else
    {
        dr.Close();
        SqlCommand cmn = new SqlCommand("INSERT INTO pmsParkingRecord (parkUsername, parkFullName, parkAddress, parkVehicPlateNo, parkPaymentMethod, parkDate, parkEntryTime) VALUES ('" + lblUsername.Text + "', '" + lblFullName.Text + "', '" + lblAddress.Text + "', '" + txtPlateNo.Text + "', '" + lblPaymentMethod.Text + "', '" + Convert.ToDateTime(DateTime.Now.ToShortDateString()) + "', '" + Convert.ToDateTime(DateTime.Now.ToShortTimeString()) + "')");
        cmn.Connection = conn;
        if (conn.State == System.Data.ConnectionState.Closed)
            conn.Open();
        SqlDataReader ds = cmn.ExecuteReader();
    }
}
else
{
    lblWarning.Text = "License Plate No is not registered!";
}
dr.Close();

The expected result suppose to insert the data into database and display the data on label.

The error shows on this line

SqlDataReader ds = cmn.ExecuteReader();

Please parametrise your queries. They are wide open to Injection at the moment. Fix the parameter problem, likely fix the error your having. — Larnu

under under · Accepted Answer · 2019-05-12T20:18:07

Don't pass parameters in query text. Instead use parameters from SQLParameterCollection.

Here is an example how to do it

private static void UpdateDemographics(Int32 customerID,
    string demoXml, string connectionString)
{
    // Update the demographics for a store, which is stored 
    // in an xml column. 
    string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
        + "WHERE CustomerID = @ID;";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        SqlCommand command = new SqlCommand(commandText, connection);
        command.Parameters.Add("@ID", SqlDbType.Int);
        command.Parameters["@ID"].Value = customerID;

        // Use AddWithValue to assign Demographics.
        // SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml);

        try
        {
            connection.Open();
            Int32 rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine("RowsAffected: {0}", rowsAffected);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
    }
}

From SQLCommand.Parameters

Advantages are strong types as well as SQL injection prevention.

How to fix The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. The statement has been terminated.'

1 Answers