vc++實作對清單框的操作 ado 操作資料庫實作對資料的增删 access

實作對資料的增删改
           

BOOL CDiaDlg::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
	m_Grid.SetExtendedStyle(LVS_EX_FLATSB
		|LVS_EX_FULLROWSELECT
		|LVS_EX_HEADERDRAGDROP
		|LVS_EX_ONECLICKACTIVATE
		|LVS_EX_GRIDLINES);
	m_Grid.InsertColumn(0,"id",LVCFMT_LEFT,110,0);
	m_Grid.InsertColumn(1,"name",LVCFMT_LEFT,110,1);
	m_Grid.InsertColumn(2,"sex",LVCFMT_LEFT,110,2);
	m_Grid.InsertColumn(3,"xueli",LVCFMT_LEFT,110,3);
	AddToGrid();


	
	return TRUE;  // return TRUE  unless you set the focus to a control
}

void CDiaDlg::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 CDiaDlg::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 CDiaDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}

void CDiaDlg::OnInitADOConn()
{
	try
	{
		//建立連接配接對象執行個體
		m_pConnection.CreateInstance("ADODB.Connection");
		//設定連接配接字元串（2000的在代碼中有！）
		CString strConnect="DRIVER={Microsoft Access Driver (*.mdb)};\
			uid=;pwd=;DBQ=shujuku.mdb";
		//使用Open方法連接配接資料庫
		m_pConnection->Open((_bstr_t)strConnect,"","",adModeUnknown);  
	}
	catch(_com_error e)
	{
		AfxMessageBox(e.Description());
	}


}

void CDiaDlg::ExitConnect()
{
	//關閉記錄集和連接配接
	if(m_pRecordset!=NULL)
		m_pRecordset->Close();
	m_pConnection->Close();


}

void CDiaDlg::AddToGrid()
{
	//連接配接資料庫
	OnInitADOConn();
	//設定查詢字元串
	_bstr_t bstrSQL = "select * from 表1 order by id desc";
	//建立記錄集指針對象執行個體
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	//打開記錄集
	m_pRecordset->Open(bstrSQL,m_pConnection.GetInterfacePtr(),adOpenDynamic,
		adLockOptimistic,adCmdText);
	while(!m_pRecordset->adoEOF)
	{
		m_Grid.InsertItem(0,"");
		m_Grid.SetItemText(0,0,(char*)(_bstr_t)m_pRecordset->GetCollect("id"));
		m_Grid.SetItemText(0,1,(char*)(_bstr_t)m_pRecordset->GetCollect("name"));
		m_Grid.SetItemText(0,2,(char*)(_bstr_t)m_pRecordset->GetCollect("sex"));
		m_Grid.SetItemText(0,3,(char*)(_bstr_t)m_pRecordset->GetCollect("xueli"));
		//将記錄集指針移動到下一條記錄
		m_pRecordset->MoveNext();
	}
	//斷開資料庫連接配接
	ExitConnect();


}

void CDiaDlg::OnButton3() 
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	if(m_Id.IsEmpty() || m_Name.IsEmpty() || m_Sex.IsEmpty() || m_Xueli.IsEmpty())
	{
		MessageBox("基礎資訊不能為空！");
		return;
	}
	OnInitADOConn();
	_bstr_t sql;
	sql = "select * from 表1";
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	m_pRecordset->Open(sql,m_pConnection.GetInterfacePtr(),adOpenDynamic,
		adLockOptimistic,adCmdText);
	try
	{
		m_pRecordset->AddNew(); //添加新行
		m_pRecordset->PutCollect("id",(_bstr_t)m_Id);
		m_pRecordset->PutCollect("name",(_bstr_t)m_Name);
		m_pRecordset->PutCollect("sex",(_bstr_t)m_Sex);
		m_pRecordset->PutCollect("xueli",(_bstr_t)m_Xueli);

		m_Name=m_Sex=m_Id=m_Xueli="";

		m_pRecordset->Update(); //更新資料表
		m_Name=m_Sex=m_Id=m_Xueli="";
		UpdateData(FALSE);

		ExitConnect();
	}
	catch(...)
	{
		MessageBox("操作失敗");
		return;
	}
	MessageBox("添加成功");
	m_Grid.DeleteAllItems(); //删除清單控件
	AddToGrid();			

}

