C＃的OleDb例外[否为一个或多个必需的参数和QUOT给定值;而试图从Access数据库中删除 [英] C# OleDb Exception "No value given for one or more required parameters" while trying to delete from Access database

问题描述

我有学期，SUBJECT，OFFER，结果，其中学期&放一台; SUBJECT的主键。当我使用查询

DELETE FROM Course_Information WHERE学期= 1 AND主题='CSE-414';

其在Access数据库完美工作，但我总是得到例外，当我试图在我的C＃代码中使用它。

如果我使用而且其作品DELETE FROM Course_Information WHERE学期= 1;

我要同时使用主题加上功放; 学期在WHERE条件（因为可能有在同一学期不同的主题）

请参阅我的代码，

  connection_string = aConnection.return_connectionString（connection_string）; 
字符串sql_query =DELETE FROM Course_Information WHERE学期=+ this.textBox1.Text +和主题=+ this.textBox2.Text +;; 
 
 OleDbConnection的连接=新的OleDbConnection（connection_string）; 
 OleDbCommand的命令=新的OleDbCommand（sql_query，连接）; 
试
 {
 connect.Open（）; 
 OleDbDataReader读卡器= Command.ExecuteReader却（）; 
 MessageBox.Show（删除成功！）; 
 connect.Close（）; 
 UpdateDatabase（）; 
} 
 
赶上（异常前）
 {
 MessageBox.Show（ex.Message）; 
} 
  

解决方案

包含围绕价值引号您从 this.textBox2.Text 得到尽可能在你的工作示例查询。

 并受='+ this.textBox2.Text +; 
  

想象 this.textBox2.Text 包含文本的富的。如果没有 WHERE 子句中添加这些报价数据库引擎将看到 ... WHERE学期= 1 AND主题= foo的但它不能找到名为富数据源的任何东西，所以假设它必须是一个参数。您需要引号信号数据库引擎这是一个字符串值，'富'的

其实，如果你切换到一个参数查询，你能避免这种类型的问题，因为你不会需要这些报价打扰在删除语句。和参数查询也将保护您免受SQL注入攻击。如果恶意用户可以输入的'或'A'='A 的在 this.textBox2.Text ，表中的所有行会被删除。

I have a table with "SEMESTER, SUBJECT, OFFER, RESULT" where "SEMESTER" & "SUBJECT" is PRIMARY KEY. When i use the query

"DELETE FROM Course_Information WHERE Semester = 1 AND Subject = 'CSE-414' ;

Its working perfectly in access database but i always get exception when i tried to use it in my c# code.

Moreover its works if i use "DELETE FROM Course_Information WHERE Semester = 1 ;

I want to use both "SUBJECT" & "SEMESTER" In the WHERE condition (Because there could be different subject in the same semester)

See my code,

connection_string = aConnection.return_connectionString(connection_string);
            string sql_query = "DELETE FROM Course_Information WHERE Semester = " + this.textBox1.Text + " AND Subject = " + this.textBox2.Text + " ;";

            OleDbConnection connect = new OleDbConnection(connection_string);
            OleDbCommand command = new OleDbCommand(sql_query, connect);
            try
            {
                connect.Open();
                OleDbDataReader reader = command.ExecuteReader();
                MessageBox.Show("Delete Successful!");
                connect.Close();
                UpdateDatabase();
            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

解决方案

Include the quotes around the value you get from this.textBox2.Text as in your working sample query.

" AND Subject = '" + this.textBox2.Text + "';";

Imagine this.textBox2.Text contains the text foo. Without adding those quotes in the WHERE clause the db engine would see ... WHERE Semester = 1 AND Subject = foo But it can't find anything in the data source named foo, so assumes it must be a parameter. You need the quotes to signal the db engine it's a string literal value, 'foo'.

Actually if you switch to a parameter query, you can avoid this type of problem because you won't need to bother with those quotes in the DELETE statement. And a parameter query will also safeguard you against SQL injection. If a malicious user can enter ' OR 'a' = 'a in this.textBox2.Text, all rows in the table would be deleted.

这篇关于C＃的OleDb例外[否为一个或多个必需的参数和QUOT给定值;而试图从Access数据库中删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！