Deleting Records from an Access DB

[chmilz]

Hello all,

I am writing an application in ASP.NET that uses a DataGrid to display product information. I am having trouble with passing the delete funtion on to the Access Database and was wondering if someone could help as I am fairly new at this...

Here is the code I have so far:

query = "Delete From Products Where ProductID = "+ pID +"";

			OleDBConnection conn = new OleDbConnection(connection);
			OleDbDataAdapter adapter = new OleDbDataAdapter();
			adapter.DeleteCommand = new OleDbCommand(query, conn);

I have the query and connection in place but I am not sure how to get this statement to be EXECUTED on the database to actually delete the record.

Any help is greatly appreciated.

Cheers

 

[CrashDome]

I believe you are looking for the DataAdapter.Update Method

example:
adapter.Update(DataSet myDataSet, string sourceTableName);

 

[chmilz]

I figured that much...

But I want to delete a value from my database... the Update Method wants to take in a dataset.

I don't have a dataset anywhere... the user selects a product to delete from the table and the delete method gets passed the product id for the SelectedIndex of the DataGrid.

Can the Update Method be called without passing any arguments?

 

[sacheson]

actually, you can do that from a Command object. Are you using the System.Data.Odbc namespace? Do you have a System DSN set up to access the db?

If so ...

string sql = "DELETE ..."
OdbcConnection cn = new OdbcConnection(<your dsn params here>);
cn.Open();
OdbcCommand cmd = new OdbcCommand(sql, cn);

try
{
    cn.ExecuteNonQuery();
}
finally
{
    cn.Close();
    cn = null;
}

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Sam,

Checking up on me? Lol... thanks so much for the input, that is exactly what I was looking for!

Thanks again

 

[chmilz]

Since I have you here sacheson,

I have one other small problem that I can't really figure out.

The ExecuteNonQuery() works great on my updates and deletes but for some reason when I click the Update button on the datagrid it loops through the "OnUpdateCommand" method twice??? Is there a property that I have to set on the Datagrid to stop this??

Cheers

Curtis

 

[sacheson]

Curtis,

I don't have much time to work through this issue today, but the problem is a known issue with the DataGrid (actually, many controls that behave in this manner). What's happening is you're doing something that is firing the OnUpdateCommand event, and the update is happening which is again firing that event. It's not really a 'bug' because it's theoretically working as designed.

In these situations, I've found that when I investigate, I can find a better event to control the flow of my application, OR I wrap my logic in the event within a member level flag (class level boolean value). When I want to exectute the code within the event, I set that value to true, then on the last line of the logic in the event, I set it to false (or put in a try ... finally block so it always gets set).

Basically like this ...

 ... in the update button click event ...
{
    _updateFlag = true;
}

and in the event handler ...

{
    if(_updateFlag == true)
    {
       try
       {
           // do a bunch of stuff
       }
       finally
       {
           _updateFlag = false;
       }
    }
}

Hope that helps.

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[chmilz]

Actually I got it! Sorry to bother ya!

The problem was that in my code I had declared the "On..." commands but in the "Design View" they were specified there as well.

As soon as I removed the entries from the Design View and was left with only the declarations in the code, everything worked great!

Have a good Friday!

Cheers,

Curtis