database.cpp

连接SQL数据库的系统源码,对于初学者十分有参考价值哦

				v = m_Rsp1->GetCollect("providerid") ;
				m_hListCtrl->SetItemText(0, 11, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("orderform") ;
				m_hListCtrl->SetItemText(0, 12, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("StockName") ;
				m_hListCtrl->SetItemText(0, 13, _bstr_t(v)) ;
			}
			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}

}

void CDatabase::UpdateStockApplyData(char *Department, char *unitprice,char *sum,char *StockName,char *Rebate,char *Purpose,char *providerid,char *orderform,char *proposer,char *Paymoney,char *merchandiseID,char *memo,char *Billmaker,char *Appid)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	try{
		sprintf(m_szSql, "select * from tb_StockApply_main where appid = '%s'", Appid) ;
		_RecordsetPtr m_Rsp ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在，更新
			sprintf(m_szSql, "update tb_StockApply_main set Department = '%s', \
				proposer = '%s', billmaker = '%s', memo = '%s' where appid='%s'",
				Department, proposer,Billmaker,memo,Appid) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_StockApply_main (AppID,Department, proposer,billmaker,memo,AppDate) \
					values ('%s','%s','%s','%s','%s',GetDate())" , Appid,Department, proposer,Billmaker,memo) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		sprintf(m_szSql, "select * from tb_orderform where orderiD = '%s'", orderform) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在，更新
			sprintf(m_szSql, "update tb_orderform set billmaker = '%s', \
				InStored = %s, providerID = '%s', sumtotal = %s, rebate = '%s', paymoney = %s \
				where orderiD='%s'",
				Billmaker, 0,providerid,sum,Rebate,Paymoney,orderform) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_orderform (billmaker, InStored,providerID,sumtotal,\
				rebate,paymoney,orderiD,ordertime) values ('%s',%d,'%s',%s,%s,%s,'%s',GetDate())" , 
					Billmaker, 0,providerid,sum,Rebate,Paymoney,orderform) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}	
		sprintf(m_szSql, "select * from tb_stockApp_sub where appid = '%s'", Appid) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在，更新
			sprintf(m_szSql, "update tb_stockApp_sub set merchandiseID = '%s', \
				unitprice = %f, [sum] = %s, Rebate = %s, purpose = '%s', providerid = '%s', orderform = '%s', StockName = '%s',Paymoney='%s' where appid='%s'",
				merchandiseID, unitprice,sum,Rebate,Purpose,providerid,orderform,StockName,Paymoney,Appid) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_stockApp_sub (merchandiseID, unitprice,sum,Rebate,Purpose,providerid,orderform,StockName,Appid,Paymoney,BestTime) \
					values ('%s',%s,%s,%s,'%s','%s','%s','%s','%s',%s,GetDate())" , 
					merchandiseID, unitprice,sum,Rebate,Purpose,providerid,orderform,StockName,Appid,Paymoney) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}	
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditStockApplyToCtrl(char *m_szAppid, CComboBox *m_hCmbDepartment,CEdit *m_hEditUnitprice,CEdit *m_hEditSum,CEdit *m_hEditStockname,CEdit *m_hEditRebate,CEdit *m_hEditPurpose,CEdit *m_hEditProviderid,CEdit *m_hEditProposer,CEdit *m_hEditPaymoney,CEdit *m_hEditOrderform,CEdit *m_hEditMerchandiseID,CEdit *m_hEditMemo,CEdit *m_hEditBillmaker,CEdit *m_hEditAppid)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	try{
		
		sprintf(m_szSql, "select * from tb_StockApply_main where AppID = '%s'", m_szAppid) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("AppID") ;
			m_hEditAppid->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Department") ;
			m_hCmbDepartment->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("proposer") ;
			m_hEditProposer->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("billmaker") ;
			m_hEditBillmaker->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("memo") ;
			m_hEditMemo->SetWindowText(_bstr_t(v)) ;
		}
		sprintf(m_szSql, "select * from tb_stockApp_sub where AppID = '%s'", m_szAppid) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("merchandiseID") ;
			m_hEditMerchandiseID->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("unitprice") ;
			m_hEditUnitprice->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("sum") ;
			m_hEditSum->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Paymoney") ;
			m_hEditPaymoney->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Rebate") ;
			m_hEditRebate->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("purpose") ;
			m_hEditPurpose->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("providerid") ;
			m_hEditProviderid->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("orderform") ;
			m_hEditOrderform->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("StockName") ;
			m_hEditStockname->SetWindowText(_bstr_t(v)) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}

}

