Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# Access Exception
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 Smile | :)
Posted 3-Apr-13 6:37am
Edited 3-Apr-13 7:55am
v3
Comments
ThePhantomUpvoter at 3-Apr-13 11:49am
   
Access uses # for datetime values. Try #'@Date Of Birth'# and see what happens.
Silenous at 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 at 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 at 3-Apr-13 12:17pm
   
Try #@Date Of Birth#, without the quotes.
Silenous at 3-Apr-13 12:24pm
   
Just did, syntax error, the correct way is '#Date Of Birth#' after all :)
ThePhantomUpvoter at 3-Apr-13 12:31pm
   
Well there you go. Great!
Silenous at 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 at 3-Apr-13 12:40pm
   
Whatever you put in quotes is going to be set as a string.
Rate this: bad
good
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.
 
Try:
 
 
var dobParam = new OleDbParameter("@Date Of Birth", OleDbType.Date); 
dobParam.Value = DateOfBirth;
 
  Permalink  
v2
Comments
Silenous at 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 at 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 at 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')" ;
 
To:
 
string sqlStr = " INSERT INTO [Customers] ([Name], [Surname], [Date Of Birth], [Phone], [Mobile], [Email]) VALUES ('@Name','@Surname', @DateOfBirth, '@Phone', '@Mobile', '@Email')" ;
Rate this: bad
good
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", "test@test.gr");
 
I'm not sure for next two numbers (2310 and 234567) passed as string.
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 3 Apr 2013
Copyright © CodeProject, 1999-2014
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