c#使用MS访问参数化OLEDB查询

ok i do have a OLEDB function that inserts data in the database. well i know the usual way on how to do SQL with OLEDB but what I want to do here is to parametize each data in the query string.

我有一个OLEDB函数，它在数据库中插入数据。我知道如何使用OLEDB来处理SQL的通常方法但是我想做的是参数化查询字符串中的每个数据。

Now I been in Google for almost 3 hours just know how to make parametized query in OLEDB but all of them are not working with me.

现在我已经在谷歌中工作了将近3个小时，只知道如何在OLEDB中进行参数化查询，但是他们都不支持我。

Now at last my query seems to work fine in the code below but still an error occurs

现在，我的查询似乎在下面的代码中运行良好，但仍然有错误发生。

my function code :

我的函数代码:

private bool dbInsert(int [] inputNumbers, DateTime datetime){
                try {
                    String sql = "INSERT INTO 655(1stNum, 2ndNum, 3rdNum, 4thNum, 5thNum, 6thNum, datedraw) VALUES(?, ?, ?, ?, ?, ? ,?)";
                    OleDbCommand dbcmd = new OleDbCommand(sql, app.oleDbConn());
                    dbcmd.Connection.Open();
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[0];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[1];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[2];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[3];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[4];
                    dbcmd.Parameters.Add("?",OleDbType.Numeric).Value = inputNumbers[5];
                    dbcmd.Parameters.Add("?",OleDbType.DBTimeStamp).Value = datetime;
                    dbcmd.ExecuteNonQuery();
                    dbcmd.Connection.Close();

                }catch(OleDbException ex){
                    showPrompt("Error reading the database",Color.Red);
                    Console.WriteLine(ex.ToString());
                }
                return true;
            }

this is the error log show in the console:

这是控制台显示的错误日志:

System.Data.OleDb.OleDbException: Data type mismatch in criteria expression. at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

System.Data.OleDb。OleDbException:标准表达式中的数据类型不匹配。在System.Data.OleDb.OleDbCommand。在System.Data.OleDb.OleDbCommand ExecuteCommandTextErrorHandling(OleDbHResult人力资源)。ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, object&executeresult)位于system . data . data . oledb . oledbcommand。在System.Data.OleDb.OleDbCommand ExecuteCommandText(对象executeResult)。ExecuteCommand(命令行为，object&executeresult)位于System.Data.OleDb.OleDbCommand。ExecuteReaderInternal(CommandBehavior, String method)位于System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

the datatypes of my columns in my access db files are Numeric from 1stNum to 6thNum and Date/Time in datedraw, of course ID is AutoNumber

我的access db文件中的列的数据类型是数字，从1到6，在datedraw中是日期/时间，当然ID是自动编号

is there any mistake in the parametize query implementation? please help me.

在参数化查询实现中是否存在错误?请帮助我。

Update

更新

I change the OleDBType for nums to Numeric as in the Access file its Numeric
我将nums的OleDBType更改为Numeric，就像访问文件中的数字一样

2 个解决方案

#1

You need single quotes around the values, like this:

您需要在值周围使用单引号，如下所示:

String sql = 
    "INSERT INTO 655(1stNum, 2ndNum, 3rdNum, 4thNum, 5thNum, 6thNum, datedraw)
    VALUES('?', '?', '?', '?', '?', '?' ,'?')";

Also, have you tried inserting a real integer instead of "?" ?

另外，您是否尝试过插入一个实整数而不是“?”吗?

#2

One thing I would check is the type: OleDbType.DBTimeStamp

我要检查的是类型:OleDbType.DBTimeStamp

If I recall, TimeStamp is a special type that's not easily compatible with the DateTime type.

如果我记得的话，时间戳是一种特殊类型，不容易与DateTime类型兼容。

Note that OleDb CAN use named parameters: see MS Access, Named parameters and Column Names

注意，OleDb可以使用命名参数:参见MS Access、命名参数和列名

#1