Insert DateTime into SQL Server using C# [duplicate]

Question

I'm new with C# ADO.NET and SQL and have a problem I just can't figure. I'm trying to insert a DateTime into SQL Server using C#. I get the message

"Conversion failed when converting date/and or time from character string"

when the program hits the cmd.ExecuteNonQuery(); line. Any help on this really appreciated

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using AutoLotConnectedLayer;
using System.Configuration;
using System.Data;

namespace AutoLotCUIClient
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine("***** The AutoLot Console UI *****\n");

            // Get connection string from App.config.
            string cnStr =
              ConfigurationManager.ConnectionStrings["AutoLotSqlProvider"].ConnectionString;
            //bool userDone = false;
            //string userCommand = "";

            // Create our InventoryDAL object.
            InventoryDAL invDAL = new InventoryDAL();
            invDAL.OpenConnection(cnStr);

            InsertNewCar(invDAL);

 #region Insert car
        private static void InsertNewCar(InventoryDAL invDAL)
        {
            // First get the user data.
            int newCarID;
            string newCarColor, newCarMake, newCarPetName;
            DateTime newDateOne;


            Console.Write("Enter Car ID: ");
            newCarID = int.Parse(Console.ReadLine());
            Console.Write("Enter Car Color: ");
            newCarColor = Console.ReadLine();
            Console.Write("Enter Car Make: ");
            newCarMake = Console.ReadLine();
            Console.Write("Enter Pet Name: ");
            newCarPetName = Console.ReadLine();
            Console.Write("Enter Date: ");

            newDateOne = DateTime.Parse(Console.ReadLine());

            // Now pass to data access library.
            // invDAL.InsertAuto(newCarID, newCarColor, newCarMake, newCarPetName);
            NewCar c = new NewCar
            {
                CarID = newCarID,
                Color = newCarColor,
                Make = newCarMake,
                PetName = newCarPetName,
                DateOne = newDateOne
            };
            invDAL.InsertAuto(c);
        }
        #endregion

DLL Being Used

using System;
using System.Collections.Generic;
using System.Text;

// We will make use of the SQL server
// provider; however, it would also be
// permissible to make use of the ADO.NET
// factory pattern for greater flexibility.
using System.Data;
using System.Data.SqlClient;

namespace AutoLotConnectedLayer
{
    public class NewCar
    {
        public int CarID { get; set; }
        public string Color { get; set; }
        public string Make { get; set; }
        public string PetName { get; set; }
        public DateTime DateOne { get; set; }
    }

    public class InventoryDAL
    {
        // This member will be used by all methods.
        private SqlConnection sqlCn = null; 

        #region Open / Close methods
        public void OpenConnection(string connectionString)
        {
            sqlCn = new SqlConnection();
            sqlCn.ConnectionString = connectionString;
            sqlCn.Open();
        }

        public void CloseConnection()


        {
            sqlCn.Close();
        }
        #endregion

        #region Insert method (no param-query)
        public void InsertAuto(NewCar car)
        {
            // Format and execute SQL statement.
            string sql = string.Format("Insert Into Inventory" +
              "(CarID, Make, Color, PetName, DateOne) Values" +
              "('{0}', '{1}', '{2}', '{3}', '{4}')", car.CarID, car.Make, car.Color, car.PetName, Convert.ToDateTime(car.DateOne) );

            // Execute using our connection.
            using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
            {

               cmd.ExecuteNonQuery();
            }
        }
}

Answer 1

Don't use dynamic SQL, instead use parameters.

string sql = string.Format("Insert Into Inventory" +
          "(CarID, Make, Color, PetName, DateOne) Values" +
          "(@CarID,...

cmd.Parameters.AddWithValue("@CarID", car.CarID);
//...

This will guard against SQL Injection and allow for better SQL optimization.