'C# OleDb Exception "No value given for one or more required parameters" while trying to delete from Access database

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);
            }
c++sharpms-accessoledbconnectionoledbexception


Solution 1:[1]

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

Solution 2:[2]

Have you changed any of the field names? I got this error when I had used Access to change a field name in my database. The old name was "DisbursementType". The new name was "ExpenseType". I used a query with the new field name to fill a dataset which worked fine.

string sqlQuery = "select * from Finance WHERE ExpenseType = 'MISC'";
System.Data.OleDb.OleDbCommand obiwan = new System.Data.OleDb.OleDbCommand();
obiwan.CommandText = sqlQuery;
this.financeTableAdapter.Adapter.SelectCommand.CommandText = sqlQuery;
this.financeTableAdapter.Fill(homeFinanceDataSet.Finance);

Using the same query string to open a record set failed.

string sqlQuery = "select * from Finance WHERE ExpenseType = 'MISC'";
string dbaseaddress = "";
ADODB.Recordset RS = new ADODB.Recordset();
if (openFileDialog1.ShowDialog()== DialogResult.OK)
        {
            dbaseaddress = openFileDialog1.FileName;
            ADODB.Connection fc = new ADODB.Connection();
            string str = "Provider=Microsoft.ACE.OLEDB.12.0;" +
          "Data Source=" + dbaseaddress + ";" +
          "Jet OLEDB:Database Password=" + "password" + ";";
            fc.Open(str);
            RS.Open(sqlQuery, fc, ADODB.CursorTypeEnum.adOpenStatic, &_ 
            ADODB.LockTypeEnum.adLockOptimistic,0);

An exception is thrown at the last line: "System.Runtime.InteropServices.COMException: 'No value given for one or more required parameters.'"

When the original name for the field is used no exception is thrown.

string sqlQuery = "select * from Finance WHERE DisbursementType = 'MISC'";
        string dbaseaddress = "";
        ADODB.Recordset RS = new ADODB.Recordset();
        if (openFileDialog1.ShowDialog()== DialogResult.OK)
        {
            dbaseaddress = openFileDialog1.FileName;
            ADODB.Connection fc = new ADODB.Connection();
            string str = "Provider=Microsoft.ACE.OLEDB.12.0;" +
          "Data Source=" + dbaseaddress + ";" +
          "Jet OLEDB:Database Password=" + "password" + ";";
            fc.Open(str);

            RS.Open(sqlQuery, fc, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic,0);
            MessageBox.Show(string.Format("{0}",RS.RecordCount));

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 AliMusa
Solution 2 Daniel Lu

Related Questions