CODEWITHSUNDEEP
c#ms-accessoledbdataadapter
Jake Gonyer
Jake Gonyer

Reputation: 1

MS Access, OleDbAdapter not deleting records but no errors

I'm trying to delete the record containing the matching text that is displayed in a datagridview. I get the string all right and nothing seems to go wrong, but none of the records are being deleted.

String text;
int i = dataGridView1.CurrentRow.Index;
text = (string)dataGridView1[0, i].Value.ToString();
String deleteSql = "DELETE FROM [Sugg] WHERE sugg_text='"+text+"'";
MessageBox.Show(text);
if (MessageBox.Show("Are you sure you want to delete this suggestion?", "Title", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
    try
    {
        oleDbConnection1.Open();
        oleDbDataAdapter1.DeleteCommand = oleDbConnection1.CreateCommand();
        oleDbDataAdapter1.DeleteCommand.CommandText = deleteSql;

        oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
        oleDbConnection1.Close();

Upvotes: 0

Views: 1227

Answers (2)

Baine Wedlock
Baine Wedlock

Reputation: 345

Try to remove the try/catch block to see if there really is no error.

The fact you don't get an error probably means that either the content of your text variable is bad, or that the error gets lost in your try/catch block. (which we can't see completely)

Also as Tim said, instead of inserting your text value directly in the SQL statement, use command parameters for these kind of things. It will prevent unexpected things from happening, such as an apostrophe inside of text.

String deleteSql = "DELETE FROM [Sugg] WHERE sugg_text=@SUGG_TEXT";
var cmd = oleDbConnection1.CreateCommand();
cmd.CommandText = deleteSql;
cmd.Parameters.AddWithValue("SUGG_TEXT", text);

Upvotes: 1

tsells
tsells

Reputation: 2771

I would try using the following syntax. The using blocks ensure you are cleaning up your connections / commands.

public void TestMethod()
        {
            string connectionstring = "someconnectionstring";
            string deletesql = "delete from table where something = somethingelse";
            using(OleDbConnection con = new OleDbConnection(connectionstring))
            {
                con.Open();
                using(OleDbCommand com = new OleDbCommand(deletesql, con))
                {
                    com.ExecuteNonQuery();
                }
                con.Close();
            }
        }

Of course you need to add your try / catch blocks as well as wrap in a transaction to be complete but this should give you a place to start.

Upvotes: 0

Related Questions