Insert DateTime into SQL Server using C#

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

`DateOne` is already DateTime and you should be using [`paramerters`](http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx) instead of string format — V4Vendetta, Dec 08 '11 at 06:09
You must use `DateTime.Now` in C# to insert date and time to `DATETIME` in database. — Yousha Aleayoub, Dec 25 '19 at 21:35

score 6 · Answer 1 · answered Dec 08 '11 at 06:09

6

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.

answered Dec 08 '11 at 06:09

Yuriy Faktorovich

67,283
14
105
142

1

+1 for using parameters over concatenating together SQL commands! – marc_s Dec 08 '11 at 06:39

Adam Rackis · Answer 2 · 2011-12-08T06:14:27.520

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

+1 for using parameters over concatenating together SQL commands! — marc_s, Dec 08 '11 at 06:46

score 2 · Answer 3 · answered Dec 08 '11 at 06:13

Never use hard-coded SQL statement. Use Stored-Procedure or Prepare (Parameter) query.

Try this,

 string sql = "Insert Into Inventory (CarID, Make, Color, PetName, DateOne)
                 Vaules (@CarID, @Make, @Color, @PetName, @DateOne)";

  using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
  {
     cmd.Parameters.Add("@CarID",SqlDbType.Int).Value=car.CarID;
     cmd.Parameters.Add("@Make",SqlDbType.VarChar,20).Value=car.Make;
     cmd.Parameters.Add("@Color",SqlDbType.VarChar,20).Value=car.Color;
     cmd.Parameters.Add("@PetName",SqlDbType.VarChar,20).Value=car.PetName;
     cmd.Parameters.Add("@DateOne",SqlDbType.DateTime).Value=car.DateOne;
     cmd.ExecuteNonQuery();
   }

+1 for using parameters over concatenating together SQL commands! — marc_s, Dec 08 '11 at 06:46

score 0 · Answer 4 · edited Sep 10 '14 at 16:31

0

you may use .Value.ToShortDateString()

for example... you will add a date on sql with a datatype of datetime, in my sample table below

Create table dateTable
(
mydate datetime
)
connection.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO dateTable VALUES(" +dateTimePicker1.Value.ToShortDateString()+", con);

cmd.ExecuteNonQuery();
connection.Close();

edited Sep 10 '14 at 16:31

answered Sep 10 '14 at 15:59

user4027662

1

Never use this code in production code !! this is a security issue !! https://owasp.org/www-community/attacks/SQL_Injection (this needs to be said because someone can try to use this code) – cpoDesign Jul 07 '20 at 21:44

score 0 · Answer 5 · answered Dec 08 '11 at 06:47

0

You must enter date in MM/DD/YYYY format when the console is asking for date

answered Dec 08 '11 at 06:47

ROBIN

1
1
4

Insert DateTime into SQL Server using C#

5 Answers5