formview1.cpp

C++编写的数据库课程设计--报刊管理,内容详细

// FORMVIEW1.cpp : 实现文件
//

#include "stdafx.h"
#include "DBDesign.h"
#include "FORMVIEW1.h"
#include ".\formview1.h"


// CFORMVIEW1 对话框

IMPLEMENT_DYNAMIC(CFORMVIEW1, CDialog)
CFORMVIEW1::CFORMVIEW1(CWnd* pParent /*=NULL*/)
	: CDialog(CFORMVIEW1::IDD, pParent)
{
}

CFORMVIEW1::~CFORMVIEW1()
{
}

BEGIN_MESSAGE_MAP(CFORMVIEW1, CDialog)
	ON_BN_CLICKED(IDC_BUTTON1, OnBnClickedButton1)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_LIST1, OnLvnItemchangedList1)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_LIST2, OnLvnItemchangedList2)
	ON_NOTIFY(NM_RCLICK, IDC_LIST1, OnNMRclickList1)
	ON_NOTIFY(NM_RCLICK, IDC_LIST2, OnNMRclickList2)
	ON_COMMAND(ID_32774, OnListMenu0)
	ON_COMMAND(ID_32776, OnListMenu1)
	ON_BN_CLICKED(IDC_BUTTON2, OnBnClickedButton2)
	ON_BN_CLICKED(IDC_BUTTON4, OnBnClickedButton4)
	ON_BN_CLICKED(IDC_BUTTON3, OnBnClickedButton3)
	ON_BN_CLICKED(IDC_BUTTON5, OnBnClickedButton5)
	ON_BN_CLICKED(IDC_BUTTON6, OnBnClickedButton6)
END_MESSAGE_MAP()

void CFORMVIEW1::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	DDX_Control(pDX, IDC_LIST1, m_list0);
	DDX_Control(pDX, IDC_LIST2, m_list1);
}
BOOL CFORMVIEW1::OnInitDialog(void)
{
	BOOL bResult;
	bResult=CDialog::OnInitDialog();

	m_list0.SetExtendedStyle( LVS_EX_GRIDLINES | LVS_EX_FULLROWSELECT | LVS_EX_HEADERDRAGDROP );
	m_list0.InsertColumn(0,"编号",LVCFMT_LEFT,70);
	m_list0.InsertColumn(1,"姓名",LVCFMT_LEFT,150);
	m_list0.InsertColumn(2,"地址",LVCFMT_LEFT,300);

	m_list1.SetExtendedStyle( LVS_EX_GRIDLINES | LVS_EX_FULLROWSELECT | LVS_EX_HEADERDRAGDROP );
	m_list1.InsertColumn(0,"编号",LVCFMT_LEFT,70);
	m_list1.InsertColumn(1,"刊物名称",LVCFMT_LEFT,150);
	m_list1.InsertColumn(2,"刊物类别",LVCFMT_LEFT,150);
	m_list1.InsertColumn(3,"单价",LVCFMT_LEFT,150);

	this->LoadUserData();
	this->LoadNMData();
	return bResult;
}