void CDatabase::ListIntostorageToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_intostorage_main") ;
	try{
		
			
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

			v = m_Rsp->GetCollect("ID") ;
			m_hListCtrl->SetItemText(0, 0, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("orderform") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("OperatorID") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("intime") ;
			m_hListCtrl->SetItemText(0, 3, _bstr_t(v)) ;

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateIntostorageData(char *ID,char *OperatorID,char *Orderform)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	try{
		sprintf(m_szSql, "select * from tb_intostorage_main where ID = '%s'", ID) ;
		_RecordsetPtr m_Rsp ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在，更新
			sprintf(m_szSql, "update tb_intostorage_main set OperatorID = '%s', \
				Orderform = '%s' where ID='%s'",OperatorID,Orderform,ID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_intostorage_main (OperatorID,Orderform,ID,intime) \
					values ('%s','%s','%s',GetDate())" , OperatorID,Orderform,ID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}	
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditIntostorageToCtrl(char *ID, CEdit *m_hEditID, CEdit *m_hEditOperatorID, CEdit *m_hEditOrderform)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp ;
	char m_szSql[512] ;
	try{
		
		sprintf(m_szSql, "select * from tb_intostorage_main where ID = '%s'", ID) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{
			v = m_Rsp->GetCollect("ID") ;
			m_hEditID->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("OperatorID") ;
			m_hEditOperatorID->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Orderform") ;
			m_hEditOrderform->SetWindowText(_bstr_t(v)) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::ListCancelinToCtrl(CListCtrl *m_hListCtrl)
{
	m_hListCtrl->DeleteAllItems() ;

	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp,m_Rsp1 ;
	char m_szSql[512] ;
	sprintf(m_szSql, "select * from tb_cancelin_main") ;
	try{
			
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		while(!m_Rsp->GetadoEOF())
		{
			m_hListCtrl->InsertItem(0, "") ;

			char m_szCancelID[30+1] ;
			v = m_Rsp->GetCollect("CancelID") ;
			strcpy(m_szCancelID, _bstr_t(v)) ;
			m_hListCtrl->SetItemText(0, 0, m_szCancelID) ;

			v = m_Rsp->GetCollect("ProviderID") ;
			m_hListCtrl->SetItemText(0, 1, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("Operator") ;
			m_hListCtrl->SetItemText(0, 2, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("principal") ;
			m_hListCtrl->SetItemText(0, 3, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("Rebate") ;
			m_hListCtrl->SetItemText(0, 4, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("SumTotal") ;
			m_hListCtrl->SetItemText(0, 5, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("Paymoney") ;
			m_hListCtrl->SetItemText(0, 6, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("Factmoney") ;
			m_hListCtrl->SetItemText(0, 7, _bstr_t(v)) ;

			v = m_Rsp->GetCollect("CancelTime") ;
			m_hListCtrl->SetItemText(0, 8, _bstr_t(v)) ;

			sprintf(m_szSql, "select * from tb_cancelIn_sub where CancelID = '%s'", m_szCancelID) ;
			m_Rsp1 = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			if(!m_Rsp1->GetadoEOF())
			{
				v = m_Rsp1->GetCollect("merchandiseID") ;
				m_hListCtrl->SetItemText(0, 9, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("unitprice") ;
				m_hListCtrl->SetItemText(0, 10, _bstr_t(v)) ;

				v = m_Rsp1->GetCollect("Cancelnum") ;
				m_hListCtrl->SetItemText(0, 11, _bstr_t(v)) ;
			}

			m_Rsp->MoveNext() ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::UpdateCalcelinData(char *unitprice, char *SumTotal, char *Rebate, char *Providerid, char *principal, char *Paymoney, char *Operator,char *merchandiseID,char *Factmoney,char *Cancelnum,char *CancelID)
{
	char m_szSql[512] ;
	_variant_t v(0L) ;
	try{
		sprintf(m_szSql, "select * from tb_cancelin_main where CancelID = '%s'", CancelID) ;
		_RecordsetPtr m_Rsp ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在，更新
			sprintf(m_szSql, "update tb_cancelin_main set ProviderID = '%s', \
				Operator = '%s', principal = '%s', Rebate = %s, SumTotal = %s, Paymoney = %s,Factmoney=%s \
				where CancelID='%s'",
				Providerid, Operator,principal,Rebate,SumTotal,Paymoney,Factmoney,CancelID) ;
				m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_cancelin_main (ProviderID, Operator,principal,Rebate,SumTotal,Paymoney,Factmoney,CancelID,CancelTime) \
					values ('%s','%s','%s',%s,%s,%s,%s,'%s',GetDate())" , 
					Providerid, Operator,principal,Rebate,SumTotal,Paymoney,Factmoney,CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
		sprintf(m_szSql, "select * from tb_cancelIn_sub where CancelID = '%s'", CancelID) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{//存在，更新
			sprintf(m_szSql, "update tb_cancelIn_sub set merchandiseID = '%s', \
				Rebate = %s, unitprice = '%s', Cancelnum = %s, paymoney = '%s' \
				where CancelID='%s'",
				merchandiseID, Rebate,unitprice,Cancelnum,Paymoney,CancelID) ;
		}
		else
		{//不存在数据，增加
			sprintf(m_szSql, "insert into tb_cancelIn_sub (merchandiseID, Rebate,unitprice,Cancelnum,Paymoney,CancelID) \
				values ('%s',%s,%s,%s,%s,'%s')" , 
					merchandiseID, Rebate,unitprice,Cancelnum,Paymoney,CancelID) ;
			m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}

void CDatabase::EditCancelinToCtrl(char *m_szCancelID, CEdit *m_hEditunitprice,CEdit *m_hEditSumTotal,CEdit *m_hEditRebate,CEdit *m_hEditProviderid,CEdit *m_hEditprincipal,CEdit *m_hEditPaymoney,CEdit *m_hEditOperator,CEdit *m_hEditmerchandiseID,CEdit *m_hEditFactmoney,CEdit *m_hEditCancelnum,CEdit *m_hEditCancelID)
{
	_variant_t v(0L) ;
	_RecordsetPtr m_Rsp,m_Rsp1 ;
	char m_szSql[512] ;
	try{
		
		sprintf(m_szSql, "select * from tb_cancelin_main where CancelID = '%s'", m_szCancelID) ;
		m_Rsp = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
		if(!m_Rsp->GetadoEOF())
		{
			char m_szCancelID[30+1] ;
			v = m_Rsp->GetCollect("CancelID") ;
			strcpy(m_szCancelID, _bstr_t(v)) ;
			m_hEditCancelID->SetWindowText(m_szCancelID) ;

			v = m_Rsp->GetCollect("ProviderID") ;
			m_hEditProviderid->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Operator") ;
			m_hEditOperator->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("principal") ;
			m_hEditprincipal->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Rebate") ;
			m_hEditRebate->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("SumTotal") ;
			m_hEditSumTotal->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Paymoney") ;
			m_hEditPaymoney->SetWindowText(_bstr_t(v)) ;

			v = m_Rsp->GetCollect("Factmoney") ;
			m_hEditFactmoney->SetWindowText(_bstr_t(v)) ;
			
			sprintf(m_szSql, "select * from tb_cancelIn_sub where CancelID = '%s'", m_szCancelID) ;
			m_Rsp1 = m_Connection->Execute(_bstr_t(m_szSql), &v, adCmdText) ;
			if(!m_Rsp1->GetadoEOF())
			{
				v = m_Rsp1->GetCollect("merchandiseID") ;
				m_hEditmerchandiseID->SetWindowText(_bstr_t(v)) ;

				v = m_Rsp1->GetCollect("unitprice") ;
				m_hEditunitprice->SetWindowText(_bstr_t(v)) ;

				v = m_Rsp1->GetCollect("Cancelnum") ;
				m_hEditCancelnum->SetWindowText(_bstr_t(v)) ;

			}
		}
	}
	catch(_com_error & e) 
	{
		char m_szTmp[1024] ;
		sprintf(m_szTmp, "执行==>%s<==, 数据库操作失败,错误原因：%s\n",m_szSql, LPCTSTR(e.Description()));
	}
}