C# OleDb Exception “No value given for one or more

问题:

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

回答1:

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.

回答2:

If you got a same error for "insert" query you can use this method for avoid exception

string sqlQuery = "INSERT into EndResultOfTestCases(IDsOfCases,TestCaseName,ResultCase,ResultLog) VALUES(@ids, @casename, @results, @logs)";

        connection = new OleDbConnection(connectionStringToDB);
        command = new OleDbCommand(sqlQuery, connection);
        command.Parameters.AddWithValue("@ids",IDs);
        command.Parameters.AddWithValue("@casename", CaseName);
        command.Parameters.AddWithValue("@results", resultOfCase);
        command.Parameters.AddWithValue("@logs", logs);
        connection.Open();
        command.ExecuteNonQuery();
        connection.Close();
    }