stumoddlg.cpp

用ODBC接口访问access数据库管理系统

// StuModDlg.cpp : implementation file
//

#include "stdafx.h"
#include "Team2.h"
#include "StuModDlg.h"

#ifdef _DEBUG
#define new DEBUG_NEW
#undef THIS_FILE
static char THIS_FILE[] = __FILE__;
#endif

#include <afx.h>
/////////////////////////////////////////////////////////////////////////////
// CStuModDlg dialog


CStuModDlg::CStuModDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CStuModDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CStuModDlg)
	m_sex = _T("");
	m_birth = 0;
	m_stuid = _T("");
	m_name = _T("");
	m_native = _T("");
	m_homeaddr = _T("");
	m_hometel = _T("");
	m_awardpunish = _T("");
	m_talent = _T("");
	m_spec = _T("");
	m_pswd = _T("");
	m_polity = _T("");
	//}}AFX_DATA_INIT
}


void CStuModDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CStuModDlg)
	DDX_Control(pDX, IDC_MODIFY, m_stumod);
	DDX_Control(pDX, IDC_DELETE, m_studel);
	DDX_Control(pDX, IDC_ADD, m_stuadd);
	DDX_Control(pDX, IDC_LIST1, m_stuinfo);
	DDX_CBString(pDX, IDC_COMBO1, m_sex);
	DDX_DateTimeCtrl(pDX, IDC_DATETIMEPICKER1, m_birth);
	DDX_Text(pDX, IDC_EDIT1, m_stuid);
	DDX_Text(pDX, IDC_EDIT2, m_name);
	DDX_Text(pDX, IDC_EDIT3, m_native);
	DDX_Text(pDX, IDC_EDIT4, m_homeaddr);
	DDX_Text(pDX, IDC_EDIT5, m_hometel);
	DDX_Text(pDX, IDC_EDIT7, m_awardpunish);
	DDX_Text(pDX, IDC_EDIT8, m_talent);
	DDX_Text(pDX, IDC_EDIT9, m_spec);
	DDX_Text(pDX, IDC_EDIT10, m_pswd);
	DDX_Text(pDX, IDC_EDIT6, m_polity);
	//}}AFX_DATA_MAP
	if(!type){
		m_stuadd.EnableWindow(false);
		m_stumod.EnableWindow(false);
		m_studel.EnableWindow(false);
	}
}


BEGIN_MESSAGE_MAP(CStuModDlg, CDialog)
	//{{AFX_MSG_MAP(CStuModDlg)
	ON_BN_CLICKED(IDC_ADD, OnAdd)
	ON_BN_CLICKED(IDC_DELETE, OnDelete)
	ON_BN_CLICKED(IDC_MODIFY, OnModify)
	ON_BN_CLICKED(IDC_QUERY, OnQuery)
	ON_BN_CLICKED(IDC_BUTTON1, OnButton1)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CStuModDlg message handlers

void CStuModDlg::OnCancel() 
{
	// TODO: Add extra cleanup here
	m_stuinfo.DeleteAllItems(); // 清空列表框
	db.Close();
	CDialog::OnCancel();
}

void CStuModDlg::OnAdd() 
{
	// TODO: Add your control notification handler code here
	if(!db.Open("db1"))return;//连接数据库

	CString strSQL;
	
	UpdateData();
	if(m_stuid.IsEmpty()) // 学员证号为空则返回
	{
		AfxMessageBox("学员证号不能为空!");
		db.Close();
		return;
	}
	CRecordset rs(&db);
	rs.Open(CRecordset::forwardOnly,"select * from stu_info where id='" + m_stuid + "'");
	if(!rs.IsEOF()) //已存在此记录
	{
		AfxMessageBox("该学员证号已存在!");
		db.Close();
		return;
	}
	CString m_birthday=m_birth.Format("%y-%m-%d");
	strSQL="insert into stu_info(id,name,sex,birth,native,homeaddr,\
		hometel,polity,awardpunish,talent,spec) \
		values('"		
		+ m_stuid + "','" // ID
		+ m_name + "','" // NAME
		+ m_sex + "','" // sex
		+ m_birthday + "','" //  BIRTHDAY
		+ m_native + "','" // native
		+ m_homeaddr + "','" // home address
		+ m_hometel + "','" // home tel
		+ m_polity + "','" // polity
		+ m_awardpunish + "','" // awardpunish
		+ m_talent + "','" // Talent
		+ m_spec + "')"; //speciality
	db.ExecuteSQL(strSQL);
	strSQL="insert into stu_pswd values('" + m_stuid + "','" + m_pswd + "')";
	db.ExecuteSQL(strSQL);

	Init(); //重新初始化窗口
	AfxMessageBox("记录添加成功!");
	db.Close();
}

