1.select
sql="select * from Group_Operator WHERE GroupName = '"+str_GroupName+"'";
sql="select * from GroupList";
2.update
sql.Format("UPDATE %s SET SendDelayTime = '" "%d" "' WHERE id = %d ","AlarmList",i,i_ID);
sql.Format("UPDATE %s SET CountDown = '" "%d" "' WHERE id = %d ","AlarmList",i_CountDownSet,i_ID);
sql.Format("UPDATE AlarmList SET ID = '" "%d" "' WHERE TagName = '" "%s" "' ",i,str_TagName);
3.时间查询
4.delete
sql="delete from Group_Operator WHERE OperatorName = '"+str_Operator_In_Group+"'";
sql+=" and GroupName ='"+str_combo1+"'";
使用字符串变量的地方需要用单引号标记起来,如 '"+var+" '
5.
直接访问
str_temp=(char*)(_bstr_t)m_pRecordset->GetCollect ("GroupName");
使用变量CString str_field="GroupName";
str_temp=(char*)(_bstr_t)m_pRecordset->GetCollect(_variant_t(CString(str_field)));
索引变量
str=(char*)(_bstr_t)pRecordset->GetCollect(_variant_t(long(ii)));
6.
sql="SELECT COUNT (*) AS counts FROM AlarmList";
m_pRecordset_2 = m_pConnection->Execute((_bstr_t)sql,NULL,adCmdText);
int i_count=m_pRecordset_2-> GetCollect("counts").intVal; //取得记录的总数目
TRACE("%d",i_count);
sql="select * from Group_Operator WHERE GroupName = '"+str_GroupName+"'";
sql="select * from GroupList";
2.update
sql.Format("UPDATE %s SET SendDelayTime = '" "%d" "' WHERE id = %d ","AlarmList",i,i_ID);
sql.Format("UPDATE %s SET CountDown = '" "%d" "' WHERE id = %d ","AlarmList",i_CountDownSet,i_ID);
sql.Format("UPDATE AlarmList SET ID = '" "%d" "' WHERE TagName = '" "%s" "' ",i,str_TagName);
3.时间查询
void CSearchDlg::OnButton1()
{
CMDIFrameWnd *pFrame = (CMDIFrameWnd*)AfxGetApp()->m_pMainWnd;//主框架指针
CMDIChildWnd *pChild = (CMDIChildWnd *) pFrame->GetActiveFrame();//子框架指针
CMyListView *pView = (CMyListView *) pChild->GetActiveView();//listview视
CListCtrl *p=&pView->GetListCtrl();
UpdateData(TRUE);
CTime time;
CString stry,strm,strd,date1,date2;
CString strhour,strmin,strsec,time1,time2;
m_date1.GetTime(time);
stry.Format("%d",time.GetYear());
strm.Format("%d",time.GetMonth());
strd.Format("%d",time.GetDay());
date1=stry+"-"+strm+"-"+strd;
TRACE("date1=%s",date1);
m_date2.GetTime(time);
stry.Format("%d",time.GetYear());
strm.Format("%d",time.GetMonth());
strd.Format("%d",time.GetDay());
date2=stry+"-"+strm+"-"+strd;
TRACE("date2=%s",date2);
m_time1.GetTime(time);
strhour.Format("%d",time.GetHour());
strmin.Format("%d",time.GetMinute());
strsec.Format("%d",time.GetSecond());
time1=" "+strhour+":"+strmin+":"+strsec;
TRACE("time1=%s",time1);
m_time2.GetTime(time);
strhour.Format("%d",time.GetHour());
strmin.Format("%d",time.GetMinute());
strsec.Format("%d",time.GetSecond());
time2=" "+strhour+":"+strmin+":"+strsec;
TRACE("time2=%s",time2);
CString str_combo1,str_combo2;
m_combo1.GetWindowText(str_combo1);
m_combo2.GetWindowText(str_combo2);
try{
CString sql;
//在aceess的时间字段设置为时间类型,具体的可以是日期比如2012-01-1,可以是时间比如17:44:02,也可以是两者的组合比如2012-01-1 17:44:02
//然后再vc中加入的时候想acces的表的时间字段中写的时候就需要按照这个格式写入字符串。
//sql查询的时候需要用#代表查询的是时间
//sql="SELECT * FROM my_table WHERE my_date>=#2012-01-1# and my_date<=#2012-01-10#";//不用变量查询
//sql.Format("select*from my_table where my_date>=#" "%s" "# and my_date<= #" "%s" "# ",date1,date2);//format形式只查询日期
//sql="select*from my_table where my_date>=#"+date1+"# and my_date<=#"+date2+"#";//只查询日期
//sql="select*from History where SendTime>=#"+date1+time1+"# and SendTime<=#"+date2+time2+"#";
if (str_combo1=="GroupList")
{
sql="select*from History where SendTime>=#"+date1+time1+"# and SendTime<=#"+date2+time2+"#";
if (str_combo2!="all")
{
sql+=" and GroupName = '"+str_combo2+"'";
}
}
else if (str_combo1=="AlarmList")
{
sql="select*from History where SendTime>=#"+date1+time1+"# and SendTime<=#"+date2+time2+"#";
if (str_combo2!="all")
{
sql+=" and TagName = '"+str_combo2+"'";
}
}
else if (str_combo1=="OperatorList")
{
sql="select*from History where SendTime>=#"+date1+time1+"# and SendTime<=#"+date2+time2+"#";
if (str_combo2!="all")
{
sql+=" and OperatorName = '"+str_combo2+"'";
}
}
TRACE("sql=%s",sql);
pView->display_table(m_pConnection,m_pRecordset,"AlarmList",p,sql);
}
catch(_com_error e)
{
AfxMessageBox(e.Description());
}
}
4.delete
sql="delete from Group_Operator WHERE OperatorName = '"+str_Operator_In_Group+"'";
sql+=" and GroupName ='"+str_combo1+"'";
使用字符串变量的地方需要用单引号标记起来,如 '"+var+" '
5.
直接访问
str_temp=(char*)(_bstr_t)m_pRecordset->GetCollect ("GroupName");
使用变量CString str_field="GroupName";
str_temp=(char*)(_bstr_t)m_pRecordset->GetCollect(_variant_t(CString(str_field)));
索引变量
str=(char*)(_bstr_t)pRecordset->GetCollect(_variant_t(long(ii)));
6.
sql="SELECT COUNT (*) AS counts FROM AlarmList";
m_pRecordset_2 = m_pConnection->Execute((_bstr_t)sql,NULL,adCmdText);
int i_count=m_pRecordset_2-> GetCollect("counts").intVal; //取得记录的总数目
TRACE("%d",i_count);