mfc搜索新建access字段_MFC利用ADO连接ACCESS数据库及其操作数据库的方法

MFC利用ADO连接ACCESS数据库及其操作数据库的方法

1，  在FileView中的StdAfx.h文件中添加：

#import "C:\Program Files\Common Files\system\ado\msadox.dll"

#import "c:\program files\common files\system\ado\msado15.dll" no_namespace rename("EOF","adoEOF")

2，  在ClassView中的应用程序类ＣXXXXApp中的InitInstance()中添加：

if(!AfxOleInit())

{

AfxMessageBox("OLE初始化出错！");

return FALSE;

}

3，  在需要连接数据库的类中添加：

_ConnectionPtr m_pConnection;

_variant_t RecordsAffected;

_RecordsetPtr m_pRecordset;

4，  连接ACCESS数据库：

try

{

m_pConnection.CreateInstance(__uuidof(Connection));

m_pConnection->Open("Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:\\datasource.mdb","","",adModeUnknown);

}

catch(_com_error e)

{

CString errormessage;

errormessage.Format("连接数据库失败!\r错误信息:%s",e.ErrorMessage());

AfxMessageBox(errormessage);

return;

}

5,操作数据库中的表：

(1)查询操作：

try

{

if (m_tradingname != _T(""))

{

m_pRecordset.CreateInstance("ADODB.Recordset"); //为Recordset对象创建实例

CString strselect;

strselect.Format ("SELECT * FROM %s ", m_tradingname);

m_pRecordset=m_pConnection->Execute(_bstr_t(strselect),&RecordsAffected,adCmdText);

浏览按钮是被按下过，初始化为否

static BOOL bIsCreated = FALSE;

_variant_t tradingday, openprice, highestprice, lowestprice, closeprice, volume;

///当第一次按下浏览按钮时

if ( bIsCreated == FALSE )

{

try

{

while(!m_pRecordset->adoEOF)

{

//vEmployeeID=m_pRecordset->GetCollect(_variant_t((long)0));

tradingday=m_pRecordset->GetCollect("TradingDay");

openprice = m_pRecordset->GetCollect("OpenPrice");

highestprice = m_pRecordset->GetCollect("HighestPrice");

lowestprice = m_pRecordset->GetCollect("LowestPrice");

closeprice = m_pRecordset->GetCollect("ClosePrice");

volume = m_pRecordset->GetCollect("Volume");

CString str;

if(tradingday.vt!=VT_NULL)

{

str+=(LPCTSTR)(_bstr_t)tradingday;

m_list.InsertItem(0, str);

}

if(openprice.vt!=VT_NULL)

{

str =(LPCTSTR)(_bstr_t)openprice;

m_list.SetItemText(0, 1, str);

}

if(highestprice.vt!=VT_NULL)

{

str =(LPCTSTR)(_bstr_t)highestprice;

m_list.SetItemText(0, 2, str);

}

if(lowestprice.vt!=VT_NULL)

{

str =(LPCTSTR)(_bstr_t)lowestprice;

m_list.SetItemText(0, 3, str);

}

if(closeprice.vt!=VT_NULL)

{

str =(LPCTSTR)(_bstr_t)closeprice;

m_list.SetItemText(0, 4, str);

}

if(volume.vt!=VT_NULL)

{

str =(LPCTSTR)(_bstr_t)volume;

m_list.SetItemText(0, 5, str);

}

m_pRecordset->MoveNext();

}

}

catch(_com_error &e)

{

AfxMessageBox(e.Description());

}

//显示要查询的合约名称

m_showtradingname = m_tradingname;

//显示查询结果的数量

m_number = m_list.GetItemCount ();

UpdateData(FALSE);

bIsCreated = TRUE;

}

else

{

m_list.DeleteAllItems();

bIsCreated = FALSE;

}

m_pRecordset->Close();

m_pRecordset=NULL;

m_pConnection->Close();

m_pConnection=NULL;

}

else

{

MessageBox("请输入合约名称!");

return ;

}

}

