querylocationdlg.cpp

A HR database application.

// QueryLocationDlg.cpp : implementation file
//

#include "stdafx.h"
#include "HRDB.h"
#include "QueryLocationDlg.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;

/////////////////////////////////////////////////////////////////////////////
// CQueryLocationDlg dialog


CQueryLocationDlg::CQueryLocationDlg(CWnd* pParent /*=NULL*/)
	: CDialog(CQueryLocationDlg::IDD, pParent)
{
	//{{AFX_DATA_INIT(CQueryLocationDlg)
	m_intQueryLocationID = 0;
	m_strResultCity = _T("");
	m_strResultLCountryID = _T("");
	m_intResultLocationID = 0;
	m_strResultPostalCode = _T("");
	m_strResultStateProvince = _T("");
	m_strResultStreetAddress = _T("");
	//}}AFX_DATA_INIT
}


void CQueryLocationDlg::DoDataExchange(CDataExchange* pDX)
{
	CDialog::DoDataExchange(pDX);
	//{{AFX_DATA_MAP(CQueryLocationDlg)
	DDX_Control(pDX, IDC_RESULT_STREET_ADDRESS, m_editResultStreetAddress);
	DDX_Control(pDX, IDC_RESULT_STATE_PROVINCE, m_editResultStateProvince);
	DDX_Control(pDX, IDC_RESULT_POSTAL_CODE, m_editResultPostalCode);
	DDX_Control(pDX, IDC_RESULT_LOCATION_ID, m_editResultLocationID);
	DDX_Control(pDX, IDC_RESULT_L_COUNTRY_ID, m_editResultLCountryID);
	DDX_Control(pDX, IDC_RESULT_CITY, m_editResultCity);
	DDX_Control(pDX, IDC_QUERY_LOCATION_ID, m_editQueryLocationID);
	DDX_Text(pDX, IDC_QUERY_LOCATION_ID, m_intQueryLocationID);
	DDX_Text(pDX, IDC_RESULT_CITY, m_strResultCity);
	DDX_Text(pDX, IDC_RESULT_L_COUNTRY_ID, m_strResultLCountryID);
	DDX_Text(pDX, IDC_RESULT_LOCATION_ID, m_intResultLocationID);
	DDX_Text(pDX, IDC_RESULT_POSTAL_CODE, m_strResultPostalCode);
	DDX_Text(pDX, IDC_RESULT_STATE_PROVINCE, m_strResultStateProvince);
	DDX_Text(pDX, IDC_RESULT_STREET_ADDRESS, m_strResultStreetAddress);
	//}}AFX_DATA_MAP
}


BEGIN_MESSAGE_MAP(CQueryLocationDlg, CDialog)
	//{{AFX_MSG_MAP(CQueryLocationDlg)
	ON_BN_CLICKED(IDC_LOCATIONS_DELETE, OnLocationsDelete)
	ON_BN_CLICKED(IDC_LOCATIONS_UPDATE, OnLocationsUpdate)
	//}}AFX_MSG_MAP
END_MESSAGE_MAP()

/////////////////////////////////////////////////////////////////////////////
// CQueryLocationDlg message handlers

BOOL CQueryLocationDlg::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 CQueryLocationDlg::OnCancel() 
{
	// 重置findResult为FALSE
	findResult = FALSE;
	
	CDialog::OnCancel();
}

