查询访问数据库表给出数据类型不匹配错误C＃ [英] Query access DB table give data type mismatch error C#

问题描述

我正在尝试查询访问数据库以获取某个工作人员输入的选定日期的列总和，但是我收到错误说数据类型不匹配



这里是代码

I'm trying to query an access db to get the sum of a column on a selected date entered by a certain staff, but I get an error saying Data type mismatch

here is the code

 private void button1_Click(object sender, EventArgs e)
 {
     getsum();
 }



public void getsum()
{
    string query = @"select SUM(SQuantity) AS Total FROM Sales where Sdate=@date AND Staffid=@mobile";
    using (var conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
          using (var cmd = new System.Data.OleDb.OleDbCommand(query, conn))
          {
            cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.VarChar).Value = '1';
            cmd.Parameters.Add("@date", System.Data.OleDb.OleDbType.VarChar).Value = dateTimePicker1.Value.Date;

            conn.Open();

            using (var rdr = cmd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    textBox1.Text = rdr["Total"].ToString();
                }
                rdr.Close();
            }
        }
}

我尝试了什么：



我已经尝试过没有Where条件且工作正常，代码就像那样

What I have tried:

I've tried it without the Where condition and it worked fine, the code was like that

 private void button1_Click(object sender, EventArgs e)
 {
     getsum();
 }



public void getsum()
{
    string query = @"select SUM(SQuantity) AS Total FROM Sales";
    using (var conn = new System.Data.OleDb.OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data source=|DataDirectory|\\crepeDB.accdb;"))
          using (var cmd = new System.Data.OleDb.OleDbCommand(query, conn))
          {
            cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.VarChar).Value = '1';
            cmd.Parameters.Add("@date", System.Data.OleDb.OleDbType.VarChar).Value = dateTimePicker1.Value.Date;

            conn.Open();

            using (var rdr = cmd.ExecuteReader())
            {
                if (rdr.Read())
                {
                    textBox1.Text = rdr["Total"].ToString();
                }
                rdr.Close();
            }
        }
}

推荐答案

我认为Access不支持命名参数;相反，它依赖于你添加它们的顺序。



尝试按照它们在查询中出现的顺序添加参数。您还需要为 @date 参数指定正确的类型。

I don't think Access supports named parameters; instead, it relies on the order in which you add them.

Try adding your parameters in the same order in which they appear in your query. You'll also want to specify the correct type for your @date parameter.

using (var cmd = new System.Data.OleDb.OleDbCommand(query, conn))
{
    cmd.Parameters.Add("@date", System.Data.OleDb.OleDbType.Date).Value = dateTimePicker1.Value.Date;
    cmd.Parameters.Add("@mobile", System.Data.OleDb.OleDbType.VarChar).Value = '1';
}

这篇关于查询访问数据库表给出数据类型不匹配错误C＃的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！