catch(_com_error &e)

{

AfxMessageBox(e.Description());

}

(2)修改

try

{

CString dataToModify;

dataToModify.Format("update %s set OpenPrice = '%f' where TradingDay = '%s'", m_tradingname, m_openprice, m_tradingday);  //需要进一步修改

m_pConnection->Execute(_bstr_t(dataToModify),&RecordsAffected,adCmdText);

}

catch(_com_error e)

{

CString errormessage;

errormessage.Format("你修改的数据不符合要求，请确定后重新修改!",e.ErrorMessage());

AfxMessageBox(errormessage);

return;

}

(3)插入

CString strinsert;

strinsert.Format ("insert into %s values(%s, %f,  %f, %f, %f, %d)",m_tradingname,  m_tradingday, m_openprice,  m_highestprice, m_lowestprice, m_closeprice, m_volume);

m_pRecordset=m_pConnection->Execute(_bstr_t(strinsert),&RecordsAffected,adCmdText);

MessageBox("输入成功输入!");

if(m_pConnection->State)

{

m_pConnection->Close();

}

(4)删除

try

{

CString dataToDel;

dataToDel.Format("delete from %s where TradingDay = '%s'", m_tradingname, m_strday);

m_pConnection->Execute(_bstr_t(dataToDel),&RecordsAffected,adCmdText);

}

catch(_com_error &e)

{

AfxMessageBox(e.Description());

}

(5)查找数据库表中的表名并且建表

CFileDialog fileDlg(TRUE);

fileDlg.m_ofn .lpstrFilter = "Text FIles(*.txt)\0*.txt\0All FIles(*.*)\0*.*\0\0";

if(IDOK == fileDlg.DoModal ())

{

CString filename = fileDlg.GetFileTitle ();     //获取文件的名字，不带后缀名

ADOX::_CatalogPtr m_pCatalog=NULL;

ADOX::_TablePtr m_pTable=NULL;

CString DBName="Provider=Microsoft.JET.OLEDB.4.0;Data source=C:\\datasource.mdb";

try

{

m_pCatalog.CreateInstance(__uuidof(ADOX::Catalog));

m_pCatalog->PutActiveConnection(_bstr_t(DBName));

int tableCount=m_pCatalog->Tables->Count;

int i=0;

BOOL nflag = true;

while(i

{

m_pTable=(ADOX::_TablePtr)m_pCatalog->Tables->GetItem((long)i);

CString tableName=(BSTR)m_pTable->Name;

if( tableName==filename )

{

nflag = false;

}

i++;

}

if (nflag)

{

MessageBox("合约表不存在，建表！");

try

{

CString strCommand;

strCommand.Format("CREATE TABLE %s(TradingDay Text(16), OpenPrice Integer, HighestPrice Integer, LowestPrice Integer, ClosePrice Integer, Volume Integer)",filename);

m_pConnection->Execute(_bstr_t(strCommand),&RecordsAffected,adCmdText);

MessageBox("创建成功!!");

}

catch(_com_error &e)

{

AfxMessageBox(e.Description());

}

}

}

catch(_com_error &e)

{

AfxMessageBox(e.Description());

return;

}

}

(6)建立数据库

(一)添加：#include "Shlwapi.h"

#pragma comment(lib,"shlwapi.lib")

(二)

CString str;

str= "C:\\"+m_dbName+".mdb";

if(PathFileExists(str))

{

CString strTemp;

strTemp.Format("%s已存在!",str);

AfxMessageBox(strTemp);

return ;

}

ADOX::_CatalogPtr m_pCatalog = NULL;

CString DBName="Provider=Microsoft.JET.OLEDB.4.0;Data source=";

DBName=DBName+str;

try

{

m_pCatalog.CreateInstance(__uuidof(ADOX::Catalog));

m_pCatalog->Create(_bstr_t((LPCTSTR)DBName));

MessageBox("建库成功!!");

}

catch(_com_error &e)

{

AfxMessageBox(e.ErrorMessage());

return ;

}

(7)