标准中的数据类型不匹配例外C＃访问 [英] DATA TYPE MISMATCH IN CRITERIA EXCEPTION C# ACCESS

问题描述

更新：我做了一些测试，看看出生日期是否真的是问题。我在Access数据库中将所有内容都更改为Text数据类型，并尝试插入Phoenyx代码提供的数据以及我遇到问题时仍然没有的数据。据我所知，因为数据库有一个自动编号ID作为第一列，所以查询必须尝试添加从ID开始的值。有关如何避开并从名字开始的任何想法吗？





你好，最近我一直在努力一个程序，我通过使用断点标准异常中的数据类型不匹配得到错误。对于我的编码，我使用C＃和Access，我一直试图找出问题，但没有运气。我将引导您完成我的代码并向您显示问题出现的位置。



这是连接到Access数据库以使用插入的代码的一部分或读取数据库中的数据

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

这是添加数据的代码部分。我用大写字母和粗体指出错误显示的地方。众所周知，名称，姓氏，电话，手机和电子邮件在Access数据库中设置为文本，出生日期设置为日期/时间。

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", "[e-mail]");
      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 :)

推荐答案

最有可能的问题是，您使用错误的数据类型设置了一个参数。

我会检查每个变量是参数的正确数据类型。



尝试：

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;

MS Access的JET数据库引擎需要两个＃（哈希）之间的日期，例如，日期bith应该传递为：

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", "[e-mail]");

我不确定下两个数字（2310和234567）是否以字符串形式传递。

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

这篇关于标准中的数据类型不匹配例外C＃访问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！