accesstestdlg.cpp

通过ODBC访问Access数据库的操作

// AccessTestDlg.cpp : implementation file
//

#include "stdafx.h"
#include "AccessTest.h"
#include "AccessTestDlg.h"

#include "mdbfield.h"

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

/////////////////////////////////////////////////////////////////////////////
// CAboutDlg dialog used for App About

class CAboutDlg : public CDialog
{
public:
	CAboutDlg();

// Dialog Data
	//{{AFX_DATA(CAboutDlg)
	enum { IDD = IDD_ABOUTBOX };
	//}}AFX_DATA

	// ClassWizard generated virtual function overrides
	//{{AFX_VIRTUAL(CAboutDlg)
	protected:
	virtual void DoDataExchange(CDataExchange* pDX);    // DDX/DDV support
	//}}AFX_VIRTUAL

// Implementation
protected:
	//{{AFX_MSG(CAboutDlg)
	//}}AFX_MSG
	DECLARE_MESSAGE_MAP()
};

CAboutDlg::CAboutDlg() : CDialog(CAboutDlg::IDD)
{
	//{{AFX_DATA_INIT(CAboutDlg)
	//}}AFX_DATA_INIT
}

void CAboutDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAboutDlg)
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAboutDlg, CDialog)
	//{{AFX_MSG_MAP(CAboutDlg)
		// No message handlers
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CAccessTestDlg dialog

CAccessTestDlg::CAccessTestDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CAccessTestDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CAccessTestDlg)
	m_UserName = _T("");
	m_PassWord = _T("");
	//}}AFX_DATA_INIT
	// Note that LoadIcon does not require a subsequent DestroyIcon in Win32
	m_hIcon = AfxGetApp()->LoadIcon(IDR_MAINFRAME);
}

void CAccessTestDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CAccessTestDlg)
	DDX_Control(pDX, IDC_COMBOUSERTYPE, m_usertypectrl);
	DDX_Control(pDX, IDC_LIST1, m_List1);
	DDX_Text(pDX, IDC_USERNAME, m_UserName);
	DDX_Text(pDX, IDC_PASSWORD, m_PassWord);
	//}}AFX_DATA_MAP
}

BEGIN_MESSAGE_MAP(CAccessTestDlg, CDialog)
	//{{AFX_MSG_MAP(CAccessTestDlg)
	ON_WM_SYSCOMMAND()
	ON_WM_PAINT()
	ON_WM_QUERYDRAGICON()
	ON_BN_CLICKED(IDC_BTNADD, OnBtnadd)
	ON_BN_CLICKED(IDC_BTNMODIFI, OnBtnmodifi)
	ON_BN_CLICKED(IDC_BTNDELETE, OnBtndelete)
	ON_NOTIFY(LVN_ITEMCHANGED, IDC_LIST1, OnItemchangedList1)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CAccessTestDlg message handlers

