当前位置: 开源爱好者 » 编程教程 » VC++教程 » 正文

VC++使用SQL语句中的时段检索例子

VC++在SQL语句中设置时段检索条件,进行分时的数据库查询操作,这里只给出了关键代码,其它代码请自行添加:

BOOL CSearchByTmDlg::OnInitDialog()
{
	CDialog::OnInitDialog();
	//初始化环境
	::CoInitialize(NULL);
	//创建并打开数据库连接对象
	_variant_t vFieldValue;
	CString strFieldValue;
	m_pCon.CreateInstance(__uuidof(Connection));
	m_pCon->Open("students","","",NULL);
	//创建并打开记录集对象
	m_pRs.CreateInstance(__uuidof(Recordset));
	m_pRs->Open("select* from table1",m_pCon.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
	return TRUE;  // return TRUE  unless you set the focus to a control
}
void CSearchByTmDlg::OnPaint()
{
	if (IsIconic())
	{
		CPaintDC dc(this); 
		SendMessage(WM_ICONERASEBKGND, (WPARAM) dc.GetSafeHdc(), 0);
		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;
		dc.DrawIcon(x, y, m_hIcon);
	}
	else
	{
		CDialog::OnPaint();
	}
}
HCURSOR CSearchByTmDlg::OnQueryDragIcon()
{
	return (HCURSOR) m_hIcon;
}
void CSearchByTmDlg::OnSearch()
{
	//情况列表框的数据
	while(m_StudentsLst.GetCount()>0)
	{
		m_StudentsLst.DeleteString(0);
	}
	UpdateData(TRUE);
	//关闭上一次查询的记录集
	m_pRs->Close();
	//格式化时间
	CString strBegin,strEnd;
	strBegin = m_tmStart.Format("%Y-%m-%d %H:%M:%S");
	strEnd = m_tmEnd.Format("%Y-%m-%d %H:%M:%S");
	//构造SQL检索语句
	CString strSql;
	strSql.Format("select * from table1 where entertm >= CDate('%s') and entertm <= CDate('%s')",strBegin,strEnd);
	//检索
	BSTR bsSql = strSql.AllocSysString();
	m_pRs->Open((CComVariant)bsSql,m_pCon.GetInterfacePtr(),adOpenDynamic,adLockOptimistic,adCmdText);
	//将查询结果在列表框中显示出来
	while(VARIANT_FALSE == m_pRs->EndOfFile)
	{
		_variant_t vFieldValue;
		CString strID,strName,strEnterTm;
		CString strItem;
		vFieldValue = m_pRs->GetCollect("id");
		strID = (char*)_bstr_t(vFieldValue);
		vFieldValue.Clear();
		vFieldValue = m_pRs->GetCollect("name");
		strName = (char*)_bstr_t(vFieldValue);
		vFieldValue.Clear();
		vFieldValue = m_pRs->GetCollect("entertm");
		strEnterTm = (char*)_bstr_t(vFieldValue);
		vFieldValue.Clear();
		strItem.Format("学号为%s的%s同学的入学时间是%s",strID,strName,strEnterTm);
		m_StudentsLst.AddString(strItem);
		m_pRs->MoveNext();
	}
}
void CSearchByTmDlg::OnDestroy()
{
	//关闭记录和连接
	m_pRs->Close();
	m_pCon->Close();
	//释放环境
	::CoUninitialize();
	CDialog::OnDestroy();
}

本例子的操作对象为Access,不过SQLServer数据库也适用的。

CopyRight 2018© 开源爱好者 All Rights Reserved 豫ICP备16030607号 隐私声明 网站地图