MFC学习（16）对于ACCESS数据库的时间查询

MFC对ACCESS数据库

注意：

1. COleDateTime m_date_time; //可以直接用COleDateTime来接收数据库的时间字段

2. sql查询的时候，时间字符串要用#%s#格式

//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();

	_RecordsetPtr m_pRecordset;
	CString strSql;

	strSql.Format("select * from threshold_set_table");
	m_pRecordset = m_AdoConn.GetRecordSet((_bstr_t)strSql);	

	int counts;
	//while循环负责遍历整个表的每条记录
	while(!(m_pRecordset->rsEOF))
	{
		//取出本条预警设置
		m_gsm_detect_warns.m_table_thr_set.m_str_garden_id = m_pRecordset->GetCollect("garden_id");
		m_gsm_detect_warns.m_table_thr_set.m_period = m_pRecordset->GetCollect("period");
		m_gsm_detect_warns.m_table_thr_set.m_threshold = m_pRecordset->GetCollect("threshold");
		m_gsm_detect_warns.m_table_thr_set.m_str_recv_nums = m_pRecordset->GetCollect("recv_nums");
		m_gsm_detect_warns.m_table_thr_set.m_warn_content = m_pRecordset->GetCollect("warn_content");
		<span style="color:#ff6666;">m_gsm_detect_warns.m_table_thr_set.m_date_time = m_pRecordset->GetCollect("count_start_time");</span>

		//该预警设置下所有果园的小实蝇数量总计
		counts = m_gsm_detect_warns.get_gardens_warn_counts();

		if(counts >= m_gsm_detect_warns.m_table_thr_set.m_threshold)
		{
			//达到预警阈值则发送报警短信
			GsmSendWarnSms();
		}

		//下一条
		m_pRecordset->MoveNext();
	}


	//关闭连接
	m_AdoConn.ExitConnect();

	return 0;

<span style="font-size:18px;">bool DlgViewWarn::show_table(void)
{
	//连接数据库
	ADOConn m_AdoConn;
	m_AdoConn.OnInitADOConn();
	_RecordsetPtr m_pRecordset;

	//取出时间起始点
	m_datetime_start.GetTime(m_view_warn_log.m_time_start);
	m_datetime_end.GetTime(m_view_warn_log.m_time_end);

	//先将时间转换成字符串
	m_view_warn_log.datetime_COle_to_str();

	CString str_sql;
	<span style="color:#ff6666;">str_sql.Format("select * from warn_log_table where date_time >= #%s# and date_time <= #%s# order by date_time asc", m_view_warn_log.m_str_time_start, m_view_warn_log.m_str_time_end);
</span>
	m_pRecordset = m_AdoConn.GetRecordSet((_bstr_t)str_sql);	
	if(m_pRecordset->rsEOF)
	{
		m_list_warn_his.DeleteAllItems();
		TRACE("m_pRecordset->rsEOF\n");
		return false;
	}
	else
	{
		m_list_warn_his.DeleteAllItems();

		int n = 0;
		CString strTemp;

		TableThresholdSet table_thr_set;
		
		while(!m_pRecordset->rsEOF)
		{
			//序号
			strTemp = m_pRecordset->GetCollect("ID");
			m_list_warn_his.InsertItem(n, strTemp);

			//果园名称
			strTemp = m_pRecordset->GetCollect("garden_id");			 
			table_thr_set.m_str_garden_id = strTemp;			 
			//切记：果园id的格式: ,1,2,
			table_thr_set.str_to_arr_int();			 
			table_thr_set.arr_id_to_name_to_show();			 
			m_list_warn_his.SetItemText(n, 1, table_thr_set.m_str_garden_name_to_show);
			 
			//预警周期
			strTemp = m_pRecordset->GetCollect("period");
			m_list_warn_his.SetItemText(n, 2, strTemp);
			 
			//预警阈值
			strTemp = m_pRecordset->GetCollect("threshold");
			m_list_warn_his.SetItemText(n, 3, strTemp);
			 
			//将数据库中的手机号码字符串，更改一下显示的形式，使之美观	
			//切记手机号码的格式: ,15986820071,120,
			strTemp = m_pRecordset->GetCollect("recv_nums");			
			table_thr_set.m_str_recv_nums = strTemp;
			table_thr_set.str_to_arr_str();
			table_thr_set.arr_num_to_num_to_show();
			m_list_warn_his.SetItemText(n, 4, table_thr_set.m_str_recv_nums_to_show);
			 
			//发送日期时间
			m_view_warn_log.m_table_warn_log.m_date_time = m_pRecordset->GetCollect("date_time");
			m_view_warn_log.m_table_warn_log.datetime_COle_to_str();
			m_list_warn_his.SetItemText(n, 5, m_view_warn_log.m_table_warn_log.m_date_time_str);
			 
			//预警短信内容
			strTemp = m_pRecordset->GetCollect("warn_content");
			m_list_warn_his.SetItemText(n, 6, strTemp);

			 

			n++;
			m_pRecordset->MoveNext();
		}
	}

	//关闭连接
	m_AdoConn.ExitConnect();

	return true;
}</span>