1

No matter what I try, I can't seem to add entries to my Access DB. I am using Access 2016. I believe my type mismatch problem is with DateTimes, but none of the solutions I have seen have worked.

using (OleDbCommand cmd = new OleDbCommand(query, conn))
            {
                cmd.CommandType = CommandType.Text;
                foreach (KeyValuePair<string, string> k in queryParams)
                {
                    if (k.Key.Equals("DOB") || k.Key.Equals("dateJoined"))
                    {
                        MessageBox.Show("This is: " + k.Key + " " + k.Value);
                        string temp = "#" + k.Value + "#";
                        MessageBox.Show(temp);
                        cmd.Parameters.AddWithValue(k.Key, temp);
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(k.Key, k.Value);
                    }
                }
                int t = cmd.ExecuteNonQuery();
            }

I have tried just adding them like any other parameter. I have tried adding them as DateTime's. I have tried enclosing them in #. I have tried enclosing them in single quotes. I have tried using DateTime.toaDate and adding them as doubles. I have checked the formats, and both the DB and the DateTimePicker use mm/dd/yyyy. All other fields are short texts, and I know those are working because I can insert short texts into other tables fine.

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Ryan_L
  • 182
  • 1
  • 7
  • 2
    `#` is only used if you input a string *literal* in the SQL, not inside a parameter. Can you show the code you tried when you tried to insert them as `DateTime` values? Because right now you have an invalid string in there. – Lasse V. Karlsen Nov 19 '17 at 22:30
  • 1
    You are not passing dates, but strings – Ňɏssa Pøngjǣrdenlarp Nov 19 '17 at 22:31
  • I can't post the whole thing because it's too long. What I think is the most relevant section is here: { DateTime dt = Convert.ToDateTime(k.Value); cmd.Parameters.AddWithValue(k.Key, dt); } – Ryan_L Nov 19 '17 at 22:34
  • 1
    Typical error caused by the wrong approach in writing database code. If you really want to use a _do_it_all_for_me_ approach then use an ORM, do not try to write a method that receives a bunch of values and updates your database in every possible scenario. You will never do it right – Steve Nov 19 '17 at 22:35
  • Also you can find something useful for you in this question https://stackoverflow.com/questions/16217464/trying-to-insert-datetime-now-into-date-time-field-gives-data-type-mismatch-er/16218074 – Steve Nov 19 '17 at 22:44
  • I originally did try to use an ORM, but I kept getting errors about the Ace.OleDB provider not being registered on the local machine, which I was unable to fix. So started work on my project without one, and until now I've done fine without it. I can add to and select from other tables fine. It's only the Members table, which has two date fields, that I'm stumped on. – Ryan_L Nov 20 '17 at 00:53

0 Answers0