void CFORMVIEW1::LoadUserData()
{
	_RecordsetPtr pRecord;
	_variant_t value;
	CString temp,sql;
	int i=0;
	// 清空list
	m_list0.DeleteAllItems();
	try
	{
		// 生成Recordset实例
		pRecord.CreateInstance("ADODB.Recordset");

		// 打开记录集，静态光标（静态记录集），乐观锁定方式
		// 查询所有语句
		sql="select cno,name,addr from [CUSTOMER] ORDER BY CNO";
		pRecord->Open(_bstr_t(sql),_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);

		while( !pRecord->adoEOF )
		{
			value = pRecord->GetCollect("cno");
			if(value.vt!=VT_NULL)
			{
				value.ChangeType(VT_BSTR);
				temp=value.bstrVal;
			}
			else
				temp="";
			m_list0.InsertItem(i,temp);

			value = pRecord->GetCollect("name");
			if(value.vt!=VT_NULL)
				temp=value.bstrVal;
			else
				temp="name";
			m_list0.SetItemText(i,1,temp.Trim());

			value = pRecord->GetCollect("addr");
			if(value.vt!=VT_NULL)
				temp=value.bstrVal;
			else
				temp="";
			m_list0.SetItemText(i,2,temp.Trim());
			
			i++;
			pRecord->MoveNext();
		}
		pRecord->Close();
		pRecord = NULL;
	}
	catch( _com_error *e )
	{
		MessageBox( e->ErrorMessage() );
	}

}
//-----------------------------------------
void CFORMVIEW1::LoadNMData()
{
	_RecordsetPtr pRecord;
	_variant_t value;
	CString temp,sql;
	int i=0;
	// 清空list
	m_list1.DeleteAllItems();
	try
	{
		// 生成Recordset实例
		pRecord.CreateInstance("ADODB.Recordset");

		// 打开记录集，静态光标（静态记录集），乐观锁定方式
		// 查询所有语句
		sql="select nmno,title,period,price from [NMTABLE] ORDER BY NMNO";
		pRecord->Open(_bstr_t(sql),_variant_t((IDispatch*)theApp.m_pConnection,true),adOpenStatic,adLockOptimistic,adCmdText);

		while( !pRecord->adoEOF )
		{
			value = pRecord->GetCollect("nmno");
			if(value.vt!=VT_NULL)
			{
				value.ChangeType(VT_BSTR);
				temp=value.bstrVal;
			}
			else
				temp="";
			m_list1.InsertItem(i,temp);

			value = pRecord->GetCollect("title");
			if(value.vt!=VT_NULL)
				temp=value.bstrVal;
			else
				temp="name";
			m_list1.SetItemText(i,1,temp.Trim());

			value = pRecord->GetCollect("period");
			if(value.vt!=VT_NULL)
				temp=value.bstrVal;
			else
				temp="";
			m_list1.SetItemText(i,2,temp.Trim());

			value = pRecord->GetCollect("price");
			if(value.vt!=VT_NULL)
			{
				value.ChangeType(VT_BSTR);
				temp=value.bstrVal;
			}
			else
				temp="";
			m_list1.SetItemText(i,3,temp.Trim());

			i++;
			pRecord->MoveNext();
		}
		pRecord->Close();
		pRecord = NULL;
	}
	catch(...)
	{
		MessageBox("载入数据出错!");
	}
}
// CFORMVIEW1 消息处理程序


void CFORMVIEW1::OnLvnItemchangedList1(NMHDR *pNMHDR, LRESULT *pResult)
{
	LPNMHEADER phdr = reinterpret_cast<LPNMHEADER>(pNMHDR);
	// TODO: 在此添加控件通知处理程序代码
	int i;
	CString temp;

	for(i =0;i<m_list0.GetItemCount();i++ )
	{
		if(m_list0.GetItemState(i,LVIS_SELECTED)==LVIS_SELECTED)//选中状态。。
		{
			break;
		}
	}
	temp=m_list0.GetItemText(i,1);
	GetDlgItem(IDC_EDIT3)->SetWindowText(temp);
	temp=m_list0.GetItemText(i,2);
	GetDlgItem(IDC_EDIT2)->SetWindowText(temp);


	*pResult = 0;
}
void CFORMVIEW1::OnLvnItemchangedList2(NMHDR *pNMHDR, LRESULT *pResult)
{
	LPNMHEADER phdr = reinterpret_cast<LPNMHEADER>(pNMHDR);
	// TODO: 在此添加控件通知处理程序代码
	*pResult = 0;

	int i;
	CString temp;

	for(i =0;i<m_list1.GetItemCount();i++ )
	{
		if(m_list1.GetItemState(i,LVIS_SELECTED)==LVIS_SELECTED)//选中状态。。
		{
			break;
		}
	}

	temp=m_list1.GetItemText(i,1);
	GetDlgItem(IDC_EDIT6)->SetWindowText(temp);
	temp=m_list1.GetItemText(i,2);
	GetDlgItem(IDC_EDIT5)->SetWindowText(temp);
	temp=m_list1.GetItemText(i,3);
	GetDlgItem(IDC_EDIT4)->SetWindowText(temp);
	
}



