1、本节目的,完成下图这两个地方功能:
2、添加变量,DVD名字变量。
3、在头文件中添加函数声明:
4、在CPP中添加函数(初始化Combox控件),将函数添加到MFC初始化函数:
void CDVDRentDlg::InitDVDNameComboBox()
{
_RecordsetPtr pDvdNameRecordset;
pDvdNameRecordset.CreateInstance(__uuidof(Recordset));
CString strSQL;
strSQL = _T("SELECT sDVDName FROM tbDVDInfo");
try
{
HRESULT hr = pDvdNameRecordset->Open(_variant_t(strSQL), m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
if (FAILED(hr)) {
return;
}
}
catch (_com_error *e) {
MessageBox(e->ErrorMessage());
return;
}
CString strValue;
_variant_t var;
int iCurrIdx = 0;
m_comboDVD.InsertString(iCurrIdx++, _T(""));
try
{
while (!pDvdNameRecordset->GetadoEof())
{
var = pDvdNameRecordset->GetCollect(_T("sDVDName"));
if (var.vt != NULL) {
strValue = (LPCTSTR)_bstr_t(var);
m_comboDVD.InsertString(iCurrIdx++, strValue);
pDvdNameRecordset->MoveNext();
}
}
pDvdNameRecordset->Close();
pDvdNameRecordset = NULL;
}
catch (_com_error *e) {
MessageBox(e->ErrorMessage());
return;
}
}
5、运行如图:
6、接下里插入查询按钮的时间响应,同样在头文件声明函数,然后在.cpp文件添加函数代码:
void CDVDRentDlg::OnBnClickedButtonQuery()
{
// TODO: 在此添加控件通知处理程序代码
UpdateData(TRUE);
_RecordsetPtr pQueryRecordset;
pQueryRecordset.CreateInstance(__uuidof(Recordset));
CString strDataFrom, strDataTo, strDVDName;
m_comboDVD.GetWindowText(strDVDName);
CString strSQL;
if ((!m_check_Date) && m_name.IsEmpty() && strDVDName.IsEmpty()) {
strSQL = _T("SELECT * FROM tbRentInfo");
}
else {
strSQL = _T("SELECT * FROM tbRentInfo where");
}
if (m_check_Date) {
CTime timeFrom, timeTo;
m_DateFrom.GetTime(timeFrom);
m_DateTo.GetTime(timeTo);
m_DateFrom.GetWindowText(strDataFrom);
m_DateTo.GetWindowText(strDataTo);
if (timeFrom.GetMonth() > timeTo.GetMonth()) {
MessageBox(_T("日期设置错误."));
return;
}
else if (timeFrom.GetMonth() == timeTo.GetMonth()) {
if (timeFrom.GetDay() > timeTo.GetDay()) {
MessageBox(_T("日期设置错误."));
return;
}
}
strSQL.AppendFormat(_T(" sDate>=\'%s\' and sDate<=\'%s\'"), strDataFrom, strDataTo);
}
if (!m_name.IsEmpty()) {
if (m_check_Date) {
strSQL.AppendFormat(_T(" and sName=\'%s\'"), m_name);
}
else {
strSQL.AppendFormat(_T(" sName=\'%s\'"), m_name);
}
}
if (!strDVDName.IsEmpty()) {
if ((!m_check_Date) && m_name.IsEmpty()) {
strSQL.AppendFormat(_T(" sDVDID=%s"), QueryDVDId(strDVDName));
}
else {
strSQL.AppendFormat(_T(" and sDVDID=%s"), QueryDVDId(strDVDName));
}
}
//
//上面构造好SQL查询语句之后,下面就开始查询啦
try
{
HRESULT hr = pQueryRecordset->Open(_variant_t(strSQL), m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
if (FAILED(hr)) {
return;
}
}
catch (_com_error* e)
{
MessageBox(e->ErrorMessage());
return;
}
//判断记录集是否为空
if (pQueryRecordset->GetadoEof()) {
MessageBox(_T("记录集为空。。。"));
return;
}
else {
_variant_t var;
CString strValue;
int curItem = 0;
m_rentInfoList.DeleteAllItems();
try {
while (!pQueryRecordset->GetadoEof()) {
var = pQueryRecordset->GetCollect((long)0);
if (var.vt != NULL)
strValue = (LPCTSTR)_bstr_t(var);
m_rentInfoList.InsertItem(curItem, strValue);
var = pQueryRecordset->GetCollect(_T("sName"));
if (var.vt != NULL)
strValue = (LPCTSTR)_bstr_t(var);
m_rentInfoList.SetItemText(curItem, 1, strValue);
var = pQueryRecordset->GetCollect(_T("sDVDID"));
if (var.vt != NULL)
strValue = (LPCTSTR)_bstr_t(var);
m_rentInfoList.SetItemText(curItem, 2, QueryDVDName(strValue));
var = pQueryRecordset->GetCollect(_T("sDate"));
if (var.vt != NULL)
strValue = (LPCTSTR)_bstr_t(var);
m_rentInfoList.SetItemText(curItem, 3, strValue);
pQueryRecordset->MoveNext();
curItem++;
}
}
catch (_com_error *e) {
MessageBox(e->ErrorMessage());
return;
}
}
pQueryRecordset->Close();
pQueryRecordset = NULL;
}
CString CDVDRentDlg::QueryDVDId(CString& DVDName)
{
HRESULT hr;
_RecordsetPtr pRentRecordset;
hr = pRentRecordset.CreateInstance(__uuidof(Recordset));
if (FAILED(hr)) {
MessageBox(_T("创建记录集对象失败."));
return FALSE;
}
try
{
CString strSQL;
CString strDVDName;
strDVDName=_T("'"+DVDName);
strDVDName = strDVDName + _T("'");
strSQL = _T("select * from tbDVDInfo where sDVDName="+strDVDName);
MessageBox(strSQL);
hr = pRentRecordset->Open(_variant_t(strSQL), m_pConnection.GetInterfacePtr(), adOpenDynamic, adLockOptimistic, adCmdText);
if (SUCCEEDED(hr)) {
_variant_t var;
CString strValue;
var = pRentRecordset->GetCollect(_T("sDVDID"));
if (var.vt != NULL) {
strValue = (LPCTSTR)_bstr_t(var);
MessageBox(strValue);
return strValue;
}
}
else {
MessageBox(_T("打开结果记录集失败."));
return FALSE;
}
}
catch (_com_error &e) {
MessageBox(e.ErrorMessage());
return FALSE;
}
pRentRecordset->Close();
pRentRecordset = NULL;
}
7、运行结果如下:
8、本节工程地址:
链接:https://pan.baidu.com/s/16i5MlvklTdhFZ0tbR4Lb9A
提取码:zeei