querycountrydlg.cpp

A HR database application.

// QueryCountryDlg.cpp : implementation file
//

#include "stdafx.h"
#include "HRDB.h"
#include "QueryCountryDlg.h"
#include "desexec.h"

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

//定义全局SQL环境变量
extern SQLHENV    henv;
extern SQLHDBC    hdbc;
extern SQLHSTMT   hstmt;
extern SQLRETURN  retcode;
extern BOOL       findResult;

/////////////////////////////////////////////////////////////////////////////
// CQueryCountryDlg dialog


CQueryCountryDlg::CQueryCountryDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CQueryCountryDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CQueryCountryDlg)
	m_strQueryCountryID = _T("");
	m_strQueryCountryName = _T("");
	m_intResultRegionID = 0;
	m_strResultCountryID = _T("");
	m_strResultCountryName = _T("");
	m_intRadioCountryID = 0;
	//}}AFX_DATA_INIT
}


void CQueryCountryDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CQueryCountryDlg)
	DDX_Control(pDX, IDC_RADIO_COUNTRY_ID, m_editRadioCountryID);
	DDX_Control(pDX, IDC_RESULT_COUNTRY_NAME, m_editResultCountryName);
	DDX_Control(pDX, IDC_RESULT_COUNTRY_ID, m_editResultCountryID);
	DDX_Control(pDX, IDC_RESULT_C_REGION_ID, m_editResultRegionID);
	DDX_Control(pDX, IDC_QUERY_COUNTRY_NAME, m_editQueryCountryName);
	DDX_Control(pDX, IDC_QUERY_COUNTRY_ID, m_editQueryCountryID);
	DDX_Text(pDX, IDC_QUERY_COUNTRY_ID, m_strQueryCountryID);
	DDX_Text(pDX, IDC_QUERY_COUNTRY_NAME, m_strQueryCountryName);
	DDX_Text(pDX, IDC_RESULT_C_REGION_ID, m_intResultRegionID);
	DDX_Text(pDX, IDC_RESULT_COUNTRY_ID, m_strResultCountryID);
	DDX_Text(pDX, IDC_RESULT_COUNTRY_NAME, m_strResultCountryName);
	DDX_Radio(pDX, IDC_RADIO_COUNTRY_ID, m_intRadioCountryID);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CQueryCountryDlg, CDialog)
	//{{AFX_MSG_MAP(CQueryCountryDlg)
	ON_BN_CLICKED(IDC_COUNTRIES_DELETE, OnCountriesDelete)
	ON_BN_CLICKED(IDC_COUNTRIES_UPDATE, OnCountriesUpdate)
	ON_BN_CLICKED(IDC_RADIO_COUNTRY_ID, OnRadioCountryId)
	ON_BN_CLICKED(IDC_RADIO_COUNTRY_NAME, OnRadioCountryName)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CQueryCountryDlg message handlers

void CQueryCountryDlg::OnOK() 
{
	// 使用参数绑定的方法查询表中数据
	SQLCHAR      param_country_id[4];     //输入参数
	SQLVARCHAR   param_country_name[40];  //输入参数
	SQLCHAR      result_country_id[4];    //输出参数
	SQLUINTEGER  result_region_id;        //输出参数
	SQLVARCHAR   result_country_name[40]; //输出参数
	SQLINTEGER   param_country_idLenOrInd = 0, param_country_nameLenOrInd = 0;
	SQLINTEGER	 result_country_idLenOrInd = 0, result_country_nameLenOrInd = 0;
	SQLINTEGER   result_region_idInd = 0;

	TODBCs ( hstmt, SQLCloseCursor( hstmt ) ); //初始化,关闭游标

	// 绑定到结果集的列
	TODBCs( hstmt, SQLBindCol( hstmt, 1, SQL_C_CHAR, result_country_id, sizeof(result_country_id), &result_country_idLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 2, SQL_C_CHAR, result_country_name, sizeof(result_country_name), &result_country_nameLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 3, SQL_C_SLONG, &result_region_id, 0, &result_region_idInd ) );
	
	if ( m_intRadioCountryID == 0 )
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"select * from countries where country_id = ?", SQL_NTS ) );

		// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
		TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									4, 0, param_country_id, sizeof(param_country_id), &param_country_idLenOrInd ) );

		// 先给参数变量赋值，然后执行SQL语句，查询表中数据
		UpdateData(TRUE); //更新控件关联变量
		strcpy( (char *)param_country_id, m_strQueryCountryID);
		param_country_idLenOrInd = SQL_NTS;
	}
	else if ( m_intRadioCountryID == 1 )
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"select * from countries where country_name = ?", SQL_NTS ) );

		// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
		TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									40, 0, param_country_name, sizeof(param_country_name), &param_country_nameLenOrInd ) );

		// 先给参数变量赋值，然后执行SQL语句，查询表中数据
		UpdateData(TRUE); //更新控件关联变量
		strcpy( (char *)param_country_name, m_strQueryCountryName);
		param_country_nameLenOrInd = SQL_NTS;
	}

	// 执行查询
	retcode = TODBCs( hstmt, SQLExecute(hstmt) );

	if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
	{
		//捕获异常
		AfxMessageBox("无权查询!");
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
	}
	else
	{
		// 用游标定位结果集
		retcode = TODBCs( hstmt, SQLFetch( hstmt ) );

		if ( retcode == SQL_NO_DATA )
		{
			findResult = FALSE;
			AfxMessageBox("未找到符合条件的记录!");
		}
		else
		{
			findResult = TRUE;
		}

		while ( retcode != SQL_NO_DATA )
		{
			m_strResultCountryID = result_country_id;
			m_strResultCountryName = result_country_name;
			m_intResultRegionID = result_region_id;
			retcode = TODBCs( hstmt, SQLFetch( hstmt ) );
			UpdateData(FALSE); //刷新编辑框内容
		}

		// 关闭游标
		TODBCs ( hstmt, SQLCloseCursor( hstmt ) );         

		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
	
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
	}
	
	//CDialog::OnOK();
}

