public int runinsertquery(string query) { try { cmd = new OleDbCommand(query, con); return cmd.ExecuteNonQuery(); } catch (Exception ex) { Out.Print.Line(ex.Message); } return -1; }
//************************************************************************** /// <Description> /// This method uses to add data to PO_ItemVendorReferenceDetail /// </Description> /// <Inputs> /// PO_ItemVendorReferenceDetailVO /// </Inputs> /// <Outputs> /// newly inserted primarkey value /// </Outputs> /// <Returns> /// void /// </Returns> /// <Authors> /// HungLa /// </Authors> /// <History> /// Tuesday, March 01, 2005 /// </History> /// <Notes> /// </Notes> //************************************************************************** public void Add(object pobjObjectVO) { const string METHOD_NAME = THIS + ".Add()"; OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; try { PO_ItemVendorReferenceDetailVO objObject = (PO_ItemVendorReferenceDetailVO)pobjObjectVO; string strSql = String.Empty; Utils utils = new Utils(); oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString); ocmdPCS = new OleDbCommand("", oconPCS); strSql = "INSERT INTO PO_ItemVendorReferenceDetail(" + PO_ItemVendorReferenceDetailTable.ITEMVENDORREFERENCEID_FLD + "," + PO_ItemVendorReferenceDetailTable.ENDDATE_FLD + "," + PO_ItemVendorReferenceDetailTable.UNITPRICE_FLD + "," + PO_ItemVendorReferenceDetailTable.FROMQUANTITY_FLD + "," + PO_ItemVendorReferenceDetailTable.TOQUANTITY_FLD + "," + PO_ItemVendorReferenceDetailTable.FROMPRICE_FLD + "," + PO_ItemVendorReferenceDetailTable.TOPRICE_FLD + ")" + "VALUES(?,?,?,?,?,?,?)"; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.ITEMVENDORREFERENCEID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.ITEMVENDORREFERENCEID_FLD].Value = objObject.ItemVendorReferenceID; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.ENDDATE_FLD, OleDbType.Date)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.ENDDATE_FLD].Value = objObject.EndDate; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.UNITPRICE_FLD, OleDbType.Decimal)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.UNITPRICE_FLD].Value = objObject.UnitPrice; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.FROMQUANTITY_FLD, OleDbType.Decimal)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.FROMQUANTITY_FLD].Value = objObject.FromQuantity; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.TOQUANTITY_FLD, OleDbType.Decimal)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.TOQUANTITY_FLD].Value = objObject.ToQuantity; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.FROMPRICE_FLD, OleDbType.Decimal)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.FROMPRICE_FLD].Value = objObject.FromPrice; ocmdPCS.Parameters.Add(new OleDbParameter(PO_ItemVendorReferenceDetailTable.TOPRICE_FLD, OleDbType.Decimal)); ocmdPCS.Parameters[PO_ItemVendorReferenceDetailTable.TOPRICE_FLD].Value = objObject.ToPrice; ocmdPCS.CommandText = strSql; ocmdPCS.Connection.Open(); ocmdPCS.ExecuteNonQuery(); } catch (OleDbException ex) { if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex); } else { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } } catch (InvalidOperationException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } }
//---------------------MENU INPUT EDIT----------------------// //SIMPAN BIODATA// private void buttonSimpanMasterBiodata_Click(object sender, EventArgs e) { //Cek Data Yang Ga Diisi// if (textBoxNamaLengkapMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Nama Lengkap belum diisi Gan"); } else if (textBoxNamaPanggilanMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Nama Panggilannya belum diisi Gan"); } else if (textBoxTempatLhrMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Tempat Lahir belum diisi Gan"); } else if (dateTimePickerTanggalLhrMasterBiodata.Value.ToString() == string.Empty) { MessageBox.Show("Tanggal belum diisi Gan"); } else if (comboBoxJenisKelaminMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Jenis Kelamin belum diisi Gan"); } else if (comboBoxAgamaMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Agama belum diisi Gan"); } else if (textBoxBahasaMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Bahasa belum diisi Gan"); } else if (textBoxKewarganegaraanMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Kewarganegaraan belum diisi Gan"); } else if (comboBoxStatusKlgMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Status Keluarga belum diisi Gan"); } else if (comboBoxEkoKlgMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Keadaan Ekonomi belum diisi Gan"); } else if (textBoxAlamatMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Alamat belum diisi Gan"); } else if (textBoxAlamatSkrgMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Alamat Sekarang belum diisi Gan"); } else if (textBoxTinggalMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Tinggal Bersama belum diisi Gan"); } else if (comboBoxKeadaan_RumahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Keadaan Rumah belum diisi Gan"); } else if (comboBoxR_TidurMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Ruang Tidur belum diisi Gan"); } else if (comboBoxR_BelajarMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Ruang Belajar belum diisi Gan"); } else if (comboBoxPenunjangBelajarMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Penunjang Belajar belum diisi Gan"); } else if (textBoxNo_TlpMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("No. Telp belum diisi Gan"); } else if (textBoxEmailMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Email belum diisi Gan"); } else if (textBoxNama_AyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Nama Ayah belum diisi Gan"); } else if (textBoxTempat_Lhr_AyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Tempat Lahir Ayah belum diisi Gan"); } else if (dateTimePickerTanggal_Lhr_AyahMasterBiodata.Value.ToString() == string.Empty) { MessageBox.Show("Tanggal Lahir Ayah belum diisi Gan"); } else if (textBoxAlamat_AyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Alamat Ayah belum diisi Gan"); } else if (comboBoxAgama_AyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Agama belum diisi Gan"); } else if (textBoxPekerjaan_AyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Pekerjaan Ayah belum diisi Gan"); } else if (textBoxPendidikan_AyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Pendidikan Ayah belum diisi Gan"); } else if (textBoxPenghasilanAyahMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Penghasilan Ayah belum diisi Gan"); } else if (textBoxNama_IbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Nama Ibu belum diisi Gan"); } else if (textBoxTempat_Lhr_IbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Tempat Lahir Ibu belum diisi Gan"); } else if (dateTimePickerTanggal_Lhr_IbuMasterBiodata.Value.ToString() == string.Empty) { MessageBox.Show("Tanggal Lahir Ibu belum diisi Gan"); } else if (textBoxAlamat_IbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Alamat Ibu belum diisi Gan"); } else if (comboBoxAgamaIbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Agama Ibu belum diisi Gan"); } else if (textBoxPekerjaan_IbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Pekerjaan Ibu belum diisi Gan"); } else if (textBoxPendidikan_IbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Pendidikan Ibu belum diisi Gan"); } else if (textBoxPenghasilan_IbuMasterBiodata.Text.Trim() == string.Empty) { MessageBox.Show("Penghasilan Ibu belum diisi Gan"); } else if (comboBoxJurusan.Text.Trim() == string.Empty) { MessageBox.Show("Jurusan belum dipilih Gan"); } else { OleDbConnection koneksi = new OleDbConnection(db); koneksi.Open(); string query = ""; query = "INSERT INTO MASTER_BIODATA (TANGGAL_DAFTAR, NAMA_LENGKAP, NAMA, " + "TEMPAT_LHR, TANGGAL_LHR, JENIS_KEL, AGAMA, " + "BAHASA, KEWARGANEGARAAN, STATUS_KLG, EKO_KLG, ALAMAT, ALAMAT_SKRG, " + "TINGGAL, KEADAAN_RUMAH, R_TIDUR, R_BELAJAR, PENUNJANG_BELAJAR, " + "NO_TLP, EMAIL, NAMA_AYAH, TEMPAT_LHR_AYAH, TANGGAL_LHR_AYAH, ALAMAT_AYAH, AGAMA_AYAH, " + "PEKERJAAN_AYAH, PENDIDIKAN_AYAH, PENGHASILAN_AYAH, NAMA_IBU, TEMPAT_LHR_IBU," + "TANGGAL_LHR_IBU, ALAMAT_IBU, AGAMA_IBU, PEKERJAAN_IBU, PENDIDIKAN_IBU, " + "PENGHASILAN_IBU, JURUSAN ) VALUES ('" + DateTime.Now.ToString() + "', '" //TANGGAL_DAFTAR + textBoxNamaLengkapMasterBiodata.Text.Replace("'", "''") + "', '" //NAMA_LENGKAP + textBoxNamaPanggilanMasterBiodata.Text.Replace("'", "''") + "', '" // NAMA + textBoxTempatLhrMasterBiodata.Text.Replace("'", "''") + "', '" //TEMPAT_LHR + dateTimePickerTanggalLhrMasterBiodata.Value.ToString() + "', '" //TANGGAL_LHR + comboBoxJenisKelaminMasterBiodata.Text.Replace("'", "''") + "', '" //JENIS_KEL + comboBoxAgamaMasterBiodata.Text.Replace("'", "''") + "', '" //AGAMA + textBoxBahasaMasterBiodata.Text.Replace("'", "''") + "', '" //BAHASA + textBoxKewarganegaraanMasterBiodata.Text.Replace("'", "''") + "', '" //KEWARGANEGARAAN + comboBoxStatusKlgMasterBiodata.Text.Replace("'", "''") + "', '" //STATUS_KLG + comboBoxEkoKlgMasterBiodata.Text.Replace("'", "''") + "', '" //EKO_KLG + textBoxAlamatMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT + textBoxAlamatSkrgMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT_SKRG + textBoxTinggalMasterBiodata.Text.Replace("'", "''") + "', '" // TINGGAL + comboBoxKeadaan_RumahMasterBiodata.Text.Replace("'", "''") + "', '" //KEADAAN_RUMAH + comboBoxR_TidurMasterBiodata.Text.Replace("'", "''") + "', '" //R_TIDUR + comboBoxR_BelajarMasterBiodata.Text.Replace("'", "''") + "', '" //R_BELAJAR + comboBoxPenunjangBelajarMasterBiodata.Text.Replace("'", "''") + "', '" //PENUNJANG_BELAJAR + textBoxNo_TlpMasterBiodata.Text.Replace("'", "''") + "', '" //NO_TLP + textBoxEmailMasterBiodata.Text.Replace("'", "''") + "', '" //EMAIL + textBoxNama_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //NAMA_AYAH + textBoxTempat_Lhr_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //TEMPAT_LHR_AYAH + dateTimePickerTanggal_Lhr_AyahMasterBiodata.Value.ToString() + "', '" //TANGGAL_LAHIR_AYAH + textBoxAlamat_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT_AYAH + comboBoxAgama_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //AGAMA_AYAH + textBoxPekerjaan_AyahMasterBiodata.Text.Replace("'", "''") + "', '" //PEKERJAAN_AYAH + textBoxPendidikan_AyahMasterBiodata.Text.Replace("'", "''") + "', " //PENDIDIKAN_AYAH + textBoxPenghasilanAyahMasterBiodata.Text.Replace("'", "''") + ", '" //PENGHASILAN_AYAH + textBoxNama_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //NAMA_IBU + textBoxTempat_Lhr_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //TEMPAT_LHR_IBU + dateTimePickerTanggal_Lhr_IbuMasterBiodata.Value.ToString() + "', '" //TANGGAL_LHR_IBU + textBoxAlamat_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //ALAMAT_IBU + comboBoxAgamaIbuMasterBiodata.Text.Replace("'", "''") + "', '" //AGAMA_IBU + textBoxPekerjaan_IbuMasterBiodata.Text.Replace("'", "''") + "', '" //PEKERJAAN_IBU + textBoxPendidikan_IbuMasterBiodata.Text.Replace("'", "''") + "', " //PENDIDIKAN_IBU + textBoxPenghasilan_IbuMasterBiodata.Text.Replace("'", "''") + ", '" //PENGHASILAN_IBU + comboBoxJurusan.Text.Replace("'", "''") + "')"; //JURUSAN //END OleDbCommand komen = new OleDbCommand(); komen.CommandText = query; komen.Connection = koneksi; komen.ExecuteNonQuery(); koneksi.Close(); MessageBox.Show("Data Berhasil Disimpan"); Data(); } }
public void ExecuteNonQuery(string sql) { OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.ExecuteNonQuery(); }
protected ColumnInfo SqlAddColumn(string columnName, string accessType, bool isDynamic, bool updateDatabase) { ColumnInfo result = null; if (updateDatabase) { try { BeforeSqlQuery(); /////////////////////////////////////////////////////////////////////////////// try { var commandText = String.Format("ALTER TABLE [{0}] ADD COLUMN [{1}] {2}", _tableName, columnName, accessType); var cmd = new OleDbCommand(commandText, _connection); cmd.ExecuteNonQuery(); } catch (Exception ex) { var hresult = -2147467259; if (ex.HResult == hresult) { throw new Exception("Too many columns defined. To continue adding new columns please open this database in Access and press 'Compact and Repair'"); } throw; } /////////////////////////////////////////////////////////////////////////////// ResetCommands(); } finally { AfterSqlQuery(); } } else { ResetCommands(); } /////////////////////////////////////////////////////////////////////////////// var clrType = AccessTypeToClrType(accessType); _table.Columns.Add(columnName, clrType); /////////////////////////////////////////////////////////////////////////////// result = new ColumnInfo { Name = columnName, Type = clrType, IsDynamic = isDynamic, IsExtra = !isDynamic }; _allColumnsList.Add(result); /////////////////////////////////////////////////////////////////////////////// AdjustSqlColumnIndices(); /////////////////////////////////////////////////////////////////////////////// return(result); }
private void WriteQuoteRecord() { string file = vari.DefaultDirectory + "Quotes.accdb"; string ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" + file + ";"; // Attempt to connect to the database using (var connection1 = new OleDbConnection(ConnectionString)) { OleDbCommand OComm = new OleDbCommand(); OComm.Connection = connection1; try { int q1; int.TryParse(tbQty1.Text, out q1); int q2; int.TryParse(tbQty2.Text, out q2); int q3; int.TryParse(tbQty3.Text, out q3); int q4; int.TryParse(tbQty4.Text, out q4); double p1; double.TryParse(lq1.Text, out p1); double p2; double.TryParse(lq2.Text, out p2); double p3; double.TryParse(lq3.Text, out p3); double p4; double.TryParse(lq4.Text, out p4); double dia; double.TryParse(cmbDiameter.Text, out dia); double length; double.TryParse(tbLength.Text, out length); double watt; double.TryParse(tbWatts.Text, out watt); double volt; double.TryParse(tbVolts.Text, out volt); int leads; int.TryParse(tbLeads.Text, out leads); int leadcov; int.TryParse(tbLeadCov.Text, out leadcov); double multi; double.TryParse(tbLabor.Text, out multi); double adder; double.TryParse(tbManualAdder.Text, out adder); string ad1 = ""; double ad1p = 0; string ad2 = ""; double ad2p = 0; string ad3 = ""; double ad3p = 0; string ad4 = ""; double ad4p = 0; string ad5 = ""; double ad5p = 0; string ad6 = ""; double ad6p = 0; //VALIDATE THE ARRAY AND SET VARIABLES TO NULL IF ARRAY IS NULL if (vari.AdderArray == null || vari.AdderArray.Length == 0) { ad1 = ""; ad1p = 0; ad2 = ""; ad2p = 0; ad3 = ""; ad3p = 0; ad4 = ""; ad4p = 0; ad5 = ""; ad5p = 0; ad6 = ""; ad6p = 0; } else { if (vari.AdderArray.GetLength(0) > 0) { ad1 = vari.AdderArray[0, 0].ToString(); ad1p = Convert.ToDouble(vari.AdderArray[0, 1]); } else { ad1 = ""; ad1p = 0; } if (vari.AdderArray.GetLength(0) > 1) { ad2 = vari.AdderArray[1, 0].ToString(); ad2p = Convert.ToDouble(vari.AdderArray[1, 1]); } else { ad2 = ""; ad2p = 0; } if (vari.AdderArray.GetLength(0) > 2) { ad3 = vari.AdderArray[2, 0].ToString(); ad3p = Convert.ToDouble(vari.AdderArray[2, 1]); } else { ad3 = ""; ad3p = 0; } if (vari.AdderArray.GetLength(0) > 3) { ad4 = vari.AdderArray[3, 0].ToString(); ad4p = Convert.ToDouble(vari.AdderArray[3, 1]); } else { ad4 = ""; ad4p = 0; } if (vari.AdderArray.GetLength(0) > 4) { ad5 = vari.AdderArray[4, 0].ToString(); ad5p = Convert.ToDouble(vari.AdderArray[4, 1]); } else { ad5 = ""; ad5p = 0; } if (vari.AdderArray.GetLength(0) > 5) { ad6 = vari.AdderArray[5, 0].ToString(); ad6p = Convert.ToDouble(vari.AdderArray[5, 1]); } else { ad6 = ""; ad6p = 0; } } connection1.Open(); // INSERT A NEW RECORD FOR THE QUOTE string CmdLine1 = "INSERT INTO CartQuotes (cust,dte,q1,q2,q3,q4,p1,p2,p3,p4,dia,length,termstyle,leads,leadcov,watts,volts,multi,adder,notes,smt,filename,ad1,ad1p,ad2,ad2p,ad3,ad3p,ad4,ad4p,ad5,ad5p,ad6,ad6p,pn) " + "VALUES ('" + myComboBox.Text + "','" + DateTime.Now + "'," + q1 + "," + q2 + "," + q3 + "," + q4 + "," + vari.p1 + "," + vari.p2 + "," + vari.p3 + "," + vari.p4 + "," + " '" + cmbDiameter.Text + "'," + length + ",'" + cmbTermStyle.Text + "'," + leads + "," + leadcov + "," + watt + "," + volt + "," + multi + "," + adder + ", '" + tbSpecials.Text + "','" + tbSMT.Text + "'," + " '" + vari.SavedPDFDir + "','" + ad1 + "'," + ad1p + ",'" + ad2 + "'," + ad2p + ",'" + ad3 + "'," + ad3p + ",'" + ad4 + "'," + ad4p + ",'" + ad5 + "'," + ad5p + ",'" + ad6 + "'," + ad6p + ",'" + vari.pn + "'); "; OleDbCommand Insert1 = new OleDbCommand(CmdLine1, connection1); Insert1.ExecuteNonQuery(); } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { connection1.Close(); } } }
public FuncStatus DataBind(BindOperator _bp) { bool _tf = true; int i; OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString()); OleDbCommand _cmd = new OleDbCommand(); OleDbDataReader _dr; _cmd.Connection = _conn; try { _conn.Open(); if (_bp == BindOperator.Update) { _cmd.CommandText = "delete from Images where pID = " + this.pID; _cmd.ExecuteNonQuery(); } if (_bp == BindOperator.Insert || _bp == BindOperator.Update) { i = 0; while (i < _max) { _cmd.CommandText = "insert into Images (Path,pID) values ('" + this.Path[i] + "'," + this.pID + ")"; _cmd.ExecuteNonQuery(); i++; } } _cmd.CommandText = "select ID,Path,pID from Images where pID=" + this.pID; _dr = _cmd.ExecuteReader(); i = 0; while (_dr.Read()) { this.ID[i] = _dr.GetInt32(0); this.Path[i] = _dr.GetValue(1).ToString(); this.pID = _dr.GetInt32(2); i++; } } catch (Exception ex) { _tf = false; Exception _ex = new Exception("ORM.Images:" + ex.Message); throw _ex; } finally { _conn.Close(); } return _tf == false ? FuncStatus.Fail : FuncStatus.Success; }
public int connectEdit(Student x, string editchoice, string editdata) { OleDbConnection connection = new OleDbConnection(); try { connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; "; //var cmd = new OleDbCommand("UPDATE Student SET "+"@editchoice"+" = '"+"@entrydata"+"' WHERE "+"@a"+" = @searchdata;"); //need correct reference var cmd = new OleDbCommand("UPDATE Student SET FirstName = 'bananak' WHERE Id = '123456';"); //need correct reference connection.Open(); cmd.Parameters.AddRange(new[] { new OleDbParameter("@entrytype", editchoice), new OleDbParameter("@entrydata", x.Id), new OleDbParameter("@a", "Id"), new OleDbParameter("@searchdata", x.Id) }); cmd.Connection = connection; Console.WriteLine(editchoice); Console.WriteLine(editdata); Console.WriteLine(x.Id); cmd.ExecuteNonQuery(); Console.WriteLine("Student edited successfully."); } catch(Exception ex) { Console.WriteLine("Error " + ex); } connection.Close(); return 0; }
private Boolean DatabaseWrite(DataEntry inputEntry) { // Writes a given inputEntry to the Access Database string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DatabaseLocation + ";Persist Security Info=False;"; string EmployeeInsertCommand = "INSERT INTO `Client Contact Information` ("; string CaseInsertCommand = "INSERT INTO Documentation ("; // Writing Insert Command strings String[] EmployeeFields = new String[10] { "Employment Number", "Last Name", "First Name", "Street Address", "City", "Province", "Postal Code", "Phone Number (Home)", "Phone Number (Work)", "Email" }; // Consider replacing with reference // Also may differ from actual DB for (int i = 0; i < EmployeeFields.Length; i++) { EmployeeInsertCommand += "`" + EmployeeFields[i] + "`"; if (i != EmployeeFields.Length - 1) { EmployeeInsertCommand += ","; } } EmployeeInsertCommand += ") VALUES ("; if (!inputEntry.ExistingPerson) { for (int i = 0; i < EmployeeFields.Length; i++) { object entryValue = inputEntry.PersonalFields[EmployeeFields[i]]; if (entryValue is int) { EmployeeInsertCommand += entryValue.ToString(); } else { EmployeeInsertCommand += "\"" + entryValue.ToString() + "\""; } if (i != EmployeeFields.Length - 1) { EmployeeInsertCommand += ","; } } } EmployeeInsertCommand += ")"; String[] CaseFields = new String[7] { "Employee Number", "Status", "LTD Eligible", "Referral Recieved", "Sick Leave Start", "Hourly Salary", "Hours Worked/Day" }; //Replace with reference probably //Ignores ID and non filled fields for (int i = 0; i < CaseFields.Length; i++) { CaseInsertCommand += "`" + CaseFields[i] + "`"; if (i != CaseFields.Length - 1) { CaseInsertCommand += ","; } } CaseInsertCommand += ") VALUES ("; for (int i = 0; i < CaseFields.Length; i++) { object entryValue = inputEntry.RTWFields[CaseFields[i]]; if (entryValue == null) { entryValue = ""; } if (entryValue is int || entryValue is bool || entryValue is decimal) { CaseInsertCommand += entryValue.ToString(); } else { CaseInsertCommand += "\"" + entryValue.ToString() + "\""; } if (i != CaseFields.Length - 1) { CaseInsertCommand += ","; } } CaseInsertCommand += ")"; OleDbConnection DatabaseConnection = new OleDbConnection(ConnectionString); OleDbCommand EmployeeCommand = new OleDbCommand(EmployeeInsertCommand, DatabaseConnection); OleDbCommand CaseCommand = new OleDbCommand(CaseInsertCommand, DatabaseConnection); // Attempts to Connect to the database try { DatabaseConnection.Open(); // DB Access Here is the first table as there is only one because of the query if (!inputEntry.ExistingPerson) { EmployeeCommand.ExecuteNonQuery(); } CaseCommand.ExecuteNonQuery(); DatabaseConnection.Close(); return(true); } catch (Exception ex) { // Make sure to close DB on fail or success DatabaseConnection.Close(); MessageBox.Show(ex.Message); if (MessageBox.Show("Error occurred in Database Connect", "Retry?", MessageBoxButtons.YesNo, MessageBoxIcon.Asterisk) == DialogResult.Yes) { return(DatabaseWrite(inputEntry)); } else { return(false); } } }
private void AddMerchandise_Click(object sender, EventArgs e) { string strName = listBox1.SelectedItem.ToString(); int stockCounts = System.Convert.ToInt32(textBox2.Text); int extingCounts = System.Convert.ToInt32(ExtingCountText.Text); OleDbConnection conn = MyDataBase.GetConn(); if (AddMerchandise.Text.Equals("添加")) { string strCmd1 = @"update Merchandise set StockCounts=StockCounts+" + stockCounts.ToString(); strCmd1 += @" where MerchandiseName='" + strName + "'"; string strCmd2 = @"update Merchandise set ExitingCounts=ExitingCounts+" + stockCounts.ToString(); strCmd2 += @" where MerchandiseName='" + strName + "'"; int iRows = 0; sqlcmd.CommandText = strCmd1; iRows = sqlcmd.ExecuteNonQuery(); if (iRows == 0) { MessageBox.Show("添加失败,请查证后重新添加。"); return; } sqlcmd.CommandText = strCmd2; iRows = sqlcmd.ExecuteNonQuery(); if (iRows == 0) { MessageBox.Show("添加失败,请查证后重新添加。"); return; } SetText(); ////////////////////////////////////////////////////////////////////////// //添加出售信息 string timeDate = System.DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff"); string strCmd3 = @"INSERT INTO stockmerchandise ( merchandisename, timedate, stockcounts ) values('" + strName + "','" + timeDate + "'," + stockCounts.ToString() + ")"; sqlcmd.CommandText = strCmd1; iRows = sqlcmd.ExecuteNonQuery(); if (iRows == 0) { MessageBox.Show("添加失败,请查证后重新添加。"); return; } } else if (AddMerchandise.Text.Equals("更新")) { string strCmd1 = @"update Merchandise set StockCounts=" + stockCounts.ToString(); strCmd1 += @" where MerchandiseName='" + strName + "'"; string strCmd2 = @"update Merchandise set ExitingCounts=" + extingCounts.ToString(); strCmd2 += @" where MerchandiseName='" + strName + "'"; int iRows = 0; sqlcmd.CommandText = strCmd1; iRows = sqlcmd.ExecuteNonQuery(); if (iRows == 0) { MessageBox.Show("更新失败,请查证后重新添加。"); return; } sqlcmd.CommandText = strCmd2; iRows = sqlcmd.ExecuteNonQuery(); if (iRows == 0) { MessageBox.Show("更新失败,请查证后重新添加。"); return; } ExtingCountText.Visible = false; ExtingCountLable.Visible = false; AddMerchandise.Text = "添加"; SetText(); } if (dataGridView1.SelectedRows.Count != 0) { if (dataGridView1.SelectedRows[0].Cells.Count > 1) { DataGridViewRow dRow = dataGridView1.SelectedRows[0]; for (int i = 0; i < dataGridView1.RowCount; i++) { string str = dRow.Cells[0].Value.ToString(); Trace.WriteLine(str); if (str.Equals(strName)) { Trace.WriteLine("Enter if :"); dataGridView1.CurrentCell = dataGridView1[0, 1]; } } } } UpdateDataGridView(); }
public bool ExecuteSQL(string pSQL, List <System.Data.OleDb.OleDbParameter> OleDbParameterList = null, bool Is_Stored_Procedure = false, int TimeOut = 30) { bool result = false; OleDbCommand command = null; OleDbConnection conn = null; try { if (this.transaction == null) { conn = new OleDbConnection(this.ConnectionString); conn.Open(); command = new OleDbCommand(pSQL, conn); command.CommandTimeout = TimeOut; if (Is_Stored_Procedure) { command.CommandType = CommandType.StoredProcedure; } else { command.CommandType = CommandType.Text; } if (OleDbParameterList != null) { foreach (var parameter in OleDbParameterList) { command.Parameters.Add(parameter); } } command.ExecuteNonQuery(); } else { if (this.transaction_connection.State == ConnectionState.Closed) { this.transaction_connection.Open(); } command = new OleDbCommand(pSQL, this.transaction_connection); command.Transaction = this.transaction; command.CommandTimeout = TimeOut; if (Is_Stored_Procedure) { command.CommandType = CommandType.StoredProcedure; } else { command.CommandType = CommandType.Text; } if (OleDbParameterList != null) { foreach (var parameter in OleDbParameterList) { command.Parameters.Add(parameter); } } command.ExecuteNonQuery(); } } catch (Exception ex) { throw (ex); } finally { if (command != null) { command.Dispose(); command = null; } if (this.transaction == null) { if (conn != null) { conn.Close(); conn.Dispose(); } } } result = true; return(result); }
/// <summary> /// /// </summary> /// <param name="pathBDXls">Ruta completa del archivo Excel</param> /// <param name="dtXml">Objeto Datatable que contiene los datos a exportar</param> /// <param name="dbCnnt">Conexion Oledb</param> /// <param name="rowIni">Fila inicial</param> /// <param name="rowFin">Fila final</param> /// <param name="count">Contador</param> private static void DataTableToXls2003(string pathBDXls, DataTable dtXml, OleDbConnection dbCnnt, int rowIni, int rowFin, int count) { try { Int32 iRows = dtXml.Rows.Count; Int32 iCount = 0; string sTb = dtXml.TableName; string sCm = String.Empty; Boolean bMp = false; StringBuilder sCm0 = new StringBuilder(); StringBuilder sCm1 = new StringBuilder(); sCm0.Append("("); sCm1.Append("("); FileInfo archivoexcel = null; string logarchivo = string.Empty; DateTime fechaarchivo = DateTime.Now; archivoexcel = new FileInfo(pathBDXls); logarchivo = "-------------------------------------------------------------------------------------------------------------------\n\n"; logarchivo = logarchivo + "Registro de generacion de archivo: " + archivoexcel.Name + "\n"; logarchivo = logarchivo + "Generando conexion con origen de datos excel 2003...\n"; logarchivo = logarchivo + "Conexion generada\n"; foreach (DataColumn dc in dtXml.Columns) { if (dc.Ordinal == 0) { sCm0.Append("["); sCm1.Append("["); } else { sCm0.Append(", ["); sCm1.Append(", ["); } if (bMp) { //sCm = maping[dc.Ordinal].Trim(); } else { sCm = dc.ColumnName.Trim(); } sCm0.Append(sCm); sCm1.Append(sCm); if (dc.DataType.Equals(System.Type.GetType("System.String"))) { sCm0.Append("] NVARCHAR"); } else if (dc.DataType.Equals(System.Type.GetType("System.DateTime"))) { sCm0.Append("] DateTime"); } else if (dc.DataType.Equals(System.Type.GetType("System.Decimal")) || dc.DataType.Equals(System.Type.GetType("System.Int32")) || dc.DataType.Equals(System.Type.GetType("System.Double"))) { sCm0.Append("] Decimal"); } else if (dc.DataType.Equals(System.Type.GetType("System.Byte")) || dc.DataType.Equals(System.Type.GetType("System.Int16")) || dc.DataType.Equals(System.Type.GetType("System.Int32"))) { sCm0.Append("] Int"); } else if (dc.DataType.Equals(System.Type.GetType("System.Boolean"))) { sCm0.Append("] Boolean"); } else { sCm0.Append("] NVARCHAR"); } sCm1.Append("]"); } sCm0.Append(")"); sCm1.Append(")"); logarchivo = logarchivo + "Generando archivo Excel 2003...\n"; if (DBXlsCreateTable(dbCnnt, (dtXml.TableName + (count + 1).ToString()), sCm0.ToString()) == false) { } logarchivo = logarchivo + "Archivo Excel 2003 generado con exito en : " + pathBDXls + "\n"; StringBuilder sCm2; string SCmd = String.Empty; OleDbCommand dbCmd = new OleDbCommand(); int iCs = dtXml.Columns.Count - 1; Int16 i; string strValor = String.Empty; dbCmd.Connection = dbCnnt; dbCnnt.Open(); logarchivo = logarchivo + "Cantidad de registros a insertar en el archivo Excel: " + dtXml.Rows.Count + "\n"; logarchivo = logarchivo + "Insertando registros en archivo Excel...\n"; for (int fila = rowIni; fila < rowFin; fila++) { DataRow dr = dtXml.Rows[fila]; iCount = iCount + 1; sCm2 = new StringBuilder(); sCm2.Append("("); for (i = 0; i <= iCs; i++) { if (i == 0) { if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.String"))) { sCm2.Append("'"); } else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime"))) { if (dr[i] != System.DBNull.Value) { sCm2.Append("'"); } } } else { if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.String"))) { sCm2.Append(", '"); } else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime"))) { if (dr[i] != System.DBNull.Value) { //Verificamos que la fecha sea mayor a 1900 if (Convert.ToDateTime(dr[i]).Year >= 1900) { sCm2.Append(", '"); } else { sCm2.Append(", "); } } else { sCm2.Append(", "); } } else { sCm2.Append(", "); } } if (dr[i] == System.DBNull.Value) { strValor = ""; if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime"))) { strValor = "Null"; } } else { if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime"))) { //Verificamos que la fecha sea mayor a 1900 if (Convert.ToDateTime(dr[i]).Year >= 1900) { strValor = Convert.ToDateTime(dr[i]).ToString("yyyy-MM-dd HH:mm:ss"); } else { strValor = "Null"; } } else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Boolean"))) { if (Convert.ToBoolean(dtXml.Columns[i]) == false) { strValor = "False"; } else { strValor = "True"; } } else { strValor = dr[i].ToString().Replace("'", "''"); //valor = strValor; } } sCm2.Append(strValor); if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.String"))) { sCm2.Append("'"); } else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.DateTime"))) { if (dr[i] != System.DBNull.Value) { //Verificamos que la fecha sea mayor a 1900 if (Convert.ToDateTime(dr[i]).Year >= 1900) { sCm2.Append("'"); } } } else if (dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Decimal")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Int16")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Int32")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Double")) || dtXml.Columns[i].DataType.Equals(System.Type.GetType("System.Byte"))) { if (strValor.Length == 0) { sCm2.Append("0"); } } } sCm2.Append(")"); SCmd = "INSERT INTO " + "[" + (sTb + (count + 1).ToString()) + "]" + sCm1.ToString() + " Values " + sCm2.ToString(); //insert = "INSERT INTO " + "[" + sTb + "]" + sCm1.ToString() + " Values " + sCm2.ToString(); dbCmd.CommandText = SCmd; dbCmd.ExecuteNonQuery(); } logarchivo = logarchivo + "Insertados " + iCount + " registros en archivo Excel con exito\n"; logarchivo = logarchivo + "Cerrando conexion en archivo Excel: " + pathBDXls + "...\n"; logarchivo = logarchivo + "Conexion en archivo Excel " + pathBDXls + " cerrada con exito\n\n"; Log_Archivo(logarchivo, archivoexcel.DirectoryName + "\\" + "LogExcel_" + fechaarchivo.ToString("dd-MM-yyyy") + ".txt"); dbCnnt.Close(); } catch (Exception ex) { if (dbCnnt.State == ConnectionState.Open) { dbCnnt.Close(); } throw ex; } }
public int Reinspect(string from, string to, string qcn, int amount) { this.Connection.Open(); OleDbTransaction transaction = this.Connection.BeginTransaction(); int result = 0; try { string cmdText = "SELECT 送檢次數 FROM 產品檢驗 WHERE 工時資料編號 = ?"; OleDbCommand cmd = new OleDbCommand(cmdText, this.Connection); cmd.Transaction = transaction; cmd.Parameters.Add(new OleDbParameter("工時資料編號", from)); int num = Convert.ToInt32(cmd.ExecuteScalar()); cmdText = "INSERT INTO 產品檢驗 (工時資料編號,QCN,待驗數量,送檢次數,最後送檢編號,送檢日期) VALUES (?,?,?,?,?,?)"; cmd = new OleDbCommand(cmdText, this.Connection); cmd.Transaction = transaction; cmd.Parameters.Add(new OleDbParameter("工時資料編號1", to)); cmd.Parameters.Add(new OleDbParameter("QCN", qcn)); cmd.Parameters.Add(new OleDbParameter("待驗數量", amount)); cmd.Parameters.Add(new OleDbParameter("送檢次數", num + 1)); cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to)); //cmd.Parameters.Add(new OleDbParameter("送檢日期", DateTime.Today)); OleDbParameter paramDate = new OleDbParameter(); paramDate.OleDbType = OleDbType.DBTimeStamp; paramDate.Value = DateTime.Now.ToString("s"); cmd.Parameters.Add(paramDate); result = cmd.ExecuteNonQuery(); cmdText = "UPDATE 產品檢驗 SET 重驗 = True WHERE 工時資料編號 = ?"; cmd = new OleDbCommand(cmdText, this.Connection); cmd.Transaction = transaction; //cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to)); cmd.Parameters.Add(new OleDbParameter("工時資料編號", from)); cmd.ExecuteNonQuery(); cmdText = "UPDATE 產品檢驗 SET 最後送檢編號 = ? WHERE 最後送檢編號 = ?"; cmd = new OleDbCommand(cmdText, this.Connection); cmd.Transaction = transaction; cmd.Parameters.Add(new OleDbParameter("最後送檢編號1", to)); cmd.Parameters.Add(new OleDbParameter("最後送檢編號2", from)); cmd.ExecuteNonQuery(); cmdText = "INSERT INTO NG原因 (工時資料編號, 原因, 來源編號) SELECT '" + to + "' as 工時資料編號, 原因, 來源編號 FROM NG原因 WHERE 工時資料編號 = ?"; cmd = new OleDbCommand(cmdText, this.Connection); cmd.Transaction = transaction; cmd.Parameters.Add(new OleDbParameter("工時資料編號", from)); cmd.ExecuteNonQuery(); transaction.Commit(); this.Connection.Close(); return result; } catch (Exception ex) { transaction.Rollback(); this.Connection.Close(); throw ex; } }
public int DeleteEx(string id) { int retVal = 0; OleDbConnection conn = Instance.Connection; OleDbTransaction transaction = null; OleDbCommand cmd = new OleDbCommand(); conn.Open(); try { transaction = conn.BeginTransaction(); cmd.Connection = conn; cmd.Transaction = transaction; //改正產品檢驗資料 //取得產品檢驗資料 cmd.CommandText = "SELECT * FROM 產品檢驗 WHERE 工時資料編號=?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("工時資料編號", id)); OleDbDataReader reader = cmd.ExecuteReader(); if (reader.Read()) { string last = reader["最後送檢編號"].ToString(); int inspectDel = (int)reader["送檢次數"]; reader.Close(); cmd.CommandText = "SELECT * FROM 產品檢驗 WHERE 最後送檢編號=? AND 工時資料編號<>? ORDER BY 送檢次數 ASC"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last)); cmd.Parameters.Add(new OleDbParameter("工時資料編號", id)); OleDbDataReader reader2 = cmd.ExecuteReader(); while (reader2.Read()) { string nextID = reader2["工時資料編號"].ToString(); int inspect = (int)reader2["送檢次數"]; if (inspect > inspectDel) { //將該筆之後的檢驗資料做處理 OleDbCommand cmd2 = new OleDbCommand(); cmd2.Connection = conn; cmd2.Transaction = transaction; cmd2.CommandText = "UPDATE 產品檢驗 SET 送檢次數=送檢次數-1 WHERE 工時資料編號=?"; cmd2.Parameters.Clear(); cmd2.Parameters.Add(new OleDbParameter("工時資料編號", nextID)); cmd2.ExecuteNonQuery(); } else { //將該筆之前的檢驗資料做處理 } } reader2.Close(); //刪除產品檢驗資料 cmd.CommandText = "DELETE FROM 產品檢驗 WHERE 工時資料編號=?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("工時資料編號", id)); cmd.ExecuteNonQuery(); //挑出最後一筆檢驗紀錄 cmd.CommandText = "SELECT 工時資料編號 FROM 產品檢驗 WHERE 最後送檢編號 =? ORDER BY 送檢次數 DESC"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last)); reader2 = cmd.ExecuteReader(); if (reader2.Read()) { string maxID = reader2["工時資料編號"].ToString(); reader2.Close(); //將原有的最後檢驗紀錄更新 cmd.CommandText = "UPDATE 產品檢驗 SET 最後送檢編號=?, 最後檢驗紀錄=FALSE, 重驗=TRUE WHERE 最後送檢編號=?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("最後送檢編號new", maxID)); cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last)); cmd.ExecuteNonQuery(); //將最後一筆設成最後檢驗記錄 cmd.CommandText = "UPDATE 產品檢驗 SET 最後檢驗紀錄=TRUE, 重驗=FALSE WHERE 工時資料編號=?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("工時資料編號", maxID)); cmd.ExecuteNonQuery(); } if (!reader2.IsClosed) reader2.Close(); } if (!reader.IsClosed) reader.Close(); //刪除工時資料 cmd.CommandText = "DELETE FROM 工時 WHERE 編號=?"; cmd.Parameters.Clear(); cmd.Parameters.Add(new OleDbParameter("編號", id)); retVal = cmd.ExecuteNonQuery(); transaction.Commit(); } catch (Exception ex) { try { transaction.Rollback(); } catch (Exception) { } try { conn.Close(); } catch (Exception) { } throw ex; } conn.Close(); return retVal; }
public FuncStatus DataBind(BindOperator _bo) { bool _tf = true; OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString()); OleDbCommand _cmd = new OleDbCommand(); OleDbDataReader _dr; _cmd.Connection = _conn; try { _conn.Open(); if (_bo == BindOperator.Select) { _cmd.CommandText = "select Products.ID,Products.Name,Products.Description from Products where " + (this.ID != 0 ? "ID=" + this.ID.ToString() : (this.Name != null ? "Name='" + this.Name : "Description like '_" + this.Description + "_") + "'"); } if (_bo == BindOperator.Insert) { _cmd.CommandText = "insert into Products (Name,Description) values ('" + this.Name + "','" + this.Description + "')"; _cmd.ExecuteScalar(); _cmd.CommandText = "select ID,Name,Description from products where id = (select Max(ID) from Products)"; } if (_bo == BindOperator.Update) { _cmd.CommandText = "update products set Name='" + this.Name + "',Description='" + this.Description + "' where ID=" +this.ID; _cmd.ExecuteNonQuery(); _cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID; } if (_bo == BindOperator.Delete) { _cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID; } _dr = _cmd.ExecuteReader(); _dr.Read(); this.ID = _dr.GetInt32(0); this.Name = _dr.GetValue(1).ToString(); this.Description = _dr.GetValue(2).ToString(); this.Images.pID = this.ID; _dr.Close(); if (_bo == BindOperator.Select) { Images.DataBind(_bo); } if (_bo == BindOperator.Insert) { Images.DataBind(_bo); } if (_bo == BindOperator.Update && this.Images.Path[0] != "") { Images.DataBind(_bo); } if (_bo == BindOperator.Delete) { _cmd.CommandText = "delete from products where ID=" + this.ID; _cmd.ExecuteNonQuery(); this.Images.clear(); this.Images.DataBind(BindOperator.Update); } this.Status = ORMStatus.Saved; } catch (Exception ex) { _tf = false; Exception _ex = new Exception("ORM.Products["+this.ID+"]:" + ex.Message); throw _ex; } finally { _conn.Close(); } return _tf == false?FuncStatus.Fail:FuncStatus.Success; }
public void WriteData() { string sql = ""; string ConnString = GetConnectionString(); string result = System.Text.RegularExpressions.Regex.Replace(GetInputParamCalculatedValue("ColMappingRules"), @",(?=[^']*'(?:[^']*'[^']*')*[^']*$)", "~^GINGER-EXCEL-COMMA-REPLACE^~"); string[] varColMaps = result.Split(','); string sSetDataUsed = ""; using (OleDbConnection Conn = new OleDbConnection(ConnString)) { try { Conn.Open(); } catch (Exception ex) { System.Threading.Thread.Sleep(3000); Conn.Open(); Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex); } OleDbCommand Cmd = new OleDbCommand(); Cmd.Connection = Conn; string SheetName = GetInputParamCalculatedValue("SheetName").Trim(); if (String.IsNullOrEmpty(SheetName)) { this.Error += "Sheet Name is empty or not selected. Please Select correct sheet name on action configurations"; Conn.Close(); return; } if (!SheetName.EndsWith("$")) { SheetName += "$"; } if (SelectAllRows == false) { sql = "Select TOP 1 * from [" + SheetName + "]"; } else { sql = "Select * from [" + SheetName + "]"; } string where = GetInputParamCalculatedValue("SelectRowsWhere"); if (!string.IsNullOrEmpty(where)) { sql += " WHERE " + where; } Cmd.CommandText = sql; DataTable dt = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = Cmd; string updateSQL = ""; try { da.Fill(dt); if (!string.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed"))) { sSetDataUsed = @", " + GetInputParamCalculatedValue("SetDataUsed"); } // we expect only 1 record if (dt.Rows.Count == 1 && SelectAllRows == false) { DataRow r = dt.Rows[0]; //Read data to variables foreach (string vc in varColMaps) { string strPrimaryKeyColumn = GetInputParamCalculatedValue("PrimaryKeyColumn"); if (strPrimaryKeyColumn.Contains("`")) { strPrimaryKeyColumn = strPrimaryKeyColumn.Replace("`", ""); } string rowKey = r[strPrimaryKeyColumn].ToString(); int res; int.TryParse(rowKey, out res); if (res == 0 || r[strPrimaryKeyColumn].GetType() == typeof(System.String)) { rowKey = "'" + rowKey + "'"; } //TODO: fix me in OO Style //Do mapping string ColName = vc.Split('=')[0]; string Value = vc.Split('=')[1]; Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ","); string txt = Value; //keeping the translation of vars to support previous implementation VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value); if (var != null) { var.Value = ValueExpression.Calculate(var.Value); txt = var.Value; } //remove '' from value txt = txt.TrimStart(new char[] { '\'' }); txt = txt.TrimEnd(new char[] { '\'' }); //TODO: create one long SQL to do the update in one time and not for each var updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + ColName + " = '" + txt + "'" + sSetDataUsed + " WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";"; this.ExInfo += updateSQL + Environment.NewLine; OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } // Do the update that row is used } else if (dt.Rows.Count > 0 && SelectAllRows == true) { updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET "; foreach (string vc in varColMaps) { //TODO: fix me in OO Style //Do mapping string ColName = vc.Split('=')[0]; string Value = vc.Split('=')[1]; Value = Value.Replace("~^GINGER-EXCEL-COMMA-REPLACE^~", ","); string txt = Value; //keeping the translation of vars to support previous implementation VariableBase var = RunOnBusinessFlow.GetHierarchyVariableByName(Value); if (var != null) { var.Value = ValueExpression.Calculate(var.Value); if (var != null) { txt = var.Value; } else { txt = Value; } } //remove '' from value txt = txt.TrimStart(new char[] { '\'' }); txt = txt.TrimEnd(new char[] { '\'' }); //TODO: create one long SQL to do the update in one time and not for each var updateSQL = updateSQL + ColName + " = '" + txt + "',"; } updateSQL = updateSQL.Substring(0, updateSQL.Length - 1); updateSQL = updateSQL + sSetDataUsed; if (!string.IsNullOrEmpty(where)) { updateSQL += " WHERE " + where + ";"; } this.ExInfo += updateSQL + Environment.NewLine; OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } else if (dt.Rows.Count == 0) { this.ExInfo = "No Rows updated with given criteria"; } } catch (Exception ex) { // Reporter.ToLog(eAppReporterLogLevel.ERROR, "Writing into excel got error " + ex.Message); this.Error = "Error when trying to update the excel: " + ex.Message + Environment.NewLine + "UpdateSQL=" + updateSQL; } finally { Conn.Close(); } // then show a message if needed if (dt.Rows.Count == 0) { //TODO: reporter // Reporter.ToUser("No rows found in excel file matching criteria - " + sql); // throw new Exception("No rows found in excel file matching criteria - " + sql); } } }
//************************************************************************** /// <Description> /// This method uses to update data to sys_ReportDrillDown /// </Description> /// <Inputs> /// sys_ReportDrillDownVO /// </Inputs> /// <Outputs> /// /// </Outputs> /// <Returns> /// /// </Returns> /// <Authors> /// HungLa /// </Authors> /// <History> /// 09-Dec-2004 /// 12/Oct/2005 Thachnn: fix bug injection /// </History> /// <Notes> /// </Notes> //************************************************************************** public void Update(object pobjObjecVO) { const string METHOD_NAME = THIS + ".Update()"; sys_ReportDrillDownVO objObject = (sys_ReportDrillDownVO)pobjObjecVO; //prepare value for parameters OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; try { string strSql = String.Empty; strSql = "UPDATE " + sys_ReportDrillDownTable.TABLE_NAME + " SET " + sys_ReportDrillDownTable.DETAILREPORTID_FLD + "= ?" + "," + sys_ReportDrillDownTable.MASTERPARA_FLD + "= ?" + "," + sys_ReportDrillDownTable.DETAILPARA_FLD + "= ?" + "," + sys_ReportDrillDownTable.FROMCOLUMN_FLD + "= ?" + "," + sys_ReportDrillDownTable.PARAORDER_FLD + "= ?" + " WHERE " + sys_ReportDrillDownTable.MASTERREPORTID_FLD + "= ?"; Utils utils = new Utils(); oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString); ocmdPCS = new OleDbCommand(strSql, oconPCS); ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.DETAILREPORTID_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[sys_ReportDrillDownTable.DETAILREPORTID_FLD].Value = objObject.DetailReportID; ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.MASTERPARA_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[sys_ReportDrillDownTable.MASTERPARA_FLD].Value = objObject.MasterPara; ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.DETAILPARA_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[sys_ReportDrillDownTable.DETAILPARA_FLD].Value = objObject.DetailPara; ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.FROMCOLUMN_FLD, OleDbType.Boolean)); ocmdPCS.Parameters[sys_ReportDrillDownTable.FROMCOLUMN_FLD].Value = objObject.FromColumn; ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.PARAORDER_FLD, OleDbType.Integer)); ocmdPCS.Parameters[sys_ReportDrillDownTable.PARAORDER_FLD].Value = objObject.ParaOrder; ocmdPCS.Parameters.Add(new OleDbParameter(sys_ReportDrillDownTable.MASTERREPORTID_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[sys_ReportDrillDownTable.MASTERREPORTID_FLD].Value = objObject.MasterReportID; ocmdPCS.CommandText = strSql; ocmdPCS.Connection.Open(); ocmdPCS.ExecuteNonQuery(); } catch (OleDbException ex) { if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex); } else { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } } catch (InvalidOperationException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } }
private void btnSave_Click(object sender, EventArgs e) { String id = null; String Ptype = null; String pno = cmbPno.Text.ToString(); String mno = cmbMno.Text.ToString(); String category = cmbCname.Text.ToString(); String brand = cmbBname.Text.ToString(); String des = txtDes.Text.ToString(); String Pdes = txtDes.Text.ToString(); String unitVal = txtUnit.Text.ToString(); String CpriceVal = txtCost.Text.ToString(); String SpriceVal = txtSell.Text.ToString(); String roqVal = txtReOrder.Text.ToString(); int unit = int.Parse(unitVal); double Cprice = double.Parse(CpriceVal); double Sprice = double.Parse(SpriceVal); int roq = int.Parse(roqVal); if (rbStock.Checked) { Ptype = "s"; } if (rbNStock.Checked) { Ptype = "ns"; } if (rbService.Checked) { Ptype = "sr"; } if (pno == String.Empty || mno == String.Empty || category == String.Empty || brand == String.Empty) { MessageBox.Show("At least one of the property values is invalid. ", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } else { DialogResult result = MessageBox.Show("Do you want to save data?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question); if (result == DialogResult.Yes) { try { con.Open(); OleDbDataReader reader = null; OleDbCommand cmd = new OleDbCommand("Select ID From Product WHERE PNo = '" + pno + "'", con); reader = cmd.ExecuteReader(); while (reader.Read()) { id = reader["ID"].ToString(); } if (id == null) { OleDbCommand cmd2 = con.CreateCommand(); cmd2.CommandText = "INSERT INTO Product (PNo,Mno,Description,PDescription,Unit,Cprice,SellPrice,ROQ,PType,CName,BName) VALUES('" + pno + "','" + mno + "','" + des + "','" + Pdes + "'," + unit + "," + Cprice + "," + Sprice + "," + roq + ",'" + Ptype + "','" + category + "','" + brand + "')"; cmd2.Connection = con; cmd2.ExecuteNonQuery(); } else { OleDbCommand cmd2 = con.CreateCommand(); cmd2.CommandText = "UPDATE Product SET Mno ='" + mno + "', Description = '" + des + "', PDescription='" + Pdes + "', Unit=" + unit + ", Cprice=" + Cprice + ", SellPrice=" + Sprice + ", ROQ=" + roq + ", PType='" + Ptype + "', CName='" + category + "', BName='" + brand + "' WHERE PNo = '" + pno + "'"; cmd2.Connection = con; cmd2.ExecuteNonQuery(); } con.Close(); cmbPno.Enabled = true; cmbPno.Text = null; btnNew.Enabled = true; btnDelete.Enabled = true; this.Product_Load(null, null); } catch (Exception ex) { MessageBox.Show("error " + ex); } } } }
public int connectRemove(Student x, string idremove) { OleDbConnection connection = new OleDbConnection(); try { connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; "; var cmd = new OleDbCommand("DELETE FROM Student WHERE Id = @a;"); //need correct reference connection.Open(); cmd.Parameters.AddRange(new[] { new OleDbParameter("@a", x.Id) }); cmd.Connection = connection; cmd.ExecuteNonQuery(); Console.WriteLine("Student removed successfully."); } catch(Exception ex) { Console.WriteLine("Error " + ex); } connection.Close(); return 0; }
//森林用途研判 private static void tdytJudge(IFeatureLayer pChangeFeaLayer, IFeatureLayer tdytFeaLayer, IWorkspace pOutWorkSpace, bool useSelection,SysCommon.CProgress vProgress) { ITable pChangeTable = pChangeFeaLayer as ITable; ITable tdytTable = tdytFeaLayer as ITable; double rol = 0.0001; IFeatureClass tdytFeaClass = tdytFeaLayer.FeatureClass; //创建结果地物类名称 IFeatureClassName pResFeaClassName = new FeatureClassNameClass(); String fcName = tdytFeaClass.AliasName.Trim().Substring(tdytFeaClass.AliasName.Trim().IndexOf(".") + 1)+"_res"; IDataset pOutDataset = (IDataset)pOutWorkSpace; IDatasetName pOutDatasetName = (IDatasetName)pResFeaClassName; pOutDatasetName.WorkspaceName = (IWorkspaceName)pOutDataset.FullName; pOutDatasetName.Name = fcName; IBasicGeoprocessor pGeoProcessor = new BasicGeoprocessorClass(); //叠置分析 pGeoProcessor.Intersect(pChangeTable, useSelection, tdytTable, false, rol, pResFeaClassName); //从叠置结果生成报表 string connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pOutDatasetName.WorkspaceName.PathName; OleDbConnection oledbconn = new OleDbConnection(connstr); oledbconn.Open(); ModTableFun.DropTable(oledbconn,"tmprel"); string sqlstr = "select "+g_XZQHDM+","+g_TBBH+","+g_MJ+" as jctbmj,"+g_TDYTFQBH+","+g_TDYTFQDM+",shape_area as jsmj,shape_area as mj,shape_area as zb into tmprel from " + fcName; //行政区划代码,编号,监测图斑面积,规划图斑编号,规划用地用途代码 OleDbCommand oledbcomm = oledbconn.CreateCommand(); oledbcomm.CommandText = sqlstr; oledbcomm.ExecuteNonQuery(); oledbcomm.CommandText = "update tmprel set zb=mj/jctbmj*100"; oledbcomm.ExecuteNonQuery(); ModTableFun.DropTable(oledbconn ,"森林用途字典"); CopyTdytDictionary(oledbconn);//从业务库里面拷贝森林用途字典过来 //根据森林用途字典更新森林用途名称 oledbcomm.CommandText = "alter table tmprel add tdytmc text(30)"; oledbcomm.ExecuteNonQuery(); oledbcomm.CommandText = "update tmprel set tdytmc=" + g_TDYTFQDM; oledbcomm.ExecuteNonQuery(); if (ModTableFun.isExist(oledbconn,"森林用途字典")) { oledbcomm.CommandText = "update tmprel a,森林用途字典 b set a.tdytmc=b.森林用途分区类型 where a." + g_TDYTFQDM + "=b.代码"; oledbcomm.ExecuteNonQuery(); } //报表模板路径 string Templatepath = Application.StartupPath + "\\..\\Template\\森林资源规划研判模板.cel"; //生成报表对话框 oledbconn.Close(); FormFlexcell frm; ModFlexcell.m_SpecialRow = -1; ModFlexcell.m_SpecialRow_ex = -1; ModFlexcell.m_SpecialRow_ex2 = -1; //规划图斑没有图斑编号怎么办??暂时使用森林用途分区编号 frm = ModFlexcell.SendDataToFlexcell(connstr, "监测图斑规划情况表", "tmprel", g_XZQHDM + "," + g_TBBH + ",jctbmj,TDYTFQBH,tdytmc," + g_MJ + "," + g_ZB, "", Templatepath, 4, 2); //弹出报表对话框 AxFlexCell.AxGrid pGrid = frm.GetGrid(); string excelPath = m_WorkPath + "\\监测图斑规划情况表.xls"; pGrid.ExportToExcel(excelPath); //frm.SaveFile(m_WorkPath + "\\监测图斑规划情况表.cel"); ModStatReport.OpenExcelFile(excelPath); }
public void writeFail(string field, string value)//写入失败统计 { cmd = new OleDbCommand("update sheet3 set " + field + "='" + value + "'where ID=1", conn); cmd.ExecuteNonQuery(); }
public void AddtoBasket(string userID, string PlayChosen, float Standard, float Child, float OAP, DateTime PlayDate, string Time, string Price, float Quantity) { string checkString; float checkValue; //needs to write to database string connString; connString = @"Provider=Microsoft.JET.OLEDB.4.0;Data Source = L:\Comp-1632-System Development Project\TicketBookingSystem\TicketBookingSystem\TicketSysDB.mdb"; OleDbConnection myConnection = new OleDbConnection(connString); myConnection.Open(); OleDbCommand checkCommand = new OleDbCommand("SELECT TicketsQuantity FROM Plays WHERE Title = @Title AND DateOfPlay = @DateOfPlay AND TimeOfPlay = @TimeOfPlay", myConnection); checkCommand.Parameters.AddWithValue("@Title", PlayChosen); checkCommand.Parameters.AddWithValue("@DateOfPlay", PlayDate); checkCommand.Parameters.AddWithValue("@TimeOfPlay", Time); OleDbDataReader reader2 = checkCommand.ExecuteReader(); while (reader2.Read()) { checkString = reader2.GetString(0); checkValue = Convert.ToInt32(checkString); if ((checkValue - Quantity) >= 0) { OleDbCommand myCommand = new OleDbCommand("INSERT INTO [Basket] (UserID, PlayChosen, StandardNo, ChildNo, OAPNo, PlayDate, PlayTime, Price, Quantity) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)", myConnection); myCommand.Parameters.AddWithValue("@UserID", userID); myCommand.Parameters.AddWithValue("@PlayChosen", PlayChosen); myCommand.Parameters.AddWithValue("@Standard", Standard); myCommand.Parameters.AddWithValue("@Child", Child); myCommand.Parameters.AddWithValue("@OAP", OAP); myCommand.Parameters.AddWithValue("@PlayDate", PlayDate); myCommand.Parameters.AddWithValue("@PlayTime", Time); myCommand.Parameters.AddWithValue("@Price", Price); myCommand.Parameters.AddWithValue("@Quantity", Quantity); myCommand.ExecuteNonQuery(); success = 1; OleDbCommand decrementCommand = new OleDbCommand("UPDATE Plays SET TicketsQuantity = @TicketsQuantity WHERE Title = @Title AND DateOfPlay = @DateOfPlay AND TimeOfPlay = @TimeOfPlay", myConnection); decrementCommand.Parameters.AddWithValue("@TicketsQuantity", (checkValue - Quantity)); decrementCommand.Parameters.AddWithValue("@Title", PlayChosen); decrementCommand.Parameters.AddWithValue("@DateOfPlay", PlayDate); decrementCommand.Parameters.AddWithValue("@TimeOfPlay", Time); decrementCommand.ExecuteNonQuery(); } else { success = 2; } } myConnection.Close(); }
private void sendUpdateBtn_Click(object sender, EventArgs e) { List <Student.Mark> marks = new List <Student.Mark>(); List <Student.Miss> misses = new List <Student.Miss>(); int resUpdMark, resAddMark, resDelMark, resAddSkip, resDelSkip; resUpdMark = resAddMark = resDelMark = resAddSkip = resDelSkip = 0; for (int i = 0; i < journalGrid.RowCount; i++) { for (int j = 2; j < journalGrid.ColumnCount; j++) { if (journalGrid[j, i].Value != null) { int idStudent = (int)journalGrid.Rows[i].Cells[0].Value; string date = journalGrid.Columns[j].HeaderText; string queryMark = "Select * " + "From Marks " + $"Where idStudent = {idStudent} And Marks.dateMark = @date And idSub = {subjectBox.SelectedValue}"; OleDbCommand commandMark = new OleDbCommand(queryMark, _connection); commandMark.Parameters.Add(new OleDbParameter("@data", date)); var readerMark = commandMark.ExecuteReader(); string querySkip = "Select * " + "From SkipLesson " + $"Where idStudent = {idStudent} And dateSkip = @date And idSub = {subjectBox.SelectedValue}"; OleDbCommand commandSkip = new OleDbCommand(querySkip, _connection); var paramDateSkip = new OleDbParameter("@date", date); commandSkip.Parameters.Add(paramDateSkip); var readerSkip = commandSkip.ExecuteReader(); if (Char.IsDigit(journalGrid[j, i].Value.ToString()[0])) { string queryCheckMark = "Select * " + "From Marks " + $"Where idStudent = {idStudent} And Marks.dateMark = @date And Marks.valueMark = {journalGrid[j, i].Value} And idSub = {subjectBox.SelectedValue}"; OleDbCommand cmdCheckMark = new OleDbCommand(queryCheckMark, _connection); OleDbParameter param = new OleDbParameter("@date", date); cmdCheckMark.Parameters.Add(param); var checker = cmdCheckMark.ExecuteReader(); cmdCheckMark.Parameters.Clear(); if (checker.HasRows) { continue; } if (readerMark.HasRows) { string query = $"Update Marks " + $"Set Marks.valueMark = {journalGrid[j, i].Value} " + $"Where idStudent = { journalGrid.Rows[i].Cells[0].Value } And Marks.dateMark = @date And idSub = { subjectBox.SelectedValue }"; OleDbCommand updateCmd = new OleDbCommand(query, _connection); OleDbParameter paramUpdateMark = updateCmd.Parameters.Add(new OleDbParameter("@date", date)); resUpdMark += updateCmd.ExecuteNonQuery(); } else if (readerSkip.HasRows) { readerSkip.Read(); string query = $"Delete From SkipLesson Where SkipLesson.id = {readerSkip["id"]}"; OleDbCommand deleteSkip = new OleDbCommand(query, _connection); resDelSkip += deleteSkip.ExecuteNonQuery(); } else { string query = $"Insert Into Marks(idStudent, idSub, dateMark, valueMark) " + $"Values({idStudent}, {subjectBox.SelectedValue}, '{journalGrid.Columns[j].HeaderText}', {journalGrid[j, i].Value})"; OleDbCommand addCommand = new OleDbCommand(query, _connection); resAddMark += addCommand.ExecuteNonQuery(); } } else if (journalGrid[j, i].Value.ToString() == "Н") { if (readerMark.HasRows) { string query = $"Delete From Marks " + $"Where idStudent = {idStudent} And Marks.dateMark = @date And idSub = { subjectBox.SelectedValue }"; OleDbCommand deleteMarkCmd = new OleDbCommand(query, _connection); deleteMarkCmd.Parameters.Add(new OleDbParameter("@date", date)); resDelMark += deleteMarkCmd.ExecuteNonQuery(); string addSkipQuery = "Insert Into SkipLesson(idSub, idStudent, dateSkip) " + $"Values( {subjectBox.SelectedValue}, {idStudent}, '{date}')"; OleDbCommand addSkipCmd = new OleDbCommand(addSkipQuery, _connection); resAddSkip += addSkipCmd.ExecuteNonQuery(); } else if (readerSkip.HasRows) { continue; } else { string query = $"Insert Into SkipLesson(idStudent, idSub, dateSkip) " + $"Values({idStudent}, {subjectBox.SelectedValue}, '{date}')"; OleDbCommand addCommand = new OleDbCommand(query, _connection); resAddSkip += addCommand.ExecuteNonQuery(); } } else if (journalGrid[j, i].Value.ToString() == "none") { if (readerMark.HasRows) { readerMark.Read(); string query = $"Delete From Marks Where id = {readerMark["id"]}"; OleDbCommand deleteMarkCmd = new OleDbCommand(query, _connection); resDelMark = deleteMarkCmd.ExecuteNonQuery(); } if (readerSkip.HasRows) { readerSkip.Read(); string query = $"Delete From SkipLesson Where id = {readerSkip["id"]}"; OleDbCommand deleteSkipCmd = new OleDbCommand(query, _connection); resDelSkip = deleteSkipCmd.ExecuteNonQuery(); } } } } } MessageBox.Show($@" Обновлено отметок: {resUpdMark} Добавлено отметок: {resAddMark} Удалено отметок : {resDelMark} Добавлено пропусков: {resAddSkip} Удалено пропусков: {resDelSkip} ", "Статистика"); }
//************************************************************************** /// <Description> /// This method uses to update data to MST_EmployeeApprovalLevel /// </Description> /// <Inputs> /// MST_EmployeeApprovalLevelVO /// </Inputs> /// <Outputs> /// /// </Outputs> /// <Returns> /// /// </Returns> /// <Authors> /// HungLa /// </Authors> /// <History> /// 09-Dec-2004 /// </History> /// <Notes> /// </Notes> //************************************************************************** public void Update(object pobjObjecVO) { const string METHOD_NAME = THIS + ".Update()"; MST_EmployeeApprovalLevelVO objObject = (MST_EmployeeApprovalLevelVO)pobjObjecVO; //prepare value for parameters OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; try { string strSql = String.Empty; Utils utils = new Utils(); oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString); ocmdPCS = new OleDbCommand(strSql, oconPCS); strSql = "UPDATE MST_EmployeeApprovalLevel SET " + MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD + "= ?" + "," + MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD + "= ?" + "," + MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD + "= ?" + " WHERE " + MST_EmployeeApprovalLevelTable.DESCRIPTION_FLD + "= ?"; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD].Value = objObject.EmployeeApprovalLevelID; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD].Value = objObject.ApprovalLevelID; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.DESCRIPTION_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.DESCRIPTION_FLD].Value = objObject.Description; ocmdPCS.CommandText = strSql; ocmdPCS.Connection.Open(); ocmdPCS.ExecuteNonQuery(); } catch (OleDbException ex) { if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex); } else { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } } catch (InvalidOperationException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } }
public void Execute(string sSQL) { OleDbCommand cmd = new OleDbCommand(sSQL, Connection); cmd.ExecuteNonQuery(); }
//************************************************************************** /// <Description> /// This method uses to add data to MST_EmployeeApprovalLevel /// </Description> /// <Inputs> /// MST_EmployeeApprovalLevelVO /// </Inputs> /// <Outputs> /// newly inserted primarkey value /// </Outputs> /// <Returns> /// void /// </Returns> /// <Authors> /// HungLa /// </Authors> /// <History> /// Tuesday, January 25, 2005 /// </History> /// <Notes> /// </Notes> //************************************************************************** public void Add(object pobjObjectVO) { const string METHOD_NAME = THIS + ".Add()"; OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; try { MST_EmployeeApprovalLevelVO objObject = (MST_EmployeeApprovalLevelVO)pobjObjectVO; string strSql = String.Empty; Utils utils = new Utils(); oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString); ocmdPCS = new OleDbCommand("", oconPCS); strSql = "INSERT INTO MST_EmployeeApprovalLevel(" + MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD + "," + MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD + "," + MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD + ")" + "VALUES(?,?,?)"; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEAPPROVALLEVELID_FLD].Value = objObject.EmployeeApprovalLevelID; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.EMPLOYEEID_FLD].Value = objObject.EmployeeID; ocmdPCS.Parameters.Add(new OleDbParameter(MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_EmployeeApprovalLevelTable.APPROVALLEVELID_FLD].Value = objObject.ApprovalLevelID; ocmdPCS.CommandText = strSql; ocmdPCS.Connection.Open(); ocmdPCS.ExecuteNonQuery(); } catch (OleDbException ex) { if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex); } else { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } } catch (InvalidOperationException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } }
protected void Page_Load(object sender, EventArgs e) { int i = 0; if (Session["tryCount"] == null)//用户名或密码错误次数不得超过一定界限 { Session["tryCount"] = 1; } if (Convert.ToInt32(Session["tryCount"]) > 50) { i = 2; } else { if (Request.Form["uid"] != null && Request.Form["pwd"] != null) { try { string username = SqlString.keepoutSqlKey(Request.Form["uid"]); string password = SqlString.keepoutSqlKey(Request.Form["pwd"]); OleDbConnection con = DB.createcon(); string sqlstr = "select * from [student] where [studentUsername]='" + username + "' and ([studentPassword]='" + password + "' or [studentPassword]='" + MD5_encode(password) + "')"; con.Open(); OleDbCommand cmd = new OleDbCommand(); cmd.CommandText = sqlstr; cmd.Connection = con; OleDbDataReader sdr = cmd.ExecuteReader(); while (sdr.Read()) { string role = sdr["roleType"].ToString(); string realname = sdr["studentName"].ToString(); password = sdr["studentPassword"].ToString(); Session["userType"] = role; Session["realName"] = realname; Session["userName"] = username; cookieOperation.setOneCookie("userType", role); cookieOperation.setOneCookie("realName", realname); cookieOperation.setOneCookie("userName", username); string logincode = MD5Encode.docubleMD5_encode(username, password); cookieOperation.setOneCookie("loginCode", logincode); i = 1; } sdr.Close(); con.Close(); if (i == 1) { string mydatetime = DateTime.Now.ToString(); string ip = Request.ServerVariables["REMOTE_HOST"].ToString(); string sqlstr2 = "update [student] set loginCount=loginCount+1,lastLoinTime='" + mydatetime + "',lastLoinIP='" + ip + "' where studentUsername='******'"; cmd.CommandText = sqlstr2; con.Open(); cmd.ExecuteNonQuery(); con.Close(); switch (Session["userType"].ToString()) { case "2": countOperation.countAddOne("adminLoginCount"); countOperation.countAddOne("onlineStudent"); systemRecord.insertOneRecord("管理员“" + username + "“登陆成功!"); break; case "3": countOperation.countAddOne("superAdminLoginCount"); countOperation.countAddOne("onlineStudent"); systemRecord.insertOneRecord("超级管理员“" + username + "“登陆成功!"); break; default: countOperation.countAddOne("onlineStudent"); systemRecord.insertOneRecord("学生“" + username + "“登陆成功!"); break; } } } catch (Exception exp) { saveErrorMessage.writeFile("用户登陆时发生错误!", exp.ToString()); i = 0; } } } if (i == 0) { Session["tryCount"] = Convert.ToInt32(Session["tryCount"]) + 1; } Response.Write(i.ToString()); }
private void button11_Click(object sender, EventArgs e) { if (button2.BackColor == Color.Green) { button2.BackColor = Color.Red; } if (button3.BackColor == Color.Green) { button3.BackColor = Color.Red; } if (button4.BackColor == Color.Green) { button4.BackColor = Color.Red; } if (button5.BackColor == Color.Green) { button5.BackColor = Color.Red; } if (button6.BackColor == Color.Green) { button6.BackColor = Color.Red; } if (button7.BackColor == Color.Green) { button7.BackColor = Color.Red; } if (button8.BackColor == Color.Green) { button8.BackColor = Color.Red; } if (button9.BackColor == Color.Green) { button9.BackColor = Color.Red; } label12.Text = ""; connection.Open(); OleDbCommand command = new OleDbCommand(); command.Connection = connection; String qry = "insert into Booking(BookingDate, CustomerName) values(?,?)"; command.CommandText = qry; command.Parameters.AddWithValue("@p1", DateTime.Now.ToString("h:mm:ss")); command.Parameters.AddWithValue("@p2", textBox1.Text); command.ExecuteNonQuery(); connection.Close(); connection.Open(); qry = "select BookingID from Booking where CustomerName ='" + textBox1.Text + "'"; command.CommandText = qry; //command.Parameters.AddWithValue("@p1", textBox1.Text); OleDbDataReader reader = command.ExecuteReader(); while (reader.Read()) { BookingID = (int)reader[0]; } connection.Close(); connection.Open(); OleDbCommand command2 = new OleDbCommand(); command2.Connection = connection; for (int i = 0; i < bookSeats.Count; i++) { qry = "insert into BookSeat(BookingID, Price, ScheduleStationID, Source, Des, SeatNumber, CarNumber) values(?,?,?,?,?,'" + bookSeats[i] + "',?)"; command2.CommandText = qry; command2.Parameters.AddWithValue("@p1", BookingID); command2.Parameters.AddWithValue("@p2", 10); if (comboBox1.SelectedIndex > comboBox2.SelectedIndex) { command2.Parameters.AddWithValue("@p3", ssID[1]); } command2.Parameters.AddWithValue("@p4", comboBox1.SelectedItem); command2.Parameters.AddWithValue("@p5", comboBox2.SelectedItem); //command2.Parameters.AddWithValue("@p6", bookSeats[i]); command2.Parameters.AddWithValue("@p6", CarNumber); command2.ExecuteNonQuery(); } connection.Close(); MessageBox.Show("Booked!"); }
private void button8_Click(object sender, EventArgs e) { for (int v = 0; v < MyData.Tables[0].Rows.Count; v++) { // MessageBox.Show("run"); if (this.a0.Text == MyData.Tables[0].Rows[v][0].ToString()) { string SQL = "delete from dtsbz where a0= '" + this.a0.Text + "'"; OleDbCommand MyCom = new OleDbCommand(SQL, MyConn); MyCom.ExecuteNonQuery(); MyData.Tables[0].Rows.RemoveAt(v); break; } } panel2.Enabled = true; /* * word 存储开始 * */ //代码域 Thread MySaveS = new Thread(new ThreadStart(save)); MySaveS.Start(); /* * word存储结束 */ /* * 数据库存储开始 */ DataRow NewRow = MyData.Tables[0].NewRow(); NewRow["a0"] = a0.Text; NewRow["a1"] = a1.Text; NewRow["a2"] = a2.Text; NewRow["a3"] = a3.Text; NewRow["a4"] = a4.Text; NewRow["a5"] = a5.Text; NewRow["a6"] = a6.Text; NewRow["a7"] = a7.Text; NewRow["a8"] = a8.Text; NewRow["a9"] = a9.Text; NewRow["a10"] = a10.Text; NewRow["a11"] = a11.Text; NewRow["a12"] = a12.Text; NewRow["a13"] = a13.Text; NewRow["a14"] = a14.Text; NewRow["a15"] = a15.Text; NewRow["time_e"] = main.currentTime.Year.ToString() + "-" + main.currentTime.Month.ToString() + "-" + main.currentTime.Day.ToString(); MyData.Tables[0].Rows.Add(NewRow); MyAd.Update(MyData); //MessageBox.Show(a13_1.CheckState); /* * 数据库的存储结束 * * * */ MessageBox.Show("保存成功!!"); i = MyData.Tables[0].Rows.Count - 1; textBox18.Text = (i + 1).ToString(); panel1.Enabled = false; button6.Enabled = true; button7.Enabled = true; button8.Enabled = false; button9.Enabled = false; button10.Enabled = true; button11.Enabled = true; button12.Enabled = true; button13.Enabled = true; button14.Enabled = true; button15.Enabled = true; button16.Enabled = true; button17.Enabled = true; OleDbDataAdapter MyAd0 = new OleDbDataAdapter("select count(a0) from dtsbz ", MyConn); DataSet MyData0 = new DataSet(); MyAd0.Fill(MyData0); this.textBox17.Text = MyData.Tables[0].Rows.Count.ToString(); }
private void button2_Click(object sender, EventArgs e) { double to = 0; double t1 = 0; double t2 = 0; double tf = 0; double bro = 0; double pay = 0; double cut1; double S_P = 0; flage = true; if (dataGridView1.Rows.Count - 1 == 0) { flage = false; } check(); if (flage == true) { com = connction.CreateCommand(); com.CommandType = CommandType.Text; try { com.CommandText = "insert into Info_Cost(Name_C,Da,Address_C,discount,pay,bro,Final_price,S_P,nodes) Values('" + textBox2.Text + "','" + dateTimePicker1.Text + "','" + textBox3.Text + "'," + 0 + "," + 0 + "," + 0 + "," + 0 + "," + 0 + ",'" + textBox5.Text + "')"; connction.Open(); com.ExecuteNonQuery(); connction.Close(); da1 = new OleDbDataAdapter("select Max(ID_C)from Info_Cost Where Name_C like '" + textBox2.Text + "%'", connction); dt1 = new DataTable(); da1.Fill(dt1); if (dt1.Rows.Count > 0) { textBox1.Text = dt1.Rows[0][0].ToString(); } for (int i = 0; i < dataGridView1.Rows.Count - 1; i++) { t1 = Convert.ToDouble(dataGridView1.Rows[i].Cells[2].Value); t2 = Convert.ToDouble(dataGridView1.Rows[i].Cells[3].Value); to = t2 * t1; tf = tf + to; com.CommandText = "insert into Menu_Cost(ID_C,ID_O,Name_Object,No_Object,Price_object,Total_price) Values(" + Convert.ToInt32(textBox1.Text) + "," + (i + 1) + ",'" + dataGridView1.Rows[i].Cells[1].Value + "'," + dataGridView1.Rows[i].Cells[2].Value + "," + dataGridView1.Rows[i].Cells[3].Value + "," + to + ")"; dataGridView1.Rows[i].Cells[0].Value = (i + 1); dataGridView1.Rows[i].Cells[5].Value = (to); connction.Open(); com.ExecuteNonQuery(); connction.Close(); } if (textBox6.Text == "") { cut1 = 0; } else { cut1 = Convert.ToDouble(textBox6.Text); } S_P = (tf - cut1); pay = Convert.ToInt32(Microsoft.VisualBasic.Interaction.InputBox(":الرجاء ادخال المبلغ الواصل من قبل الزبون", "ادخال المبلغ", S_P.ToString())); bro = S_P - pay; com.CommandText = "update Info_Cost set discount=" + cut1 + ",pay=" + pay + ",bro=" + bro + ",Final_price=" + tf + ",S_P=" + S_P + " where ID_C=" + Convert.ToInt32(textBox1.Text) + ""; connction.Open(); com.ExecuteNonQuery(); connction.Close(); textBox8.Text = pay.ToString(); textBox4.Text = S_P.ToString(); textBox7.Text = tf.ToString(); textBox9.Text = bro.ToString(); MessageBox.Show("تم حفظ البيانات بنجاح"); button2.Enabled = false; textBox2.ReadOnly = true; textBox3.ReadOnly = true; textBox6.ReadOnly = true; dataGridView1.ReadOnly = true; button3.Text = "قائمة جديدة"; } catch (Exception ex) { MessageBox.Show("هناك نقص في البيانات المدخلة يرجى مراجع البيانات", "خطا في الادخال"); Console.WriteLine(ex.Message); } } else { MessageBox.Show("هناك نقص اما باسم الزبون او بنوع المادة او العدد او سعر المفرد!!الرجاء ادخال البيانات كاملة هناك"); } }
public void ReadData() { //TODO: check what is required on the machine and maybe support for other versions string ConnString = GetConnectionString(); string sql = ""; using (OleDbConnection Conn = new OleDbConnection(ConnString)) { try { Conn.Open(); } catch (Exception ex) { System.Threading.Thread.Sleep(3000); Conn.Open(); Reporter.ToLog(eLogLevel.ERROR, $"Method - {MethodBase.GetCurrentMethod().Name}, Error - {ex.StackTrace}", ex); } OleDbCommand Cmd = new OleDbCommand(); Cmd.Connection = Conn; string SheetName = GetInputParamCalculatedValue("SheetName").Trim(); if (!SheetName.EndsWith("$")) { SheetName += "$"; } if (SelectAllRows == false) { sql = "Select TOP 1 * from [" + SheetName + "]"; } else { sql = "Select * from [" + SheetName + "]"; } string where = GetInputParamCalculatedValue("SelectRowsWhere"); if (!string.IsNullOrEmpty(where)) { sql += " WHERE " + where; } Cmd.CommandText = sql; DataTable dt = new DataTable(); OleDbDataAdapter da = new OleDbDataAdapter(); da.SelectCommand = Cmd; try { da.Fill(dt); // we expect only 1 record if (dt.Rows.Count == 1 && SelectAllRows == false) { DataRow r = dt.Rows[0]; for (int i = 0; i < dt.Columns.Count; i++) { AddOrUpdateReturnParamActual(dt.Columns[i].ColumnName, ((object)r[i]).ToString()); } if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed"))) { string rowKey = r[GetInputParamCalculatedValue("PrimaryKeyColumn")].ToString(); string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed") + " WHERE " + GetInputParamCalculatedValue("PrimaryKeyColumn") + "=" + rowKey + ";"; OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } } else if (dt.Rows.Count > 0 && SelectAllRows == true) { for (int j = 0; j < dt.Rows.Count; j++) { DataRow r = dt.Rows[j]; for (int i = 0; i < dt.Columns.Count; i++) { AddOrUpdateReturnParamActualWithPath(dt.Columns[i].ColumnName, ((object)r[i]).ToString(), "" + (j + 1).ToString()); } } if (!String.IsNullOrEmpty(GetInputParamCalculatedValue("SetDataUsed"))) { string updateSQL = @"UPDATE [" + GetInputParamCalculatedValue("SheetName") + "$] SET " + GetInputParamCalculatedValue("SetDataUsed"); if (!string.IsNullOrEmpty(where)) { updateSQL += " WHERE " + where + ";"; } OleDbCommand myCommand = new OleDbCommand(); myCommand.Connection = Conn; myCommand.CommandText = updateSQL; myCommand.ExecuteNonQuery(); } } else if (dt.Rows.Count != 1 && SelectAllRows == false) { Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed; Error = "Excel Query should return only one row" + Environment.NewLine + sql + Environment.NewLine + "Returned: " + dt.Rows.Count + " Records"; } } catch (Exception ex) { this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed; Error = ex.Message; } finally { Conn.Close(); } if (dt.Rows.Count == 0) { this.Status = Amdocs.Ginger.CoreNET.Execution.eRunStatus.Failed; Error = "No rows found in excel file matching criteria - " + sql; } } }
/// <summary> /// This method uses to add data to MST_WorkingDayDetail /// </summary> /// <Inputs> /// MST_WorkingDayDetailVO /// </Inputs> /// <Returns> /// void /// </Returns> /// <History> /// Thursday, July 21, 2005 /// </History> public void Add(object pobjObjectVO) { const string METHOD_NAME = THIS + ".Add()"; OleDbConnection oconPCS = null; OleDbCommand ocmdPCS = null; try { MST_WorkingDayDetailVO objObject = (MST_WorkingDayDetailVO)pobjObjectVO; string strSql = String.Empty; Utils utils = new Utils(); oconPCS = new OleDbConnection(Utils.Instance.OleDbConnectionString); ocmdPCS = new OleDbCommand("", oconPCS); strSql = "INSERT INTO MST_WorkingDayDetail(" + MST_WorkingDayDetailTable.OFFDAY_FLD + "," + MST_WorkingDayDetailTable.COMMENT_FLD + "," + MST_WorkingDayDetailTable.WORKINGDAYMASTERID_FLD + ")" + "VALUES(?,?,?)"; ocmdPCS.Parameters.Add(new OleDbParameter(MST_WorkingDayDetailTable.OFFDAY_FLD, OleDbType.Date)); ocmdPCS.Parameters[MST_WorkingDayDetailTable.OFFDAY_FLD].Value = objObject.OffDay; ocmdPCS.Parameters.Add(new OleDbParameter(MST_WorkingDayDetailTable.COMMENT_FLD, OleDbType.VarWChar)); ocmdPCS.Parameters[MST_WorkingDayDetailTable.COMMENT_FLD].Value = objObject.Comment; ocmdPCS.Parameters.Add(new OleDbParameter(MST_WorkingDayDetailTable.WORKINGDAYMASTERID_FLD, OleDbType.Integer)); ocmdPCS.Parameters[MST_WorkingDayDetailTable.WORKINGDAYMASTERID_FLD].Value = objObject.WorkingDayMasterID; ocmdPCS.CommandText = strSql; ocmdPCS.Connection.Open(); ocmdPCS.ExecuteNonQuery(); } catch (OleDbException ex) { if (ex.Errors[1].NativeError == ErrorCode.SQLDUPLICATE_KEYCODE) { throw new PCSDBException(ErrorCode.DUPLICATE_KEY, METHOD_NAME, ex); } else { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } } catch (InvalidOperationException ex) { throw new PCSDBException(ErrorCode.ERROR_DB, METHOD_NAME, ex); } catch (Exception ex) { throw new PCSDBException(ErrorCode.OTHER_ERROR, METHOD_NAME, ex); } finally { if (oconPCS != null) { if (oconPCS.State != ConnectionState.Closed) { oconPCS.Close(); } } } }
//发表帖子 public void add(Post post) { OleDbConnection conn = null; OleDbCommand comm = null; try { conn = DBManage.getConn(); conn.Open(); comm = conn.CreateCommand(); comm.CommandText = "insert into post(userId,title,content,postTime,userIp,replyNum,browseNum) values (@userId,@title,@content,@postTime,@userIp,@replyNum,@browseNum)"; //动态设置参数 List <OleDbParameter> paras = new List <OleDbParameter>(); //paras.Add(new OleDbParameter() { ParameterName = "@postId", Value = post.PostId }); paras.Add(new OleDbParameter() { ParameterName = "@userId", Value = post.UserId }); paras.Add(new OleDbParameter() { ParameterName = "@title", Value = post.Title }); paras.Add(new OleDbParameter() { ParameterName = "@content", Value = post.Content }); paras.Add(new OleDbParameter() { ParameterName = "@postTime", Value = post.PostTime.ToString() }); paras.Add(new OleDbParameter() { ParameterName = "@userIp", Value = post.UserIp }); paras.Add(new OleDbParameter() { ParameterName = "@replyNum", Value = post.ReplyNum }); paras.Add(new OleDbParameter() { ParameterName = "@browseNum", Value = post.BrowseNum }); comm.Parameters.AddRange(paras.ToArray <OleDbParameter>()); //3.执行命令 comm.ExecuteNonQuery(); } catch (Exception e) { Console.Write(e.Message); throw new Exception(); } finally { if (conn != null) { DBManage.closeConn(conn); } } }
protected void ImageButton2_Click(object sender, ImageClickEventArgs e) { OleDbConnection vtbaglanti = new OleDbConnection(); vtbaglanti.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + Server.MapPath("~/App_Data\\panel.mdb"); vtbaglanti.Open(); string ara = "SELECT count(kullaniciadi) FROM admin Where kullaniciadi = '" + txtKullaniciadi.Text + "'"; OleDbCommand sa = new OleDbCommand(ara, vtbaglanti); int c = Convert.ToInt32(sa.ExecuteScalar()); if (c != 0) { Response.Write("<script>alert('Kullanıcı adı geçersiz')</script>"); } else if (txtAd.Text == "" || txtSoyad.Text == "" || txtEposta.Text == "" || txtSifre1.Text == "" || txtSifre2.Text == "" || dpDay.SelectedValue == "" || dpMonth.SelectedValue == "" || dpYear.SelectedValue == "" || txtCeptelefonu.Text == "" || DropDownList3.SelectedIndex==0 ||(rdBay.Checked==false && rdBayan.Checked==false)) Response.Write("<script>alert('Lütfen Bilgilerinizi Eksiksiz Doldurunuz...')</script>"); else if (txtSifre1.Text != txtSifre2.Text) { Response.Write("<script>alert('Girdiğiniz Şifreler Eşleşmiyor')</script>"); } else if (CheckBox1.Checked == false) { Response.Write("<script>alert('Canon Hizmet Şartlarını ve Gizlilik Politikasını Kabul Ediyorum Seçeneğini İşaretleyiniz')</script>"); } else { if (vtbaglanti.State == ConnectionState.Closed) { vtbaglanti.Open(); } OleDbCommand kaydet = new OleDbCommand("insert into admin(ad,soyad,eposta,kullaniciadi,sifre1,sifre2,dogumgun,dogumay,dogumyıl,cinsiyet,ceptelefonu,konum) values(@ad,@soyad,@eposta,@kullaniciadi,@sifre1,@sifre2,@dogumgun,@dogumay,@dogumyıl,@cinsiyet,@ceptelefonu,@konum)", vtbaglanti); kaydet.Parameters.AddWithValue("@ad", txtAd.Text); kaydet.Parameters.AddWithValue("@soyad", txtSoyad.Text); kaydet.Parameters.AddWithValue("@eposta", txtEposta.Text); kaydet.Parameters.AddWithValue("@kullaniciadi", txtKullaniciadi.Text); kaydet.Parameters.AddWithValue("@sifre1", txtSifre1.Text); kaydet.Parameters.AddWithValue("@sifre2", txtSifre2.Text); kaydet.Parameters.AddWithValue("@dogumgun", dpDay.SelectedValue); kaydet.Parameters.AddWithValue("@dogumay", dpMonth.SelectedValue); kaydet.Parameters.AddWithValue("@dogumyıl", dpYear.SelectedValue); if (rdBay.Checked) { kaydet.Parameters.AddWithValue("@cinsiyet", "Bay"); Session["cinsiyet"] = "Mr"; } else { kaydet.Parameters.AddWithValue("@cinsiyet", "Bayan"); Session["cinsiyet"] = "Mrs"; } kaydet.Parameters.AddWithValue("@ceptelefonu", txtCeptelefonu.Text); kaydet.Parameters.AddWithValue("@konum", DropDownList3.SelectedValue); kaydet.ExecuteNonQuery(); vtbaglanti.Close(); Response.Redirect("frmBilgi.aspx"); } }
public void execute(string query) { OleDbTransaction trans = this.conn.BeginTransaction(); OleDbCommand comm = new OleDbCommand(); comm.Connection = this.conn; comm.CommandText = query; comm.Transaction = trans; comm.ExecuteNonQuery(); trans.Commit(); }
/// <summary> /// 将数据导出至Excel文件 /// </summary> /// <param name="Table">DataTable对象</param> /// <param name="ExcelFilePath">Excel文件路径</param> public static bool OutputToExcel(DataTable Table, string ExcelFilePath) { if (File.Exists(ExcelFilePath)) { throw new Exception("该文件已经存在!"); } if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table")) { Table.TableName = "Sheet1"; } //数据表的列数 int ColCount = Table.Columns.Count; //用于记数,实例化参数时的序号 int i = 0; //创建参数 OleDbParameter[] para = new OleDbParameter[ColCount]; //创建表结构的SQL语句 string TableStructStr = @"Create Table " + Table.TableName + "("; //连接字符串 string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString); //创建表结构 OleDbCommand objCmd = new OleDbCommand(); //数据类型集合 ArrayList DataTypeList = new ArrayList(); DataTypeList.Add("System.Decimal"); DataTypeList.Add("System.Double"); DataTypeList.Add("System.Int16"); DataTypeList.Add("System.Int32"); DataTypeList.Add("System.Int64"); DataTypeList.Add("System.Single"); //遍历数据表的所有列,用于创建表结构 foreach (DataColumn col in Table.Columns) { //如果列属于数字列,则设置该列的数据类型为double if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0) { para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double); objCmd.Parameters.Add(para[i]); //如果是最后一列 if (i + 1 == ColCount) { TableStructStr += col.ColumnName + " double)"; } else { TableStructStr += col.ColumnName + " double,"; } } else { para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar); objCmd.Parameters.Add(para[i]); //如果是最后一列 if (i + 1 == ColCount) { TableStructStr += col.ColumnName + " varchar)"; } else { TableStructStr += col.ColumnName + " varchar,"; } } i++; } //创建Excel文件及文件结构 try { objCmd.Connection = objConn; objCmd.CommandText = TableStructStr; if (objConn.State == ConnectionState.Closed) { objConn.Open(); } objCmd.ExecuteNonQuery(); } catch (Exception exp) { throw exp; } //插入记录的SQL语句 string InsertSql_1 = "Insert into " + Table.TableName + " ("; string InsertSql_2 = " Values ("; string InsertSql = ""; //遍历所有列,用于插入记录,在此创建插入记录的SQL语句 for (int colID = 0; colID < ColCount; colID++) { if (colID + 1 == ColCount) //最后一列 { InsertSql_1 += Table.Columns[colID].ColumnName + ")"; InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")"; } else { InsertSql_1 += Table.Columns[colID].ColumnName + ","; InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ","; } } InsertSql = InsertSql_1 + InsertSql_2; //遍历数据表的所有数据行 for (int rowID = 0; rowID < Table.Rows.Count; rowID++) { for (int colID = 0; colID < ColCount; colID++) { if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "") { para[colID].Value = 0; } else { para[colID].Value = Table.Rows[rowID][colID].ToString().Trim(); } } try { objCmd.CommandText = InsertSql; objCmd.ExecuteNonQuery(); } catch (Exception exp) { string str = exp.Message; } } try { if (objConn.State == ConnectionState.Open) { objConn.Close(); } } catch (Exception exp) { throw exp; } return(true); }
public int connectRegUG(UnderGrad x) { OleDbConnection connection = new OleDbConnection(); try { connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb; Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; "; connection.Open(); var cmd = new OleDbCommand("INSERT INTO Student ([Id],[Firstname], [Lastname] ,[Email] , [Address], [Subject], [intCAOdata], [doubleDegreegrade]) VALUES (@a, @b, @c, @d, @e, @f, @g, @h)"); cmd.Connection = connection; cmd.Parameters.AddRange(new[] { new OleDbParameter("@a", x.Id), new OleDbParameter("@b", x.FirstName), new OleDbParameter("@c", x.LastName), new OleDbParameter("@d", x.Email), ////cant find reference to correct variables new OleDbParameter("@e", x.Address), new OleDbParameter("@f", x.Subject), new OleDbParameter("@g", x.CAO), new OleDbParameter("@h", "") }); cmd.ExecuteNonQuery(); Console.WriteLine("Student registered successfully."); } catch (Exception ex) { Console.WriteLine("Error " + ex); } connection.Close(); return 0; }
/// <summary> /// 将数据导出至Excel文件 /// </summary> /// <param name="Table">DataTable对象</param> /// <param name="Columns">要导出的数据列集合</param> /// <param name="ExcelFilePath">Excel文件路径</param> public static bool OutputToExcel(DataTable Table, ArrayList Columns, string ExcelFilePath) { if (File.Exists(ExcelFilePath)) { throw new Exception("该文件已经存在!"); } //如果数据列数大于表的列数,取数据表的所有列 if (Columns.Count > Table.Columns.Count) { for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++) { Columns.RemoveAt(s); //移除数据表列数后的所有列 } } //遍历所有的数据列,如果有数据列的数据类型不是 DataColumn,则将它移除 DataColumn column = new DataColumn(); for (int j = 0; j < Columns.Count; j++) { try { column = (DataColumn)Columns[j]; } catch (Exception) { Columns.RemoveAt(j); } } if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table")) { Table.TableName = "Sheet1"; } //数据表的列数 int ColCount = Columns.Count; //创建参数 OleDbParameter[] para = new OleDbParameter[ColCount]; //创建表结构的SQL语句 string TableStructStr = @"Create Table " + Table.TableName + "("; //连接字符串 string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;"; OleDbConnection objConn = new OleDbConnection(connString); //创建表结构 OleDbCommand objCmd = new OleDbCommand(); //数据类型集合 ArrayList DataTypeList = new ArrayList(); DataTypeList.Add("System.Decimal"); DataTypeList.Add("System.Double"); DataTypeList.Add("System.Int16"); DataTypeList.Add("System.Int32"); DataTypeList.Add("System.Int64"); DataTypeList.Add("System.Single"); DataColumn col = new DataColumn(); //遍历数据表的所有列,用于创建表结构 for (int k = 0; k < ColCount; k++) { col = (DataColumn)Columns[k]; //列的数据类型是数字型 if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0) { para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double); objCmd.Parameters.Add(para[k]); //如果是最后一列 if (k + 1 == ColCount) { TableStructStr += col.Caption.Trim() + " Double)"; } else { TableStructStr += col.Caption.Trim() + " Double,"; } } else { para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar); objCmd.Parameters.Add(para[k]); //如果是最后一列 if (k + 1 == ColCount) { TableStructStr += col.Caption.Trim() + " VarChar)"; } else { TableStructStr += col.Caption.Trim() + " VarChar,"; } } } //创建Excel文件及文件结构 try { objCmd.Connection = objConn; objCmd.CommandText = TableStructStr; if (objConn.State == ConnectionState.Closed) { objConn.Open(); } objCmd.ExecuteNonQuery(); } catch (Exception exp) { throw exp; } //插入记录的SQL语句 string InsertSql_1 = "Insert into " + Table.TableName + " ("; string InsertSql_2 = " Values ("; string InsertSql = ""; //遍历所有列,用于插入记录,在此创建插入记录的SQL语句 for (int colID = 0; colID < ColCount; colID++) { if (colID + 1 == ColCount) //最后一列 { InsertSql_1 += Columns[colID].ToString().Trim() + ")"; InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")"; } else { InsertSql_1 += Columns[colID].ToString().Trim() + ","; InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ","; } } InsertSql = InsertSql_1 + InsertSql_2; //遍历数据表的所有数据行 DataColumn DataCol = new DataColumn(); for (int rowID = 0; rowID < Table.Rows.Count; rowID++) { for (int colID = 0; colID < ColCount; colID++) { //因为列不连续,所以在取得单元格时不能用行列编号,列需得用列的名称 DataCol = (DataColumn)Columns[colID]; if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "") { para[colID].Value = 0; } else { para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim(); } } try { objCmd.CommandText = InsertSql; objCmd.ExecuteNonQuery(); } catch (Exception exp) { string str = exp.Message; } } try { if (objConn.State == ConnectionState.Open) { objConn.Close(); } } catch (Exception exp) { throw exp; } return(true); }
public void writeRate(Structs.RateInt ri)//写入rate { cmd = new OleDbCommand("update sheet2 set TESTNO='" + ri.COUNT.ToString() + "',FIRSTPASSNO='" + ri.FIRSTPASSNO.ToString() + "',FIRSTPASSRATE='" + ri.FIRSTPASSRATE.ToString("0.00") + "',REPEATNO='" + ri.REPEATNO.ToString() + "', REPEATRATE='" + ri.REPEATRATE.ToString("0.00") + "'where ID=1", conn); cmd.ExecuteNonQuery(); }
public static void executeNonQuery(OleDbConnection conn, string insertStatement) { OleDbCommand comm = new OleDbCommand(insertStatement, conn); comm.ExecuteNonQuery(); }