JoshF91 JoshF91 - 4 years ago 361
SQL Question

Updating DateTime in access through C#. Data mismatch

I have a date and time loaded into a textbox for editing, but I need to store it as a datetime in my access database not a string and cannot remember or find the syntax to parse it in my SQL parameters... here is my code anyway...

string strSql = "UPDATE OCR SET OCR = @OCR, [OCR Title] = @OCRTitle, DeadlineDate = @DeadlineDate;";

using (OleDbConnection newConn = new OleDbConnection(strProvider))
{
using (OleDbCommand dbCmd = new OleDbCommand(strSql, newConn))
{
dbCmd.CommandType = CommandType.Text;
dbCmd.Parameters.AddWithValue("@OCRTitle", textBox6.Text);
dbCmd.Parameters.AddWithValue("@OCR", textBox5.Text);
dbCmd.Parameters.AddWithValue("@DeadlineDate", textBox7.Text);

newConn.Open();
dbCmd.ExecuteNonQuery();
}
}

Answer Source

You're specifying a string as the deadline date value. You should specify a DateTime instead.

You can use DateTime.Parse (or DateTime.ParseExact, or DateTime.TryParseExact) to parse the text representation if you really have to - but it would be better to use a date-based control to start with, rather than having a text representation at all.

(It's not clear what sort of application this is - WinForms, ASP.NET etc - but most GUIs have some sort of date picker these days.)

EDIT: Additionally, you need to change the order in which you add the parameters to the command such that it matches the order in which the parameters are used in the SQL statement. These are effectively positional parameters - the names are ignored. It would probably be clearer to use ? than named parameters in the SQL.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download