void CFORMVIEW1::OnNMRclickList1(NMHDR *pNMHDR, LRESULT *pResult)
{
	CPoint oPoint;
	int istat;
	CMenu menu ,*pSubMenu; //定义下面要用到的cmenu对象

	
	istat=m_list0.GetSelectionMark();//用istat存放当前选定的是第几项
	if(istat<0)	return ;

	menu.LoadMenu(IDR_MENU2); //装载自定义的右键菜单
	pSubMenu = menu.GetSubMenu(0);//获取第一个弹出菜单，所以第一个菜单必须有子菜单
	GetCursorPos( &oPoint);//获取当前光标的位置，以便使得菜单可以跟随光标
	pSubMenu->TrackPopupMenu (TPM_LEFTALIGN, oPoint.x, oPoint.y, this); //在指定位置显示弹出菜单

	*pResult = 0;
}

void CFORMVIEW1::OnNMRclickList2(NMHDR *pNMHDR, LRESULT *pResult)
{
	CPoint oPoint;
	int istat;
	CMenu menu ,*pSubMenu; //定义下面要用到的cmenu对象

	
	istat=m_list1.GetSelectionMark();//用istat存放当前选定的是第几项
	if(istat<0)	return ;

	menu.LoadMenu(IDR_MENU3); //装载自定义的右键菜单
	pSubMenu = menu.GetSubMenu(0);//获取第一个弹出菜单，所以第一个菜单必须有子菜单
	GetCursorPos( &oPoint);//获取当前光标的位置，以便使得菜单可以跟随光标
	pSubMenu->TrackPopupMenu (TPM_LEFTALIGN, oPoint.x, oPoint.y, this); //在指定位置显示弹出菜单


	*pResult = 0;
}
void CFORMVIEW1::OnListMenu0()
{
	this->LoadUserData();
}

void CFORMVIEW1::OnListMenu1()
{
	this->LoadNMData();
}



void CFORMVIEW1::OnBnClickedButton2()
{
	CString SQL,temp;
	_CommandPtr m_pCommand;


	GetDlgItem(IDC_EDIT3)->GetWindowText(temp);// cno,name,addr from [CUSTOMER]
	SQL=(CString)"insert into [CUSTOMER](name,addr) values ('" + temp;
	GetDlgItem(IDC_EDIT2)->GetWindowText(temp);
	SQL+="','"+temp+"')";
	try
	{
		m_pCommand.CreateInstance("ADODB.Command");
		m_pCommand->ActiveConnection = theApp.m_pConnection;
		m_pCommand->CommandText = _bstr_t(SQL); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
		//m_pCommand->Release();
	}
	catch(...)
	{
		MessageBox("执行SQL语句出错!");
	}
	this->LoadUserData();
}

void CFORMVIEW1::OnBnClickedButton4()
{
	CString SQL,temp;
	_CommandPtr m_pCommand;


	GetDlgItem(IDC_EDIT6)->GetWindowText(temp);// title,period,price from [NMTABLE]
	SQL=(CString)"insert into [NMTABLE](title,period,price) values ('" + temp;
	GetDlgItem(IDC_EDIT5)->GetWindowText(temp);
	SQL+="','"+temp+"',";
	GetDlgItem(IDC_EDIT4)->GetWindowText(temp);
	SQL+=temp+")";

	try
	{
		m_pCommand.CreateInstance("ADODB.Command");
		m_pCommand->ActiveConnection = theApp.m_pConnection;
		m_pCommand->CommandText = _bstr_t(SQL); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
		//m_pCommand->Release();
	}
	catch(...)
	{
		MessageBox("执行SQL语句出错!");
	}
	this->LoadNMData();
}

