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

Question

I would like a help please. When I want insert datetimepicker value into my table but he doesn't can insert and he show me a message

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

You can help me please !

    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {

    }

    private void add_Click(object sender, EventArgs e)
    {
        cmd.CommandText = "Insert into clients values (" + cintxt.Text + ", '" + nomtxt.Text + "', '" + prntxt.Text + "', '" + datenaiss.Value + "', '" + addtxt.Text + "', '" + teltxt.Text + "')";
        cnx.Open();
        cmd.ExecuteNonQuery();
        cnx.Close();
    }

It is clearly one of your column type is datetime and you try to insert it character (since your all values is .Text, that's why probably all are strings). And please use paramterized queries. This kind of string concatenations are open for SQL Injection attacks. — Soner Gönül
– Soner Gönül, Commented Jun 28, 2014 at 12:47
Use parameterized query passing parameters with the correct datatype — Steve
– Steve, Commented Jun 28, 2014 at 12:47
date type in sql-server is date and the same message show me when i change type of date to datetime. — user3002886
– user3002886, Commented Jun 28, 2014 at 12:49
The problem isn't the data type in Sql Server; the problem is the data type in your code. — Dan Puzey
– Dan Puzey, Commented Jun 28, 2014 at 13:09

Steve · Accepted Answer · 2014-06-28 13:24:33Z

5

Your code that tries to insert the record should be changed to use a parameterized approach.

This could be an example

private void add_Click(object sender, EventArgs e)
{
    string commandText = @"Insert into clients values 
                           (@id, @name, @prn, @datan, @addr, @tel)";

    using(SqlConnection cnx = new SqlConnection(connectionString))
    using(SqlCommand cmd = new SqlCommand(commandText, cnx)
    {
        cmd.Parameters.AddWithValue("@id", Convert.ToInt32(cintxt.Text));
        cmd.Parameters.AddWithValue("@name", nomtxt.Text);
        cmd.Parameters.AddWithValue("@prn", prntxt.Text);
        cmd.Parameters.AddWithValue("@datan", datenaiss.Value);
        cmd.Parameters.AddWithValue("@addr", addtxt.Text);
        cmd.Parameters.AddWithValue("@tel", teltxt.Text );
        cnx.Open();
        cmd.ExecuteNonQuery();
    }
}

In this code I have changed something. First, the connection and the command are no more global variables but just local and are enclosed in a using statement that ensure a proper closing and disposing also if, for any reason, the code throws an exception.

Second the command text is no more a concatenation of strings, but it is only a single string with parameters placeholders. Concatenating string to build command texts is really a bad practice. Sql Injection hackers wait for commands built in this way to hit your database and, as you have already seen, more often than not, the underlying datatable doesn't understand a string that represent a date to be a valid date.

Finally the command parameters collection is filled with a parameter for every field expected by the command text placeholders. Notice that in this way you build parameters that are of the datatype of the value passed not simply strings that are not expected by the datatable fields. Of course in your actual situation it is possible that some of these parameters should be changed to match exactly your datatable field (for example I don't know id the first field is an integer or not)

EDIT As requested by comments below I add also some considerations on the method AddWithValue.

While it is convenient and expressive it could be a problem if your program call this code with frequency or if your database is under heavy use. The preferred method to use is

 cmd.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = nomtxt.Text;
 .....

See MSDN SqlParameterCollection.Add
and more info about the difference between Add and AddWithValue are explained here
How Data Access Code Affects Database Performance

edited Jun 28, 2014 at 13:24

answered Jun 28, 2014 at 12:51

Steve

217k22 gold badges242 silver badges296 bronze badges

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

3 Comments

Dan Guzman Over a year ago

One minor suggestion for the parameterized query to use the Add method instead of the AddWithValue. Although it's not as convenient, it will avoid cache bloat in SQL Server since each string value of different length will result in a different cache entry. For example: "cmd.Parameters.Add("@id", SqlDbType.Int).Value = Int32.Parse(cintxt.Text);"

Steve Over a year ago

Yes of course, (and giving an exact size for string parameters is a big performance gain) but I wish to keep it simple for this.

Alexander Bell Over a year ago

Keep in mind that DateTimePicker returns a nullable type of DateTime?, thus use the "??" operator to ensure smooth conversion to param. Rgds,

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related