rentdlg.cpp

Visual C++实践与提高－－数据库开发与工程应用篇 随书代码 希望对用ADO开发的人会有所帮助 这是第4、5章的

		//DeleteRecord(rentid,dvdName);
		DeleteRecordTran(rentid,dvdName);
		m_rentInfoList.DeleteItem(sel);		
	}
	else if(sel<0) MessageBox("列表中无选中记录!");	
}


void CRentDlg::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);
			//调用使用存储过程的函数
			int dvdID=QueryDVDIDSP(dvdName);
			CString strDVDID;
			strDVDID.Format("%d",dvdID);
			//修改tbDVDInfo表中Num字段的值
			AddDVDNum(strDVDID);   
		}
	}
	catch (_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
        return ;
	}  
}

void CRentDlg::OnButtonQuery() 
{
    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;
	}    
    //如果用户输入了租借人名,就需要在SQL中加入该条件
	if(!m_name.IsEmpty())
	{
		if(m_check_Date)
		   temp.Format("and Name='%s'",m_name);
		else
			temp.Format("Name='%s'",m_name);
		strSQL+=temp;
	}
	//如果用户选择了影碟名字,需要在SQL命令中加入该条件
	if(!strDVD.IsEmpty())
	{   
		if((!m_check_Date)&&(m_name.IsEmpty()))
			//QueryDVDID函数可以通过影碟的名字获得编号DVDID
			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;
	//删除List控件中所有的数据,以便显示查询结果
	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;
}

//参数：影碟名字(CString)
//返回值：tbDVDInfo表中影碟的编号DVDID(int),若错误返回-1
int CRentDlg::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 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+=1;
		strValue.Format("%d",dvdNum);
		pDVDRecordset->PutCollect("Num",_variant_t(strValue));
		pDVDRecordset->Update();
	  }
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
	}  
	pDVDRecordset->Close();
	pDVDRecordset=NULL;

}

int CRentDlg::QueryDVDIDSP(CString dvdName)
{    
	_CommandPtr pIDCommand;
	pIDCommand.CreateInstance(__uuidof(Command));
	_bstr_t storeproc("getIDbyName");
	_variant_t var;
	CString strValue;
	int IDvalue;
 try{	
	pIDCommand->ActiveConnection=m_pConnection;
	pIDCommand->CommandText=storeproc;
	pIDCommand->CommandType=adCmdStoredProc;
	pIDCommand->Parameters->Refresh();
    pIDCommand->Parameters->Item[_variant_t((long)1)]->Value=_variant_t((LPCTSTR)dvdName);
	_RecordsetPtr pIDRecordset;
	pIDRecordset.CreateInstance(__uuidof(Recordset));
	
    pIDRecordset=pIDCommand->Execute(NULL,NULL,adCmdStoredProc);
	if((pIDRecordset->BOF)&&(pIDRecordset->adoEOF))
	{
		MessageBox("error!");
		pIDRecordset->Close();
		pIDRecordset=NULL;
		return -1;
	}
	var=pIDRecordset->GetCollect("DVDID");
	pIDRecordset->Close();
	pIDRecordset=NULL;

 }
 catch(_com_error *e)
 {
	AfxMessageBox(e->ErrorMessage());
    return -1;
 } 

 if(var.vt != VT_NULL)
	{
	 strValue = (LPCSTR)_bstr_t(var);
	 IDvalue=atoi(strValue);
	 return IDvalue;
	}
   else return-1;
}   


CString CRentDlg::QueryDVDNameSP(CString dvdID)
{
	_CommandPtr pNameCommand;
	pNameCommand.CreateInstance(__uuidof(Command));
	_bstr_t storeproc("getNamebyID");
	_variant_t var;
	long ID=atol(dvdID);
	CString strValue="";
	try{	
		pNameCommand->ActiveConnection=m_pConnection;
		pNameCommand->CommandText=storeproc;
		pNameCommand->CommandType=adCmdStoredProc;
		pNameCommand->Parameters->Refresh();
		pNameCommand->Parameters->Item[_variant_t((long)1)]->Value=_variant_t(ID);
		_RecordsetPtr pNameRecordset;
		pNameRecordset.CreateInstance(__uuidof(Recordset));
		
		pNameRecordset=pNameCommand->Execute(NULL,NULL,adCmdStoredProc);
		if((pNameRecordset->BOF)&&(pNameRecordset->adoEOF))
		{
			MessageBox("error!");
			pNameRecordset->Close();
			pNameRecordset=NULL;
			return strValue;
		}
		var=pNameRecordset->GetCollect("DVDName");
		pNameRecordset->Close();
		pNameRecordset=NULL;
		
	}
	catch(_com_error *e)
	{
		AfxMessageBox(e->ErrorMessage());
		return strValue;
	} 
	
	if(var.vt != VT_NULL)
	{
		strValue = (LPCSTR)_bstr_t(var);	
	}
	return strValue;
	
}

void CRentDlg::OnCheckDate() 
{
	// TODO: Add your control notification handler code here
	if(m_check_Date==FALSE)
	{
		m_check_Date=TRUE;
		m_DateFrom.EnableWindow(TRUE);
		m_DateTo.EnableWindow(TRUE);
	}
	else
	{
        m_check_Date=FALSE;
		m_DateFrom.EnableWindow(FALSE);
		m_DateTo.EnableWindow(FALSE);
	}
}




void CRentDlg::DeleteRecordTran(CString ID, CString dvdName)
{
	_bstr_t vSQL;
	_variant_t RecordsAffected;
	_variant_t var;
	CString strValue;
	int dvdNum;
	_RecordsetPtr	pDVDRecordset;
    pDVDRecordset.CreateInstance(__uuidof(Recordset));
	//获得影碟编号DVDID
	int dvdID=QueryDVDIDSP(dvdName);
	CString strDVDID;
	strDVDID.Format("%d",dvdID);

	vSQL="delete from tbRentInfo where ID="+ID;
	try
	{	m_pConnection->BeginTrans();

		m_pConnection->Execute(_bstr_t(vSQL),&RecordsAffected,adCmdText);
		vSQL="select * from tbDVDInfo where DVDID="+strDVDID;
		pDVDRecordset->Open(vSQL,
			m_pConnection.GetInterfacePtr(),	 
			adOpenDynamic,
			adLockOptimistic,
			adCmdText);
		if((pDVDRecordset->BOF)&&(pDVDRecordset->adoEOF))
		{ MessageBox("error!");
		pDVDRecordset->Close();
		pDVDRecordset=NULL;
		return;
		}
		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();
		}

		m_pConnection->CommitTrans();
	}
	catch(_com_error *e)
	{
		m_pConnection->RollbackTrans();
		AfxMessageBox(e->ErrorMessage());
	}
}