C# date time not match sql date

Question

I try to develop a windows form program and I need to insert data. I need to insert today's date and time but when I try I get an error. I use access database. Where is my fault?

cmd = new OleDbCommand();
con.Open();
cmd.Connection = con;
cmd.CommandText = "insert into kayit (evraktarihi,evrakgelistarihi,gelissaati,geldigiyer,konusu,faturano,htfno,talepno,satınpersonel,teslimkisi,yapılanislem,acıklama,islemtarih) " +
    "values ('" + dateTimePicker1.Value.ToShortDateString() + "','" + DateTime.Today.ToShortDateString() + "','" + DateTime.Now.ToLongTimeString() + "','" + textBox2.Text.ToString() + "','" + 
    textBox3.Text.ToString() + "','" + textBox4.Text.ToString() + "','" + textBox5.Text.ToString() + "','" + textBox6.Text.ToString() + "','" + textBox7.Text.ToString() + "','" + kullanici + "','" + 
     "','" + textBox8.Text.ToString() + "','" + textBox1.Text.ToString() + "','" + dateTimePicker3.Value.ToShortDateString() + "')";
cmd.ExecuteNonQuery();
con.Close();

I tried to change datetime.today to datetimepicker1.value but still doesn't work.

I can't find my error. I think this is a type unmatching error or sql syntax.

Answer 1

When requesting for help about an error, you should always include the error message and other relevant details. Your error is probably inherent to wrong number of parameters or the format of some of them.

As already stated in my previous comment, the use of ToString after Text properties is useless: they are already String values. More important, the use of ToShortDateString and similar methods are subject to globalization settings and what you get from such calls is probably incompatible with the format that the backend is expecting (different separators, different order of parts, etc.. ).

Looking in detail at your code, I found that the values clause contains an empty string before textBox8.Text.ToString(): it is split between line breaks, so hard to spot. It is

kullanici + "','" + "','" + textBox8.Text.ToString()

By the way, I here rewrite your code in a more readable (thus longer) form. This provides several advatanges:

• The SQL command is shorter and more maintanable and readable.
• You don't need to convert to string every value and don't need to concatenate them.
• It does not suffer from SQL injection vulnerabilities.
• I included code to dispose resources properly (by means of try/finally and using).

However note that:

• The names of the parameters are totally arbitrary (they can also be @p1, @p2, etc..).
• The OleDbType types I specified are my best guess about your needs, maybe you have to change them.
• The actual values set for date and time parameters are also my best guess about what your intentions are.
• I would not mix SQL logic and UI access (but that's a totally different matter).

see OleDbParameter Class for explanation of query parameters.

con.Open();
try
{
    using var cmd = con.CreateCommand();

    cmd.Parameters.Add("@evraktarihi", OleDbType.DBDate).Value = dateTimePicker1.Value;
    cmd.Parameters.Add("@evrakgelistarihi", OleDbType.DBTime).Value = DateTime.Today.TimeOfDay;
    cmd.Parameters.Add("@gelissaati", OleDbType.DBTime).Value = DateTime.Today.TimeOfDay;
    cmd.Parameters.Add("@geldigiyer", OleDbType.BSTR).Value = textBox2.Text;
    cmd.Parameters.Add("@konusu", OleDbType.BSTR).Value = textBox3.Text;
    cmd.Parameters.Add("@faturano", OleDbType.BSTR).Value = textBox4.Text;
    cmd.Parameters.Add("@htfno", OleDbType.BSTR).Value = textBox5.Text;
    cmd.Parameters.Add("@talepno", OleDbType.BSTR).Value = textBox6.Text;
    cmd.Parameters.Add("@satınpersonel", OleDbType.BSTR).Value = textBox7.Text;
    cmd.Parameters.Add("@teslimkisi", OleDbType.BSTR).Value = kullanici;
    cmd.Parameters.Add("@yapılanislem", OleDbType.BSTR).Value = textBox8.Text;
    cmd.Parameters.Add("@acıklama", OleDbType.BSTR).Value = textBox1.Text;
    cmd.Parameters.Add("@islemtarih", OleDbType.DBDate).Value = dateTimePicker3.Value;

    cmd.CommandText = "insert into kayit"
      + "(evraktarihi,evrakgelistarihi,gelissaati,geldigiyer,konusu,faturano,htfno,talepno,satınpersonel,teslimkisi,yapılanislem,acıklama,islemtarih)"
      + "values"
      + "(@evraktarihi,@evrakgelistarihi,@gelissaati,@geldigiyer,@konusu,@faturano,@htfno,@talepno,@satınpersonel,@teslimkisi,@yapılanislem,@acıklama,@islemtarih)";

    cmd.ExecuteNonQuery();
}
finally
{
    con.Close();
}