void CStuModDlg::OnDelete() 
{
	// TODO: Add your control notification handler code here
	int i=m_stuinfo.GetSelectionMark();
	CString strSQL;
	if(i==-1)
	{
		MessageBox("请先选择记录!");		
	}
	else
	{
		if(!db.Open("db1"))return;//连接数据库
		CString m_id=m_stuinfo.GetItemText(i,0);
		strSQL="delete from stu_info where id='" + m_id +"'";
		db.ExecuteSQL(strSQL);
		strSQL="delete from stu_pswd where id='" + m_id +"'";
		db.ExecuteSQL(strSQL);
		strSQL="delete from score where stu_id='" + m_id +"'";
		db.ExecuteSQL(strSQL);
		UpdateData();
		db.Close();	
	}
}

void CStuModDlg::OnModify() 
{
	// TODO: Add your control notification handler code here
	UpdateData(); // 更新数据
	if(!db.Open("db1"))return;//连接数据库
	CString strSQL1,strSQL2;
	// 写SQL语句
	CString m_birthday=m_birth.Format("%y-%m-%d");
	strSQL1="update stu_info set\
		name='" + m_name  //NAME
		+ "',sex='" + m_sex // SEX
		+ "',birth='" + m_birthday // birthday
		+ "',native='" + m_native // native
		+ "',homeaddr='" + m_homeaddr // homeaddr
		+ "',hometel='" + m_hometel // hometelL
		+ "',polity='" + m_polity // polity
		+ "',awardpunish='" + m_awardpunish // awardpunish
		+ "',talent='" + m_talent // Talent
		+ "',spec='" + m_spec // speciality
		+ "' where id='" + m_stuid + "'"; // 条件
	db.ExecuteSQL(strSQL1); // 执行
	strSQL2="update stu_pswd set\
		pswd='" + m_pswd //password
		+ "' where id='" + m_stuid +"'";//条件
	db.ExecuteSQL(strSQL2); //执行
	db.Close();
	AfxMessageBox("学员记录修改成功!");
}

