数据库SLGO.MDB中 表名:bill, ID:20111010164032(存储的是年月日时分秒)通过这个ID的时间和当前时间比较,大于一个月的都删掉,数据库中只保留一个月的数据。再增加新数据前判断,下面代码是加入新的数据。贴一段我的代码:void CDrugProjectPage::OnBtnNext()
{ 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;
pApp->m_pRecordset->MoveFirst();
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("name",_variant_t(m_nPersonName));
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());
} if( ! m_bEnableNext )
{
AfxMessageBox(_T("只有设定了用药之后,才可以进入运行界面。"), MB_OK|MB_ICONSTOP);
return ;
}
CPropertyPage::OnOK(); CWnd* pwSheet = GetParent();
if( pwSheet )
pwSheet->PostMessage(WM_COMMAND, IDOK, 0);
}
{ 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;
pApp->m_pRecordset->MoveFirst();
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("name",_variant_t(m_nPersonName));
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());
} if( ! m_bEnableNext )
{
AfxMessageBox(_T("只有设定了用药之后,才可以进入运行界面。"), MB_OK|MB_ICONSTOP);
return ;
}
CPropertyPage::OnOK(); CWnd* pwSheet = GetParent();
if( pwSheet )
pwSheet->PostMessage(WM_COMMAND, IDOK, 0);
}
解决方案 »
- 有么有人会:list控件中实现分页显示记录呀?
- Create出来的ListCtrl无法实现自绘(DrawItem)
- 为什么OnTimer在win98运行时,呼快呼慢?而在win2k中正常?
- 用wise制作安装程序,如何添加一个启动程序的快捷方式到桌面?
- 如何实现一个ListCtrl控件上击鼠标右键,弹出菜单,选择add、delete等执行添加、删除等
- 如何知道当前硬盘有几个分区?
- 问一个很菜的问题,快来拿分!!
- 关于非阻塞的问题
- 请问怎么样将.DOC文件转为文件?谢谢!
- 好怪的错误???CStringArray不能做参数吗?
- Failed to create empty document.
- 有一些关于界面的问题想请大家帮忙解答一下
比如取得今天的时间,得到一个月前的时间,那么,用sql语句直接删除应该可以的!好像不需要先选择吧
CTime cc = CTime::GetCurrentTime();
CTime cl(_ttoi(cs.Left(4)),_ttoi(cs.Mid(4,2)),_ttoi(cs.Mid(6,2)),_ttoi(cs.Mid(8,2)),_ttoi(cs.Mid(10,2)),_ttoi(cs.Right(2)) );
CTimeSpan ts = cc - cl;
if ( ts.GetDays() > 30 )
//删除操作
delete FROM bill where id = '20111010164032';
但问题没解决,所以新开了一贴。
先谢谢各位。我用ACCESS数据库,数据库名字SLGO.MDB,有两个表bill,goods.每次运行软件,增加数据时,先判断数据库中的数据是否有超过一个月的数据,有则删除,只保留一个月的数据记录就可以了。
表bill中存储的数据分别是:id,type,time,height。
id :20111031123456 表示2011-10-31 12:34:56time :2011-10-31 12:34:56现在想无论通过id或time ,只要判断数据大月一个数,就删除,不是删除一个数据,是所有大于一个月的数据。刚开始弄ACCESS,请多指教,谢谢。
表bill中存储的数据分别是:id,type,time,height。
id :20111031123456 表示2011-10-31 12:34:56time :2011-10-31 12:34:56/////
谢谢,能否写一下SQL语句,
while(pApp->m_pRecordset->IsEof())
{
result = CurrTime - tempTime;
if ((UINT)result-30)
{
pApp->m_pRecordset->Delete(adAffectCurrent);
pApp->m_pRecordset->Update();
}pApp->m_pRecordset->MoveNext();
}
pApp->m_pRecordset->IsEof()报错,我改为pApp->m_pRecordset->adoEOF,行吗?修改后如下:程序执行不了,编译能通过。 CString stime = "2011-08-10 14:59:49";//m_strBillID1;
COleDateTime CurrTime = COleDateTime::GetCurrentTime();
COleDateTime tempTime;
tempTime.ParseDateTime(stime);
COleDateTimeSpan result = CurrTime - tempTime;
try
{
CString str;
pApp->m_pRecordset->MoveFirst(); pApp->m_pRecordset->MoveFirst();
while(pApp->m_pRecordset->adoEOF)
{
result = CurrTime - tempTime;
if ((UINT)result-30)
{
pApp->m_pRecordset->Delete(adAffectCurrent);
pApp->m_pRecordset->Update();
} 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 %H:%M:%S"));
pApp->m_pRecordset->PutCollect("time",_variant_t(str));
// pApp->m_pRecordset->PutCollect("name",_variant_t(m_nPersonName));
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());
}
头文件:
#import "c:\Program Files\Common Files\System\ADO\msado15.dll" no_namespace rename("EOF","adoEOF")程序文件:
while(pApp->m_pRecordset->adoEOF)
{
.........
}
DELETE bill WHERE datediff('d',(left('ID', 4) + '-' + mid('ID', 5, 2) + '-' + mid('ID', 7, 2)), now()) > 30 //删除大于30天的数据
strSql.Format(_T("DELETE FROM bill WHERE id<=val(format(dateadd('m',-1,now()),'yyyymmddhhnnss'))"));
DELETE FROM bill WHERE datediff('d',(left(ID, 4) + '-' + mid(ID, 5, 2) + '-' + mid(ID, 7, 2)), now()) > 30 //删除大于30天的数据
少打一个FROM