void CEmpEdit::OnBnClickedButton2()
{
// TODO: Next Record
CString var_name,var_id,var_sh,var_sa,var_dep;
CEmpTable recset(&database);
CString SqlString("SELECT * FROM EmpTable");
short col_index = 0;
rec_index++;
if(rec_index == (rc+1))
rec_index =1;
recset.Open(CRecordset::dynaset,SqlString,CRecordset::readOnly);
recset.SetAbsolutePosition(rec_index);
recset.GetFieldValue(col_index+1,var_id);
recset.GetFieldValue(col_index+2,var_name);
recset.GetFieldValue(col_index+3,var_dep);
recset.GetFieldValue(col_index+4,var_sh);
recset.GetFieldValue(col_index+5,var_sa);
EID->SetWindowText(var_id);
ENA->SetWindowText(var_name);
EWO->SetWindowText(var_sh);
ESA->SetWindowText(var_sa);
EDE->SetWindowText(var_dep);
}
void CEmpEdit::OnBnClickedButton7()
{
// TODO: Remove
CString SqlString;
CString var_name,var_id,var_sh,var_sa,var_dep,var_Eid;
CEmpTable recset(&database);
SqlString = "SELECT * FROM EmpTable";
recset.Open(CRecordset::dynaset,SqlString,CRecordset::none);
recset.SetAbsolutePosition(rec_index);
int col_index=0;
if(recset.CanUpdate())
{
recset.Delete();
if(rec_index == rc){
recset.MoveFirst();
}
else
recset.MoveNext();
AfxMessageBox("Record Successfully deleted!");
recset.GetFieldValue(col_index+1,var_id);
recset.GetFieldValue(col_index+2,var_name);
recset.GetFieldValue(col_index+3,var_dep);
recset.GetFieldValue(col_index+4,var_sh);
recset.GetFieldValue(col_index+5,var_sa);
EID->SetWindowText(var_id);
ENA->SetWindowText(var_name);
EWO->SetWindowText(var_sh);
ESA->SetWindowText(var_sa);
EDE->SetWindowText(var_dep);
rc--;
}
}
void CEmpEdit::OnBnClickedButton1()
{
// TODO: New
CString var_id;
//EID->SetSel(0,-1);
ENA->SetSel(0,-1);
EWO->SetSel(0,-1);
ESA->SetSel(0,-1);
EDE->SetSel(0,-1);
//EID->Clear();
ENA->Clear();
EWO->Clear();
ESA->Clear();
EDE->Clear();
CEmpTable recset(&database);
CString SqlString("SELECT * FROM EmpTable");
short col_index = 0;
recset.Open(CRecordset::dynaset,SqlString,CRecordset::readOnly);
//AfxMessageBox(_T("5"));
recset.MoveLast();
recset.GetFieldValue(col_index+1,var_id);
var_id.Remove('E');
//AfxMessageBox(var_id);
int k=_ttoi(var_id);
k++;
CString id;
id.Format("E%d",k);
EID->SetWindowText(id);
}
void CEmpEdit::OnBnClickedButton6()
{
// TODO: Update
CString SqlString,s1;
CString var_name,var_id,var_sh,var_sa,var_dep,var_Eid;
int flg=0,col_index=1;
CEmpTable recset(&database);
EID->GetWindowText(var_id);
ENA->GetWindowText(var_name);
EWO->GetWindowText(var_sh);
ESA->GetWindowText(var_sa);
EDE->GetWindowText(var_dep);
CString coma(", ");
//CString CB(");");
CString sc(" ;");
CString Quo("'");
CString Ei(" [Employee ID] = ");
CString De(" , [Deprtment] = ");
CString Sh(" , [Shift] = ");
CString Sa(" , [Salary] = ");
CString Id(" [ID] = ");
CString sp(" AND ");
CString wh(" WHERE ");
CString Bo(" ( ");
CString Bc(" ) ");
recset.Open(CRecordset::dynaset,_T("SELECT * FROM EmpTable"),CRecordset::readOnly);
recset.SetAbsolutePosition(rec_index);
//recset.GetFieldValue(col_index,var_id);
recset.GetFieldValue(col_index-1,var_Eid);
s1.Format("UPDATE EmpTable SET [Employee Name] = ");
SqlString=s1+Quo+var_name+Quo+De+Quo+var_dep+Quo+Sh+Quo+var_sh+Quo+Sa+var_sa+wh+Id+var_Eid+sc;
AfxMessageBox(SqlString);
try
{
database.ExecuteSQL(SqlString);
}
catch(CDBException* pe)
{
flg=1;
// The error code is in pe->m_nRetCode
pe->ReportError();
pe->Delete();
}
if (flg == 0)
{
MessageBox(_T("Record Successfully Updated To The Database"),_T("Successfull"));
}
}
///////////////////////////////////////////////////////////////////////////////
// void GetExcelDriver(CListCtrl* pList, CString strTitle)
// 參數：
// pList 需要導出的List控件指针
// strTitle 導出的數據表標題
// 說明:\
// 導出CListCtrl控件的全部數據到Excel文件。Excel文件名由用戶通過"另存為"
// 對話框輸入指定。創建名為strTitle的工作表,將List控件內的所有數據(包括列
// 名和數據項)以文本的形式保存到Excel工作表中。保持行列關係。
///////////////////////////////////////////////////////////////////////////////
void ExportExcelToList(CListCtrl* pList, CString strTitle)
{
CString warningStr;
CDatabase database;
CString sDriver;
CString sExcelFile;
CString sSql;
CString tableName = strTitle;
// 檢查是否有安裝Excel驅動 "Microsoft Excel Driver (*.xls)"
sDriver = GetExcelDriver();
if (sDriver.IsEmpty())
{
//沒有發現Excel驅動
AfxMessageBox(L"沒有安装Excel!\n請先安裝Excel軟件才能使用Excel導出功能!!");
return;
}
//默認文件名(獲得文件名)
if (!LoadGetDefaultXlsFileName(sExcelFile))
return;
//創建進行存取的字符串
sSql.Format(L"ODBC;DRIVER={%s};DSN='''';DBQ=%s",sDriver, sExcelFile);
// 創建數據庫 (既Excel表格文件)
if (database.Open(NULL,false,false,sSql)) //開啟資料庫
{
pList->DeleteAllItems();
//創建結構表
CString sItem;
CString columnName;
LVCOLUMN columnData;
columnData.mask = LVCF_TEXT;
columnData.cchTextMax = 100;
columnData.pszText = columnName.GetBuffer(100);
int ListCount = 0;
CRecordset recset(&database);
/*****************修改SQL語法在這**************************/
sSql = L"";
for (int j = 0; pList->GetColumn(j, &columnData); j++)
{
if (j != 0)
{
sSql = sSql + L", ";
}
sSql = sSql + columnData.pszText;
}
columnName.ReleaseBuffer();
sSql = L"SELECT " + sSql + L" FROM " + tableName;
AfxMessageBox(sSql);
recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);
while (!recset.IsEOF())
{
pList->InsertItem(ListCount, NULL);
for (int i = 0; pList->GetColumn(i, &columnData); i++)
{
pList->GetColumn(i, &columnData);
recset.GetFieldValue(columnData.pszText, sItem);
LONG Value = _ttol(sItem);
sItem.Format(L"%d", Value);
pList->SetItemText(ListCount, i, sItem);
}
//next
ListCount++;
recset.MoveNext();
}
}
//關閉數據庫
database.Close();
warningStr.Format(L"%s檔案導入成功!", sExcelFile);
AfxMessageBox(warningStr);
}
