DATA TYPE MISMATCH IN CRITERIA EXCEPTION C# ACCESS

See more:

C#

MS-Access

exceptions

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;

   try
   {
      //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.
      sqlCmd.Parameters.Add(nameParam);
      sqlCmd.Parameters.Add(surnameParam);
      sqlCmd.Parameters.Add(dobParam);
      sqlCmd.Parameters.Add(phoneParam);
      sqlCmd.Parameters.Add(mobileParam);
      sqlCmd.Parameters.Add(emailParam);

      //Using "sqlCmd", it opens a connection to the dataset.
      sqlCmd.Connection.Open();
      //Using "sqlCmd", it executes an SQL statement against the Connection and returns the number of rows affected by it.
      //DATA TYPE MISMATCH IN CRITERIA EXCEPTION
      sqlCmd.ExecuteNonQuery();
      }
      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);
      }
      finally
      {
         //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.
               sqlCmd.Connection.Close();
            }
            //Disposes the sqlCmd connection.
            sqlCmd.Connection.Dispose();
         }
      }

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)
{
   executeTests();
}

private void executeTests()
{
   CustomerTableSelect();
   CustomerTableInsert();
}

private void CustomerTableInsert()
{
   try
   {
      CustomerCRUDmanager.Instance.SQLInsertRecord("Test Name1", "Test Surname1", "03/11/1993", "2310", "234567", "test@test.gr");
      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 :)