void CStuModDlg::OnQuery() 
{
	// TODO: Add your control notification handler code here
	UpdateData(); // 更新数据
	if(!db.Open("db1"))return;//连接数据库
	int i=0;
	CRecordset rs(&db);
	CString str_sex;
	CString s_id,s_name,s_native,s_homeaddr,s_hometel,s_polity,s_ap,s_talent,s_spe,s_pswd;
	CDBVariant varValue;	
	rs.Open(CRecordset::forwardOnly,"select * from stu_info where id='"+m_stuid+"'");
	char buf[20];
	if(!rs.IsEOF()) 
	{
		//得到数据
		rs.GetFieldValue("id",s_id);
		rs.GetFieldValue("name",s_name);
		rs.GetFieldValue("sex",str_sex);
		rs.GetFieldValue("birth",varValue);
		sprintf(buf,"%d-%d-%d",varValue.m_pdate->year,varValue.m_pdate->month,varValue.m_pdate->day); 		
		rs.GetFieldValue("native",s_native);
		rs.GetFieldValue("homeaddr",s_homeaddr);
		rs.GetFieldValue("hometel",s_hometel);
		rs.GetFieldValue("polity",s_polity);
		rs.GetFieldValue("awardpunish",s_ap);
		rs.GetFieldValue("talent",s_talent);
		rs.GetFieldValue("spec",s_spe);		
		rs.Close();

		//得到口令
		rs.Open(CRecordset::forwardOnly,
			"select pswd from stu_pswd where id='"+s_id+"'");
		rs.GetFieldValue("pswd",s_pswd);
		rs.Close();

		//写入编辑框
		m_sex=_T(str_sex);
		m_name=_T(s_name);
//		m_birth=varValue.m_pdate;
		CTime t(varValue.m_pdate->year,varValue.m_pdate->month,varValue.m_pdate->day,0,0,0);
		m_birth=t;
		m_native=_T(s_native);
		m_homeaddr=_T(s_homeaddr);
		m_hometel=_T(s_hometel);
		m_polity=_T(s_polity);
		m_awardpunish=_T(s_ap);
		m_talent=_T(s_talent);
		m_spec=_T(s_spe);
		m_pswd=_T(s_pswd);
		
		//加入列表
		m_stuinfo.InsertItem(i,"");
		m_stuinfo.SetItemText(i,0,s_id);
		m_stuinfo.SetItemText(i,1,s_name);
		m_stuinfo.SetItemText(i,2,str_sex);
//		m_stuinfo.SetItemText(i,3,str_birth);
		m_stuinfo.SetItemText(i,3,buf);		
		m_stuinfo.SetItemText(i,4,s_native);
		m_stuinfo.SetItemText(i,5,s_homeaddr);
		m_stuinfo.SetItemText(i,6,s_hometel);
		m_stuinfo.SetItemText(i,7,s_polity);
		m_stuinfo.SetItemText(i,8,s_ap);
		m_stuinfo.SetItemText(i,9,s_talent);
		m_stuinfo.SetItemText(i,10,s_spe);
		m_stuinfo.SetItemText(i,11,s_pswd);
//		i++;
		UpdateData(FALSE);
		Exist=TRUE; //该学员存在
	}
	else 
	{
		rs.Close();
		m_sex=_T("");
		m_stuid=_T("");
		m_name=_T("");
		m_native=_T("");
		m_homeaddr=_T("");
		m_hometel=_T("");
		m_polity=_T("");
		m_awardpunish=_T("");
		m_talent=_T("");
		m_spec=_T("");
		m_pswd=_T("");
		CTime t=NULL;
		m_birth=t;
		UpdateData(FALSE);

		Exist=FALSE; //该学员不存在
	}
	if(!Exist) // 该学员证号不存在
	{
		AfxMessageBox("该学员证号不存在");
		db.Close();
		return;
	}
	db.Close();	
}

BOOL CStuModDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	// TODO: Add extra initialization here
	// 给m_stuinfo加入网格
	DWORD style=m_stuinfo.GetExtendedStyle();
	m_stuinfo.SetExtendedStyle(style|LVS_EX_GRIDLINES);
	
	m_stuinfo.InsertColumn(0,"学员证号");
	m_stuinfo.InsertColumn(1,"姓名");
	m_stuinfo.InsertColumn(2,"性别");
	m_stuinfo.InsertColumn(3,"出生日期");
	m_stuinfo.InsertColumn(4,"籍贯");
	m_stuinfo.InsertColumn(5,"家庭住址");
	m_stuinfo.InsertColumn(6,"家庭电话");
	m_stuinfo.InsertColumn(7,"政治面貌");
	m_stuinfo.InsertColumn(8,"奖惩记录");
	m_stuinfo.InsertColumn(9,"特长");
	m_stuinfo.InsertColumn(10,"专业");	
	m_stuinfo.InsertColumn(11,"口令");
	RECT rectbasic;
	m_stuinfo.GetWindowRect(&rectbasic);
	int widbasic = rectbasic.right - rectbasic.left;
	m_stuinfo.SetColumnWidth(0,widbasic/12);
	m_stuinfo.SetColumnWidth(1,widbasic/12);
	m_stuinfo.SetColumnWidth(2,widbasic/12);	
	m_stuinfo.SetColumnWidth(3,widbasic/12);
	m_stuinfo.SetColumnWidth(4,widbasic/12);
	m_stuinfo.SetColumnWidth(5,widbasic/12);
	m_stuinfo.SetColumnWidth(6,widbasic/12);
	m_stuinfo.SetColumnWidth(7,widbasic/12);
	m_stuinfo.SetColumnWidth(8,widbasic/12);
	m_stuinfo.SetColumnWidth(9,widbasic/12);
	m_stuinfo.SetColumnWidth(10,widbasic/12);
	m_stuinfo.SetColumnWidth(11,widbasic/12);
	m_stuinfo.SetExtendedStyle(LVS_EX_FULLROWSELECT);
	Init();
