Trying to get my delete button to work PLEASE HELP!!

[ogsirus]

Hi Guys need a bit of help. I right a c# program that connects to a database and do various things. One of them is to delete a record. currently I can delete a record that i specify but i want it to delete a selected row in the dataset.

string connectionString = null;
            OleDbConnection connection;
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
            string sql = null;
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            connection = new OleDbConnection(connectionString);
            sql = "delete from Books where BookKey = ?";
            try
            {
                connection.Open();
                oledbAdapter.DeleteCommand = connection.CreateCommand();
                oledbAdapter.DeleteCommand.CommandText = sql;
                oledbAdapter.DeleteCommand.Parameters.Add("BookKey", OleDbType.Char, 0, "BookKey");
                

                
                MessageBox.Show("row deleted");
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
            finally
            {
                
                connection.Close();

This is the code. right now it will only delete whatever number i put in the sql command it doesnt like the question mark.

any suggestions would be great

Thank You

[jugosoft]

Try with this code, I changed SQL query and parameter:

string connectionString = null;
            OleDbConnection connection;
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
            string sql = null;
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            connection = new OleDbConnection(connectionString);
            sql = "delete from Books where BookKey = @BookKey";
            try
            {
                connection.Open();
                oledbAdapter.DeleteCommand = connection.CreateCommand();
                oledbAdapter.DeleteCommand.CommandText = sql;
                oledbAdapter.DeleteCommand.Parameters.Add(new OleDbParameter("BookKey", OleDbType.Char, 0, "BookKey"));

                MessageBox.Show("row deleted");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            finally
            {
                connection.Close();
            }

[ogsirus]

Just gave it a go. im getting the same results as previously. Dont know if its selecting the row i want to delete right.

if i were to say [ sql = "delete from Books where BookKey = 109"; the ] then it will delete book 109 despite whatever i may select in my listbox.

i tried "targetRow.delete" but still no jot with that

The Code again is

void ButtonDelete(object s, EventArgs e)
        
        {
            DataRow selectedRow = setdata.Tables[0].Rows[listBox1.SelectedIndex];
          
            string connectionString = null;
            OleDbConnection connection;
            OleDbDataAdapter oledbAdapter = new OleDbDataAdapter();
            string sql = null;
            connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + @"data source=Book.mdb";
            connection = new OleDbConnection(connectionString);
            sql = "delete from Books where BookKey = @BookKey";
            try
            {
                connection.Open();
                oledbAdapter.DeleteCommand = connection.CreateCommand();
                oledbAdapter.DeleteCommand.CommandText = sql;
                oledbAdapter.DeleteCommand.Parameters.Add(new OleDbParameter("BookKey", OleDbType.Char, 0, "BookKey"));
                

                
                MessageBox.Show("row deleted");
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
            finally
            {
                
                connection.Close();
                refreshlist(); // this is basically my start button which contains the info to retrieve the newly edited information


Thank you Again
                
               
            }
                 
         }

[kvprajapati]

To execute DataAdapter's command queries (Insert/delete/update), you need to issue "Update" method of DataAdapter but here is quite different story. Please refer follow code snippet.

void ButtonDelete(object s, EventArgs e)
        {
            DataRow selectedRow = setdata.Tables[0].Rows[listBox1.SelectedIndex];
          
            string connectionString = null;
            OleDbConnection connection;
            OleDbCommand cmd = new OleDbCommand();
            string sql = null;
            connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;data source=x:\path\Book.mdb";
            connection = new OleDbConnection(connectionString);
            sql = "delete from Books where BookKey = @BookKey";
            connection.Open();
            cmd.CommandText = sql;
            cmd.Connection=connection;
            cmd.Parameters.AddWithValue"@BookKey",selectedRow["BookKey"]);
            cmd.ExecuteNonQuery();
            connection.Close();                
            refreshlist(); // this is basically my start button which contains the info to retrieve the newly edited information

}

[ogsirus]

Thank You

It working fine now. I tried using dataAdapters but i was getting errors such as 'unhandled event' or null value'

Just wondering would i use the same sort of method for inserting a new row to the dataset