void CQueryCountryDlg::OnCancel() 
{
	// 重置findResult为FALSE
	findResult = FALSE;
	
	CDialog::OnCancel();
}

BOOL CQueryCountryDlg::OnInitDialog() 
{
	CDialog::OnInitDialog();
	
	findResult = FALSE; //初始化
	
	return TRUE;  // return TRUE unless you set the focus to a control
	              // EXCEPTION: OCX Property Pages should return FALSE
}

void CQueryCountryDlg::OnCountriesDelete() 
{
	// 使用参数绑定的方法查询表中数据
	//SQLUINTEGER  result_region_id;       
	SQLCHAR      result_country_id[4];		//输出参数
	SQLINTEGER	 result_country_idLenOrInd = 0;
	CString      rString = "";

	// 获得编辑框中的值
	//m_editResultRegionName.GetWindowText(rString);

	if (findResult == TRUE)
	{
		if (AfxMessageBox("确定要删除该记录吗?", MB_OKCANCEL, (UINT)-1) == IDOK)
		{
			// 准备包含参数标志符的SQL语句
			TODBCs( hstmt, SQLPrepare( hstmt,                        
				(unsigned char *)"delete from countries where country_id = ?", SQL_NTS ) );

			// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
			TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
								4, 0, result_country_id, sizeof(result_country_id), &result_country_idLenOrInd ) );

			// 先给参数变量赋值，然后执行SQL语句，更新表中数据
			UpdateData(TRUE); //更新控件关联变量
			strcpy( (char *)result_country_id, m_strResultCountryID);
			result_country_idLenOrInd = SQL_NTS;

			// 执行更新
			retcode = TODBCs( hstmt, SQLExecute(hstmt) );   

			if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
			{
				//捕获异常
				AfxMessageBox("无权删除,或删除失败!");
			}

			// 释放绑定的参数
			TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
	
			// 释放绑定的参数
			TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
		}
	}
	else
	{
		AfxMessageBox("不能删除数据,因为未找到符合条件的记录!");
	}
}

void CQueryCountryDlg::OnCountriesUpdate() 
{
	// 使用参数绑定的方法查询表中数据
	SQLCHAR      result_country_id[4];	  //输出参数
	SQLUINTEGER  result_region_id;        //输出参数
	SQLVARCHAR   result_country_name[40]; //输出参数
	SQLINTEGER	 result_region_idInd = 0, result_country_nameLenOrInd = 0;
	SQLINTEGER   result_country_idLenOrInd = 0;

	if (findResult == TRUE)
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"update countries set country_name = ?, region_id = ? where country_id = ?", SQL_NTS ) );

		// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
		TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									40, 0, result_country_name, sizeof(result_country_name), &result_country_nameLenOrInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
									4, 0, &result_region_id, 0, &result_region_idInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									4, 0, result_country_id, sizeof(result_country_id), &result_country_idLenOrInd ) );

		// 先给参数变量赋值，然后执行SQL语句，更新表中数据
		UpdateData(TRUE); //更新控件关联变量
		result_region_id = m_intResultRegionID;
		strcpy( (char *)result_country_id, m_strResultCountryID);
		strcpy( (char *)result_country_name, m_strResultCountryName);
		result_country_idLenOrInd = SQL_NTS;
		result_country_nameLenOrInd = SQL_NTS;

		// 执行更新
		retcode = TODBCs( hstmt, SQLExecute(hstmt) );

		if (retcode != SQL_SUCCESS && retcode != SQL_SUCCESS_WITH_INFO)
		{
			//捕获异常
			AfxMessageBox("无权更新,或更新失败!");
		}

		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_RESET_PARAMS ) );
	
		// 释放绑定的参数
		TODBCs( hstmt, SQLFreeStmt( hstmt, SQL_UNBIND ) );
	}
	else
	{
		AfxMessageBox("不能更新数据,因为未找到符合条件的记录!");
	}
}

void CQueryCountryDlg::OnRadioCountryId() 
{
	m_editQueryCountryID.SetReadOnly(FALSE);
	m_editQueryCountryName.SetReadOnly(TRUE);
	m_intRadioCountryID = 0;
}

void CQueryCountryDlg::OnRadioCountryName() 
{
	m_editQueryCountryID.SetReadOnly(TRUE);
	m_editQueryCountryName.SetReadOnly(FALSE);
	m_intRadioCountryID = 1;
}