VC + + Access database operations (query, INSERT, UPDATE, DELETE, etc.)

Microsoft Office Access is made up of Microsoft published by relational database management system . Access database is often used in small software systems, such as: production management , Sales Management , Inventory management and other types of enterprise management software, its greatest advantage is: easy to learn, flexible use.

Below we combined with examples to detail, in VC + + MFC, how to use Access database files for data storage, how to achieve database data query, INSERT, UPDATE and delete operations.

(the instance can be downloaded in my CSDN resource: http://download.csdn.net/detail/margin1988/8235865)

First, how do you create an Access database that can be used by VC + + MFC programs and create a data table in that database?

First step: Open Microsoft Office Access software, click "Blank Database";

Step Two: Set the file name and file type of the pre-created blank database (filename: point32.mdb, file type: Microsoft Office Access 2000 database (*.mdb) );

The third step: "Create" a blank database;

Fourth step: Set the database password for the database (in this case the password is set to: 1234);

Fifth step: Create a table in the database, for example: Testtab ( numbering , name, gender, age);

Sixth step: After the table is created, save and close the database, and then set the database file ( Point32.mdb cut into your VC + + program debug or release directory, the preparation is complete.

second, in the VC + + MFC written to the database testtab table for data query, INSERT, UPDATE, delete and other operations methods:

(1) Import the DLLs required by the Access database in order to access it in ADO mode

#import "C:\Program Files\Common Files\system\ado\msado15.dll" no_namespace rename ("EOF", "adoeof")// ADO accesses Access database required

(2) in the entry function of the program, initialize OLE to support the application

AfxOleInit ();

(3) Get the path of the application (EXE)

CString path;//application is located on the path Char filepath[256];char* Ppath; GetModuleFileName (AfxGetInstanceHandle (), filepath,256);pP ath  = STRRCHR (filepath, ' \ \ '); *ppath = 0;path = FilePath

(4) creating A database access connection string

char* ptconnectstr;//Database connection string CString connstr =  "Provider=Microsoft.Jet.OLEDB.4.0;Data source="; connstr + = path; ConnStr + = "\\point32.mdb"; connstr + = "; Jet oledb:database password= ' 1234 ' "; Ptconnectstr = ConnStr. GetBuffer (0);

(5) query Testtab table data method implementation

Query the data in the table and display it in the list control control in void Cpoint32dlg::readuserinfo () {//selectm_list. Deleteallitems ();//Clear list _connectionptr m_pconnection;_recordsetptr m_precordset;try{m_pconnection.createinstance ( __uuidof (Connection)); M_pconnection->open (Ptconnectstr, "", "", adModeUnknown);} catch (_com_error e) {CString errormessage;errormessage. Format ("database connection failed. \ r Error Message:%s", E.errormessage ());//afxmessagebox (errormessage); MessageBox (errormessage, "Connection Failed", mb_iconexclamation); if (m_pconnection->state) m_pconnection->close (); return ;} try{//get the data and put it CString cmd;cmd in the data set. Format ("SELECT * from Testtab"); M_precordset.createinstance ("ADODB. Recordset "); M_precordset->open (cmd. GetBuffer (), _variant_t ((idispatch*) m_pconnection,true), adopenstatic,adlockoptimistic,adcmdtext);//processing data and displaying _ variant_t Varbuffer;long index = 0;//Note: Must be a long type int countitem = 0; CString Str;while (!m_precordset->adoeof) {index = 0;//Read ID number varbuffer = M_precordset->getcollect (_variant_t ( index)); if (varbuffer.vt!=vt_null) {str. Format ("%d", varbuffer.lval); m_List. InsertItem (countitem,str. GetBuffer ());} Read other information while (Index < 3) {Index++;varbuffer = M_precordset->getcollect (_variant_t (index)); if (VARBUFFER.VT!=VT _null) {str = (LPCTSTR) (_bstr_t) varbuffer;m_list. Setitemtext (countitem,index,str. GetBuffer ());}} M_precordset->movenext (); countitem++;}} catch (_com_error &e) {//afxmessagebox (e.description ()); MessageBox (E.description (), "database operation failed.", mb_iconexclamation); if (m_precordset->state) m_precordset->close (); if (m_pconnection->state) m_pconnection->close (); return;} if (m_precordset->state) m_precordset->close (); if (m_pconnection->state) M_pconnection->close ();}

(6) inserting data into the Testtab table to implement the method

Inserts data into the table and updates the data displayed in the list control control void Cpoint32dlg::onbnclickedbutton1 () {//insert_connectionptr m_pconnection;_ variant_t recordsaffected;try{m_pconnection.createinstance (__uuidof (Connection)); M_pconnection->open ( Ptconnectstr, "", "", adModeUnknown);} catch (_com_error e) {CString errormessage;errormessage. Format ("database connection failed. \ r Error Message:%s", E.errormessage ()); MessageBox (ErrorMessage, "Add Failed", mb_iconexclamation); return;} Try{cstring strcmd= "INSERT into Testtab (uname,ugender,uage) VALUES (' tester ', ' male ', ' ') '; for (int i=0;i<5;i++) {m_ Pconnection->execute (Strcmd.allocsysstring (), &recordsaffected,adcmdtext);}} catch (_com_error &e) {//afxmessagebox (e.description ()); MessageBox (E.description (), "Add Failure", mb_iconexclamation), if (m_pconnection->state) M_pconnection->close (); r Eturn;} if (m_pconnection->state) m_pconnection->close ();//messagebox ("Add succeeded!", "message"); M_update. EnableWindow (TRUE); M_delete. EnableWindow (TRUE); Readuserinfo ();}

(7) update The data method implementation in the Testtab table

Updates the data in the table and updates the display of the list control control void Cpoint32dlg::onbnclickedbutton3 () {//Update_connectionptr m_pconnection;_variant _t recordsaffected;try{m_pconnection.createinstance (__uuidof (Connection)); M_pconnection->open (PtConnectStr, " "," ", adModeUnknown);} catch (_com_error e) {CString errormessage;errormessage. Format ("database connection failed. \ r Error Message:%s", E.errormessage ()); MessageBox (errormessage, "       modification failed       ", mb_iconexclamation); return;} Try{cstring strcmd= "UPDATE testtab SET [ugender]= ' female ', [uage]= ' WHERE [uname]= ' tester ']; M_pconnection->execute ( Strcmd.allocsysstring (), &recordsaffected,adcmdtext);} catch (_com_error &e) {//afxmessagebox (e.description ()); MessageBox (E.description (), "       Modify failed       ", mb_iconexclamation), if (m_pconnection->state) m_pconnection-> Close (); return;} if (m_pconnection->state) m_pconnection->close ();//messagebox ("Modified successfully!", "message"); Readuserinfo ();}

(8) Delete The data in the Testtab table and reset the auto number primary key (key) method in the table reality

Delete the data in the table, reset the AutoNumber (starting with 1), and update the list control control to show void Cpoint32dlg::onbnclickedbutton4 () {//Delete_connectionptr m_ pconnection;_variant_t recordsaffected;try{m_pconnection.createinstance (__uuidof (Connection)); m_pConnection- >open (Ptconnectstr, "", "", adModeUnknown);} catch (_com_error e) {CString errormessage;errormessage. Format ("Connection database failed!\r error message:%s", E.errormessage ()); MessageBox (errormessage, "Delete failed", mb_iconexclamation); return;} try{//Delete all data in the table CString strcmd= "delete from Testtab"; M_pconnection->execute (strcmd.allocsysstring (),& Recordsaffected,adcmdtext);//Resets the auto-numbering ID in the table to increase it from 1 (all data in the table must be deleted first) strcmd= "ALTER TABLE TESTTAB alter COLUMN ID COUNTER ( ) "; M_pconnection->execute (Strcmd.allocsysstring (), &recordsaffected,adcmdtext);} catch (_com_error &e) {//afxmessagebox (e.description ()); MessageBox (E.description (), "Delete failed", mb_iconexclamation), if (m_pconnection->state) m_pconnection->close (); return;} if (m_pconnection->state) m_pconnection->close ();//messagebox ("Delete succeeded!", "done"); M_insert. EnableWindow (TRUE); m_update. EnableWindow (FALSE); M_delete. EnableWindow (FALSE); Readuserinfo ();}

VC + + Access database operations (query, INSERT, UPDATE, DELETE, etc.)