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);
}
If you got a same error for "insert" query you can use this method for avoid exception
Include the quotes around the value you get from
this.textBox2.Text
as in your working sample query.Imagine
this.textBox2.Text
contains the text foo. Without adding those quotes in theWHERE
clause the db engine would see... WHERE Semester = 1 AND Subject = foo
But it can't find anything in the data source namedfoo
, 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 inthis.textBox2.Text
, all rows in the table would be deleted.