rentdlg.cpp

这个一个VC连接MSSQL数据库的小软件

	_RecordsetPtr pDVDNameRecordset;
	pDVDNameRecordset.CreateInstance(__uuidof(Recordset));
	CString strValue;
	_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 CRentDlg::InitComboCtr()
{
	_RecordsetPtr pDVDNameRecordset;
	pDVDNameRecordset.CreateInstance(__uuidof(Recordset));
	try
	{
		HRESULT hr;
		hr=pDVDNameRecordset->Open("select DVDName from tbDVDInfo",m_pConnection.GetInterfacePtr(),
			adOpenDynamic,adLockOptimistic,adCmdText);
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return;
	}
	CString strValue;
	_variant_t var;
	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 CRentDlg::OnButtonQuery() 
{
	// TODO: Add your control notification handler code here
	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_DateFrom.GetTime(timeFrom);
		m_DateTo.GetTime(timeTo);
		m_DateFrom.GetWindowText(strDateFrom);
		m_DateTo.GetWindowText(strDateTo);
		if(timeFrom.GetMonth()>timeTo.GetMonth())
		{
			MessageBox("Date set is wrong!");
			return;
		}
		else if(timeFrom.GetMonth()==timeTo.GetMonth())
		{
			if(timeFrom.GetDay()>timeTo.GetDay())
			{
				MessageBox("Date set is wrong!");
				return;
			}
		}
		temp.Format(" Date>='%s' and Date<='%s'",strDateFrom,strDateTo);
		strSQL+=temp;
	}
	if(!m_name.IsEmpty())
	{
		if(!m_check_Date)
			temp.Format(" Name='%s'",m_name);
		else
			temp.Format(" and 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_rentInfoList.DeleteAllItems();
		try
		{
			while(!pQueryRecordset->adoEOF)
			{
				var=pQueryRecordset->GetCollect("ID");
				if(var.vt!=VT_NULL)
					strValue=(LPCSTR)_bstr_t(var);
				m_rentInfoList.InsertItem(curItem,strValue);

				var=pQueryRecordset->GetCollect("Name");
				if(var.vt!=VT_NULL)
					strValue=(LPCSTR)_bstr_t(var);
				m_rentInfoList.SetItemText(curItem,1,strValue);

				var=pQueryRecordset->GetCollect("DVDID");
				if(var.vt!=VT_NULL)
					strValue=(LPCSTR)_bstr_t(var);
				m_rentInfoList.SetItemText(curItem,2,QueryDVDName(strValue));

				var=pQueryRecordset->GetCollect("Date");
				if(var.vt!=VT_NULL)
					strValue=(LPCSTR)_bstr_t(var);
				m_rentInfoList.SetItemText(curItem,3,strValue);

				pQueryRecordset->MoveNext();
				curItem++;
			}
		}
		catch(_com_error *e)
		{
			AfxMessageBox(e->ErrorMessage());
		}
	}
	pQueryRecordset->Close;
	pQueryRecordset=NULL;
}

int CRentDlg::QueryDVDID(CString dvdName)
{
	_RecordsetPtr pDVDIDRecordset;
	pDVDIDRecordset.CreateInstance(__uuidof(Recordset));
	CString strSQL,strValue;
	int Value;
	strSQL.Format("select DVDID from tbDVDInfo where DVDName='%s'",dvdName);
	try
	{
		HRESULT hr;
		hr=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 CRentDlg::OnButtonRent() 
{
	// TODO: Add your control notification handler code here
	CRentDVDDlg rentDvdDlg;
	if(rentDvdDlg.DoModal()==IDOK)
		UpdateList();
}

void CRentDlg::UpdateList()
{
	m_rentInfoList.DeleteAllItems();
	InitListCtr();
}


void CRentDlg::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++;
			strValue.Format("%d",dvdNum);
			pDVDRecordset->PutCollect("Num",_variant_t(strValue));
			pDVDRecordset->Update();
		}
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}
	pDVDRecordset->Close();
	pDVDRecordset=NULL;
}

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

void CRentDlg::OnButtonDele() 
{
	// TODO: Add your control notification handler code here
	int sel=m_rentInfoList.GetSelectionMark();
	if(sel>=0&&AfxMessageBox("是否删除?",MB_YESNO)==IDYES)
	{
		CString rentid=m_rentInfoList.GetItemText(sel,0);
		CString dvdName=m_rentInfoList.GetItemText(sel,2);
		DeleteRecord(rentid,dvdName);
		m_rentInfoList.DeleteItem(sel);
	}
	else if(sel<0)
		MessageBox("列表中无选中记录!");
}


void CRentDlg::OnBUTTONStore() 
{
	// TODO: Add your control notification handler code here
	CStoreDlg storedlg;
	storedlg.DoModal();
}