BOOL CAccessTestDlg::OnInitDialog()
{
	CDialog::OnInitDialog();

	// Add "About..." menu item to system menu.

	// IDM_ABOUTBOX must be in the system command range.
	ASSERT((IDM_ABOUTBOX & 0xFFF0) == IDM_ABOUTBOX);
	ASSERT(IDM_ABOUTBOX < 0xF000);

	CMenu* pSysMenu = GetSystemMenu(FALSE);
	if (pSysMenu != NULL)
	{
		CString strAboutMenu;
		strAboutMenu.LoadString(IDS_ABOUTBOX);
		if (!strAboutMenu.IsEmpty())
		{
			pSysMenu->AppendMenu(MF_SEPARATOR);
			pSysMenu->AppendMenu(MF_STRING, IDM_ABOUTBOX, strAboutMenu);
		}
	}

	// Set the icon for this dialog.  The framework does this automatically
	//  when the application's main window is not a dialog
	SetIcon(m_hIcon, TRUE);			// Set big icon
	SetIcon(m_hIcon, FALSE);		// Set small icon
	
	// TODO: Add extra initialization here
	//int i;

	m_ImageList.Create(IDB_CLASS_VIEW, 32, 1, RGB(255, 255, 255));
	m_List1.SetImageList(&m_ImageList,TVSIL_NORMAL);

	//InitDatabase();
	//GetAllRecord();
	try
	{
		CString  sDSN="ODBC;DRIVER=Microsoft Access Driver (*.mdb);DSN='';UID=admin;PWD=wangqien;DBQ=NetDvr.mdb";
		m_database=new CDatabase;
		if(!m_database->Open(NULL,FALSE,FALSE,sDSN,FALSE))
		{
			AfxMessageBox("ODBC Connection Faile!");
			CDialog::EndDialog(0);
			GetParent()->PostMessage(WM_CLOSE);
			return FALSE;
		}
	
        CString strSQL="select * from user";
		m_pSet=new CMyRecordset(m_database);
		m_pSet->Open(CRecordset::dynaset,strSQL);
	}
	catch(CDBException* pEx)
	{
		pEx->ReportError();
	}

	GetUserRecord();

	/*m_pSet->MoveFirst();
	((CEdit *)GetDlgItem(IDC_USERNAME))->SetWindowText(m_pSet->m_username);
	m_username=m_pSet->m_username;
	((CEdit *)GetDlgItem(IDC_PASSWORD))->SetWindowText(m_pSet->m_password);
	m_password=m_pSet->m_password;
	i=atoi(m_pSet->m_usertype);*/
	m_usertypectrl.SetCurSel(0);

	//m_List1.SetItemState(0,0);
	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CAccessTestDlg::OnSysCommand(UINT nID, LPARAM lParam)
{
	if ((nID & 0xFFF0) == IDM_ABOUTBOX)
	{
		CAboutDlg dlgAbout;
		dlgAbout.DoModal();
	}
	else
	{
		CDialog::OnSysCommand(nID, lParam);
	}
}

// If you add a minimize button to your dialog, you will need the code below
//  to draw the icon.  For MFC applications using the document/view model,
//  this is automatically done for you by the framework.

void CAccessTestDlg::OnPaint() 
{
	if (IsIconic())
	{
		CPaintDC dc(this); // device context for painting

		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);

		// Center icon in client rectangle
		int cxIcon = GetSystemMetrics(SM_CXICON);
		int cyIcon = GetSystemMetrics(SM_CYICON);
		CRect rect;
		GetClientRect(&rect);
		int x = (rect.Width() - cxIcon + 1) / 2;
		int y = (rect.Height() - cyIcon + 1) / 2;

		// Draw the icon
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}

// The system calls this to obtain the cursor to display while the user drags
//  the minimized window.
HCURSOR CAccessTestDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CAccessTestDlg::InitDatabase()
{
	//////////////////////////////ACCESS///////////////////////////////////       
    char* szDesc;	
    TCHAR Temppath[MAX_PATH];
    CString tem ;
    ::GetCurrentDirectory(MAX_PATH,Temppath);
    tem  =Temppath;
    tem =tem +_T("\\NetDvr.mdb");

	szDesc=new char[256];
    sprintf(szDesc,"DSN=%s;DBQ=%s  ","usermanageDB",tem);
 		 
    if (FALSE == SQLConfigDataSource(NULL,ODBC_ADD_DSN,"Microsoft Access Driver (*.mdb)\0",(LPCSTR)szDesc))
	{
	    AfxMessageBox("创建DSN时，出现错误！");
	    return ;
	}
	////////////////////////对ACCESS操作/////////////////////////////////////////////////////////
    CDatabase database;
    CString sSql;
    //创建
    TRY
	{
		// 创建进行存取的字符串	
		sSql.Format("DSN=%s;;","usermanageDB"  );
		// 创建数据库 (既Excel表格文件)
		database.OpenEx(sSql,CDatabase::noOdbcDialog) ; 
	}
    CATCH(CDBException, e)
	{
        // The error code is in e->m_nRetCode
	}
    END_CATCH

    CString tableName;
    tableName="user";
 
    TRY
	{
	    //创建表结构 		
        sSql.Format( "CREATE TABLE %s (序号 TEXT,用户名 TEXT,密码 TEXT,权限 TEXT)",tableName);
        database.ExecuteSQL(sSql);
	}
    CATCH(CDBException, e)
	{
        // The error code is in e->m_nRetCode
	}
    END_CATCH
 
    if (database.IsOpen ())
	  database.Close();
}

void CAccessTestDlg::GetAllRecord()
{
    //CurPos=-1;
    CDatabase database;
    CString sSql;
    //创建
    TRY
	{
        //创建进行存取的字符串	
		//sSql.Format("DSN=%s","systemDB" );
	    sSql.Format("DSN=%s;; ","usermanageDB"   );
        //创建数据库 (既Excel表格文件)
        database.OpenEx(sSql,CDatabase::noOdbcDialog) ;
	}
    CATCH(CDBException, e)
	{
        // The error code is in e->m_nRetCode
	}
    END_CATCH

    CRecordset recordset;
    recordset.m_pDatabase =&database;
  
    CString tableName;
    tableName="user" ;
    //BOOL  m_bNotExist=TRUE;
    TRY
	{
	    //创建表结构 员工名 ,密码
	    recordset.m_strFilter =""; 
	    sSql.Format ("select * from %s  ",tableName   );
        recordset.Open(CRecordset::dynaset,sSql); 
	    if (recordset.IsBOF()&&recordset.IsEOF())
		{
		}
	    else
		{   	 
			recordset.MoveFirst ();
			CDBVariant  varValue[3];
			m_List1.DeleteAllItems();
			m_CurItemCount=0;
			//char buf[30];
			while  (!recordset.IsEOF ())
			{
				/*int  i_static=0; 
				recordset.GetFieldValue(i_static,varValue[0]);
				vmsuser.UserID=(int)varValue[0].m_dblVal;*/
				
				recordset.GetFieldValue(1,varValue[1]);
				strcpy(vmsuser.UserName, *varValue[1].m_pstring);
				m_List1.InsertItem(m_CurItemCount,*varValue[1].m_pstring,1);

				/*recordset.GetFieldValue(2,varValue[2]);
				strcpy(vmsuser.Password, *varValue[2].m_pstring);

				recordset.GetFieldValue(3,varValue[3]);
				strcpy(vmsuser.UserType, *varValue[3].m_pstring);*/
				
				//recordset.GetFieldValue(1,varValue[1]);
				//m_List1.SetItemText(m_CurItemCount,1,*varValue[1].m_pstring);
			 
				//recordset.GetFieldValue(2,varValue[2]);
				//sprintf(buf,"%d",(int)varValue[2].m_dblVal);
				//m_List1.SetItemText(m_CurItemCount,2,buf);
				m_CurItemCount++;
			 
   				recordset.MoveNext();
			}
		}
	}
    CATCH(CDBException, e)
	{
        // The error code is in e->m_nRetCode  
	}
    END_CATCH
    if (recordset.IsOpen ())
       recordset.Close ();
  
    if (database.IsOpen ())
	   database.Close();
}

void CAccessTestDlg::GetUserRecord()
{
	CString temp;
	char *buf;
	int j=0;

	m_List1.DeleteAllItems();
	m_pSet->MoveFirst();
	while( !m_pSet->IsEOF() )
	{
		for(int i=0;i<MAX_LEN;i++)
		{
			m_pSet->GetFieldValue(dbFields[i].name,temp);
			buf=temp.GetBuffer(temp.GetLength());
			strcpy(dbFields[i].m_name,buf);
		}
		m_List1.InsertItem(j,(LPCTSTR)dbFields[1].m_name,1);
		j++;
		m_pSet->MoveNext();
	}
}

BOOL CAccessTestDlg::DestroyWindow() 
{
	// TODO: Add your specialized code here and/or call the base class
	if(m_pSet!=NULL)
	{ 
		m_pSet->Close();
		m_database->Close();
		delete m_pSet;
		delete m_database;
	}	
	return CDialog::DestroyWindow();
}

void CAccessTestDlg::OnBtnadd() 
{
	// TODO: Add your control notification handler code here
	int i;

	if(!UpdateData(TRUE))
		return;
	if(m_UserName.IsEmpty())
	{ 
		MessageBox("用户名不能为空！","警告",MB_ICONINFORMATION);
		   return;
	}
	i=FindRecorde(m_UserName);
	if (i!=0)
	{
		MessageBox("该用户已经在数据库中存在！","警告",MB_ICONINFORMATION);
	    return;
	}
	i = m_List1.GetItemCount();
	//i=m_pSet->GetRecordCount();
	m_List1.InsertItem(i,m_UserName,1);

	m_pSet->AddNew();  	
	SetValue(true);
	m_pSet->Update();
	m_pSet->Requery();
	m_pSet->MoveLast();
	//m_pSet->MoveFirst();

	((CEdit *)GetDlgItem(IDC_USERNAME))->SetWindowText(m_pSet->m_username);
	m_UserName=m_pSet->m_username;
	((CEdit *)GetDlgItem(IDC_PASSWORD))->SetWindowText(m_pSet->m_password);
	m_PassWord=m_pSet->m_password;
	i=atoi(m_pSet->m_usertype);
	m_usertypectrl.SetCurSel(i);

	MessageBox("添加成功！","警告",MB_ICONINFORMATION);
}

void CAccessTestDlg::OnBtnmodifi() 
{
	// TODO: Add your control notification handler code here
	int i;
	POSITION pos;

	pos = m_List1.GetFirstSelectedItemPosition();
	if (pos)
	{
	if(!UpdateData(TRUE))
		return;
	if(m_UserName.IsEmpty())
		return;
	if(stricmp(m_UserName, "admin") == 0)
	{
		MessageBox("系统管理员不能修改！","警告",MB_ICONINFORMATION);
		return;
	}
	//i=FindRecorde(m_UserName);
	//if (i==0)
	   //return;

	m_pSet->Edit();
	SetValue(false);
	m_pSet->Update();
	m_pSet->Requery();
	UpdateData(TRUE);
	GetUserRecord();

	((CEdit *)GetDlgItem(IDC_USERNAME))->SetWindowText(m_pSet->m_username);
	m_UserName=m_pSet->m_username;
	((CEdit *)GetDlgItem(IDC_PASSWORD))->SetWindowText(m_pSet->m_password);
	m_PassWord=m_pSet->m_password;
	i=atoi(m_pSet->m_usertype);
	m_usertypectrl.SetCurSel(i);

	MessageBox("修改成功！","警告",MB_ICONINFORMATION);
	}
}

void CAccessTestDlg::OnBtndelete() 
{
	// TODO: Add your control notification handler code here
	int i;
	POSITION pos;

	pos = m_List1.GetFirstSelectedItemPosition();
	if (pos)
	{
	if(!UpdateData(TRUE))
		return;
	if(m_UserName.IsEmpty())
		return;
	if(stricmp(m_UserName, "admin") == 0)
	{
		MessageBox("系统管理员不能删除！","警告",MB_ICONINFORMATION);
		return;
	}
	//i=FindRecorde(m_UserName);
	//if (i==0)
	   //return;

	if(MessageBox("确定要删除这条记录吗？","删除记录",MB_YESNO|MB_ICONINFORMATION) != IDYES)
	{
		//m_List1.DeleteItem(i);
		return;
	}
	m_pSet->Delete();
	
	ModifyRecord();
	GetUserRecord();

	((CEdit *)GetDlgItem(IDC_USERNAME))->SetWindowText(m_pSet->m_username);
	m_UserName=m_pSet->m_username;
	((CEdit *)GetDlgItem(IDC_PASSWORD))->SetWindowText(m_pSet->m_password);
	m_PassWord=m_pSet->m_password;
	i=atoi(m_pSet->m_usertype);
	m_usertypectrl.SetCurSel(i);

	MessageBox("删除成功！","警告",MB_ICONINFORMATION);
	}
}

void CAccessTestDlg::SetValue(bool userid)
{
	int i,j;

	UpdateData(TRUE);
	if (userid)
	{
		i = m_List1.GetItemCount();
		//i=m_pSet->GetRecordCount();
		m_pSet->m_userid.Format("%d", i);
	}
	m_pSet->m_username    =   m_UserName;
	m_pSet->m_password    =   m_PassWord;
	j=m_usertypectrl.GetCurSel();
	m_pSet->m_usertype.Format("%d", j);
}

int CAccessTestDlg::FindRecorde(CString name) 
{
	CString str;
	//POSITION pos;
	//int nItem;

	//pos = m_List1.GetFirstSelectedItemPosition();
    //str=m_List1.GetItemText (int (pos-1), 0);
	//if (str!="")
	//{
		m_pSet->MoveFirst();
		while( !m_pSet->IsEOF() )
		{
			m_pSet->GetFieldValue(dbFields[1].name,str);
			if(stricmp(str, name) == 0)
			{
				//nItem = m_List1.GetNextSelectedItem(pos);
				return 1;
			}
			m_pSet->MoveNext();
		}
	//}
	return 0;
}

void CAccessTestDlg::OnItemchangedList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	NM_LISTVIEW* pNMListView = (NM_LISTVIEW*)pNMHDR;
	// TODO: Add your control notification handler code here
	CString str,temp;
	POSITION pos;
	int i;

	pos = m_List1.GetFirstSelectedItemPosition();
    str=m_List1.GetItemText (int (pos-1), 0);
	if (str!="")
	{
		m_pSet->MoveFirst();
		while( !m_pSet->IsEOF() )
		{
			m_pSet->GetFieldValue(dbFields[1].name,temp);
			if(stricmp(str, temp) == 0)
			{
				m_UserName.Format("%s", temp);
				((CEdit *)GetDlgItem(IDC_USERNAME))->SetWindowText(str);
				//m_username=temp;
				
				m_pSet->GetFieldValue(dbFields[2].name,temp);
				m_PassWord.Format("%s", temp);
				((CEdit *)GetDlgItem(IDC_PASSWORD))->SetWindowText(temp);
				//m_password=temp;

				m_pSet->GetFieldValue(dbFields[3].name,temp);
				i=atoi(temp);
				m_usertypectrl.SetCurSel(i);

				break;
			}
			m_pSet->MoveNext();
		}
	}

	*pResult = 0;
}

void CAccessTestDlg::ModifyRecord()
{
	CString temp;
	int j=0;

	m_pSet->MoveFirst();
	while( !m_pSet->IsEOF() )
	{
		m_pSet->Edit();
		m_pSet->m_userid.Format("%d", j);
		m_pSet->Update();
		//m_pSet->Requery();
		UpdateData(TRUE);
		j++;
		m_pSet->MoveNext();
	}
}