void CQueryLocationDlg::OnOK() 
{
	// 使用参数绑定的方法查询表中数据
	SQLUINTEGER param_location_id;         //输入参数
	SQLUINTEGER result_location_id;        //输出参数
	SQLVARCHAR  result_street_address[40]; //输出参数
	SQLVARCHAR  result_postal_code[12];    //输出参数
    SQLVARCHAR  result_city[30];           //输出参数
	SQLVARCHAR  result_state_province[25]; //输出参数
	SQLCHAR     result_country_id[4];      //输出参数
	SQLINTEGER  param_location_idInd = 0, result_location_idInd = 0;
	SQLINTEGER  result_street_addressLenOrInd = 0, result_postal_codeLenOrInd = 0;
	SQLINTEGER  result_cityLenOrInd = 0, result_state_provinceLenOrInd =0;
	SQLINTEGER  result_country_idLenOrInd = 0;

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

	// 绑定到结果集的列
	TODBCs( hstmt, SQLBindCol( hstmt, 1, SQL_C_SLONG, &result_location_id, 0, &result_location_idInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 2, SQL_C_CHAR, result_street_address, sizeof(result_street_address), &result_street_addressLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 3, SQL_C_CHAR, result_postal_code, sizeof(result_postal_code), &result_postal_codeLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 4, SQL_C_CHAR, result_city, sizeof(result_city), &result_cityLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 5, SQL_C_CHAR, result_state_province, sizeof(result_state_province), &result_state_provinceLenOrInd ) );
	TODBCs( hstmt, SQLBindCol( hstmt, 6, SQL_C_CHAR, result_country_id, sizeof(result_country_id), &result_country_idLenOrInd ) );

	// 准备包含参数标志符的SQL语句
	TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"select * from locations where location_id = ?", SQL_NTS ) );

	// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
	TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
							4, 0, &param_location_id, 0, &param_location_idInd ) );

	// 先给参数变量赋值，然后执行SQL语句，查询表中数据
	UpdateData(TRUE); //更新控件关联变量
	param_location_id = m_intQueryLocationID;

	// 执行查询
	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_intResultLocationID = result_location_id;
			m_strResultStreetAddress = result_street_address;
			m_strResultPostalCode = result_postal_code;
			m_strResultCity = result_city;
			m_strResultStateProvince = result_state_province;
			m_strResultLCountryID = result_country_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 CQueryLocationDlg::OnLocationsDelete() 
{
	// 使用参数绑定的方法查询表中数据
	SQLUINTEGER  result_location_id;    //输出参数   
	SQLINTEGER	 result_location_idInd = 0;
	
	if (findResult == TRUE)
	{
		if (AfxMessageBox("确定要删除该记录吗?", MB_OKCANCEL, (UINT)-1) == IDOK)
		{
			// 准备包含参数标志符的SQL语句
			TODBCs( hstmt, SQLPrepare( hstmt,                        
				(unsigned char *)"delete from locations where location_id = ?", SQL_NTS ) );

			// 为每一个参数都调用函数SQLBindParameter, 绑定相应的参数
			TODBCs( hstmt, SQLBindParameter( hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
								4, 0, &result_location_id, 0, &result_location_idInd ) );

			// 先给参数变量赋值，然后执行SQL语句，更新表中数据
			UpdateData(TRUE); //更新控件关联变量
			result_location_id = m_intResultLocationID;

			// 执行更新
			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 CQueryLocationDlg::OnLocationsUpdate() 
{
	// 使用参数绑定的方法查询表中数据
	SQLUINTEGER result_location_id;        //输出参数
	SQLVARCHAR  result_street_address[40]; //输出参数
	SQLVARCHAR  result_postal_code[12];    //输出参数
    SQLVARCHAR  result_city[30];           //输出参数
	SQLVARCHAR  result_state_province[25]; //输出参数
	SQLCHAR     result_country_id[4];      //输出参数
	SQLINTEGER  result_location_idInd = 0;
	SQLINTEGER  result_street_addressLenOrInd = 0, result_postal_codeLenOrInd = 0;
	SQLINTEGER  result_cityLenOrInd = 0, result_state_provinceLenOrInd =0;
	SQLINTEGER  result_country_idLenOrInd = 0;

	if (findResult == TRUE)
	{
		// 准备包含参数标志符的SQL语句
		TODBCs( hstmt, SQLPrepare( hstmt,                        
			(unsigned char *)"update locations set street_address = ?, postal_code = ?, \
			city = ?, state_province = ?, country_id = ? where location_id = ?", SQL_NTS ) );

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

		TODBCs( hstmt, SQLBindParameter( hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									12, 0, result_postal_code, sizeof(result_postal_code), &result_postal_codeLenOrInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									30, 0, result_city, sizeof(result_city), &result_cityLenOrInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									25, 0, result_state_province, sizeof(result_state_province), &result_state_provinceLenOrInd ) );
		
		TODBCs( hstmt, SQLBindParameter( hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_VARCHAR,
									4, 0, result_country_id, sizeof(result_country_id), &result_country_idLenOrInd ) );

		TODBCs( hstmt, SQLBindParameter( hstmt, 6, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER,
									4, 0, &result_location_id, 0, &result_location_idInd ) );

		// 先给参数变量赋值，然后执行SQL语句，更新表中数据
		UpdateData(TRUE); //更新控件关联变量
		result_location_id = m_intResultLocationID;
		strcpy( (char *)result_street_address, m_strResultStreetAddress);
		strcpy( (char *)result_postal_code, m_strResultPostalCode);
		strcpy( (char *)result_city, m_strResultCity);
		strcpy( (char *)result_state_province, m_strResultStateProvince);
		strcpy( (char *)result_country_id, m_strResultLCountryID);
		result_street_addressLenOrInd = SQL_NTS;
		result_postal_codeLenOrInd = SQL_NTS;
		result_cityLenOrInd = SQL_NTS;
		result_state_provinceLenOrInd = SQL_NTS;
		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("不能更新数据,因为未找到符合条件的记录!");
	}
}