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

DateOne is already DateTime and you should be using paramerters instead of string format — V4Vendetta
– V4Vendetta, Commented Dec 8, 2011 at 6:09
You must use DateTime.Now in C# to insert date and time to DATETIME in database. — Yousha Aleayoub
– Yousha Aleayoub, Commented Dec 25, 2019 at 21:35

Yuriy Faktorovich · Accepted Answer · 2011-12-08 06:09:38Z

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 8, 2011 at 6:09

Yuriy Faktorovich

68.9k14 gold badges107 silver badges150 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marc_s Over a year ago

+1 for using parameters over concatenating together SQL commands!

Adam Rackis · Accepted Answer · 2011-12-08 06:14:27Z

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!

KV Prajapati · Accepted Answer · 2011-12-08 06:13:47Z

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!

ROBIN · Accepted Answer · 2011-12-08 06:47:33Z

0

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

answered Dec 8, 2011 at 6:47

ROBIN

11 silver badge4 bronze badges

Comments

user4027662 · Accepted Answer · 2014-09-10 16:31:02Z

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, 2014 at 16:31

user3962541

answered Sep 10, 2014 at 15:59

user4027662

1

1 Comment

cpoDesign Over a year ago

Never use this code in production code !! this is a security issue !! owasp.org/www-community/attacks/SQL_Injection (this needs to be said because someone can try to use this code)

Collectives™ on Stack Overflow

Insert DateTime into SQL Server using C# [duplicate]

5 Answers 5

1 Comment

1 Comment

1 Comment

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

1 Comment

1 Comment

Comments

1 Comment

Linked

Related