//	RefreshData();
	UpdateData(false);
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

/*void CStuModDlg::RefreshData()
{
	UpdateData(true); // 更新数据
	m_stuinfo.DeleteAllItems(); // 清空列表框
	if(!db.Open("db1"))return;//连接数据库
	int i=0;
	CRecordset rs(&db);
	CString str_sex;
	CString s_id,s_name,s_native,s_homeaddr,s_hometel,s_polity,s_ap,s_talent,s_spe,s_pswd;
	CDBVariant varValue;	
	char buf[20];	
	rs.Open(CRecordset::forwardOnly,"select * from stu_info");	
	while (!rs.IsEOF()) 
	{
		//得到数据
		rs.GetFieldValue("id",s_id);
		rs.GetFieldValue("name",s_name);
		rs.GetFieldValue("sex",str_sex);
		rs.GetFieldValue("birth",varValue);
		sprintf(buf,"%d-%d-%d",varValue.m_pdate->year,varValue.m_pdate->month,varValue.m_pdate->day); 		
		rs.GetFieldValue("native",s_native);
		rs.GetFieldValue("homeaddr",s_homeaddr);
		rs.GetFieldValue("hometel",s_hometel);
		rs.GetFieldValue("polity",s_polity);
		rs.GetFieldValue("awardpunish",s_ap);
		rs.GetFieldValue("talent",s_talent);
		rs.GetFieldValue("spec",s_spe);	
		rs.Close();
		
		//得到口令
		rs.Open(CRecordset::forwardOnly,
			"select pswd from stu_pswd where id='"+s_id+"'");
		rs.GetFieldValue("pswd",s_pswd);
		rs.Close();

		//加入列表
		m_stuinfo.InsertItem(i,"");
		m_stuinfo.SetItemText(i,0,s_id);
		m_stuinfo.SetItemText(i,1,s_name);
		m_stuinfo.SetItemText(i,2,str_sex);
		CTime t(varValue.m_pdate->year,varValue.m_pdate->month,varValue.m_pdate->day,0,0,0);
		m_birth=t;
		m_stuinfo.SetItemText(i,4,s_native);
		m_stuinfo.SetItemText(i,5,s_homeaddr);
		m_stuinfo.SetItemText(i,6,s_hometel);
		m_stuinfo.SetItemText(i,7,s_polity);
		m_stuinfo.SetItemText(i,8,s_ap);
		m_stuinfo.SetItemText(i,9,s_talent);
		m_stuinfo.SetItemText(i,10,s_spe);
		m_stuinfo.SetItemText(i,11,s_pswd);
		i++;
		rs.MoveNext();
	}
	rs.Close();
}*/

void CStuModDlg::Init()
{
	m_sex=_T("");
	m_stuid=_T("");
	m_name=_T("");
	m_native=_T("");
	m_homeaddr=_T("");
	m_hometel=_T("");
	m_polity=_T("");
	m_awardpunish=_T("");
	m_talent=_T("");
	m_spec=_T("");
	m_pswd=_T("");
	CTime t(1980,1,1,0,0,0);
	m_birth=t;
//	i=0;
	UpdateData(FALSE);
}



void CStuModDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
			m_stuadd.EnableWindow(false);
		    m_stumod.EnableWindow(false);
			m_studel.EnableWindow(false);
			UpdateData(FALSE);
}

int CStuModDlg::DoModal() 
{
	// TODO: Add your specialized code here and/or call the base class
	m_stuadd.EnableWindow(false);
	m_stumod.EnableWindow(false);
	m_studel.EnableWindow(false);
	UpdateData(FALSE);
	return CDialog::DoModal();
}