这些参数有什么问题吗?
我有一个包含 7 个字段的 Access 文件:
DocID - text - primary
SourceID - text
ReceivedDay - Date/Time
Summary - text
DueDay - Date/Time
Person - text
Status - Yes/No
现在我想使用以下代码更新此文件:
const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\DocMan.mdb;Persist Security Info=True";
const string InsertQuery = "INSERT Into Docs(DocID,ReceivedDay,Summary,Person,DueDay,Status,SourceID) Values(@DocID,@ReceivedDay,@Summary,@Person,@DueDay,@Status,@SourceID)";
string DocID = textBox1.Text;
string SourceID = comboBox1.SelectedIndex.ToString();
DateTime ReceivedDay = dateTimePicker1.Value;
string Summary = richTextBox1.Text;
string Person = textBox2.Text;
DateTime DueDay = dateTimePicker2.Value;
bool Status = false;
OleDbConnection cnn = new OleDbConnection(ConnectionString);
cnn.Open();
OleDbCommand cmd = new OleDbCommand(InsertQuery, cnn);
cmd.Parameters.AddWithValue("@DocID", DocID);
cmd.Parameters.AddWithValue("@SourceID", SourceID);
cmd.Parameters.AddWithValue("@ReceivedDay", ReceivedDay);
cmd.Parameters.AddWithValue("@Summary", Summary);
cmd.Parameters.AddWithValue("@Person", Person);
cmd.Parameters.AddWithValue("@DueDay", DueDay);
cmd.Parameters.AddWithValue("@Status", Status);
cmd.ExecuteNonQuery();
cnn.Close();
但出现异常:
Data type mismatch in criteria expression.
如何解决此问题?
编辑:我使用不同的方法解决了这个问题:
我构建了一个这样的查询:
INSERT INTO Docs
(DocID, SourceID, ReceivedDay, Summary, Person, DueDay, Status)
VALUES (?, ?, ?, ?, ?, ?, ?)
然后使用 TableAdapter 来调用它:
string DocID = textBox1.Text;
string SourceID = comboBox1.SelectedIndex.ToString();
DateTime ReceivedDay = dateTimePicker1.Value.Date;
string Summary = richTextBox1.Text;
string Person = textBox2.Text;
DateTime DueDay = dateTimePicker2.Value.Date;
bool Status = false;
DocManDataSetTableAdapters.DocsTableAdapter docsTableAdapter = new DocManDataSetTableAdapters.DocsTableAdapter();
docsTableAdapter.InsertQuery(DocID,SourceID,ReceivedDay,Summary,Person,DueDay,false);
更简单,现在工作正常。 谢谢你们
I have an Access file with 7 fields:
DocID - text - primary
SourceID - text
ReceivedDay - Date/Time
Summary - text
DueDay - Date/Time
Person - text
Status - Yes/No
Now I want to update this file with the following code:
const string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\DocMan.mdb;Persist Security Info=True";
const string InsertQuery = "INSERT Into Docs(DocID,ReceivedDay,Summary,Person,DueDay,Status,SourceID) Values(@DocID,@ReceivedDay,@Summary,@Person,@DueDay,@Status,@SourceID)";
string DocID = textBox1.Text;
string SourceID = comboBox1.SelectedIndex.ToString();
DateTime ReceivedDay = dateTimePicker1.Value;
string Summary = richTextBox1.Text;
string Person = textBox2.Text;
DateTime DueDay = dateTimePicker2.Value;
bool Status = false;
OleDbConnection cnn = new OleDbConnection(ConnectionString);
cnn.Open();
OleDbCommand cmd = new OleDbCommand(InsertQuery, cnn);
cmd.Parameters.AddWithValue("@DocID", DocID);
cmd.Parameters.AddWithValue("@SourceID", SourceID);
cmd.Parameters.AddWithValue("@ReceivedDay", ReceivedDay);
cmd.Parameters.AddWithValue("@Summary", Summary);
cmd.Parameters.AddWithValue("@Person", Person);
cmd.Parameters.AddWithValue("@DueDay", DueDay);
cmd.Parameters.AddWithValue("@Status", Status);
cmd.ExecuteNonQuery();
cnn.Close();
But I get an exception:
Data type mismatch in criteria expression.
How can I fix this?
EDIT: I fixed this, using a different approach:
I built a query like that:
INSERT INTO Docs
(DocID, SourceID, ReceivedDay, Summary, Person, DueDay, Status)
VALUES (?, ?, ?, ?, ?, ?, ?)
and then used a TableAdapter to call it:
string DocID = textBox1.Text;
string SourceID = comboBox1.SelectedIndex.ToString();
DateTime ReceivedDay = dateTimePicker1.Value.Date;
string Summary = richTextBox1.Text;
string Person = textBox2.Text;
DateTime DueDay = dateTimePicker2.Value.Date;
bool Status = false;
DocManDataSetTableAdapters.DocsTableAdapter docsTableAdapter = new DocManDataSetTableAdapters.DocsTableAdapter();
docsTableAdapter.InsertQuery(DocID,SourceID,ReceivedDay,Summary,Person,DueDay,false);
Much more simple, and It works fine now. Thank you all
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
只需询问 google,我猜超过 10000 个点击率相当可观。 你的论点“我不认为......”在你证明它之前是无效的。
这是 MSDN 所说的:
Simply ask google, I guess more than 10000 hits is quite impressive. Your argument "I don't think that..." is not valid until you proved it.
This is what MSDN says:
问题是因为添加参数时参数的顺序不同。
例如,在注释行 (
//adapter.InsertQuery...
) 中,您有DocID
,然后是RecievedDay
...添加它们时,首先添加DocID
,然后添加SourceID
。确保它们的顺序相同...这适用于 sql 语句或存储过程。
这是因为 ADO.NET 在使用 OLEDB 提供程序时不支持命名参数,并且由于您正在连接到 Access DB,因此您实际上正在使用 OLEDB 提供程序...因此参数的顺序很重要。
如果它们按顺序排列,但仍然无法正常工作,那么我认为这可能是
DateTime
的问题;在将其添加为参数之前尝试将其转换为字符串:
并确保日期的格式为美国格式 (
m/d/yyyy
) 或 ISO 格式 (年-月-日
)The problem is because the parameters are not in the same order when you are adding them.
For example, in your commented line (
//adapter.InsertQuery...
), you haveDocID
and thenRecievedDay
...yet when you are adding them, you first addDocID
and then addSourceID
.Make sure that they are in the same order...and this applies to both sql statements or stored procedures.
This is because ADO.NET does not support named parameters when using an OLEDB provider, and since you are connecting to an Access DB, you are infact using an OLEDB provider...so the order of the parameters does matter.
If they are in order, and it's still not working, then I think that it might be an issue with the
DateTime
s;Try converting it to string before adding it as a parameter :
And also make sure that the format of the date is in U.S. format (
m/d/yyyy
) or ISO Format (yyyy-mm-dd
)OleDb不支持命名参数,所以Dreas的答案是正确的。
当您使用 OleDb 时,您必须按照参数在查询中出现的顺序添加参数,因为您为它们指定的名称不会被使用。
OleDb does not support named parameters, so the answer of Dreas is correct.
When you use OleDb, then you have to add the parameters in the same order as they appear in the query, since the names that you give them, are not used.