Q:

Insert DateTime into SQL Server using C#

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();
            }
        }
}

A:

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.

A:

Instead of using String.Format to put together a giant string, use a parameterized query.
cmd.CommandText = 
    "Insert Into Inventory (CarID, Make, Color, PetName, DateOne) Values" +
          "(@carId, @make, @color, @petName, @dateOne)";

cmd.Parameters.AddWithValue("@carId", car.CarID);
cmd.Parameters.AddWithValue("@make", car.Make);
cmd.Parameters.AddWithValue("@color", car.Color);
cmd.Parameters.AddWithValue("@petName", car.PetName);
cmd.Parameters.AddWithValue("@dateOne", car.DateOne);

In addition to obviating the need for awkard string concatenation, this will also guard against Sql Injection attacks.  With your old way, just imagine what would happen if someone typed in '); drop table cars; -- for your new car's make—or if someone just wanted to have their car's pet name be the O'LearyMobile

