//----------------------------------------------------------------------------------------------------------------------- // 제목 : ADO 연결을 하는 함수 // // 인자 : Transaction = False // // 설명 : Transation 이 True 인 경우 트랙잭션을 시작한다 //----------------------------------------------------------------------------------------------------------------------- public static void OpenCN(bool rTransAction) { try { if (CN != null) { if (CN.State == Convert.ToInt32(ADODB.ObjectStateEnum.adStateOpen)) { return; } else { CN.Close(); } } CloseAll(); //Connection 오브젝트의 생성 CN = new ADODB.Connection(); //Connect By Using Active Data Object CN.CursorLocation = ADODB.CursorLocationEnum.adUseClient; //CN.CommandTimeout = Convert.ToInt32(CM_Main.QUERYTIME); CN.Open(CNString); if (rTransAction == true) { CN.BeginTrans(); //트랜잭션 } } catch (Exception ex) { //MessageBox.Show(e.Message + "\n Data Base Error!", "확인"); CN.Close(); } finally { } }
private void btnsave_Click(object sender, EventArgs e) { ADODB.Recordset tmp = new ADODB.Recordset(); try { if (ADOconn.State == 0) { ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0); } ADODB.Recordset rec = new ADODB.Recordset(); Conn.Close(); // Conn.Open(); bool isempty; isempty = false; if (isedit) { if (txtpriv.Text.Substring(1, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } else { if (txtpriv.Text.Substring(0, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } if (isempty) { MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry"); return; } try { // ADOconn.BeginTrans(); //if (cmbmonth.SelectedIndex<0) //{ // MessageBox.Show("Invalid Salary Month, Please Select a Valid Month", "Invalid Entry"); // return; //} ADOconn.BeginTrans(); int i = 0; string entry_no = dtentry.Value.Date.ToString("yyyyMMdd"); if (isedit) { sql = "delete from reconcil_det where Reconcil_No = '" + entry_no + "'"; object a; ADOconn.Execute(sql, out a); } sql = "select * from reconcil_det where Reconcil_No = '" + entry_no + "'"; rec = new ADODB.Recordset(); rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); if (rec.RecordCount == 0) { // rec.AddNew(); } else { MessageBox.Show("Entry Already Exist , Cannot enter as a New entry", "Invalid Entry"); return; } for (i = 0; i < dgv1.Rows.Count; i++) { if (dgv1[0, i].Value != null) { rec.AddNew(); if (dgv1[3, i].Value == null) { dgv1[3, i].Value = 0; } if (dgv1[4, i].Value == null) { dgv1[4, i].Value = 0; } double amt = Convert.ToDouble(dgv1[3, i].Value) + Convert.ToDouble(dgv1[4, i].Value); rec.Fields["Reconcil_No"].Value = entry_no; rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date; rec.Fields["Reconcil_user"].Value = Gvar.username; rec.Fields["Amount"].Value = Math.Abs(amt); rec.Fields["Bank_No"].Value = cmbbank.SelectedValue; rec.Fields["DR_CR"].Value = dgv1[2, i].Value; rec.Fields["plus_minus"].Value = ""; rec.Fields["pay_date"].Value = dgv1[0, i].Value; rec.Fields["SNO"].Value = i + 1; rec.Fields["trn_type"].Value = "A"; rec.Fields["NARRATION"].Value = dgv1[5, i].Value; rec.Update(); } } for (i = 0; i < dgvbank.Rows.Count; i++) { if (dgvbank[2, i].Value != null) { rec.AddNew(); if (dgvbank[0, i].Value == null) { dgvbank[0, i].Value = ""; } if (dgvbank[1, i].Value == null) { dgvbank[1, i].Value = 0; } double amt = Convert.ToDouble(dgvbank[2, i].Value); rec.Fields["Reconcil_No"].Value = entry_no; rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date; rec.Fields["Reconcil_user"].Value = Gvar.username; rec.Fields["Amount"].Value = Math.Abs(amt); rec.Fields["Bank_No"].Value = cmbbank.SelectedValue; if (dgvbank[1, i].Value == "+") { rec.Fields["DR_CR"].Value = "D"; } else { rec.Fields["DR_CR"].Value = "C"; } rec.Fields["plus_minus"].Value = dgvbank[1, i].Value; rec.Fields["pay_date"].Value = ""; rec.Fields["SNO"].Value = i + 1; rec.Fields["trn_type"].Value = "B"; rec.Fields["NARRATION"].Value = dgvbank[0, i].Value; rec.Update(); } } for (i = 0; i < dgvbook.Rows.Count; i++) { if (dgvbook[2, i].Value != null) { rec.AddNew(); if (dgvbook[0, i].Value == null) { dgvbook[0, i].Value = ""; } if (dgvbook[1, i].Value == null) { dgvbook[1, i].Value = 0; } double amt = Convert.ToDouble(dgvbook[2, i].Value); rec.Fields["Reconcil_No"].Value = entry_no; rec.Fields["Reconcil_Date"].Value = dtentry.Value.Date; rec.Fields["Reconcil_user"].Value = Gvar.username; rec.Fields["Amount"].Value = Math.Abs(amt); rec.Fields["Bank_No"].Value = cmbbank.SelectedValue; if (dgvbook[1, i].Value == "+") { rec.Fields["DR_CR"].Value = "D"; } else { rec.Fields["DR_CR"].Value = "C"; } rec.Fields["plus_minus"].Value = dgvbook[1, i].Value; rec.Fields["pay_date"].Value = ""; rec.Fields["SNO"].Value = i + 1; rec.Fields["trn_type"].Value = "C"; rec.Fields["NARRATION"].Value = dgvbook[0, i].Value; rec.Update(); } } ADOconn.CommitTrans(); isedit = true; MessageBox.Show("Successfully Saved"); } catch (Exception ex) { ADOconn.RollbackTrans(); MessageBox.Show(ex.Message); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
private void btnsave_Click(object sender, EventArgs e) { ADODB.Recordset tmp = new ADODB.Recordset(); try { if (!Program.session_valid(dt1.Value.Date.ToString("yyyy-MM-dd"))) { MessageBox.Show("There is no valid Finance Session Found, Please check the Entry Date or Contact Admin ", "Invalid Transaction Date ", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); return; } if (ADOconn.State == 0) { ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0); } ADODB.Recordset rec = new ADODB.Recordset(); Conn.Close(); // Conn.Open(); bool isempty; isempty = false; if (isedit) { if (txtpriv.Text.Substring(1, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } else { if (txtpriv.Text.Substring(0, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } //if (isempty) //{ // MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry"); // return; //} try { // ADOconn.BeginTrans(); // if (!isedit) // { // if (txtismanual.Text == "0") // { // gen_accno(); // tmp = new ADODB.Recordset(); // sql = "update ACC_TYPE SET CUR_NO = CUR_NO+1 WHERE ACC_TYPE_CODE=" + cmbtype.SelectedValue; // tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1); // } // } if (txtsalacno.Text == "") { MessageBox.Show("Invalid Salary Account Number, Please Enter a Valid Number", "Invalid Entry"); return; } if (cmbmonth.SelectedIndex < 0) { MessageBox.Show("Invalid Salary Month, Please Select a Valid Month", "Invalid Entry"); return; } if (txtnet.Text.Trim() == "") { txtnet.Text = "0"; } // //if (txtvalue.Text.Trim() == "") txtvalue.Text = "0"; isini = true; if (txtbasic.Text == "") { txtbasic.Text = "0"; } if (txtempid.Text == "") { txtempid.Text = "0"; } if (txthousing.Text == "") { txthousing.Text = "0"; } if (txttransport.Text == "") { txttransport.Text = "0"; } if (txtother.Text == "") { txtother.Text = "0"; } if (txtdeduction.Text == "") { txtdeduction.Text = "0"; } if (txtloan.Text == "") { txtloan.Text = "0"; } if (txtcontractno.Text == "") { txtcontractno.Text = "0"; } if (txtworkeddays.Text == "") { txtworkeddays.Text = "0"; } if (txtnet.Text == "") { txtnet.Text = "0"; } if (txtsalacno.Text == "") { txtsalacno.Text = "0"; } if (txtempid.Text == "") { txtempid.Text = "0"; } if (txtpaidamt.Text == "") { txtpaidamt.Text = "0"; } if (txtbrncode.Text == "") { txtbrncode.Text = Gvar.brn_code.ToString(); } if (txtcontractno.Text == "0") { MessageBox.Show("Invalid Contract Numebr, Please check and Try Again", "Invalid Entry"); return; } if (Convert.ToDecimal(txtpaidamt.Text) > Convert.ToDecimal(txtnet.Text)) { MessageBox.Show("There is already a higher Paid amount found, Please check and Try Again", "Invalid Entry"); return; } isini = false; find_total(); //rec.Fields["veh_no"].Value = Convert.ToDouble(txtvehno.Text.Trim()); ADOconn.BeginTrans(); int TRN_BY = 13; sql = "select * from TRN_accounts where trn_NO = '" + txtactrnno.Text.Trim() + "' AND TRN_BY = 13 and Sno = 1"; rec = new ADODB.Recordset(); rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); long trnno = 0; long trnno2 = 0; if (rec.RecordCount == 0) { rec.AddNew(); tmp = new ADODB.Recordset(); sql = "SELECT * FROM TRNNO"; tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1); //if (tmp.Fields[0].Value) // txttrn.Text="1"; //else if (tmp.Fields[0].Value == DBNull.Value) { trnno = 1; } else { trnno = Convert.ToInt64(tmp.Fields[0].Value.ToString()); trnno2 = trnno + 1; } rec.Fields["trn_no"].Value = trnno; rec.Fields["trn_no2"].Value = trnno + 1; } trnno = Convert.ToInt64(rec.Fields["trn_no"].Value); double amt = Convert.ToDouble(txtnet.Text); long doc_no = -1 * trnno; string DR_CR = "D"; string DR_CR1 = "C"; rec.Fields["acc_no"].Value = txtempacno.Text; rec.Fields["EntrY_no"].Value = 0; double rate = 1; rec.Fields["PAY_AMOUNT"].Value = Math.Abs(amt * rate); rec.Fields["F_PAY_AMOUNT"].Value = Math.Abs(amt); rec.Fields["F_RATE"].Value = 1; rec.Fields["TRN_BY"].Value = TRN_BY; rec.Fields["DR_CR"].Value = DR_CR; rec.Fields["user_ID"].Value = Gvar.Userid; rec.Fields["PAYBY"].Value = txtsalacno.Text; //rec.Fields["RQTY"].Value = 0; rec.Fields["SNO"].Value = 1; // rec.Fields["FRACTION"].Value = dgv1["fraction", i].Value; rec.Fields["NARRATION"].Value = "Salary Process of " + cmbmonth.Text; rec.Fields["Voucher_No"].Value = 0; rec.Fields["cost_code"].Value = 0; rec.Fields["dept_code"].Value = 0; rec.Fields["pay_date"].Value = dt1.Value; rec.Fields["doc_no"].Value = doc_no; rec.Fields["NYEAR"].Value = dt1.Value.Year; rec.Fields["brn_code"].Value = Gvar.brn_code; rec.Fields["currency"].Value = "SR"; rec.Fields["brn_code"].Value = txtbrncode.Text; rec.Fields["trn_type"].Value = TRN_BY; rec.Update(); sql = "select * from TRN_accounts where trn_NO2 = '" + txtactrnno.Text.Trim() + "' AND TRN_BY = 13 and Sno = 2"; rec = new ADODB.Recordset(); rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); if (rec.RecordCount == 0) { rec.AddNew(); tmp = new ADODB.Recordset(); sql = "SELECT * FROM TRNNO"; tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1); //if (tmp.Fields[0].Value) // txttrn.Text="1"; //else if (tmp.Fields[0].Value == DBNull.Value) { trnno2 = 1; } else { trnno2 = Convert.ToInt64(tmp.Fields[0].Value.ToString()); } rec.Fields["trn_no"].Value = trnno2; rec.Fields["trn_no2"].Value = trnno; } trnno2 = Convert.ToInt64(rec.Fields["trn_no"].Value); rec.Fields["acc_no"].Value = txtsalacno.Text; rec.Fields["EntrY_no"].Value = 0; rec.Fields["PAY_AMOUNT"].Value = Math.Abs(amt * rate); rec.Fields["F_PAY_AMOUNT"].Value = Math.Abs(amt); rec.Fields["F_RATE"].Value = 1; rec.Fields["TRN_BY"].Value = TRN_BY; rec.Fields["DR_CR"].Value = DR_CR1; rec.Fields["user_ID"].Value = Gvar.Userid; rec.Fields["PAYBY"].Value = txtempacno.Text; //rec.Fields["RQTY"].Value = 0; rec.Fields["SNO"].Value = 2; // rec.Fields["FRACTION"].Value = dgv1["fraction", i].Value; rec.Fields["NARRATION"].Value = "Salary Process of " + cmbmonth.Text + " for " + lblname.Text; rec.Fields["Voucher_No"].Value = 0; rec.Fields["cost_code"].Value = 0; rec.Fields["dept_code"].Value = 0; rec.Fields["pay_date"].Value = dt1.Value; rec.Fields["doc_no"].Value = doc_no; rec.Fields["NYEAR"].Value = dt1.Value.Year; rec.Fields["brn_code"].Value = txtbrncode.Text; rec.Fields["trn_type"].Value = TRN_BY; rec.Fields["currency"].Value = "SR"; rec.Update(); rec = new ADODB.Recordset(); sql = "SELECT * FROM salary_det where salary_month = '" + cmbmonth.Text + "' and emp_id =" + txtempid.Text; // sql = "SELECT * FROM Accounts where Acc_no ='" + txtaccno.Text.Trim() + "'"; rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); if (rec.RecordCount == 0) { rec.AddNew(); } rec.Fields["EMP_ID"].Value = txtempid.Text.Trim(); rec.Fields["SALARY_MONTH"].Value = cmbmonth.Text.Trim(); rec.Fields["EFFECT_DATE"].Value = dt1.Value.Date.ToString("yyyy-MM-dd"); rec.Fields["FULLNAME"].Value = lblname.Text; rec.Fields["BASIC"].Value = txtbasic.Text; rec.Fields["HOUSING"].Value = txthousing.Text.Trim(); rec.Fields["TRANSPORTATION"].Value = txttransport.Text.Trim(); rec.Fields["OTHER"].Value = txtother.Text.Trim(); rec.Fields["DEDUCTION"].Value = txtdeduction.Text.Trim(); rec.Fields["LOAN_AMT"].Value = txtloan.Text.Trim(); rec.Fields["REMARKS"].Value = txtremarks.Text.Trim(); rec.Fields["NET_AMOUNT"].Value = txtnet.Text.Trim(); rec.Fields["SAL_ACNO"].Value = txtsalacno.Text.Trim(); rec.Fields["EMP_ACNO"].Value = txtempacno.Text.Trim(); rec.Fields["worked_days"].Value = txtworkeddays.Text.Trim(); rec.Fields["CONTRACT_ID"].Value = txtcontractno.Text.Trim(); rec.Fields["NYEAR"].Value = dt1.Value.Year.ToString(); rec.Fields["TRN_NO1"].Value = trnno; rec.Fields["TRN_NO2"].Value = trnno2; rec.Fields["EMP_BraNCH"].Value = txtbrncode.Text.Trim(); rec.Update(); ADOconn.CommitTrans(); populate_empdet(); //DataGridViewRow row = dgv1.CurrentRow; //if (txtempid.Text.ToString() == dgv1[1, dgv1.CurrentRow.Index].Value.ToString()) //{ // dgv1.Rows.Remove(row); //} isedit = true; MessageBox.Show("Successfully Saved"); } catch (Exception ex) { ADOconn.RollbackTrans(); MessageBox.Show(ex.Message); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
//string sql; private void save_form() { ADODB.Connection ADOconn = new ADODB.Connection(); ADODB.Recordset tmp = new ADODB.Recordset(); try { //if (tooltip.Text.Trim().Length < 3) //{ // MessageBox.Show("Invalid Length of Code, Must Be 3 Digit!!"); // return; //} ADOconn.Open("Provider=SQLOLEDB;Initial Catalog= " + decoder.InitialCatalog + ";Data Source=" + decoder.DataSource + ";", decoder.UserID, decoder.Password, 0); ADODB.Recordset rec = new ADODB.Recordset(); Conn.Close(); // Conn.Open(); bool isempty; isempty = false; if (isedit) { if (txtpriv.Text.Substring(1, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } else { if (txtpriv.Text.Substring(0, 1) == "0") { MessageBox.Show("Insufficient Priveleges ", "Insufficient Priveleges "); return; } } foreach (Control gb in this.Controls) { if (gb is GroupBox) { foreach (Control tb in gb.Controls) { if (tb is TextBox) { if (tb.Tag == "1") { tb.BackColor = System.Drawing.Color.White; if (string.IsNullOrEmpty(tb.Text.Trim())) { tb.BackColor = System.Drawing.Color.Yellow; isempty = true; } } } } } } if (isempty) { MessageBox.Show("Entry Not Completed, Please fill all Yellow Marked fileds!!", "Invalid Entry"); return; } try { ADOconn.BeginTrans(); if (!isedit) { gen_accno(); tmp = new ADODB.Recordset(); //sql = "update ACC_TYPE SET CUR_NO = CUR_NO+1 WHERE ACC_TYPE_CODE=" + cmbCostCode.SelectedValue; // tmp.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, -1); } if (txtProjectAmt.Text.Trim() == "") { txtProjectAmt.Text = "0"; } //if (txtvalue.Text.Trim() == "") txtvalue.Text = "0"; //sql = "SELECT * FROM Customer where Customer_no =" + Convert.ToDecimal(txtcuscode.Text.Trim()); sql = "SELECT * FROM project_master where project_code ='" + txtprojectcode.Text.Trim() + "'"; rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); if (rec.RecordCount == 0) { rec.AddNew(); } if (txtProjectAmt.Text == "") { txtProjectAmt.Text = "0"; } if (txtpercent.Text == "") { txtpercent.Text = "0"; } //rec.Fields["veh_no"].Value = Convert.ToDouble(txtvehno.Text.Trim()); rec.Fields["Project_Code"].Value = txtprojectcode.Text.Trim(); rec.Fields["Project_Name"].Value = txtProjectname.Text.Trim(); rec.Fields["Client_code"].Value = cmbacc.SelectedValue; rec.Fields["Cost_Code"].Value = Convert.ToInt32(txtprojectcode.Text); //cmbCostCode.SelectedValue; rec.Fields["Start_date"].Value = dt1.Value.Date.ToString("yyyy-MM-dd");; rec.Fields["End_Date"].Value = dtend.Value.Date.ToString("yyyy-MM-dd"); rec.Fields["Project_det"].Value = txtDescription.Text.Trim(); rec.Fields["completed"].Value = Convert.ToDouble(txtpercent.Text); rec.Fields["Status"].Value = cmbstatus.Text.Trim(); rec.Fields["Incharge"].Value = cmbincharge.Text.Trim(); rec.Fields["Location"].Value = cmblocaltion.Text.Trim(); rec.Fields["Project_Amt"].Value = Convert.ToDouble(txtProjectAmt.Text); rec.Update(); sql = "select Cost_Code,Cost_name from Cost_Master where cost_code= " + Convert.ToInt32(txtprojectcode.Text); rec = new ADODB.Recordset(); rec.Open(sql, ADOconn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1); if (rec.RecordCount == 0) { rec.AddNew(); } rec.Fields["Cost_Code"].Value = Convert.ToInt32(txtprojectcode.Text); rec.Fields["Cost_name"].Value = txtProjectname.Text.Trim(); rec.Update(); ADOconn.CommitTrans(); isedit = true; MessageBox.Show("Successfully Saved"); load_leaders(); } catch (Exception ex) { ADOconn.RollbackTrans(); MessageBox.Show(ex.Message); } } catch (Exception ex) { MessageBox.Show(ex.Message); } }