Control Panel Forum Hosting Help ASP.NET 4 & SQL 2008 R2 Hosting Resources Notice

Total members: 210362
Average new registrations per day (in last 7 days): 16

New Registration: Open

Register Now

Home » Resources » How to Delete data in the MS Access database for ASP.NET application

ASP.NET	ADO.NET	Web Services	Remoting	Visual Studio 2005
Error Bank	Interview Questions	Tips & Tricks	XML	HTML
Jscript/Javascript	IIS	Windows		General

Submit Resource or code snippet ... and get surprise gifts ASP.NET web hosting

Win Digital camera, ASP.NET Books, Free softwares!!

How to Delete data in the MS Access database for ASP.NET application

20 Jun, 2010

Author: Alhadith hazro

Summary
How to Delete data in the MS Access database for ASP.NET application

.NET Classes used :

OdbcConnection

OdbcCommand

How to Delete data in the MS Access database for ASP.NET application
Following demo provides you the quickest way to learn how to delete data in the mdb file while using asp.net. It requires only 2-or-3 lines of code, extra code is optional, which depends on you, what you want to delete. One varialbe for ConnectionString is required and one for QueryString is required. And then the part of code under using statement for OdbcConnection class used for doing connection with access database.
Here is how I used above technique:
namespace DashingData
{
public class DashingDataUser
{
private void OnDeletingData()
{
string connectionString = string.Format(Driver={{Microsoft Access Driver (*.mdb)}};DBQ={0}, ~/App_Data/YourDataFile.mdb);
try
{
const string queryString = select * FROM CustomerTable;

using (var connection = new OdbcConnection(connectionString))
{
var adapter = new OdbcDataAdapter(queryString, connectionString);
var dataset = new DataSet();
connection.Open();
adapter.Fill(dataset);

var table = dataset.Tables[0];
var query = table.AsEnumerable().Where(
x => x.Field(CustomerID).Equals(txtSearch.Text)
).Select(c => c);

var view = query.AsDataView();
view.RowStateFilter = DataViewRowState.CurrentRows;

var newQuery = (from DataRowView c in view
select c).FirstOrDefault();

if (newQuery != null)
{
string querystring = DELETE FROM CustomerTable WHERE CustomerID=' + txtSearch.Text + ';

using (var conn = new OdbcConnection(connectionString))
{
var command = new OdbcCommand(querystring) { Connection = conn };
conn.Open();
command.ExecuteNonQuery();
}
lblError.Text = The record of Customer ID + txtSearch.Text + is deleted successfully.;
}
if (newQuery == null)
{
lblError.Text = Customer ID + txtSearch.Text + doesn't found in the database.;
}
}
return;
}
catch(Exception ex)
{
lblError.Text = ex.Message;
}
}
}
}

I've created a DashingContext entity class. That serve as LINQ-to-SQL entity class. LINQ-to-SQL also works fine for MS Access database searching purpose, but CRUD Operations can't be done with LINQ-to-SQL for mdb file. You must explicitly call traditional OdbcConnection class methos for CRUD operations. Some types of MS Access don't match with CLR types, i.e. True/False type don't match with CLR Boolean type. So that, in that case, you must use LINQ-to-Dataset for rich-searching.
My mdb file contains a table, named: CustomerTable. I used it above.

Feedbacks about this page from members:

- No Feedbacks yet !! -

Submit Feedback View All Feedbacks