Error when saving datetime to a SQL Server database

Question

I'm doing a bit of C# Winforms coding in my spare time, just getting to grips with everything. I have a SQL script which creates a local db on vs2012 as follows:

-- Creating table 'Users'--
CREATE TABLE [dbo].[Users]
(
    [UserID] int IDENTITY(1,1) NOT NULL,
    [Surname] nvarchar(30)  NOT NULL,
    [Forename] nvarchar(30)  NOT NULL,
    [Company] nvarchar (30) NOT NULL,
    [SecurityLevel] int NOT NULL,
    [IssueDate] DateTime  NOT NULL,
    [ExpiryDate] DateTime  NOT NULL,
    [CardID] int NOT NULL,
);
GO

Now I want to save details to that table, so I created a method:

  private void btnSaveDetails_Click(object sender, EventArgs e)
  {
        SqlConnection sc = new SqlConnection();
        SqlCommand com = new SqlCommand();
        sc.ConnectionString = (Properties.Settings.Default.BioEngineering);
        sc.Open();
        com.Connection = sc;
        com.CommandText = ("INSERT INTO Users (Forename, Surname, Company, SecurityLevel, IssueDate, ExpiryDate, CardID) VALUES ('" + this.txtFirstName.Text + "','" + this.txtLastName.Text + "','" + this.txtCompany.Text + "','" + this.cboSecurityLevel.Text + "','" + this.dtpIssueDate.Value + "','" + this.dtpExpiryDate.Value + "','" + this.cboCardID.Text + "');");

        com.ExecuteNonQuery();
        sc.Close();
    }

When I run the code I get an error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I know it has something to do with the datetime format of either the SQL or C# equivalent but I don't know how to format the datetime in order to comply to the error. Any ideas? I tried formatting it withing the Command Text line but it didn't seem to resolve the issue.

You should use Parameterized Queries. Not just that, but part of your issue is .Value, SQL expects the DateTime in format: "yyyy-MM-dd HH:mm:ss.fffffff" depending on column type. Also, DateTime2 is more appropriate for the SQL column type. — Der Kommissar, Apr 20 '15 at 20:29
Not should, must. Your problem looks less to be a code problem and more to be a data problem. What are the values you are putting in? — crthompson, Apr 20 '15 at 20:31
@EBrown You are right about the parameterized queries, but you could have stopped there. The string coercion and expected format is irrelevant once OP moves to proper parameter usage. — DonBoitnott, Apr 20 '15 at 20:39
thanks for letting me know about parameterized Queries @EBrown. As i say im getting to grips with things so sorry for the novice mistake of allowing sql attacks. — Matchbox2093, Apr 20 '15 at 20:41
DateTime Format is important while casting, example : today french datetime compared to today us date time is not the same string but the same date. — MacKentoch, Apr 20 '15 at 20:42
@MacKentoch That's a display issue, not related to storing the data. A date is a date, as you say. — DonBoitnott, Apr 20 '15 at 20:43
goldeneye Read this, please: https://msdn.microsoft.com/en-us/library/zdtaw1bw(v=vs.110).aspx Your utilization of DateTimePicker.Value casts it to the default, localized, SQL-unrepresentable string. As @DonBoitnott and I have said, switching to Parameterized Queries will not only fix this problem, but remove a plethora of SQL injection attacks that anyone could execute on your code as it stands now. Also: https://msdn.microsoft.com/en-us/library/vstudio/bb738521%28v=vs.100%29.aspx — Der Kommissar, Apr 20 '15 at 20:46
@EBrown thank you very much for pointing me on the right path! — Matchbox2093, Apr 20 '15 at 20:47
possible duplicate of [The conversion of a varchar data type to a datetime data type resulted in an out-of-range value error](http://stackoverflow.com/questions/12393665/the-conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out) — Marnix van Valen, Apr 20 '15 at 21:27

Abdul Saleem · Answer 1 · 2015-04-21T01:12:42.640

Datepicker.Value is returned as

[Your_System_Short_Date_Format] + [Space] + [Your_System_Long_Time_Format]

I don't know why in this format but just found when checked with the Default Long Format while adding the DateTimePicker.

So you are passing a value depending upon you system [Regional/Calendar] settings where the SQL engine expects to be in a format like EBrown said

yyyy-MM-dd HH:mm:ss.fffffff

So if you surely want to Concatenate then you can use like

 command.CommandText = 
    "INSERT INTO EMPLOYEES(DateOfBirth) VALUES ('" +
     dtpDateOfBirth.Value.ToString("yyyy-MM-dd HH:mm:ss") + "')";

But i truly recommend you to keep a practice of using parameters from the beginning itself. Or else after making a huge application, you might need to walk back to change those concatenations to Parameters.

It is simple like

command.CommandText = "INSERT INTO EMPLOYEES(DateOfBirth) VALUES (@dob)";

command.Parameters.Add("@dob", SqlDbType.DateTime).Value = dtpDateOfBirth.Value;

     // Or Simply

command.Parameters.AddWithValue("@dob", dtpDateOfBirth.Value);

You can add more parameters with different Data Types using parameters. The SqlCommand class safely converts those to an SqlCommand especially from Injection Attacks.

pjobs · Answer 2 · 2015-04-21T00:55:23.900

-4

First of all, the way you did is very crude way of running sql statements that is prone to lots of errors, memory leaks, sql injection attacks, and security issues.

--You should use using statement to dispose connection & command objects, better error handling.

-- you should use parameterized queries or stored procs or ORMs like nhibernate or EF.

Anyway error in your code is as follows

convert date fields to this format .ToString("MM/dd/YYYY")

 private void btnSaveDetails_Click(object sender, EventArgs e)
    {
        SqlConnection sc = new SqlConnection();
        SqlCommand com = new SqlCommand();
        sc.ConnectionString = (Properties.Settings.Default.BioEngineering);
        sc.Open();
        com.Connection = sc;
        com.CommandText = ("INSERT INTO Users (Forename, Surname, Company, SecurityLevel, IssueDate, ExpiryDate, CardID) VALUES ('" + this.txtFirstName.Text + "','" + this.txtLastName.Text + "','" + this.txtCompany.Text + "','" + this.cboSecurityLevel.Text + "','" + this.dtpIssueDate.Value.ToString("MM/dd/YYYY") + "','" + this.dtpExpiryDate.Value.ToString("MM/dd/YYYY") + "','" + this.cboCardID.Text + "');");

        com.ExecuteNonQuery();
        sc.Close();
    }

edited Apr 21 '15 at 00:55

answered Apr 20 '15 at 20:40

pjobs

1,247
12
14

I know man, ORM, Stored procs , Parameterized quries, lots of options...one step a time for him, let him get a feel of it first :). you guys too serious man, want to teach him best practices in 101 class. – pjobs Apr 20 '15 at 20:41
wow, and you think learn to code is similar to going to doctor for illness – pjobs Apr 20 '15 at 20:45
I can find issue with every line of code in the question man, and we are not correcting every one of them. – pjobs Apr 20 '15 at 20:51
and using statement around command /connection objects, writing a separate service for all database calls,and may be inject it through dependency injection into the form etc.. – pjobs Apr 20 '15 at 20:54
Finding other flaws in code is common. A complete answer fleshes out those problems as well in clear concise ways. – crthompson Apr 20 '15 at 20:56

Error when saving datetime to a SQL Server database

2 Answers2