Why isn't OleDbCommand and OleDbType.Date not working, and no error?

Sorry for the rather lame question... But here is my dilemma...

I'm trying to reduce repetitive code as much as possible here, since I have several similar queries, that just vary in parameter counts.

Using AccessDB ( I don't have a choice here ). The below code is what works for me, however, when I have that one date that needs to be entered. It doesn't enter the date, nor does it give me any kind of error.

public bool DoUpdate(string query, string[] data) {

        using (OleDbConnection conn = new OleDbConnection(this.ConnStr))
        {
            try
            {
                OleDbCommand cmd = new OleDbCommand(query, conn);
                DateTime tmp;
                for (int i = 0; i < data.Length; i++)
                {
                    if (DateTime.TryParse(data[i], out tmp)) // Checks if valid date...
                        cmd.Parameters.Add(new OleDbParameter("?", OleDbType.Date) { Value = tmp.ToString() });
                    else
                        cmd.Parameters.AddWithValue("?", data[i]);
                }
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.ErrorCode.ToString() + "\n\n" + ex.Message + "\n\n" + query);
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    return false;
                }
            }
        }
        return true;
    }

In use:

// [ time string is DateTime.Now.ToString("M/dd/yyyy h:mm:ss tt") ]
if ( DB.DoUpdate("UPDATE Loads SET Customer='?', FinishTime=?, Carrier='?', Reference='?', Tags='?', Status='Received' WHERE LoadID = ?",
         new string[] { Item["Customer"], Item["FinishTime"], Item["Carrier"], Item["Reference"], Item["TagIDs"], Item["LoadID"] }) ) { ... }

Date Column is "FinishTime". Mind you, I've tried several variants in the SQL. Such as: [FinishTime]=? / [Finishtime]=#?# / [FinishTime]='?' / FinishTime='?' ... Also, what made me put in the DateTime.TryParse to begin with was it not working. ( Though I had initially received an error... ) Even if I cut out the Parse code, no error, and the SQL is passed like normal, only the date isn't inputted either.

Note, I also generate an excel spreadsheet with the Date and time on there with no issues, so the time string is being passed just fine.

Any ideas? Comments?

Jon Skeet
people
quotationmark

You've got quotes round most of your parameters in the SQL, which means the other parameters won't be where you expect them to be. Your SQL should be:

UPDATE Loads
SET Customer=?, FinishTime=?, Carrier=?, Reference=?, Tags=?, Status='Received'
WHERE LoadID = ?

Additionally, I would strongly advise against passing all the data around as strings. Don't convert the DateTime values into strings at all if you can help it, and certainly not in the SQL statement. You've currently got:

cmd.Parameters.Add(new OleDbParameter("?", OleDbType.Date) { Value = tmp.ToString() });

This would be better as:

cmd.Parameters.Add("?", OleDbType.Date).Value = tmp;

... but it would be better if you didn't even need to parse the string to get tmp to start with. (It's not really clear where the date is coming from, and whether it has to be as a string there.)

The more date/string conversions you have, the more room there is for the format and culture to mess things up - you only need one step to format the date in a way that the next step parsing it doesn't expect for the whole thing to fail.

people

See more on this question at Stackoverflow