Click here to Skip to main content
13,000,017 members (47,827 online)
Rate this:
Please Sign up or sign in to vote.
See more:
UPDATE: I did a bit of a test to see if the Date of Birth was actually the problem. I changed everything into Text data type in my Access database and tried inserting the data both provided with Phoenyx's code and with the one I had when I had the question and still nothing. To my understanding, because the database has an autonumber ID as the first column, the query must be trying to add values starting at the ID. Any idea on how to dodge that and make it start from Name?

Hello, lately I have been working on a program and I am getting an error through the use of breakpoints saying "Data type mismatch in criteria exception". For my coding I use C# and Access, I have been trying to figure out the problem but to no luck. I will guide you through my code and show you where the issue comes up.

This is the part of the code that connects to the Access database to use the insert or read the data in the database
public Object ConnString
   get { return Properties.Settings.Default.GoGreeceConnectionString; } 
public OleDbConnection CreateConn()
   return new OleDbConnection(this.ConnString.ToString());

This is the part of the code that adds the data. I point out with capitals and bold where the error is shown. Just so it is known, Name, Surname, Phone, Mobile and Email are set as Text in the Access Database, Date of Birth is set as a Date/Time.
public void SQLInsertRecord(string Name, string Surname, string DateOfBirth, string Phone, string Mobile, string Email)
   //Creates the string "sqlStr". This string carries the SQL command that inserts the values Names, Surname,
   //Date of Birth, Phone, Mobile and Email into the datatable according to each ones column.
   string sqlStr = "INSERT INTO [Customers] ([Name], [Surname], [Date Of Birth], [Phone], [Mobile], [Email]) VALUES ('@Name','@Surname', '@Date Of Birth', '@Phone', '@Mobile', '@Email')";
   //This is a representation of the SQL statement named "sqlCmd" and sets it as null;
   OleDbCommand sqlCmd = null;
      //This is the parameter of the OleDbCommand "sqlCmd" that does the mapping to the Customer dataset column.
      //It creates a variable for each column and the values that will be inserted to them.
      var nameParam = new OleDbParameter("@Name", Name);
      var surnameParam = new OleDbParameter("@Surname", Surname);
      var dobParam = new OleDbParameter("@Date Of Birth", DateOfBirth);
      var phoneParam = new OleDbParameter("@Phone", Phone);
      var mobileParam = new OleDbParameter("@Mobile", Mobile);
      var emailParam = new OleDbParameter("@Email", Email);
      //This instructs "sqlCmd" to create a new representation of the SQL statement to execute against the data source,
      //Using the methods provided in the brackets.
      sqlCmd = new OleDbCommand(sqlStr, ConnManager.Instance.CreateConn());
      //Adds each value to its accordinated column through the mapping created by its set parameter.
      //Using "sqlCmd", it opens a connection to the dataset.
      //Using "sqlCmd", it executes an SQL statement against the Connection and returns the number of rows affected by it.
      catch (Exception ex)
         //If try does not work, this line of code throws an error message of the exception.
         throw new Exception("Error: Could not insert data", ex);
         //Checks if "sqlCmd" and the "sqlCmd.Connection" are null.
         if (((sqlCmd != null)) && ((sqlCmd.Connection != null)))
            //Checks the state of the "sqlCmd" connection if it is closed.
            if ((!(sqlCmd.Connection.State == System.Data.ConnectionState.Closed)))
               //Closes the sqlCmd connection.
            //Disposes the sqlCmd connection.

This is the line of code that executes the tests and which through I find the issue.
private void executeTestbutton_Click_1(object sender, EventArgs e)
private void executeTests()
private void CustomerTableInsert()
      CustomerCRUDmanager.Instance.SQLInsertRecord("Test Name1", "Test Surname1", "03/11/1993", "2310", "234567", "");
      this.testTextbox.AppendText("Successfully added into the database");
   catch (Exception ex)
      this.testTextbox.AppendText("Error: Could not add row in the database");
      MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);

Thank you for any help you provide :)
Posted 3-Apr-13 5:37am
Updated 3-Apr-13 6:55am
ThePhantomUpvoter 3-Apr-13 11:49am
Access uses # for datetime values. Try #'@Date Of Birth'# and see what happens.
Silenous 3-Apr-13 11:54am
Now I am getting this "Syntax error in date in query expression '#'@Date Of Birth''." using the ## you told me to use Phantom. Any other way to point out that that is a date using C# and Access?
ThePhantomUpvoter 3-Apr-13 12:11pm
Sorry about that. I am a bit rusty on Access. I know that you have to use # but not real sure about the exact syntax. Try the # inside the quotes. You might also try using AddWithValue instead of creating the parameters like you are.
ThePhantomUpvoter 3-Apr-13 12:17pm
Try #@Date Of Birth#, without the quotes.
Silenous 3-Apr-13 12:24pm
Just did, syntax error, the correct way is '#Date Of Birth#' after all :)
ThePhantomUpvoter 3-Apr-13 12:31pm
Well there you go. Great!
Silenous 3-Apr-13 12:33pm
But I still get the data type part. I used Phoenyx's part of the code and from what I noticed using the breakpoints, the code picks up what I give it to enter in the database but everything is set as string. Now how will I set the DOB as a date/time, like it is in Access?
ThePhantomUpvoter 3-Apr-13 12:40pm
Whatever you put in quotes is going to be set as a string.
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Most likely, your issue is that you are setting one of your parameters with the wrong data type.
I would check that each variable is the correct datatype for the parameter.


var dobParam = new OleDbParameter("@Date Of Birth", OleDbType.Date); 
dobParam.Value = DateOfBirth;
Silenous 3-Apr-13 12:11pm
To Pheonyx: I tried the piece of code you gave me but it still shows up with the same error, again on the sqlCmd.ExecuteNonQuery();
Pheonyx 3-Apr-13 12:44pm
Try changing all occurrences of @Date Of Birth to read @DateOfBirth, it could be that the spaces are causing an issue? This is just speculation though.

Also, when the error is raised, can you view the stack trace to see if it identifies which value is erroring?
Pheonyx 3-Apr-13 12:53pm
try changing:

string sqlStr = " INSERT INTO [Customers] ([Name], [Surname], [Date Of Birth], [Phone], [Mobile], [Email]) VALUES ('@Name','@Surname', '@Date Of Birth', '@Phone', '@Mobile', '@Email')" ;


string sqlStr = " INSERT INTO [Customers] ([Name], [Surname], [Date Of Birth], [Phone], [Mobile], [Email]) VALUES ('@Name','@Surname', @DateOfBirth, '@Phone', '@Mobile', '@Email')" ;
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

JET database engine for MS Access expects dates between two "#" (hashes), for example, date bith should be passed as:
CustomerCRUDmanager.Instance.SQLInsertRecord("Test Name1", "Test Surname1", "#03/11/1993#", "2310", "234567", "");

I'm not sure for next two numbers (2310 and 234567) passed as string.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web01 | 2.8.170624.1 | Last Updated 3 Apr 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100