Click here to Skip to main content
13,665,664 members
Click here to Skip to main content
Add your own
alternative version

Stats

78.1K views
4.3K downloads
20 bookmarked
Posted 12 Jan 2015
Licenced CPOL

MS Access (*.mdb) + C#: SELECT, INSERT, DELETE and UPDATE Queries

, 12 Jan 2015
Rate this:
Please Sign up or sign in to vote.
Queries in Microsoft Access (MDB) databases.

See Also

Same tip for SQL Server

Prepare to Run

  1. Create a C# project, turn it platform in x86 from Any CPU and save it. Any CPU unsupported because Microsoft Jet Ole DB 4.0 (library that allows you integrate MDB files) has only x86 version. If you'll run you Any CPU application on x64 OS, your application will run in x64 mode, i.e. will can't use x86 libs.
  2. Create database in MS Access, save it as Test.mdb name in path with YourApplication.vshost.exe file (probably it's bin\x86\Debug\ folder in your project directory).
  3. Create Table_1 table in this database with 3 columns:
    • id (counter, key field)
    • int_col (int)
    • text_col (text)

Tip: it isn't recommended to use spaces in table or column names because it complicates the process of writing queries (you must use [ ] for names with spaces), and may cause troubles in OleDbCommandBuilder work if you use it to automatically generate INSERT, UPDATE, DELETE queries for DB changes saving.

Use "_" characters intead.

Connect Database

// it's your DB file path:
// ApplicationEXEPath\Test.mdb
var DBPath = Application.StartupPath + "\\Test.mdb";

conn = new OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;"
    + "Data Source=" + DBPath);
conn.Open();

Run INSERT Query

// txtInsert.Text:
// INSERT INTO Table_1 (text_col, int_col) VALUES ('Text', 9);
//
// inserts 1 row into Table_1 table
using (OleDbCommand cmd = new OleDbCommand(txtInsert.Text, conn))
{
    cmd.ExecuteNonQuery();
}

Run SELECT Query to Get Table Content into Grid

using (DataTable dt = new DataTable())
{
    // txtSelect.Text:
    // SELECT id, text_col, int_col FROM Table_1
    // or
    // SELECT * FROM Table_1
    //
    // selects all content from table and adds it to datatable binded to datagridview
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(txtSelect.Text, conn))
    {
        adapter.Fill(dt);
    }
    dgvSelect.DataSource = dt;
}

Run UPDATE Query to Modify Row

// txtUpdate.Text:
// UPDATE Table_1 SET [text_col]='Updated text', [int_col]=2014 WHERE id=2;
//
// changes 2nd row in Table_1
using (OleDbCommand cmd = new OleDbCommand(txtUpdate.Text, conn))
{
    cmd.ExecuteNonQuery();
}

Run DELETE Query to Delete Row

// txtDelete.Text:
// DELETE FROM Table_1 WHERE id=2;
//
// removes 2nd row in Table_1
using (OleDbCommand cmd = new OleDbCommand(txtDelete.Text, conn))
{
    cmd.ExecuteNonQuery();
}

Security Diclaimer

...Yep, it is easest (for developer) and quickest way to develop DB client applications - to get SQL queries from TextBoxes.

But, not for user. Because it quite unconvenient, and very unsafe, because allows users use SQL injections to modify or remove DB content! 

Last - normal and even useful, if the database is used only a few people, and any "villain" does not have access to them. But in other cases, you should not do it!

 

 

 

To be continued...

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author


You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 4 Pin
Member 1367329012-Mar-18 2:58
memberMember 1367329012-Mar-18 2:58 
QuestionUse ACE for 64bit/32bit Pin
cjb11012-Jan-15 21:07
membercjb11012-Jan-15 21:07 
GeneralThoughts Pin
PIEBALDconsult12-Jan-15 6:28
protectorPIEBALDconsult12-Jan-15 6:28 
Questioncan we define procedure or function in ms access? Pin
8838813212-Jan-15 2:02
professional8838813212-Jan-15 2:02 
AnswerRe: can we define procedure or function in ms access? Pin
Emiliarge12-Jan-15 2:16
professionalEmiliarge12-Jan-15 2:16 
GeneralRe: can we define procedure or function in ms access? Pin
8838813212-Jan-15 2:21
professional8838813212-Jan-15 2:21 
AnswerRe: can we define procedure or function in ms access? Pin
cjb11012-Jan-15 21:10
membercjb11012-Jan-15 21:10 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01-2016 | 2.8.180810.1 | Last Updated 12 Jan 2015
Article Copyright 2015 by Emiliarge
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid