请教:VC6+ADO+ACCESS 删除大于一个月的数据。
3xcom 2011-10-20 01:11:45 通过日期判断,如果存储的数据超过一个月,则删除一个月前的数据,只保留一个月内的数据。
代码如下:
第一部分:初始化
//初始化com环境
::CoInitialize(NULL);
//创建连接对象和记录集对象
HRESULT hr;
try
{
hr = m_pConnection.CreateInstance("ADODB.Connection");//创建Connection对象
if(SUCCEEDED(hr))
{
hr = m_pConnection->Open(
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Slgo.mdb",
"","",adModeUnknown);///连接数据库
///上面一句中连接字串中的Provider是针对ACCESS2000环境的,
//对于ACCESS97,需要改为:Provider=Microsoft.Jet.OLEDB.3.51
}
}
catch(_com_error e)///捕捉异常
{
CString errormessage;
errormessage.Format(_T("连接数据库失败!\r\n错误信息:%s"),e.ErrorMessage());
AfxMessageBox(errormessage);///显示错误信息
}
m_pRecordset.CreateInstance(__uuidof(Recordset));
第二部分:
CString Temp_m_strBillID;
CString strSql;
CTciApp* pApp = (CTciApp*)AfxGetApp();
m_strBillID1=m_nPersonNo;
//判断单据号是否重复
strSql.Format(_T("SELECT * FROM bill where id = '%s'"),m_strBillID1);
HRESULT hr = pApp->m_pRecordset->Open(strSql.AllocSysString(),
pApp->m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
if(!SUCCEEDED(hr))
{
AfxMessageBox(_T("打开bill表出错"));
return;
}
if(!pApp->m_pRecordset->adoEOF)
{
AfxMessageBox(_T("已经存在该单据编号的记录"));
pApp->m_pRecordset->Close();
return;
}
pApp->m_pRecordset->Close();
//新增一条记录
strSql = _T("SELECT * FROM bill");
hr = pApp->m_pRecordset->Open(strSql.AllocSysString(),
pApp->m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
if(!SUCCEEDED(hr))
{
AfxMessageBox(_T("打开bill表出错"));
return;
///
}
try
{
CString str;
// COleDateTimeSpan result;
//
// COleDateTime CurrTime = COleDateTime::GetCurrentTime();
// COleDateTime tempTime;
// tempTime.ParseDateTime(m_strTimer);
pApp->m_pRecordset->MoveFirst();
// while(((UINT)result-30))
// {
// m_strTimer = (char*)(_bstr_t)pApp->m_pRecordset->GetCollect(_variant_t(_T("time")));
//
// result = CurrTime - tempTime;
//
// strSql.Format(_T("delete * FROM bill where time = '%s'"),m_strTimer);
// HRESULT hr = pApp->m_pRecordset->Open(strSql.AllocSysString(),
// pApp->m_pConnection.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
// //delete from 你的表名 where 是否党员='不是的值' and age between 25 and 45
//
//
// pApp->m_pRecordset->MoveNext();
// }
pApp->m_pRecordset->AddNew();
pApp->m_pRecordset->PutCollect("id",_variant_t(m_strBillID1));
pApp->m_pRecordset->PutCollect("type",_variant_t(m_nSectionNo));
str = m_tTime.Format(_T("%Y-%m-%d"));
pApp->m_pRecordset->PutCollect("time",_variant_t(str));
pApp->m_pRecordset->PutCollect("height",_variant_t(tci_person_height));
pApp->m_pRecordset->PutCollect("weight",_variant_t(tci_person_weight));
pApp->m_pRecordset->PutCollect("age",_variant_t(tci_person_age));
if(tci_person_sex==0)
pApp->m_pRecordset->PutCollect("sex",_variant_t(_T("男")));
else
pApp->m_pRecordset->PutCollect("sex",_variant_t(_T("女")));
for(int i=0;i<3;i++)
{
CRunChannel* prunChan1 = g_pRunTci->GetRunChannel(i);
const StTciRunParam* pRunParam1 = prunChan1->GetRunParam();
const StTciRunParam* m_pRunParam=g_pRunTci->GetRunChannel(0)->GetRunParam();
const DrugInfo* pDI1 = g_pDrugInfo->GetDrugInfo(pRunParam1->iDrugNameID);
const KineticInfo* pKI1 = g_pDrugInfo->GetKineticInfo(pRunParam1->iDrugNameID, pRunParam1->iKineticID);
switch(i)
{
case 0:
{
pApp->m_pRecordset->PutCollect("FirstChnlmode",_variant_t(g_szV_Mode[pRunParam1->iMode]));
pApp->m_pRecordset->PutCollect("FirstChnldrug",_variant_t(pDI1->szName));
pApp->m_pRecordset->PutCollect("FirstChnlmodel",_variant_t(pKI1->szName));
pApp->m_pRecordset->PutCollect("FirstChnlconc",_variant_t(pRunParam1->tci_syringe_drug_conc));
pApp->m_pRecordset->PutCollect("FirstChnltarget",_variant_t(m_pRunParam->tci_conc_SP));
}break;
case 1:
{
pApp->m_pRecordset->PutCollect("SecondChnlmode",_variant_t(g_szV_Mode[pRunParam1->iMode]));
pApp->m_pRecordset->PutCollect("SecondChnldrug",_variant_t(pDI1->szName));
pApp->m_pRecordset->PutCollect("SecondChnlmodel",_variant_t(pKI1->szName));
pApp->m_pRecordset->PutCollect("SecondChnlconc",_variant_t(pRunParam1->tci_syringe_drug_conc));
pApp->m_pRecordset->PutCollect("SecondChnltarget",_variant_t(m_pRunParam->tci_conc_SP));
}
break;
case 2:
{
pApp->m_pRecordset->PutCollect("ThirdChnlmode",_variant_t(g_szV_Mode[pRunParam1->iMode]));
pApp->m_pRecordset->PutCollect("ThirdChnldrug",_variant_t(pDI1->szName));
pApp->m_pRecordset->PutCollect("ThirdChnlmodel",_variant_t(pKI1->szName));
pApp->m_pRecordset->PutCollect("ThirdChnlconc",_variant_t(pRunParam1->tci_syringe_drug_conc));
pApp->m_pRecordset->PutCollect("ThirdChnltarget",_variant_t(m_pRunParam->tci_conc_SP));
}
break;
default:break;
}
}
pApp->m_pRecordset->Update();
pApp->m_pRecordset->Close();
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}