void CFORMVIEW1::OnBnClickedButton3()
{
	CString SQL,temp,temp2;
	_CommandPtr m_pCommand;
	int i;

	i=m_list0.GetSelectionMark();
	if(i==-1)
	{
		MessageBox("请先选择要更新的项");
		return;
	}

	GetDlgItem(IDC_EDIT3)->GetWindowText(temp);// cno,name,addr from [CUSTOMER]
	SQL=(CString)"update  [CUSTOMER] set name='" + temp;
	GetDlgItem(IDC_EDIT2)->GetWindowText(temp);
	temp2=m_list0.GetItemText(i,0);
	SQL+="',addr='"+temp+"' where cno="+temp2;
	try
	{
		m_pCommand.CreateInstance("ADODB.Command");
		m_pCommand->ActiveConnection = theApp.m_pConnection;
		m_pCommand->CommandText = _bstr_t(SQL); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
	}
	catch(...)
	{
		MessageBox("执行SQL语句出错!");
	}
	this->LoadUserData();
}

void CFORMVIEW1::OnBnClickedButton5()
{
	CString SQL,temp,temp2;
	_CommandPtr m_pCommand;
	int i;

	i=m_list1.GetSelectionMark();
	if(i==-1)
	{
		MessageBox("请先选择要更新的项");
		return;
	}


	GetDlgItem(IDC_EDIT6)->GetWindowText(temp);// title,period,price from [NMTABLE]
	SQL=(CString)"update  [NMTABLE] set title='" + temp;
	GetDlgItem(IDC_EDIT5)->GetWindowText(temp);
	SQL+="',period='"+temp;
	GetDlgItem(IDC_EDIT4)->GetWindowText(temp);
	temp2=m_list1.GetItemText(i,0);
	SQL+="',price="+temp+" where NMNO="+temp2;

	try
	{
		m_pCommand.CreateInstance("ADODB.Command");
		m_pCommand->ActiveConnection = theApp.m_pConnection;
		m_pCommand->CommandText = _bstr_t(SQL); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
	}
	catch(...)
	{
		MessageBox("执行SQL语句出错!");
	}
	this->LoadNMData();
}
void CFORMVIEW1::OnBnClickedButton1()
{
	CString SQL1,SQL2;
	_CommandPtr m_pCommand;
	int i;

	i=m_list0.GetSelectionMark();
	if(i==-1)
	{
		MessageBox("请先选择要删除的项");
		return;
	}

	if(MessageBox("删除用户信息将同时删除此用户的订报信息，您确信要删除吗?","警告",MB_ICONWARNING|MB_YESNO)==IDNO)
		return;
	// cno,name,addr from [CUSTOMER]
	SQL1=(CString)"delete  from [CUSTOMER] where CNO=" + m_list0.GetItemText(i,0);
	SQL2="DELETE FROM [CUS_ORDER] WHERE CNO="+m_list0.GetItemText(i,0);
	try
	{
		m_pCommand.CreateInstance("ADODB.Command");
		m_pCommand->ActiveConnection = theApp.m_pConnection;
		m_pCommand->CommandText = _bstr_t(SQL2); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
		m_pCommand->CommandText = _bstr_t(SQL1); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
	}
	catch(...)
	{
		MessageBox("执行SQL语句出错!");
	}
	this->LoadUserData();

}

void CFORMVIEW1::OnBnClickedButton6()
{
	CString SQL;
	_CommandPtr m_pCommand;
	int i;

	i=m_list1.GetSelectionMark();
	if(i==-1)
	{
		MessageBox("请先选择要删除的项");
		return;
	}
	if(MessageBox("此操作不可撤消，您确信要删除吗?","警告",MB_ICONWARNING|MB_YESNO)==IDNO)
		return;

	SQL=(CString)"delete  from [NMTABLE] where NMNO=" + m_list1.GetItemText(i,0);
	try
	{
		m_pCommand.CreateInstance("ADODB.Command");
		m_pCommand->ActiveConnection = theApp.m_pConnection;
		m_pCommand->CommandText = _bstr_t(SQL); 
		m_pCommand->Execute(NULL, NULL,adCmdText); 
	}
	catch(...)
	{
		MessageBox("执行SQL语句出错!");
	}
	this->LoadNMData();

}