rentdvddlg.cpp

该系统是一个集数据库操作与数据集显示的单机系统

	_variant_t var;
	_bstr_t vSQL;
	vSQL = "SELECT DVDName FROM tbDVDInfo WHERE DVDID="+DVDID;
	try
	{
		HRESULT hr;
		hr=pDVDNameRecordset->Open(vSQL,
			m_pConnection.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
            adCmdText);
		if(!FAILED(hr))
		{
			var=pDVDNameRecordset->GetCollect("DVDName");
			if(var.vt != VT_NULL)
				strValue = (LPCSTR) _bstr_t(var);
		}
		else strValue="";
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		strValue="";
	}
	pDVDNameRecordset->Close();
	pDVDNameRecordset=NULL;
	return strValue;
}


void CRentDVDDlg::InitComboCtr()
{
	_RecordsetPtr pDVDNameRecordset;
	pDVDNameRecordset.CreateInstance(__uuidof(Recordset));
	try
	{
		pDVDNameRecordset->Open("SELECT DVDName FROM tbDVDInfo",
			m_pConnection.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return;
	}
	
	_variant_t var;
	CString strValue;
	//m_comboDVD.AddString("");
	try
	{
		while(!pDVDNameRecordset->ADOEOF)
		{
			var=pDVDNameRecordset->GetCollect("DVDName");
			if(var.vt != VT_NULL)
				strValue = (LPCSTR) _bstr_t(var);
			m_comboDVD.AddString(strValue);
			pDVDNameRecordset->MoveNext();
		}
		pDVDNameRecordset->Close();
		pDVDNameRecordset=NULL;
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}
}


void CRentDVDDlg::OnButtonQuery() 
{
	// TODO: Add your control notification handler code here
	GetDlgItem(IDC_STATIC1)->SetWindowText("查询结果");
	UpdateData(TRUE);
	_RecordsetPtr pQueryRecordset;
	pQueryRecordset.CreateInstance(__uuidof(Recordset));
    CString strDateFrom,strDateTo,strDVD;
	CString strSQL,temp;
    m_comboDVD.GetWindowText(strDVD);
	if((!m_check_Date)&&(m_Name.IsEmpty())&&(strDVD.IsEmpty()))
		strSQL="select * from tbRentInfo";
	else
		strSQL = "select * from tbRentInfo where ";
	
	if(m_check_Date)
	{
		CTime timeFrom,timeTo;
		m_dateTimerPicker_From.GetTime(timeFrom);
		m_dateTimerPicker_To.GetTime(timeTo);
		m_dateTimerPicker_From.GetWindowText(strDateFrom);
		m_dateTimerPicker_To.GetWindowText(strDateTo);
		
		if(timeFrom.GetYear()>timeTo.GetYear())
		{
			MessageBox("Date set was wrong!");
			return;
		}
		else if(timeFrom.GetYear()==timeTo.GetYear())
		{
		if(timeFrom.GetMonth()>timeTo.GetMonth())
		{
			MessageBox("Date set was wrong!");
			return;
		}
		else if(timeFrom.GetMonth()==timeTo.GetMonth())
		{
			if(timeFrom.GetDay()>timeTo.GetDay())
		{
			MessageBox("Date was set incorrectly!");
			return;
		}
		}
		}
		temp.Format("Date>='%s' and Date<='%s'",strDateFrom,strDateTo);
		strSQL+=temp;
	}
	
	if(!m_Name.IsEmpty())
	{
		if(m_check_Date)
			temp.Format("and Name='%s'",m_Name);
		else
			temp.Format("Name='%s'",m_Name);
		strSQL+=temp;
	}
	
	if(!strDVD.IsEmpty())
	{
		if((!m_check_Date)&&(m_Name.IsEmpty()))
			temp.Format("DVDID=%d",QueryDVDID(strDVD));
		else
			temp.Format("and DVDID=%d",QueryDVDID(strDVD));
		strSQL+=temp;
	}
	
	try//错误点
	{
		pQueryRecordset->Open(_variant_t(strSQL),
			m_pConnection.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return ;
	}//错误点
	
	if((pQueryRecordset->BOF)&&(pQueryRecordset->ADOEOF))
		MessageBox("There is no records!");
	else
	{
		_variant_t var;
		CString strValue;
		int curItem=0;
		m_listctl_RentInfo.DeleteAllItems();
		try
		{
			while(!pQueryRecordset->ADOEOF)
			{
				var = pQueryRecordset->GetCollect("ID");
				if(var.vt != VT_NULL)
					strValue = (LPCSTR)_bstr_t(var);
				m_listctl_RentInfo.InsertItem(curItem,strValue);
				var = pQueryRecordset->GetCollect("Name");
				if(var.vt != VT_NULL)
					strValue = (LPCSTR)_bstr_t(var);
				m_listctl_RentInfo.SetItemText(curItem,1,strValue);
				var = pQueryRecordset->GetCollect("DVDID");
				if(var.vt != VT_NULL)
					strValue = (LPCSTR)_bstr_t(var);
				m_listctl_RentInfo.SetItemText(curItem,2,QueryDVDName(strValue));
				var = pQueryRecordset->GetCollect("Date");
				if(var.vt != VT_NULL)
					strValue = (LPCSTR)_bstr_t(var);
				m_listctl_RentInfo.SetItemText(curItem,3,strValue);
				
				pQueryRecordset->MoveNext();
				curItem++;
			}
		}
		catch(_com_error *e)
		{
			AfxMessageBox(e->ErrorMessage());
		}
	}
	pQueryRecordset->Close();
	pQueryRecordset=NULL;
}


int CRentDVDDlg::QueryDVDID(CString dvdName)
{
	_RecordsetPtr pDVDIDRecordset;
	pDVDIDRecordset.CreateInstance(__uuidof(Recordset));

	int value;
	CString strSQL,strValue;
	strSQL.Format("select DVDID from tbDVDInfo where DVDName='%s'",dvdName);
	try
	{
		pDVDIDRecordset->Open(_variant_t (strSQL),
			m_pConnection.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		value=-1;
		return value;
	}
	_variant_t var;
	var = pDVDIDRecordset->GetCollect("DVDID");
	if(var.vt != VT_NULL)
	{
		strValue = (LPCSTR) _bstr_t(var);
		value=atoi(strValue);
	}
	else
		value=-1;

	pDVDIDRecordset->Close();
	pDVDIDRecordset=NULL;
	return value;
}


void CRentDVDDlg::DeleteRecord(CString ID, CString dvdName)
{
	HRESULT hr;
	_bstr_t vSQL;
	vSQL="delete from tbRentInfo where ID="+ID;
	_variant_t RecordsAffected;
	try
	{
		hr=m_pConnection->Execute(_bstr_t(vSQL),
			&RecordsAffected,adCmdText);
		if(SUCCEEDED(hr))
		{
			int dvdID=QueryDVDID(dvdName);
			CString strDVDID;
			strDVDID.Format("%d",dvdID);
			AddDVDNum(strDVDID);
		}
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return;
	}

}


void CRentDVDDlg::OnButtonDele() 
{
	// TODO: Add your control notification handler code here
	int sel = m_listctl_RentInfo.GetSelectionMark();
	if(sel>=0 && AfxMessageBox("是否删除？",MB_YESNO)==IDYES)
	{
		CString rentID=m_listctl_RentInfo.GetItemText(sel,0);
		CString dvdName=m_listctl_RentInfo.GetItemText(sel,2);
		
		DeleteRecord(rentID,dvdName);
		
		m_listctl_RentInfo.DeleteItem(sel);
	}
	else if(sel<0) 
		MessageBox("请选择要删除的项!");
}


void CRentDVDDlg::AddDVDNum(CString strDVDID)
{
	_RecordsetPtr pDVDRecordset;
	pDVDRecordset.CreateInstance(__uuidof(Recordset));
	_bstr_t vSQL;
	vSQL="select * from tbDVDInfo where DVDID="+strDVDID;
	try
	{
		pDVDRecordset->Open(vSQL,
			m_pConnection.GetInterfacePtr(),
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return;
	}

	if((pDVDRecordset->BOF)&&(pDVDRecordset->ADOEOF))
	{
		MessageBox("error!");
		pDVDRecordset->Close();
		pDVDRecordset=NULL;
		return;
	}
	_variant_t var;
	CString strValue;
	int dvdNum;
	try
	{
		var=pDVDRecordset->GetCollect("Num");
		if(var.vt != VT_NULL)
		{
			strValue = (LPCSTR)_bstr_t(var);
			dvdNum = atoi(strValue);
			dvdNum+=1;
			strValue.Format("%d",dvdNum);
			pDVDRecordset->PutCollect("Num",_variant_t(strValue));
			pDVDRecordset->Update();
		}
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}

	pDVDRecordset->Close();
	pDVDRecordset=NULL;
}