CODEWITHSUNDEEP
c#sql-server
user3002886
user3002886

Reputation: 1

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

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

Upvotes: 0

Views: 236

Answers (1)

Steve
Steve

Reputation: 216293

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

Upvotes: 5

Related Questions