void CDiaDlg::OnButton2() 
{
	// TODO: Add your control notification handler code here
	OnInitADOConn();
	_bstr_t sql;
	sql = "select * from 表1";
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	m_pRecordset->Open(sql,m_pConnection.GetInterfacePtr(),adOpenDynamic,
		adLockOptimistic,adCmdText);
	long pos = m_Grid.GetSelectionMark();
	try
	{
		m_pRecordset->Move((long)pos,vtMissing);
		m_pRecordset->Delete(adAffectCurrent);
		m_pRecordset->Update();
		m_Name=m_Sex=m_Id=m_Xueli="";
		UpdateData(FALSE);

		ExitConnect();
	}
	catch(...)
	{
		MessageBox("操作失敗");
		return;
	}
	MessageBox("删除成功");
	m_Grid.DeleteAllItems();
	AddToGrid();	

}

void CDiaDlg::OnClickList1(NMHDR* pNMHDR, LRESULT* pResult) 
{
	// TODO: Add your control notification handler code here
	long pos = m_Grid.GetSelectionMark();
	m_Id = m_Grid.GetItemText(pos,0);
	m_Name = m_Grid.GetItemText(pos,1);
	m_Sex = m_Grid.GetItemText(pos,2);
	m_Xueli = m_Grid.GetItemText(pos,3);
	UpdateData(FALSE);	

	*pResult = 0;
}

void CDiaDlg::OnButton1() 
{
	// TODO: Add your control notification handler code here
	UpdateData(TRUE);
	if(m_Id.IsEmpty() || m_Name.IsEmpty() || m_Sex.IsEmpty() || m_Xueli.IsEmpty())
	{
		MessageBox("基礎資訊不能為空！");
		return;
	}
	OnInitADOConn();

	_RecordsetPtr pRs("ADODB.Recordset");

	_bstr_t sql;
	sql = "select * from 表1";
	m_pRecordset.CreateInstance(__uuidof(Recordset));
	m_pRecordset->Open(sql,m_pConnection.GetInterfacePtr(),adOpenDynamic,
		adLockOptimistic,adCmdText);
	long pos = m_Grid.GetSelectionMark();
	_variant_t var,varIndex;
	CString vSQL;
	_variant_t RecordsAffected;

	try
	{
		UpdateData(TRUE);
		m_pRecordset->AddNew(); //添加新行
		if(m_Id.IsEmpty())
			return ;
		
		//m_pRecordset->Move((long)pos,vtMissing);
		//m_pRecordset->Delete(adAffectCurrent);
	//	m_pRecordset->Update();	

		m_pRecordset->PutCollect("id",(_bstr_t)m_Id);
		m_pRecordset->PutCollect("name",(_bstr_t)m_Name);
		m_pRecordset->PutCollect("sex",(_bstr_t)m_Sex);
		m_pRecordset->PutCollect("xueli",(_bstr_t)m_Xueli);

		m_pRecordset->Move((long)pos,vtMissing);
		m_pRecordset->Delete(adAffectCurrent);

		
		m_pRecordset->Update();
		m_Name=m_Sex=m_Id=m_Xueli="";
		UpdateData(FALSE);
	}

	
	catch(...)
	{
	

		MessageBox("操作失敗");
		return;
	
	}
	MessageBox("修改成功");
	m_Grid.DeleteAllItems();
	AddToGrid();	


}

void CDiaDlg::OnButton5() 
{
	// TODO: Add your control notification handler code here
	m_Id= "";
	m_Name = "";
	m_Sex = "";
	m_Xueli = "";
	UpdateData(FALSE);	

}