public object[] db_access(string strSQL) { ADODB.Connection objCon; ADODB.Recordset objRec; object[,] dataRows; object[] dataSuite; string strCon; objCon = new ADODB.Connection(); objRec = new ADODB.Recordset(); //establish the connection string and open the database connection strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; objCon.Open(strCon); //execute the SQL and return the recrodset of results objRec = objCon.Execute(strSQL, out missing, 0); //populate a two dinmensional object array with the results dataRows = objRec.GetRows(); //get a one dimensional array that can be placed into the Test Suite dropdown dataSuite = thinArray(dataRows); //close the recordset objRec.Close(); //close the database connection objCon.Close(); return dataSuite; }
private void cmdClose_Click(System.Object eventSender, System.EventArgs eventArgs) { int MStockNew = 0; // ERROR: Not supported in C#: OnErrorStatement ADODB.Recordset rsB = default(ADODB.Recordset); ADODB.Recordset rsk = default(ADODB.Recordset); rsB = new ADODB.Recordset(); rsk = new ADODB.Recordset(); rsk = modRecordSet.getRS(ref "SELECT * FROM " + Te_Name + ""); if (rsk.RecordCount > 0) { //UPGRADE_WARNING: Couldn't resolve default property of object MStockNew. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"' MStockNew = 2; rsB = modRecordSet.getRS(ref "INSERT INTO StockGroup(StockGroup_Name,StockGroup_Disabled)VALUES('" + Te_Name + "',0)"); UpdateCatalogID(ref (Te_Name)); } MyErH: if (rsLIQ.State) { rsLIQ.Close(); } this.Close(); }
private void coletadados() { ADODB.Recordset dados = new ADODB.Recordset(); ADODB.Recordset itens = new ADODB.Recordset(); String SQL = "SELECT * FROM SAÍDA WHERE (COD_SAI = " + _id + ");"; String SQLProdutos = "SELECT COD, ESTOQUE FORM PRODUTOS;"; //Buscando os itens dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); itens.Open(SQLProdutos, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); while (!(dados.EOF || dados.BOF)) { itens.Find("cod =" + dados.Fields["[cod do CD]"].Value, 0, ADODB.SearchDirectionEnum.adSearchForward); if (!(itens.BOF || itens.EOF)) { itens.Fields["Estoque"].Value = Convert.ToInt64(itens.Fields["Estoque"].Value) + Convert.ToInt64(dados.Fields["Qunatidade"].Value); } dados.MoveNext(); } //EXcluindo ITENS SQL = "DELETE FROM SAÍDA WHERE (COD_SAI = " + _id + ");"; dados.Close(); dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); //Excluindo capa SQL = "DELETE FROM cod_sai WHERE(COD_SAI = " + _id + ");"; dados.Close(); dados.Open(SQL, new Conexao().getDb4(), ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); }
//传入SQL语句,得到该SQL语句执行的结果并返回。 private object[,] plSqlReturn(string sQuery) { object[,] ArrayRecords = null; RsRowCount = 0; RsColCount = 0; ADODB.Recordset AdoRs; try { if (GeneralCommon.M_CN1.State == 0) { if (GeneralCommon.GF_DbConnect() == false) { return(null); } } AdoRs = new ADODB.Recordset(); AdoRs.Open(sQuery, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1); if (AdoRs.BOF || AdoRs.EOF) { GeneralCommon.Gp_MsgBoxDisplay("没有数据...!", "I", "提示"); AdoRs.Close(); AdoRs = null; if (GeneralCommon.M_CN1.State != 0) { GeneralCommon.M_CN1.Close(); } Cursor.Current = Cursors.Default; return(null); } RsRowCount = AdoRs.RecordCount; RsColCount = AdoRs.Fields.Count; ArrayRecords = new object[RsRowCount, RsColCount]; int i = 0; while (!AdoRs.EOF) { for (int j = 0; j < AdoRs.Fields.Count; j++) { ArrayRecords[i, j] = AdoRs.Fields[j].Value; } i++; AdoRs.MoveNext(); } AdoRs.Close(); AdoRs = null; if (GeneralCommon.M_CN1.State != 0) { GeneralCommon.M_CN1.Close(); } return(ArrayRecords); } catch (Exception ex) { AdoRs = null; if (GeneralCommon.M_CN1.State != 0) { GeneralCommon.M_CN1.Close(); } return(null); } }
private void FarmerPayment_Load(object sender, EventArgs e) { Temp1.Open(@"select * from farmerpayment", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic); if (Temp1.RecordCount == 0) { textBox1.Text = "1"; } else { textBox1.Text = (Temp1.RecordCount + 1).ToString(); } Temp1.Close(); textBox1.Enabled = false; textBox2.Enabled = false; Temp2.Open(@"select * from farmerdetails", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic); while (Temp2.EOF == false) { Column1.Items.Add(Temp2.Fields["fNames"].Value.ToString()); Temp2.MoveNext(); } Temp2.Close(); // comboBox2.Properties.DropDownStyle = ComboBoxStyle.DropDownList; comboBox2.Properties.Items.Add("CASH"); comboBox2.Properties.Items.Add("CHEQUE"); label7.Visible = false; textBox5.Visible = false; dataGridView1.EditingControlShowing += new DataGridViewEditingControlShowingEventHandler(dataGrid1_Edit); }
private void custmreceipt_Load(object sender, EventArgs e) { textBox1.Enabled = false; textBox2.Enabled = false; textBox4.Text = "0"; textBox3.Text = "0"; // comboBox2.DropDownStyle = ComboBoxStyle.DropDownList; comboBox2.Properties.Items.Add("CASH"); comboBox2.Properties.Items.Add("CHEQUE"); // comboBox1.Properties.DropDownStyle = ComboBoxStyle.DropDownList; label7.Visible = false; textBox5.Visible = false; Temp1.Open(@"select * from customerdetails", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic); while (Temp1.EOF == false) { comboBox1.Properties.Items.Add(Temp1.Fields["CName"].Value); Temp1.MoveNext(); } Temp1.Close(); Temp1.Open(@"select * from payments", Program.DB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic); if (Temp1.RecordCount == 0) { textBox1.Text = "1"; } else { Temp1.MoveLast(); int a = Temp1.Fields["payID"].Value + 1; textBox1.Text = a.ToString(); Temp1.MoveNext(); } Temp1.Close(); }
private bool Addr_Display() { bool isHaveData = false; if (GeneralCommon.M_CN1.State == 0) { if (GeneralCommon.GF_DbConnect() == false) { return(isHaveData); } } ADODB.Recordset AdoRs = new ADODB.Recordset(); try { string sQuery1 = " SELECT LOCATION FROM FP_STDYARD WHERE PROD_TYPE = 'S' AND YARD_TYPE = '" + rib_prev + "' AND ZONE_TYPE = '" + opt_prev + "' AND YARD_KND = 'ZB' "; AdoRs.Open(sQuery1, GeneralCommon.M_CN1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, -1); if (AdoRs.EOF || AdoRs.BOF) { AdoRs.Close(); GeneralCommon.M_CN1.Close(); this.ss1_Sheet1.RowCount = 0; this.ss1_Sheet1.ColumnCount = 0; return(isHaveData); } while (!AdoRs.EOF) { string temp = AdoRs.Fields[0].Value.ToString(); int rowIndex = int.Parse(temp.Substring(3, 2)); int columnIndex = int.Parse(temp.Substring(5, 2)); this.ss1_Sheet1.Cells[rowIndex * 2 - 2, columnIndex - 1].Text = temp; this.ss1_Sheet1.Cells[rowIndex * 2 - 2, columnIndex - 1].Tag = temp; this.ss1_Sheet1.Cells[rowIndex * 2 - 1, columnIndex - 1].Tag = temp; AdoRs.MoveNext(); } isHaveData = true; AdoRs.Close(); AdoRs = null; if (GeneralCommon.M_CN1.State != 0) { GeneralCommon.M_CN1.Close(); } return(isHaveData); } catch (Exception ex) { AdoRs.Close(); AdoRs = null; GeneralCommon.Gp_MsgBoxDisplay("数据错误,请检查是否有异常数据...!!" + ex.Message, "W", "警告"); Cursor.Current = Cursors.Default; if (GeneralCommon.M_CN1.State != 0) { GeneralCommon.M_CN1.Close(); } return(isHaveData); } }
public bool loadData(ref string lName) { bool functionReturnValue = false; ADODB.Recordset rs = default(ADODB.Recordset); string lSQL = null; int x = 0; string lID = null; gField = lName; rs = modRecordSet.getRS(ref "SELECT * From ftConstruct WHERE (ftConstruct_Name = '" + Strings.Replace(lName, "'", "''") + "')"); gFieldID = rs.Fields("ftConstruct_FieldID").Value; gFieldName = rs.Fields("ftConstruct_FieldName").Value; gHeading = rs.Fields("ftConstruct_DisplayName").Value; lSQL = rs.Fields("ftConstruct_SQL").Value; rs.Close(); rs = modRecordSet.getRS(ref lSQL); //Display the list of Titles in the DataCombo loading = true; functionReturnValue = false; x = -1; gArray = new string[rs.RecordCount, 3]; while (!(rs.EOF)) { x = x + 1; gArray[x, 0] = rs.Fields(gFieldID).Value; gArray[x, 1] = rs.Fields(gFieldName).Value + ""; gArray[x, 2] = Convert.ToString(0); rs.MoveNext(); } rs.Close(); rs = modRecordSet.getRS(ref "SELECT ftDataItem_ID From ftDataItem WHERE (ftDataItem_PersonID = " + modRecordSet.gPersonID + ") AND (ftDataItem_FieldName = '" + Strings.Replace(gField, "'", "''") + "')"); while (!(rs.EOF)) { lID = rs.Fields("ftDataItem_ID").Value; for (x = 0; x <= Information.UBound(gArray); x++) { if (lID == gArray[x, 0]) { gArray[x, 2] = Convert.ToString(1); } } rs.MoveNext(); } rs.Close(); setup(); functionReturnValue = true; loadLanguage(); ShowDialog(); loading = false; return(functionReturnValue); }
public static DataTable LoadDBF(string FilePath, string Sql) { ADODB.Recordset rs = new ADODB.Recordset(); ADODB.Connection conn = new ADODB.Connection(); try { conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='dbase 5.0';Data Source = " + System.IO.Path.GetDirectoryName(FilePath)); rs.Open(Sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly); object ary = rs.GetRows(); object[,] aa = (object[, ])ary; int UB1 = aa.GetUpperBound(1); int UB0 = aa.GetUpperBound(0); DataTable dt = new DataTable(); for (int i = 0; i <= UB0; i++) { dt.Columns.Add(i.ToString()); } for (int i = 0; i <= UB1; i++) { DataRow dr = dt.NewRow(); for (int j = 0; j <= UB0; j++) { dr[j] = aa[j, i].ToString(); } dt.Rows.Add(dr); } rs.Close(); conn.Close(); return(dt); } catch (Exception e) { rs.Close(); conn.Close(); return(null); } }
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs) { ADODB.Recordset rs = default(ADODB.Recordset); string sql = null; ADODB.Recordset rsData = default(ADODB.Recordset); CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); CrystalDecisions.CrystalReports.Engine.ReportDocument Report1 = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); Report.Load("cryNegativeProfit.rpt"); Report1.Load("crysqZero.rpt"); string lOrder = null; string lWhere = null; if (gRS.RecordCount) { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; rs = modRecordSet.getRS(ref "SELECT * FROM Company"); Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); rs.Close(); //Report.VerifyOnEveryPrint = True Report.Database.Tables(1).SetDataSource(gRS); //Report.Database.SetDataSource(gRS, 3) My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); } if (gRSsq.RecordCount) { System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; rs = modRecordSet.getRS(ref "SELECT * FROM Company"); Report1.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); rs.Close(); //Report1.VerifyOnEveryPrint = True Report1.Database.Tables(1).SetDataSource(gRSsq); //Report1.Database.SetDataSource(gRSsq, 3) My.MyProject.Forms.frmReportShow.Text = Report1.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report1; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); } }
public static object[,] LoadExcelAry(string FilePath, string Sql) { ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); try { //FilePath = "C:\\Users\\Administrator\\Desktop\\测试模版\\Shp\\1\\凤凰观村2组.xls"; //Sql = "Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码"; //Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码 //Select * From [发包方信息$A1:BB2000] conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + FilePath); rs.Open(Sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1); object ary = rs.GetRows(); object[,] aa = (object[, ])ary; rs.Close(); conn.Close(); int UB1 = aa.GetUpperBound(1); int UB0 = aa.GetUpperBound(0); for (int i = 0; i <= UB1; i++) { for (int j = 0; j <= UB0; j++) { ErrorMsg = (i + "-" + j); if (Convert.IsDBNull(aa[j, i]) || aa[j, i] == null) { aa[j, i] = ""; } } } //object test0 = aa[1, 3]; //object test1 = aa[15, 3]; //object test2 = aa[17, 3]; return(aa); } catch (Exception) { rs.Close(); conn.Close(); return(null); } }
public object[] db_access(string strSQL) { ADODB.Connection objCon; ADODB.Recordset objRec; object[,] dataRows; object[] dataSuite; string strCon; objCon = new ADODB.Connection(); objRec = new ADODB.Recordset(); //establish the connection string and open the database connection strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; objCon.Open(strCon); //execute the SQL and return the recrodset of results objRec = objCon.Execute(strSQL, out missing, 0); //populate a two dinmensional object array with the results dataRows = objRec.GetRows(); //get a one dimensional array that can be placed into the Test Suite dropdown dataSuite = thinArray(dataRows); //close the recordset objRec.Close(); //close the database connection objCon.Close(); return(dataSuite); }
//METODO QUE VERIFICQUE SI EXISTE UNA INFORMACION EN LA TABLA // DE LA BASE DE DATOS public bool MYSQL_EXISTE_DATO(string instruccionSQL) { this.establecerConexionODBC(); bool functionReturnValue = false; ADODB.Recordset rst = new ADODB.Recordset(); rst.CursorLocation = ADODB.CursorLocationEnum.adUseClient; try { rst.Open(instruccionSQL, MYSQL_CONEXION_ODBC, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, -1); if (!rst.EOF) { functionReturnValue = true; } else { functionReturnValue = false; } rst.Close(); } catch (System.Exception ex) { MessageBox.Show("ERROR: " + ex.Message); } rst = null; this.cerrarConexionODBC(); return(functionReturnValue); }
private void cmdPrint_Click(System.Object eventSender, System.EventArgs eventArgs) { ADODB.Recordset rs = default(ADODB.Recordset); string sql = null; CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); Report.Load("cryStockItemPrice.rpt"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; rs = modRecordSet.getRS(ref "SELECT * FROM Company"); Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); rs.Close(); Report.SetParameterValue("txtFilter", lblHeading.Text); Report.SetParameterValue("txtTitle1", "Where Sales Channel = " + cmbChannel.CtlText); //Report.Database.SetDataSource(adoPrimaryRS, 3) Report.Database.Tables(1).SetDataSource(adoPrimaryRS); //Report.VerifyOnEveryPrint = True My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); }
static List <MyParagraph> getTitleParagraphs(ADODB.Connection cnn, UInt64 titleId) { var paragraphLst = new List <MyParagraph>(); //get title var qry = "select * from paragraphs where titleId = ? "; var rst = new ADODB.Recordset(); rst.Open(qry.Replace("?", titleId.ToString()), cnn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, (int)ADODB.CommandTypeEnum.adCmdText); rst.MoveFirst(); while (!rst.EOF) { var par = new MyParagraph(); par.order = Convert.ToInt32(rst.Fields["order"].Value); par.alignment = Convert.ToInt32(rst.Fields["alignment"].Value); par.leftIndent = Convert.ToInt32(rst.Fields["leftIndent"].Value); par.fontSize = Convert.ToInt32(rst.Fields["fontSize"].Value); par.fontBold = Convert.ToInt32(rst.Fields["fontBold"].Value); par.fontItalic = Convert.ToInt32(rst.Fields["fontItalic"].Value); par.content = Convert.ToString(rst.Fields["content"].Value); paragraphLst.Add(par); rst.MoveNext(); } rst.Close(); return(paragraphLst); }
private bool IsCorrectionJob() { bool bReturn = false; ADODB.Recordset oRS = null; try { oRS = DataContext.OpenRecordset("select G3E_JOBTYPE from g3e_job where g3e_identifier = ?", ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic, (int)ADODB.CommandTypeEnum.adCmdText, DataContext.ActiveJob); oRS.MoveFirst(); if (Convert.ToString(oRS.Fields["G3E_JOBTYPE"].Value).Equals("NON-WR")) { bReturn = true; } } catch (Exception ex) { throw ex; } finally { if (oRS != null) { if (oRS.State == 1) { oRS.Close(); oRS.ActiveConnection = null; } oRS = null; } } return(bReturn); }
public void buildCriteria(ref string filter_Renamed) { ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Recordset RSitem = default(ADODB.Recordset); gHeading = ""; gCriteria = ""; rs = modRecordSet.getRS(ref "SELECT * From ftOrderSet Where (((ftSet_Group) = 'order')) ORDER BY ftSet_Order;"); if (rs.BOF | rs.EOF) { } else { while (!(rs.EOF)) { RSitem = modRecordSet.getRS(ref "SELECT * From ftOrder WHERE (((ftData_PersonID)=" + modRecordSet.gPersonID + ") AND ((ftData_FieldName)='" + Strings.Replace(rs.Fields("ftSet_Name").Value, "'", "''") + "'));"); if (RSitem.BOF | RSitem.EOF) { } else { gHeading = gHeading + " AND " + RSitem.Fields("ftData_Heading").Value; gCriteria = gCriteria + " AND " + Strings.Replace(RSitem.Fields("ftData_SQL").Value, "[field]", rs.Fields("ftSet_Field").Value); } RSitem.Close(); rs.moveNext(); } gCriteria = Strings.Replace(gCriteria, "=", "<>"); gCriteria = Strings.Replace(gCriteria, " OR ", " AND "); } rs.Close(); if (!string.IsNullOrEmpty(gHeading)) { gHeading = Strings.Mid(gHeading, 6); } }
/// <summary> /// Extracts the data contained in the ADO recordset and stores it in /// XmlSource. If there is another recordset, then the first was actually /// Extended attributes metadata, so that document is move to XmlExtendedAttributes, /// and the data in the second recordset is store into XmlSource. /// </summary> private void GetSource() { ADODB.Connection oConn = null; if (oRs == null) { OpenRecordset(CreateCommand(ref oConn)); } MSXML2.DOMDocument D1 = new MSXML2.DOMDocumentClass(); // load the 1st dataset oRs.Save(D1, ADODB.PersistFormatEnum.adPersistXML); XmlSource = new XmlDocument(); XmlSource.LoadXml(D1.xml); object RecordsAffected; oRs = oRs.NextRecordset(out RecordsAffected); if (oRs != null && oRs.State == 1) { XmlExtendedAttributes = XmlSource; // Load the 2nd dataset oRs.Save(D1, ADODB.PersistFormatEnum.adPersistXML); XmlSource = new XmlDocument(); XmlSource.LoadXml(D1.xml); } if (oRs != null && oRs.State == 1) { oRs.Close(); } if (oConn != null) { oConn.Close(); } }
public static void AddData <T>(this IDatabaseObject <T> source) where T : class { string name = typeof(T).Name; string sql = "[" + name + "]"; ADODB.Recordset rs = DataSeverConnection.Instance.Recordset(sql, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic); rs.AddNew(); PropertyInfo[] properties = typeof(T).GetProperties(); for (int i = 0; i < rs.Fields.Count; i++) { PropertyInfo property = null; properties.Each(f => { if (f.Name == rs.Fields[i].Name) { property = f; return; } }); rs.Fields[i].Value = property.GetValue(source); } rs.Update(); rs.Close(); }
public static string ReadExcel(string FilePath, int C, int R) { ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); string Sql = "Select * From [Sheet1$]"; //Select 宗地编码,土地类型,承包方名称,承包方编码 From [地块信息$] Order By 承包方编码,宗地编码 conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + FilePath); rs.Open(Sql, conn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockReadOnly, -1); object ary = rs.GetRows(); object[,] aa = (object[, ])ary; rs.Close(); conn.Close(); if ((string)aa[C - 1, R - 2] == null) { return(""); } return((string)aa[C - 1, R - 2]); }
private void ErgebnisseForm_LoadBestellungen(ADODB.Connection cn, ADODB.Recordset rs) { rs.Open("Select * From myOrderlist", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid1.Rows.Add(); metroGrid1.Rows[i].Cells["Artikelnummer_Bestellung"].Value = rs.Fields["article"].Value; metroGrid1.Rows[i].Cells["Menge_Bestellung"].Value = rs.Fields["quantity"].Value; // Überprüfung auf Eil oder Normalbestellung if (Convert.ToInt32(rs.Fields["modus"].Value) == 4) { metroGrid1.Rows[i].Cells["Bestellart_Bestellung"].Value = "Eil"; } else if (Convert.ToInt32(rs.Fields["modus"].Value) == 5) { metroGrid1.Rows[i].Cells["Bestellart_Bestellung"].Value = "Normal"; } rs.MoveNext(); ++i; } i = 0; rs.Close(); }
private static bool DeleteAsketPor() { string Select = "Select Time.Id, IdRazrabAsket from " + DB.TABLE_REDMINE_ISSUES + " as ISSUES " + " left join " + DB.TABLE_REDMINE_TIME_ENTRIES + " as Time on ISSUES.Id = Time.IssueId " + " where Deleted = 1 and DeletedInAsketPor = 0"; ADODB.Recordset RsPor = DB.Select(Select); if (RsPor == null) { return(false); } string Update = ""; while (!(RsPor.EOF)) { if (RsPor.Fields["IdRazrabAsket"].Value > 0) { Update = "Delete from " + DB.TABLE_REPORT_DEVELOPER + " where Код=" + RsPor.Fields["IdRazrabAsket"].Value; DB.ExecuteQuery(Update); Update = "Update" + DB.TABLE_REDMINE_TIME_ENTRIES + " set DeletedInAsketPor= 1 where id=" + RsPor.Fields["Id"].Value; DB.ExecuteQuery(Update); } RsPor.MoveNext(); } RsPor.Close(); return(true); }
public void TestDbRunSql() { ADODB.Connection conn = null; AdoHelper helper = null; try { using (helper = AdoHelper.ThreadInstance("", DbProviderType.Odbc)) { conn = helper.Connection(); Assert.AreEqual(conn.State, 1); string sql = " "; ADODB.Recordset rs = new ADODB.Recordset(); rs.Open(sql, conn, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockUnspecified, -1); int a = int.Parse(rs.Fields[0].Value.ToString()); rs.Close(); Assert.IsTrue((a > 0)); } } catch (Exception ex) { Console.WriteLine(string.Format("{0}\n{1}", ex.Message, ex.ToString())); throw; } Assert.AreEqual(conn.State, 0); }
private void ErgebnisseForm_LoadZugang(ADODB.Connection cn, ADODB.Recordset rs) { rs.Open("Select * From Lagerzugang", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid1.Rows.Add(); metroGrid1.Rows[i].Cells["Artikelnummer_Zugang"].Value = rs.Fields["article"].Value; metroGrid1.Rows[i].Cells["Bestellperiode_Zugang"].Value = rs.Fields["orderperiod"].Value; // Überprüfung auf Eil oder Normalbestellung if (Convert.ToInt32(rs.Fields["modus"].Value) == 4) { metroGrid1.Rows[i].Cells["Modus_Zugang"].Value = "Eil"; } else if (Convert.ToInt32(rs.Fields["modus"].Value) == 5) { metroGrid1.Rows[i].Cells["Modus_Zugang"].Value = "Normal"; } metroGrid1.Rows[i].Cells["Lieferzeit_Zugang"].Value = rs.Fields["Lieferzeit"].Value; metroGrid1.Rows[i].Cells["Menge_Zugang"].Value = rs.Fields["amount"].Value; metroGrid1.Rows[i].Cells["Preis_Zugang"].Value = rs.Fields["price"].Value; rs.MoveNext(); ++i; } i = 0; rs.Close(); }
public string get_next_service_id() { string latest_serv_id = ""; string next_serv_id = ""; string sql = sql_lib.get_latest_servise_id(); ADODB.Recordset rs = null; try { if (mdb.RsOpen(out rs, sql) == false) { return(next_serv_id); } if (rs.EOF == false) { latest_serv_id = rs.Fields["id"].Value.ToString(); next_serv_id = (Convert.ToInt32(latest_serv_id) + 1).ToString(); } rs.Close(); rs = null; } catch (Exception ex) { MessageBox.Show(ex.ToString()); } return(next_serv_id); }
public string GetResMessage() { ADODB.Recordset reccust = null; string resMessage = ""; try { classE1_1.Open(); DbUse.open_ado_rec(classE1_1.globaldb, ref reccust, "zstblresults"); resMessage = ""; if (!reccust.EOF) { resMessage = resMessage + (string)reccust.Fields["Results"].Value; reccust.MoveNext(); } if (reccust != null) { reccust.Close(); reccust = null; } classE1_1.Close(); } catch (Exception ex) { logFiles.ErrorLog(ex); } resMessage = resMessage.Replace("\n", "<br/>"); return(DbUse.reproduceRedColor(resMessage)); }
private void loadStockSales() { ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Recordset rsStock = default(ADODB.Recordset); ADODB.Recordset rsGroup = default(ADODB.Recordset); string sql = null; ADODB.Recordset rsData = default(ADODB.Recordset); CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); Report.Load("crySalesByShrink.rpt"); ReportNone.Load("cryNoRecords.rpt"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; rs = modReport.getRSreport(ref "SELECT Report.Report_Heading, aCompany.Company_Name FROM aCompany, Report;"); Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); Report.SetParameterValue("txtDayEnd", rs.Fields("Report_Heading")); rs.Close(); rs = modReport.getRSreport(ref "SELECT * FROM Link Where LinkID=2 AND Link_SectionID=1"); if (string.IsNullOrEmpty(rs.Fields("Link_Name").Value)) { Report.ReportDefinition.Sections("Section2").SectionFormat.EnableSuppress = true; } else { Report.SetParameterValue("txtFilter", Strings.Replace(rs.Fields("Link_Name").Value, "''", "'")); } sql = "SELECT aStockItem.StockItemID, aStockItem.StockItem_Name, thejoin.shrink, thejoin.quantity, thejoin.price, thejoin.list, thejoin.actual FROM aStockItem INNER JOIN (SELECT SaleItem.SaleItem_StockItemID AS StockItem, SaleItem.SaleItem_ShrinkQuantity AS shrink, Sum(IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity])) AS quantity, Sum(([SaleItem_Price]*[SaleItem_Quantity])) AS price, Sum((([SaleItem_ListCost]+[SaleItem_DepositCost])*IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity]))*(1+[SaleItem_Vat]/100)) AS list, Sum((([SaleItem_ActualCost]+[SaleItem_DepositCost])*IIf([SaleItem_Reversal],0-[SaleItem_Quantity],[SaleItem_Quantity]))*(1+[SaleItem_Vat]/100)) AS actual From SaleItem Where (((SaleItem.SaleItem_DepositType) = 0) And ((SaleItem.SaleItem_Revoke) = 0)) GROUP BY SaleItem.SaleItem_StockItemID, SaleItem.SaleItem_ShrinkQuantity ) AS thejoin ON aStockItem.StockItemID = thejoin.StockItem "; sql = sql + rs.Fields("Link_SQL").Value + " ORDER BY aStockItem.StockItem_Name, thejoin.shrink;"; rs = modReport.getRSreport(ref sql); if (rs.BOF | rs.EOF) { ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString); ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString); My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone; My.MyProject.Forms.frmReportShow.mReport = ReportNone; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); return; } Report.Database.Tables(1).SetDataSource(rs); //Report.VerifyOnEveryPrint = True My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); }
public void GetDMSInfo(long value) { ADODB.Recordset rs = DMSConnection.GetInstance().Recordset("SELECT * FROM [dbo.itemloc] WHERE [itm_num]=" + value.ToString()); rs.Each(r => { DCLocation temp = new DCLocation(r.Fields["location"].Value.ToString()); temp.QtyOH = (int)r.Fields["qty_on_hand"].Value; temp.PickZone = (short)r.Fields["pic_zone"].Value; temp.StorageFunction = (byte)r.Fields["stg_fnc_code"].Value; if (temp.LocationType == "Forward") { _forward = temp; _totOH += _forward.QtyOH; } else { if (temp.PickZone < 900) { _reserves.Add(temp); _totOH += temp.QtyOH; } } }); rs.Close(); rs = null; }
private void menge_Produkt3_Button_Click(object sender, EventArgs e) { metroGrid3.Rows.Clear(); //Recordset ADODB.Connection cn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); try { cn.Open(cnStr); rs.Open("Select Artikel, Sum(Anzahl) as Anzahl_Menge From Listen where Endprodukt = 3 group by Artikel", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid3.Rows.Add(); metroGrid3.Rows[i].Cells["Artikel_Mengen"].Value = Convert.ToInt32(rs.Fields["Artikel"].Value); metroGrid3.Rows[i].Cells["Anzahl_Menge"].Value = Convert.ToInt32(rs.Fields["Anzahl_Menge"].Value); rs.MoveNext(); i++; } rs.Close(); } catch (Exception fehler) { Console.WriteLine("Ein Fehler ist aufgetreten", fehler); //Try catch überprüfen.... } finally { cn.Close(); } }
private void StammdatenForm_Load(object sender, EventArgs e) { //Recordset ADODB.Connection cn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); //Connection string. cn.Open(cnStr); rs.Open("Select * From Artikelstammdaten", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); int i = 0; while (!rs.EOF) { metroGrid1.Rows.Add(); metroGrid1.Rows[i].Cells["Artikelnummer"].Value = Convert.ToInt32(rs.Fields["Artikelnummer"].Value); metroGrid1.Rows[i].Cells["Bezeichnung"].Value = rs.Fields["Bezeichnung"].Value; metroGrid1.Rows[i].Cells["Verwendung"].Value = rs.Fields["Verwendung"].Value; metroGrid1.Rows[i].Cells["Lieferkosten"].Value = rs.Fields["Lieferkosten"].Value; metroGrid1.Rows[i].Cells["Lieferzeit"].Value = rs.Fields["Lieferzeit"].Value; metroGrid1.Rows[i].Cells["Lieferzeitabweichung"].Value = rs.Fields["Lieferzeitabweichung"].Value; rs.MoveNext(); ++i; } i = 0; rs.Close(); rs.Open("Select * From PersMaschKosten", cn, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, -1); while (!rs.EOF) { metroGrid4.Rows.Add(); metroGrid4.Rows[i].Cells["Arbeitsplatz"].Value = Convert.ToInt32(rs.Fields["Arbeitsplatz"].Value); metroGrid4.Rows[i].Cells["Lohn1"].Value = Convert.ToDouble(rs.Fields["1Lohn"].Value); metroGrid4.Rows[i].Cells["Lohn2"].Value = Convert.ToDouble(rs.Fields["2Lohn"].Value); metroGrid4.Rows[i].Cells["Lohn3"].Value = Convert.ToDouble(rs.Fields["3Lohn"].Value); metroGrid4.Rows[i].Cells["ÜLohn"].Value = Convert.ToDouble(rs.Fields["ÜLohn"].Value); metroGrid4.Rows[i].Cells["varKosten"].Value = Convert.ToDouble(rs.Fields["varKosten"].Value); metroGrid4.Rows[i].Cells["fixeKosten"].Value = Convert.ToDouble(rs.Fields["fixeKosten"].Value); rs.MoveNext(); ++i; } rs.Close(); cn.Close(); refreshComboBox1(); }
public static void fnKillRS(ADODB.Recordset oRS) { if (oRS.State == 1) { oRS.Close(); } oRS = null; }
public static void tstBrowser(string pth, string datSource, string steName, string tstName, int radnum, string baseURL, ref string[,] tstResult, out int fndExcep, out int fnlFail) { testname = tstName; int vfyFunc; int eleNum; int maxCols; int stpCount; int itmCount; int tstFail; char chr = Convert.ToChar(34); object[,] tmpArray; string[] outArray; string[] lstTest; string[] dbArray; string[,] dataArray; string[,] rsltArray; string argID; string functionID; string getNeg; string nmFunc; string step; string strCon; string strSQL; string tstID; string xlPath; ADODB.Connection objCon; ADODB.Recordset objRec; TestSuite lstObject; getNeg = ""; tstFail = 0; fnlFail = 0; fndExcep = 0; DateTime tmp = DateTime.Now; //date-time at the time of test running vfyFunc = 0; rsltArray = new string[1, 6]; //open browser based on selection on the GUI tstObject tstObj = new tstObject(radnum); //TextFileOps.Write(pth, "<li>", 100); switch (datSource) { case "DB": { //db connection string strCon = "driver={MySQL ODBC 5.1 Driver};server=107.22.232.228;uid=qa_people;pwd=thehandcontrols;" + "database=functional_test_data;option=3"; //database connections objects objCon = new ADODB.Connection(); objRec = new ADODB.Recordset(); //open the connection to the database objCon.Open(strCon); //SQL to execute strSQL = "SELECT id FROM test WHERE name = '" + tstName + "'"; //open recordset and get test id with SQL objRec.Open(strSQL, objCon); //set test id to a string variable tmpArray = objRec.GetRows(); tstID = Convert.ToString(tmpArray[0, 0]); //close the recordset objRec.Close(); //SQL to execute strSQL = "SELECT COUNT(*) FROM step WHERE test_id = '" + tstID + "'"; //open recordset and get the number of stepsto execute with SQL objRec.Open(strSQL, objCon); //get the count value from the recordset and convert to an int fpor use in the step loop tmpArray = objRec.GetRows(); stpCount = Convert.ToInt32(tmpArray[0, 0]); //close the recordset objRec.Close(); //set up a for loop to run all steps in a test for (int stp = 0; stp < stpCount; stp++) { if (stp == 23) stp = 23; step = "Step " + Convert.ToString(stp + 1); //SQL to execute strSQL = "SELECT function_id, argument_set_id FROM step WHERE (test_id = '" + tstID + "') AND (number = " + (stp + 1) + ")"; //open recordset and get all the ids necessary for this step objRec.Open(strSQL, objCon); tmpArray = objRec.GetRows(); //set the function id functionID = Convert.ToString(tmpArray[0, 0]); //set the hash string for the argument set argID = Convert.ToString(tmpArray[1, 0]); //close the recordset objRec.Close(); //get the function name strSQL = "SELECT function_name FROM function WHERE id = '" + functionID + "'"; objRec.Open(strSQL, objCon); tmpArray = objRec.GetRows(); //set the function name variable nmFunc = Convert.ToString(tmpArray[0, 0]); //close the recordset objRec.Close(); // get the number of argments to be set to the application strSQL = "SELECT COUNT(*) FROM argument WHERE argument_set_id = '" + argID + "'"; objRec.Open(strSQL, objCon); tmpArray = objRec.GetRows(); //set the itmCount varable itmCount = Convert.ToInt32(tmpArray[0, 0]); //close the recordset objRec.Close(); if (itmCount > 0) { //set the array size for the inArray parameter of the drive function dbArray = new string[itmCount]; //set the array with blank values for (int x = 0; x < itmCount; x++) dbArray[x] = String.Empty; //get the argument data from the database and populate into the dbArray strSQL = "SELECT value FROM argument WHERE argument_set_id = '" + argID + "' ORDER BY seq ASC"; objRec.Open(strSQL, objCon); tmpArray = objRec.GetRows(); //close the recordset objRec.Close(); for (int stpData = 0; stpData < tmpArray.Length; stpData++) { if (stpData != tmpArray.Length) dbArray[stpData] = Convert.ToString(tmpArray[0, stpData]); } } else { dbArray = new string[1]; dbArray[0] = ""; } //call driveFunction to execute the step noted in dataArray[tstSuite. 0] TextFileOps.Write(pth, "<li>", 100); //initialize anm array to hold all of the test results that wiill be written to the results file tstResult = new string[1, 9]; //pass the appropriate vars to the drivefunction tstObj.driveFunction(tstObj, nmFunc, step, dbArray, baseURL, datSource, pth, ref getNeg, ref tstResult, out fndExcep, out tstFail); for (int x = 0; x < tstResult.GetLength(0); x++) { TextFileOps.Write(pth, "<div>", 100); if (fndExcep != -1) { if (tstResult[x, 0] == "verify") { switch (tstResult[x, 1].Trim()) { case "button": Recorder.btnVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], pth, getNeg); break; case "dropdown": Recorder.dropdownListVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 5], tstResult[x, 3], pth, getNeg); break; case "field": Recorder.fldVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "grades": Recorder.gradeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth); break; case "image": Recorder.imgVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "link": Recorder.lnkVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "table": Recorder.tblVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth, getNeg); break; case "text": case "field text": Recorder.txtVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "outcome": Recorder.outcomeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth); break; case "percentage": Recorder.pctVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth); break; } } else { TextFileOps.Write(pth, tstResult[x, 1].ToString(), Convert.ToInt32(tstResult[x, 2])); } } else { TextFileOps.Write(pth, "<div>", 100); TextFileOps.Write(pth, "Exception Found.............(" + step + ")", 80); TextFileOps.Write(pth, "<br />", 100); TextFileOps.Write(pth, tstResult[x, 1].ToString(), -1); } TextFileOps.Write(pth, "</div>", 100); } tstResult = null; TextFileOps.Write(pth, "<br />", 100); TextFileOps.Write(pth, "</li>", 100); if (tstFail == -1) fnlFail = -1; if (fndExcep == -1) { break; } } break; } case "EX": { //Initialize Firefox and construct the dataArray //the out parameter is the maxColumns in what will be the fnlArray and hence the width of each item in the array xlPath = TestSuite.getXlPath(steName); lstObject = new TestSuite(); lstObject.getTestListing(out lstTest, xlPath); dataArray = tstObj.xlFunctions(tstObj, xlPath, tstName, "Master", out maxCols); //set eleNum (number of elements) to 0 for (int runStep = 0; runStep < dataArray.GetLength(0); runStep++) { eleNum = 0; for (int stpNum = 3; stpNum < dataArray.GetLength(1); stpNum++) { //Get the number of elements in this step data from dataArray if (dataArray[runStep, stpNum] != null) { eleNum++; //increment elenum in the presence of a value } //in dataArray[tstSuite, stpNum] else { break; } } //initialize and set outArray with all data values in the dataArray line item if (eleNum != 0) { outArray = new string[eleNum]; //set outArray to eleNum //set the outarray with all data values or passing to driveFunction for (int setArray = 0; setArray < eleNum; setArray++) { outArray[setArray] = dataArray[runStep, setArray + 3]; } } else { outArray = new string[1]; outArray[0] = ""; } nmFunc = dataArray[runStep, 0]; step = dataArray[runStep, 2]; dbArray = outArray; //call driveFunction to execute the step noted in dataArray[tstSuite. 0] TextFileOps.Write(pth, "<li>", 100); //initialize anm array to hold all of the test results that wiill be written to the results file tstResult = new string[1, 9]; //pass the appropriate vars to the drivefunction tstObj.driveFunction(tstObj, nmFunc, step, dbArray, baseURL, datSource, pth, ref getNeg, ref tstResult, out fndExcep, out tstFail); for (int x = 0; x < tstResult.GetLength(0); x++) { TextFileOps.Write(pth, "<div>", 100); if (fndExcep != -1) { if (tstResult[x, 0] == "verify") switch (tstResult[x, 1].Trim()) { case "button": Recorder.btnVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], pth, getNeg); break; case "dropdown": Recorder.dropdownListVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 5], tstResult[x, 3], pth, getNeg); break; case "field": Recorder.fldVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "grades": Recorder.gradeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth); break; case "image": Recorder.imgVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "link": Recorder.lnkVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "table": Recorder.tblVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth, getNeg); break; case "text": case "field text": Recorder.txtVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 5], pth, getNeg); break; case "outcome": Recorder.outcomeVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], tstResult[x, 6], tstResult[x, 7], tstResult[x, 8], pth); break; case "percentage": Recorder.pctVerify(Convert.ToBoolean(tstResult[x, 2]), tstResult[x, 3], tstResult[x, 4], tstResult[x, 5], pth); break; case "weight": Recorder.weightWrite(tstResult[x, 2], tstResult[x, 3], pth); break; } else { TextFileOps.Write(pth, tstResult[x, 1].ToString(), Convert.ToInt32(tstResult[x, 2])); } } else { TextFileOps.Write(pth, "<div>", 100); TextFileOps.Write(pth, "Exception Found.............(" + step + ")", 80); TextFileOps.Write(pth, tstResult[x, 1].ToString(), -1); } TextFileOps.Write(pth, "</div>", 100); } vfyFunc = 0; tstResult = null; TextFileOps.Write(pth, "<br />", 100); //call driveFunction to execute the step noted in dataArray[tstSuite. 0] TextFileOps.Write(pth, "</li>", 100); if (tstFail == -1) fnlFail = -1; if (fndExcep == -1) { break; } } break; } } TextFileOps.Write(pth, "</ul>", 100); switch (radnum) { case 1: { TextFileOps.Write(pth, "<li>", 80); TextFileOps.Write(pth, "Closing Chrome..........\r\n", 80); TextFileOps.Write(pth, "</li>", 80); break; } case 2: TextFileOps.Write(pth, "<li>", 80); TextFileOps.Write(pth, "Closing Internet Explorer..........\r\n", 80); TextFileOps.Write(pth, "</li>", 80); break; { } default: //close Firefox TextFileOps.Write(pth, "<li>", 80); TextFileOps.Write(pth, "Closing Firefox..........\r\n", 80); TextFileOps.Write(pth, "</li>", 80); break; } //exit chromedriver.exe if Chrome is the browser being used if niot here by an exception if (fndExcep != -1) { if (radnum == 1 || radnum == 2) tstObj.Quit(); else tstObj.Close(); } //put a couple newlines in the result file to seperate results for (int a = 0; a < 1; a++) TextFileOps.Write(pth, "\r\n", 0); }
public void ExportToCSV(bool PrintHeader = true) { string ExportFilePath = null; ADODB.Recordset rs = default(ADODB.Recordset); int i = 0; int TotalRecords = 0; bool ErrorOccured = false; short NumberOfFields = 0; const string quote = "\""; //Faster then Chr$(34) string sql = null; Scripting.FileSystemObject fso = new Scripting.FileSystemObject(); cmdStart.Enabled = false; cmdExit.Enabled = false; txtPassword.Enabled = false; string ptbl = null; string t_day = null; string t_Mon = null; if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Day(DateAndTime.Today)))) == 1) t_day = "0" + Strings.Trim(Convert.ToString(DateAndTime.Day(DateAndTime.Today))); else t_day = Convert.ToString(DateAndTime.Day(DateAndTime.Today)); if (Strings.Len(Strings.Trim(Conversion.Str(DateAndTime.Month(DateAndTime.Today)))) == 1) t_Mon = "0" + Strings.Trim(Convert.ToString(DateAndTime.Month(DateAndTime.Today))); else t_Mon = Conversion.Str(DateAndTime.Month(DateAndTime.Today)); ExportFilePath = modRecordSet.serverPath + "4POSDebtor" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day); if (fso.FileExists(ExportFilePath + ".csv")) fso.DeleteFile((ExportFilePath + ".csv")); rs = modRecordSet.getRS(ref "SELECT CustomerID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Current as CurrentBalance,Customer_30Days as 30Days, Customer_60Days as 60days, Customer_90Days as 90Days, Customer_120Days as 120Days,Customer_150Days as 150Days FROM Customer"); prgBar.Maximum = rs.RecordCount; if (rs.RecordCount > 0) { FileSystem.FileOpen(1, ExportFilePath + ".csv", OpenMode.Output); var _with2 = modRecordSet.getRS(ref ref "SELECT CustomerID, Customer_InvoiceName, Customer_DepartmentName, Customer_FirstName, Customer_Surname, Customer_PhysicalAddress, Customer_PostalAddress, Customer_Telephone, Customer_Current as CurrentBalance,Customer_30Days as 30Days, Customer_60Days as 60days, Customer_90Days as 90Days, Customer_120Days as 120Days,Customer_150Days as 150Days FROM Customer"); rs.MoveFirst(); NumberOfFields = rs.Fields.Count - 1; if (PrintHeader) { //Now add the field names for (i = 0; i <= NumberOfFields - 1; i++) { FileSystem.Print(1, rs.Fields(i).name + ","); //similar to the ones below } FileSystem.PrintLine(1, rs.Fields(NumberOfFields).name); } while (!rs.EOF) { prgBar.Value = prgBar.Value + 1; // ERROR: Not supported in C#: OnErrorStatement TotalRecords = TotalRecords + 1; //If there is an emty field, for (i = 0; i <= NumberOfFields; i++) { //add a , to indicate it is if ((Information.IsDBNull(rs.Fields(i).Value))) { FileSystem.Print(1, ","); //empty } else { if (i == NumberOfFields) { FileSystem.Print(1, quote + Strings.Trim(Convert.ToString(rs.Fields(i).Value)) + quote); } else { FileSystem.Print(1, quote + Strings.Trim(Convert.ToString(rs.Fields(i).Value)) + quote + ","); } } //Putting data under "" will not } //confuse the reader of the file DoEventsEx(); //between Dhaka, Bangladesh as two FileSystem.PrintLine(1); //fields or as one field. rs.moveNext(); } FileSystem.FileClose(1); Interaction.MsgBox("Customer details were successfully exported to : " + FilePath + "" + "4POSProd" + Strings.Trim(Convert.ToString(DateAndTime.Year(DateAndTime.Today))) + Strings.Trim(t_Mon) + Strings.Trim(t_day) + ".csv", MsgBoxStyle.OkOnly, "Customers"); // DoEvents // DoEvents // MsgBox "Now Zeroising...", vbOKOnly, "Customers" // cmdStart.Enabled = False // cmdExit.Enabled = False // Set rsZ = getRS("SELECT CustomerID FROM Customer") // Do While Not rsZ.EOF // DoEvents // cmdProcess_Click (rsZ("CustomerID")) // DoEvents // rsZ.moveNext // Loop //MsgBox "Completed", vbOKOnly, "Customers" //cmdStart.Enabled = True //cmdExit.Enabled = True this.Close(); } System.Windows.Forms.Cursor.Current = Cursors.Default; rs.Close(); //cnnDB.Close //Set cnnDB = Nothing //closeConnection }
private void cmdPrintHistory_Click(System.Object eventSender, System.EventArgs eventArgs) { int i = 0; int x = 0; string sql = null; string databaseName = null; short y = 0; short lMonth = 0; ADODB.Connection cn = default(ADODB.Connection); ADODB.Recordset rs = new ADODB.Recordset(); bool bReset = false; // ERROR: Not supported in C#: OnErrorStatement //If cmdShowHistory.Caption = "Show Full History " Then // cmdShowHistory.Caption = "Show Current Month" //ElseIf cmdShowHistory.Caption = "Show Full History" Then // cmdShowHistory.Caption = "Show Current Month" //Else // cmdShowHistory.Caption = "Show Full History " // cmdsearch_Click // Exit Sub //End If if (gLoading) return; gLoading = true; Cursor = System.Windows.Forms.Cursors.WaitCursor; System.Windows.Forms.Application.DoEvents(); y = cmbMonth.Items.Count - 1; //lvTransaction.ListItems.Clear //lblcount.Caption = "0 of 0" //lvTransaction.Visible = False sql = "DELETE tempCustomerHistory.* FROM tempCustomerHistory;"; modRecordSet.cnnDB.Execute(sql); string lPosString = null; //(cmbMonth.ListCount - 1) for (i = 0; i <= y; i++) { lMonth = Convert.ToInt32(cmbMonth.SelectedItem(i)); if (lMonth == gMonthEnd) { databaseName = "pricing.mdb"; } else { databaseName = "Month" + lMonth + ".mdb"; } cn = modRecordSet.openConnectionInstance(ref databaseName); if (cn == null) { goto nextMonth; //Exit Sub } //Dim lString As String //Dim lCustomerString As String //Dim lStockString As String if (this.cmbPOS.SelectedIndex) lPosString = " AND (Sale_PosID=" + cmbPOS.SelectedIndex + ")"; sql = "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ") AND (CustomerTransaction.CustomerTransaction_MonthEndID=(" + lMonth + "))) ORDER BY CustomerTransaction.CustomerTransactionID DESC;"; sql = "SELECT * FROM CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ") AND (CustomerTransaction.CustomerTransaction_MonthEndID=(" + lMonth + "))) ORDER BY CustomerTransaction.CustomerTransactionID DESC;"; Debug.Print(sql); rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //Dim lvItem As listItem x = 0; //lvTransaction.Visible = False while (!(rs.EOF)) { x = x + 1; if (gLoading) { } else { break; // TODO: might not be correct. Was : Exit Do } bResetError: //lblcount.Caption = x & " of " & rs.RecordCount System.Windows.Forms.Application.DoEvents(); if (bReset) { bReset = false; // ERROR: Not supported in C#: OnErrorStatement sql = "INSERT INTO tempCustomerHistory ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done ) "; sql = sql + "SELECT " + rs.Fields("CustomerTransactionID").Value + ", " + rs.Fields("CustomerTransaction_CustomerID").Value + ", " + rs.Fields("CustomerTransaction_TransactionTypeID").Value + ", " + rs.Fields("CustomerTransaction_DayEndID").Value + ", " + rs.Fields("CustomerTransaction_MonthEndID").Value + ", " + rs.Fields("CustomerTransaction_ReferenceID").Value + ", #" + rs.Fields("CustomerTransaction_Date").Value + "#, '" + rs.Fields("CustomerTransaction_Description").Value + "', " + rs.Fields("CustomerTransaction_Amount").Value + ", '" + rs.Fields("CustomerTransaction_Reference").Value + "', '" + rs.Fields("CustomerTransaction_PersonName").Value + "', " + rs.Fields("CustomerTransaction_Done").Value + ";"; modRecordSet.cnnDB.Execute(sql); } else { sql = "INSERT INTO tempCustomerHistory ( CustomerTransactionID, CustomerTransaction_CustomerID, CustomerTransaction_TransactionTypeID, CustomerTransaction_DayEndID, CustomerTransaction_MonthEndID, CustomerTransaction_ReferenceID, CustomerTransaction_Date, CustomerTransaction_Description, CustomerTransaction_Amount, CustomerTransaction_Reference, CustomerTransaction_PersonName, CustomerTransaction_Done, CustomerTransaction_Main, CustomerTransaction_Child, CustomerTransaction_Allocated ) "; sql = sql + "SELECT " + rs.Fields("CustomerTransactionID").Value + ", " + rs.Fields("CustomerTransaction_CustomerID").Value + ", " + rs.Fields("CustomerTransaction_TransactionTypeID").Value + ", " + rs.Fields("CustomerTransaction_DayEndID").Value + ", " + rs.Fields("CustomerTransaction_MonthEndID").Value + ", " + rs.Fields("CustomerTransaction_ReferenceID").Value + ", #" + rs.Fields("CustomerTransaction_Date").Value + "#, '" + rs.Fields("CustomerTransaction_Description").Value + "', " + rs.Fields("CustomerTransaction_Amount").Value + ", '" + rs.Fields("CustomerTransaction_Reference").Value + "', '" + rs.Fields("CustomerTransaction_PersonName").Value + "', " + rs.Fields("CustomerTransaction_Done").Value + ", " + rs.Fields("CustomerTransaction_Main").Value + ", " + rs.Fields("CustomerTransaction_Child").Value + ", " + rs.Fields("CustomerTransaction_Allocated").Value + ";"; modRecordSet.cnnDB.Execute(sql); } //If rs("CustomerTransaction_Reference") <> "Month End" Then // Set lvItem = lvTransaction.ListItems.Add(, "K" & rs("CustomerTransactionID") & "_" & databaseName & "_" & rs("CustomerTransaction_ReferenceID") & "_" & rs("CustomerTransaction_TransactionTypeID"), Format(rs("CustomerTransaction_Date"), "yyyy mmm dd hh:mm")) // lvItem.SubItems(1) = rs("CustomerTransaction_Reference") // lvItem.SubItems(2) = rs("TransactionType_Name") // lvItem.SubItems(3) = FormatNumber(rs("debit"), 2) // lvItem.SubItems(4) = FormatNumber(rs("credit"), 2) //End If rs.MoveNext(); } //lvTransaction.Visible = True rs.Close(); nextMonth: } //lvTransaction.Visible = True report_CustomerStatementFullHistory(ref adoPrimaryRS.Fields("CustomerID").Value); Cursor = System.Windows.Forms.Cursors.Default; gLoading = false; return; ErrShowHistory: if (Strings.InStr(Strings.LCase(Err().Description), "not a valid path")) { Interaction.MsgBox(Err().Number + " - " + Err().Description); return; } else if (Err().Number == Convert.ToDouble("3265")) { bReset = true; //Resume bResetError } else { Interaction.MsgBox(Err().Number + " - " + Err().Description); return; // ERROR: Not supported in C#: ResumeStatement } }
public void report_CustomerStatementFullHistory(ref int id) { ADODB.Recordset rsInterest = default(ADODB.Recordset); ADODB.Recordset rsTransaction = default(ADODB.Recordset); ADODB.Recordset rsCompany = default(ADODB.Recordset); string lNumber = null; string lAddress = null; ADODB.Recordset rs = new ADODB.Recordset(); string sql = null; //Dim Report As New cryCustomerStatementFull CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); System.DateTime lDate = default(System.DateTime); short gMonth = 0; ADODB.Connection cnnDBStatement = new ADODB.Connection(); Report.Load("cryCustomerStatementFull.rpt"); var _with1 = cnnDBStatement; _with1.Provider = "MSDataShape"; cnnDBStatement.Open("Data Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + modRecordSet.serverPath + "pricing.mdb" + ";User Id=liquid;Password=lqd;Jet OLEDB:System Database=" + modRecordSet.serverPath + "Secured.mdw"); rs = modRecordSet.getRS(ref "SELECT Company_MonthendID FROM Company;"); if (rs.Fields("Company_MonthendID").Value <= 1) { gMonth = 1; } else { gMonth = rs.Fields("Company_MonthendID").Value; //- 1 } rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));"); //rs.Open "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" & gMonth & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText if (Information.IsDBNull(rs.Fields("MonthEnd_Date").Value) == true | rs.RecordCount == 0) { gMonth = 1; rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));"); } Report.SetParameterValue("txtStatementDate", Strings.Format(DateAndTime.Today, "dd mmm yyyy")); if (rs.RecordCount) { //Report.txtStatementDate.SetText Format(rs("MonthEnd_Date"), "dd mmm yyyy") lDate = rs.Fields("MonthEnd_Date").Value; } else { } rs.Close(); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); lDate = System.Date.FromOADate(lDate.ToOADate() + 10); lDate = DateAndTime.DateSerial(DateAndTime.Year(lDate), DateAndTime.Month(lDate), 1); lDate = System.Date.FromOADate(lDate + rs.Fields("Company_PaymentDay").Value - 1); //Report.txtPaymentDate.SetText Format(lDate, "dd mmm yyyy") lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", "); if (Strings.Right(lAddress, 2) == ", ") { lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2); } Report.Database.Tables(1).SetDataSource(rs); Report.SetParameterValue("txtAddress", lAddress); lNumber = ""; if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value)) lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value; if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value)) { if (!string.IsNullOrEmpty(lNumber)) lNumber = lNumber + " / "; lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value)) { if (!string.IsNullOrEmpty(lNumber)) lNumber = lNumber + " / "; lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value; } Report.SetParameterValue("txtNumbers", lNumber); //New banking details if (Information.IsDBNull(rs.Fields("Company_BankName").Value)) { } else { Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName")); } if (Information.IsDBNull(rs.Fields("Company_BranchName").Value)) { } else { Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName")); } if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value)) { } else { Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode")); } if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value)) { } else { Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber")); } //................... rsCompany = new ADODB.Recordset(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(2).SetDataSource(rsCompany); rsTransaction = new ADODB.Recordset(); //changed for OPEN ITEM //rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _ //'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsTransaction.Open("SELECT tempCustomerHistory.CustomerTransactionID, tempCustomerHistory.CustomerTransaction_CustomerID, tempCustomerHistory.CustomerTransaction_TransactionTypeID, tempCustomerHistory.CustomerTransaction_DayEndID, tempCustomerHistory.CustomerTransaction_MonthEndID, tempCustomerHistory.CustomerTransaction_ReferenceID, tempCustomerHistory.CustomerTransaction_Date, tempCustomerHistory.CustomerTransaction_Description, tempCustomerHistory.CustomerTransaction_Amount-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]), tempCustomerHistory.CustomerTransaction_Reference & IIf([tempCustomerHistory.CustomerTransaction_Allocated]<>[CustomerTransaction_Amount] AND [tempCustomerHistory.CustomerTransaction_Allocated]<>0,' (P)',Null), tempCustomerHistory.CustomerTransaction_PersonName, TransactionType.TransactionType_Name," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]))>0,([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated])),Null) AS debit," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]))<0,([CustomerTransaction_Amount]-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated])),Null) AS credit, tempCustomerHistory.CustomerTransaction_Main, tempCustomerHistory.CustomerTransaction_Child, tempCustomerHistory.CustomerTransaction_Allocated FROM tempCustomerHistory INNER JOIN TransactionType ON tempCustomerHistory.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((tempCustomerHistory.CustomerTransaction_CustomerID)=" + id + ") AND ((tempCustomerHistory.CustomerTransaction_Amount-IIf(IsNull([tempCustomerHistory.CustomerTransaction_Allocated]),0,[tempCustomerHistory.CustomerTransaction_Allocated]))<>0)) ORDER BY tempCustomerHistory.CustomerTransaction_Date;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rsTransaction.BOF | rsTransaction.EOF) { rsTransaction = new ADODB.Recordset(); rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(3).SetDataSource(rsTransaction); //Exit Sub } else { Report.Database.Tables(3).SetDataSource(rsTransaction); } //rs.Close rsInterest = new ADODB.Recordset(); rsInterest.Open("SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //If rsInterest.BOF Or rsInterest.EOF Then if (rsInterest.RecordCount > 0) { //Report.Field20.Top = 280 //Report.Field21.Top = 280 //Report.Field22.Top = 280 //Report.Field23.Top = 280 Report.Database.Tables(4).SetDataSource(rsInterest); } else { rsInterest = new ADODB.Recordset(); rsInterest.Open("SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //Report.Field20.Suppress = True //Report.Field21.Suppress = True //Report.Field22.Suppress = True //Report.Field23.Suppress = True Report.Database.Tables(4).SetDataSource(rsInterest); //Exit Sub //Set rsInterest = New Recordset //rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText } // rsInterest.Open ("SELECT Interest.*, Interest.Description,WHERE (((Interest.CustomerID)=" & id & "));"), cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText //Dim rsinte As String // rsinte = rsInterest("Description") //If rsInterest.RecordCount > 0 Then // Report.Database.Tables(4).SetDataSource rsInterest, 3 //Else // Set rsInterest = New Recordset // rsInterest.Open "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText // Report.Database.Tables(4).SetDataSource rsInterest, 3 //End If //Set rsTrans = New Recordset //Dim NewDateC As String //NewDateC = Format(Now) //rsTrans.Open "" //Report.Database.Tables(4).SetDataSource rsTrans, 3 if (rsTransaction.BOF | rsTransaction.EOF) { return; //rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _ //'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText } if (rs.Fields("Company_DebtorPrintBal").Value == true) { CrystalDecisions.CrystalReports.Engine.TextObject textObject = Report.ReportDefinition.ReportObjects("Text5"); textObject.Color = Color.White; } //Report.PrintOut False, 1 //Screen.MousePointer = vbDefault My.MyProject.Forms.frmReportShow.Text = "Customer Statement"; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "1"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); }
private void LoadExtraDataForView() { try { if(this._array.Count > 0) { ADODB.Connection cnn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); ADOX.Catalog cat = new ADOX.Catalog(); // Open the Connection cnn.Open(dbRoot.ConnectionString, null, null, 0); cat.ActiveConnection = cnn; rs.Source = cat.Views[this.View.Name].Command; rs.Fields.Refresh(); ADODB.Fields flds = rs.Fields; Column col = this._array[0] as Column; f_TypeName = new DataColumn("TYPE_NAME", typeof(string)); col._row.Table.Columns.Add(f_TypeName); f_AutoKey = new DataColumn("AUTO_INCREMENT", typeof(Boolean)); col._row.Table.Columns.Add(f_AutoKey); Column c = null; ADODB.Field fld = null; int count = this._array.Count; for( int index = 0; index < count; index++) { fld = flds[index]; c = (Column)this[fld.Name]; c._row["TYPE_NAME"] = fld.Type.ToString(); c._row["AUTO_INCREMENT"] = false; } rs.Close(); cnn.Close(); } } catch {} }
public void CopyTable(ADOX.Table tblAccess) { ADODB.Recordset recMaster = new ADODB.Recordset(); ADODB.Recordset recLoop = new ADODB.Recordset(); int intLoop = 0; string strInfile = ""; string strSQL = "SELECT "; string strRecord; string strLoadFilePath = strSourceDbPath.Replace("\\", "\\\\"); string strFileName = strTempPath + tblAccess.Name + ".txt"; StreamWriter sw = new StreamWriter(strFileName, false); //create the infile strInfile += "LOAD DATA LOCAL INFILE '" + strFileName + "' INTO TABLE " + strMySQLDBName + "." + tblAccess.Name + " "; strInfile += "FIELDS TERMINATED BY ',' "; strInfile += "ESCAPED BY '\\\\' "; strInfile += "LINES TERMINATED BY 0x0d0a "; strInfile += "("; //loop through fields to enumerate them for the infile and build a select statement for (intLoop = 0; intLoop < tblAccess.Columns.Count; intLoop++) { strInfile += MySQLName((tblAccess.Columns[intLoop].Name)); switch (tblAccess.Columns[intLoop].Type) { case ADOX.DataTypeEnum.adDate: //convert to MySQL datetime format strSQL += "FORMAT([" + tblAccess.Columns[intLoop].Name + "], 'YYYY-MM-DD HH:MM:SS') as " + tblAccess.Columns[intLoop].Name; break; default: strSQL += "[" + tblAccess.Columns[intLoop].Name + "]"; break; } if (intLoop < tblAccess.Columns.Count - 1) { strSQL += ","; strInfile += ", "; } } strInfile += ");"; strSQL += " FROM [" + tblAccess.Name + "]"; //open the "Master" recordset recMaster.CursorLocation = ADODB.CursorLocationEnum.adUseClient; recMaster.Open(strSQL, conJCMS_db, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); //create the "Loop" recordset, this is a clone of the master, with the exception //that the definedsize for text fields is lengthened. This is because the added //escape characters could potentially exceed the field length in the master recordset recLoop.CursorLocation = ADODB.CursorLocationEnum.adUseClient; ADODB.Fields fdsLoop = recLoop.Fields; ADODB.Fields fdsMaster = recMaster.Fields; foreach (ADODB.Field fldIn in fdsMaster) { if (fldIn.Type.ToString().IndexOf("Char") > 0) { fdsLoop.Append(fldIn.Name, fldIn.Type, fldIn.DefinedSize + 30, ADODB.FieldAttributeEnum.adFldIsNullable, null); } else { fdsLoop.Append(fldIn.Name, fldIn.Type, fldIn.DefinedSize, ADODB.FieldAttributeEnum.adFldIsNullable, null); } } recLoop.Open(System.Reflection.Missing.Value, System.Reflection.Missing.Value, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, 0); recLoop.AddNew(System.Reflection.Missing.Value, System.Reflection.Missing.Value); while (!recMaster.EOF) { for (int columnIndex = 0; columnIndex < recMaster.Fields.Count; columnIndex++) { recLoop.Fields[columnIndex].Value = recMaster.Fields[columnIndex].Value; if (recLoop.Fields[columnIndex].Value.ToString().Length > 0) { if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf("\\", 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace("\\", "\\\\"); } if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(",", 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(",", "\\,"); } if ((recLoop.Fields[columnIndex].Value.ToString().IndexOf(System.Environment.NewLine, 0) + 1) > 0) { recLoop.Fields[columnIndex].Value = recLoop.Fields[columnIndex].Value.ToString().Replace(System.Environment.NewLine, " "); } } } strRecord = recLoop.GetString(ADODB.StringFormatEnum.adClipString, 1, ",", System.Environment.NewLine, "\\N"); recLoop.MovePrevious(); sw.Write(strRecord); recMaster.MoveNext(); } recMaster.Close(); recMaster.ActiveConnection = null; try { recLoop.Close(); } catch { } sw.Close(); ExecuteSQL(strInfile); File.Delete(strFileName); recLoop = null; }
public void TestRefreshDataTableWithADODBRecordSet_Schema() { OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; DataSet stuDS = new DataSet(); DataTable stuTable = new DataTable("student"); stuDS.Tables.Add(stuTable); //Use ADO objects from ADO library (msado15.dll) imported // as.NET library ADODB.dll using TlbImp.exe ADODB.Connection adoConn = new ADODB.Connection(); ADODB.Recordset adoRS = new ADODB.Recordset(); adoConn.Open("Provider=CUBRIDProvider;Location=test-db-server;Data Source=demodb;User Id=dba;Port=30000", "", "", -1); adoRS.Open("SELECT * FROM student", adoConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1); adapter.Fill(stuTable, adoRS); adoRS.Requery(0); int refreshRowCount = adapter.Fill(stuTable, adoRS); // This method does not call Close on the ADO object when the fill operation is complete. adoRS.Close(); adoConn.Close(); Assert.IsNotNull(stuTable.PrimaryKey); Assert.AreEqual(3, refreshRowCount); }
// Output data to xml public static Boolean bExportDataToXML(String sDatabaseLocation, Boolean bDebug) { clsLog clsLog_ = new clsLog(msLogFile, msVersionData); clsError clsError_ = new clsError(); ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); clsLog_.mLog(Constants.gcInfo, "Exporting data to xml ..."); String sOneLanPropertyfile = sDatabaseLocation + "\\xml\\OneLanProperty.xml"; String sOneLanAccomfile = sDatabaseLocation + "\\xml\\OneLanAccom.xml"; if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bExportDataToXML, sOneLanPropertyfile = " + sOneLanPropertyfile); } if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bExportDataToXML, sOneLanAccomfile = " + sOneLanAccomfile); } try { // Open connection to database conn.Open(gcDSN + sDatabaseLocation + "\\" + gsOneLanDatabase); // Remove previous versions of the files if (File.Exists(sOneLanPropertyfile)) {File.Delete(sOneLanPropertyfile);} if (File.Exists(sOneLanAccomfile)) {File.Delete(sOneLanAccomfile);} // Open the recordset rs.Open("SELECT * FROM Property", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1); rs.Save(sOneLanPropertyfile, ADODB.PersistFormatEnum.adPersistXML); rs.Close(); rs.Open("SELECT * FROM Accom", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1); rs.Save(sOneLanAccomfile, ADODB.PersistFormatEnum.adPersistXML); rs.Close(); conn.Close(); return true; } catch (Exception e) { clsError_.mLogError("Problem exporting data to xml", "onelan", "bExportDataToXML", e, msVersionData, msLogFile, false); return false; } }
private void cmdInv_Click(System.Object eventSender, System.EventArgs eventArgs) { CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); int personID = 0; int posID = 0; string[] lArray = null; string lAddress = null; //On Error Resume Next ADODB.Connection cn = default(ADODB.Connection); short x = 0; string databaseName = null; int lID = 0; CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); ADODB.Recordset rs = default(ADODB.Recordset); ADODB.Recordset rsItems = default(ADODB.Recordset); string sql = null; //UPGRADE_WARNING: Screen property Screen.MousePointer has a new behavior. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6BA9B8D2-2A32-4B6E-8D36-44949974A5B4"' System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; if (lvTransaction.FocusedItem == null) return; //lID = Mid(Split(Me.lvTransaction.SelectedItem.Key, "_")(0), 2) if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[2]) == 0) { Interaction.MsgBox("There is no Sale document attached!", MsgBoxStyle.Information); System.Windows.Forms.Cursor.Current = Cursors.Default; return; } lID = Convert.ToInt32(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[2]); //If openConnection() Then // frmMain.lblPath.Caption = serverPath // closeConnection //End If //Set cn = openConnectionInstance() //lMonth = cmbMonth.ItemData(cmbMonth.ListIndex) //Dim lLineitem As lineitem //If lMonth = gMonthEnd Then // databaseName = "" //Else // databaseName = "Month" & lMonth & ".mdb" //End If databaseName = Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[1]; cn = modRecordSet.openConnectionInstance(ref databaseName); if (cn == null) { return; } transaction lTransaction = new transaction(); lineItem lLineitem = null; customer lCustomer = new customer(); transactionSpecial lSpecial = new transactionSpecial(); if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[3]) == 2) { //sale //Dim Report As New cryReceipt //Set Report = New cryReceipt rs = new ADODB.Recordset(); sql = "SELECT Sale.* From Sale WHERE (((SaleID)=" + lID + "));"; rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rs.RecordCount) { lTransaction.cashierID = rs.Fields("Sale_PersonID").Value; lTransaction.channelID = rs.Fields("Sale_ChannelID").Value; lTransaction.managerID = rs.Fields("Sale_ManagerID").Value; lTransaction.paymentDiscount = rs.Fields("Sale_Discount").Value; lTransaction.paymentSlip = rs.Fields("Sale_Slip").Value; lTransaction.paymentSubTotal = rs.Fields("Sale_SubTotal").Value; lTransaction.paymentTender = rs.Fields("Sale_Tender").Value; lTransaction.paymentTotal = rs.Fields("Sale_Total").Value; lTransaction.paymentType = rs.Fields("Sale_PaymentType").Value; lTransaction.posID = rs.Fields("Sale_POSID").Value; lTransaction.transactionDate = rs.Fields("Sale_DatePOS").Value; lTransaction.transactionID = rs.Fields("Sale_Reference").Value + ""; lTransaction.transactionType = "Sale"; //If prPrevSerial_p = True Then strSerial = rs("Sale_Serialref") rs.Close(); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); lTransaction.companyName = rs.Fields("Company_Name").Value; //gParameters.companyName lTransaction.footer = " "; //gParameters.footer & "" lTransaction.heading1 = rs.Fields("Company_PhysicalAddress").Value; //gParameters.heading1 & "" lTransaction.heading2 = " "; //gParameters.heading2 & "" lTransaction.heading3 = " "; //gParameters.heading3 & "" lTransaction.taxNumber = rs.Fields("Company_TaxNumber").Value; //gParameters.taxNumber & "" rs.Close(); sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + lTransaction.cashierID + "));"; rs = modRecordSet.getRS(ref sql); //rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText if (rs.RecordCount) { lTransaction.cashierName = rs.Fields("personName").Value + ""; } rs.Close(); sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + lTransaction.managerID + "));"; rs = modRecordSet.getRS(ref sql); //rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText if (rs.RecordCount) { lTransaction.managerName = rs.Fields("personName").Value + ""; } rs.Close(); sql = "SELECT POS_Name From POS WHERE (((POS.POSID)=" + lTransaction.posID + "));"; rs = modRecordSet.getRS(ref sql); //rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText if (rs.RecordCount) { lTransaction.posName = rs.Fields("POS_Name").Value + ""; } rs.Close(); //sql = "SELECT SaleItem.*, StockItem.StockItem_Name AS longName, StockItem.StockItem_ReceiptName AS receiptName, Catalogue.Catalogue_Barcode AS code,'Sale' as saleType FROM (StockItem INNER JOIN SaleItem ON StockItem.StockItemID = SaleItem.SaleItem_StockItemID) INNER JOIN Catalogue ON (Catalogue.Catalogue_Quantity = SaleItem.SaleItem_ShrinkQuantity) AND (SaleItem.SaleItem_StockItemID = Catalogue.Catalogue_StockItemID) WHERE (((SaleItem.SaleItem_DepositType)=0) AND ((SaleItem.SaleItem_SaleID)=" & lID & "))" //sql = sql & " UNION " //sql = sql & "SELECT SaleItem.*, Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code,'Deposit' as saleType FROM Deposit INNER JOIN SaleItem ON Deposit.DepositID = SaleItem.SaleItem_StockItemID WHERE (((SaleItem.SaleItem_DepositType)<>0) AND ((SaleItem.SaleItem_SaleID)=" & lID & "));" sql = "SELECT SaleItem.*, 'Sale' AS saleType From SaleItem Where (((SaleItem.SaleItem_DepositType) = 0) And ((SaleItem.SaleItem_SaleID) = " + lID + "))"; sql = sql + " UNION "; sql = sql + "SELECT SaleItem.*, 'Deposit' AS saleType From SaleItem WHERE (((SaleItem.SaleItem_DepositType)<>0) AND ((SaleItem.SaleItem_SaleID)=" + lID + "));"; rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); while (!(rs.EOF)) { lLineitem = new lineItem(); lLineitem.build = 0; lLineitem.depositType = rs.Fields("SaleItem_DepositType").Value; lLineitem.id = rs.Fields("SaleItem_StockItemID").Value; lLineitem.lineNo = rs.Fields("SaleItem_LineNo").Value; lLineitem.originalPrice = rs.Fields("SaleItem_PriceOriginal").Value; lLineitem.price = rs.Fields("SaleItem_Price").Value; lLineitem.quantity = rs.Fields("SaleItem_Quantity").Value; lLineitem.reversal = rs.Fields("SaleItem_Reversal").Value; lLineitem.revoke = rs.Fields("SaleItem_Revoke").Value; lLineitem.setBuild = rs.Fields("SaleItem_SetID").Value; lLineitem.shrink = rs.Fields("SaleItem_ShrinkQuantity").Value; lLineitem.vat = rs.Fields("SaleItem_Vat").Value; lLineitem.transactionType = rs.Fields("SaleType").Value + ""; //lLineitem.code = rs("Code") & "" switch (lLineitem.depositType) { case 0: sql = "SELECT StockItem.StockItem_Name AS longName, StockItem.StockItem_ReceiptName AS receiptName From StockItem WHERE (((StockItemID)=" + rs.Fields("SaleItem_StockItemID").Value + "));"; rsItems = modRecordSet.getRS(ref sql); if (rsItems.RecordCount) { lLineitem.name = rs.Fields("SaleItem_ShrinkQuantity").Value + " x " + rsItems.Fields("longName").Value; lLineitem.receiptName = rs.Fields("SaleItem_ShrinkQuantity").Value + "x" + rsItems.Fields("receiptName").Value; } rsItems.Close(); sql = "SELECT Catalogue.Catalogue_Barcode AS code FROM Catalogue WHERE (Catalogue.Catalogue_Quantity = " + rs.Fields("SaleItem_ShrinkQuantity").Value + ") AND (Catalogue.Catalogue_StockItemID = " + rs.Fields("SaleItem_StockItemID").Value + ");"; rsItems = modRecordSet.getRS(ref sql); if (rsItems.RecordCount) { lLineitem.code = rsItems.Fields("Code").Value + ""; } rsItems.Close(); break; case 1: sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));"; rsItems = modRecordSet.getRS(ref sql); if (rsItems.RecordCount) { lLineitem.name = rs.Fields("longName").Value + "-Unit"; lLineitem.receiptName = rs.Fields("receiptName").Value + "(U)"; lLineitem.code = rsItems.Fields("Code").Value + ""; } rsItems.Close(); break; case 2: sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));"; rsItems = modRecordSet.getRS(ref sql); if (rsItems.RecordCount) { lLineitem.name = rs.Fields("longName").Value + "-Empty Crate"; lLineitem.receiptName = rs.Fields("receiptName").Value + "(E)"; lLineitem.code = rsItems.Fields("Code").Value + ""; } rsItems.Close(); break; case 3: sql = "SELECT Deposit.Deposit_Name AS longName, Deposit.Deposit_ReceiptName AS receiptName, Deposit.Deposit_Key AS code From Deposit WHERE (((DepositID)=" + rs.Fields("SaleItem_StockItemID").Value + "));"; rsItems = modRecordSet.getRS(ref sql); if (rsItems.RecordCount) { lLineitem.name = rs.Fields("longName").Value + "-Full Case"; lLineitem.receiptName = rs.Fields("receiptName").Value + "(F)"; lLineitem.code = rsItems.Fields("Code").Value + ""; } rsItems.Close(); break; } lTransaction.lineItems.Add(ref lLineitem); rs.MoveNext(); } rs.Close(); sql = "SELECT Customer.*, CustomerTransaction.* FROM (CustomerTransaction INNER JOIN Sale ON CustomerTransaction.CustomerTransaction_ReferenceID = Sale.SaleID) INNER JOIN Customer ON CustomerTransaction.CustomerTransaction_CustomerID = Customer.CustomerID WHERE (((Sale.SaleID)=" + lID + "));"; rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rs.RecordCount) { lCustomer.channelID = lTransaction.channelID; lCustomer.creditLimit = rs.Fields("Customer_ChannelID").Value; lCustomer.department = rs.Fields("Customer_DepartmentName").Value + ""; lCustomer.fax = rs.Fields("Customer_Fax").Value + ""; lCustomer.Key = rs.Fields("CustomerID").Value; lCustomer.name = rs.Fields("Customer_InvoiceName").Value + ""; lCustomer.outstanding = 0; lCustomer.person = rs.Fields("Customer_FirstName").Value + " " + rs.Fields("Customer_Surname").Value; lCustomer.physical = rs.Fields("Customer_PhysicalAddress").Value + ""; lCustomer.postal = rs.Fields("Customer_PostalAddress").Value + ""; lCustomer.signed_Renamed = rs.Fields("CustomerTransaction_PersonName").Value; lCustomer.telephone = rs.Fields("Customer_Telephone").Value + ""; lCustomer.terms = Convert.ToInt16(rs.Fields("Customer_Terms").Value + ""); lCustomer.tax = rs.Fields("Customer_VatNumber").Value + ""; if (rs.Fields("CustomerTransaction_TransactionTypeID").Value == 3) { lTransaction.transactionType = "Payment"; lTransaction.paymentDiscount = 0; } lTransaction.customer_Renamed = lCustomer; } rs.Close(); sql = "SELECT Consignment.* FROM Consignment INNER JOIN Sale ON Consignment.Consignment_SaleID = Sale.SaleID WHERE (((Sale.SaleID)=" + lID + "));"; rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rs.BOF & rs.EOF) { } else { lSpecial.address = rs.Fields("Consignment_Address").Value; lSpecial.mobile = rs.Fields("Consignment_Mobile").Value; lSpecial.name = rs.Fields("Consignment_Name").Value; lSpecial.quote = 0; lSpecial.saleID = lID; lSpecial.telephone = rs.Fields("Consignment_Number").Value; lSpecial.transactionType = "Consignment"; lTransaction.transactionSpecial_Renamed = lSpecial; } rs.Close(); sql = "SELECT Consignment.* FROM Consignment INNER JOIN Sale ON Consignment.Consignment_CompleteSaleID = Sale.SaleID WHERE (((Sale.SaleID)=" + lID + "));"; rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rs.BOF & rs.EOF) { } else { lSpecial.address = rs.Fields("Consignment_Address").Value; lSpecial.mobile = rs.Fields("Consignment_Mobile").Value; lSpecial.name = rs.Fields("Consignment_Name").Value; lSpecial.quote = 0; lSpecial.saleID = lID; lSpecial.telephone = rs.Fields("Consignment_Number").Value; lSpecial.transactionType = "Consignment Complete"; lTransaction.transactionSpecial_Renamed = lSpecial; } printTransactionA4(ref lTransaction); } } else if (Convert.ToDouble(Strings.Split(this.lvTransaction.FocusedItem.Name, "_")[3]) == 3) { //payment //Dim Report As New cryPayment Report.Load("cryPayment.rpt"); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); if (Information.IsDBNull(rs.Fields("Company_PhysicalAddress").Value)) { } else { lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", "); if (Strings.Right(lAddress, 2) == ", ") { lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2); } } Report.SetParameterValue("txtCompanyDetails1", lAddress); if (Information.IsDBNull(rs.Fields("Company_Telephone").Value)) { } else { Report.SetParameterValue("txtCompanyDetails2", rs.Fields("Company_Telephone")); } if (Information.IsDBNull(rs.Fields("Company_TaxNumber").Value)) { } else { Report.SetParameterValue("txtCompanyDetails4", "TAX NO :" + rs.Fields("Company_TaxNumber").Value); } //If lTransaction.heading2 <> "" Then // Report.txtCompanyDetails2.SetText lTransaction.heading2 // If lTransaction.heading3 <> "" Then // Report.txtCompanyDetails3.SetText lTransaction.heading3 // // Else // If lTransaction.taxNumber <> "" Then // Report.txtCompanyDetails4.SetText "TAX NO :" & lTransaction.taxNumber // End If // End If //Else // If lTransaction.heading3 <> "" Then // Report.txtCompanyDetails2.SetText lTransaction.heading3 // Else // If lTransaction.taxNumber <> "" Then // Report.txtCompanyDetails2.SetText "TAX NO :" & lTransaction.taxNumber // End If // End If //End If rs.Close(); Report.SetParameterValue("txtCustomer", _txtFields_2.Text); if (!string.IsNullOrEmpty(_txtFields_6.Text)) { lArray = Strings.Split(_txtFields_6.Text, Constants.vbCrLf); Report.SetParameterValue("txtCustAddress1", lArray[0]); if (Information.UBound(lArray) >= 1) Report.SetParameterValue("txtCustAddress2", lArray[1]); if (Information.UBound(lArray) >= 2) Report.SetParameterValue("txtCustAddress3", lArray[2]); if (Information.UBound(lArray) >= 3) Report.SetParameterValue("txtCustAddress4", lArray[3]); } if (!string.IsNullOrEmpty(_txtFields_0.Text)) Report.SetParameterValue("txtCustAddress5", "TAX NO: " + _txtFields_0.Text); //If LCase(databaseName) = "pricing.mdb" Then // sql = "SELECT Sale.SaleID, POS.POS_Name, Person.Person_FirstName, Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN Person ON Sale.Sale_PersonID = Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));" //Else // sql = "SELECT Sale.SaleID, POS.POS_Name, M_Person.Person_FirstName, M_Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN M_Person ON Sale.Sale_PersonID = M_Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));" //End If //sql = "SELECT Sale.SaleID, POS.POS_Name, Person.Person_FirstName, Person.Person_LastName, Sale.Sale_Date, Sale.Sale_DatePOS, Sale.Sale_Reference, Sale.Sale_Total, Sale.Sale_Tender FROM (Sale INNER JOIN POS ON Sale.Sale_PosID = POS.POSID) INNER JOIN Person ON Sale.Sale_PersonID = Person.PersonID WHERE (((Sale.SaleID)=" & lID & "));" sql = "SELECT Sale.* From Sale WHERE (((SaleID)=" + lID + "));"; rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rs.RecordCount) { posID = rs.Fields("Sale_PosID").Value; personID = rs.Fields("Sale_PersonID").Value; Report.SetParameterValue("txtInvoiceNumber", rs.Fields("Sale_Reference")); Report.SetParameterValue("txtInvoiceDate", Strings.Format(rs.Fields("Sale_DatePOS").Value, "dd mmm yyyy hh:mm")); //Report.txtPOS.SetText rs("POS_Name") //Report.txtCashier.SetText rs("Person_FirstName") & rs("Person_LastName") Report.SetParameterValue("txtAmount", Strings.FormatNumber(rs.Fields("Sale_Total").Value, 2)); Report.SetParameterValue("txtChange", Strings.FormatNumber(rs.Fields("Sale_Tender").Value - rs.Fields("Sale_Total").Value, 2)); // lTransaction.paymentTotal - lTransaction.paymentTender, 2) Report.SetParameterValue("txtTender", Strings.FormatNumber(rs.Fields("Sale_Tender").Value, 2)); // lTransaction.paymentTender, 2) //Report.txtPaidBy.SetText lTransaction.customer.signed Report.ReportDefinition.Sections("txtPaidBy").SectionFormat.EnableSuppress = true; rs.Close(); sql = "SELECT [Person_FirstName] & ' ' & [Person_LastName] AS personName From Person WHERE (((PersonID)=" + personID + "));"; rs = modRecordSet.getRS(ref sql); if (rs.RecordCount) { Report.SetParameterValue("txtCashier", rs.Fields("personName").Value + ""); } rs.Close(); sql = "SELECT POS_Name From POS WHERE (((POS.POSID)=" + posID + "));"; rs = modRecordSet.getRS(ref sql); if (rs.RecordCount) { Report.SetParameterValue("txtPOS", rs.Fields("POS_Name").Value + ""); } rs.Close(); sql = "SELECT CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_ReferenceID From CustomerTransaction WHERE (((CustomerTransaction.CustomerTransaction_TransactionTypeID)=8) AND ((CustomerTransaction.CustomerTransaction_ReferenceID)=" + lID + "));"; Debug.Print(sql); rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rs.RecordCount) { Report.SetParameterValue("txtSettlement", Strings.FormatNumber(rs.Fields("CustomerTransaction_Amount").Value, 2)); } else { Report.SetParameterValue("txtSettlement", Strings.FormatNumber("0.00", 2)); } My.MyProject.Forms.frmReportShow.Text = "Customer Statement"; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); } else { ReportNone.Load("cryNoRecords.rpt"); ReportNone.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); ReportNone.SetParameterValue("txtTitle", "Customer Statement"); //UPGRADE_WARNING: Couldn't resolve default property of object ReportNone.txtTitle. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"' My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone; My.MyProject.Forms.frmReportShow.mReport = ReportNone; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); return; } } }
private void cmdsearch_Click(System.Object eventSender, System.EventArgs eventArgs) { int x = 0; string sql = null; string databaseName = null; short y = 0; short lMonth = 0; ADODB.Connection cn = default(ADODB.Connection); ADODB.Recordset rs = new ADODB.Recordset(); if (gLoading) return; gLoading = true; Cursor = System.Windows.Forms.Cursors.WaitCursor; System.Windows.Forms.Application.DoEvents(); //lMonth = cmbMonth.ItemData(cmbMonth.ListIndex) //If lMonth = gMonthEnd Then databaseName = "pricing.mdb"; //Else // databaseName = "Month" & lMonth & ".mdb" //End If //If openConnection() Then // frmMain.lblPath.Caption = serverPath // closeConnection //End If //Set cn = openConnectionInstance(databaseName) //If cn Is Nothing Then // Exit Sub //End If //Dim lString As String System.Windows.Forms.Application.DoEvents(); if (rs.State) rs.Close(); sql = "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ")) ORDER BY CustomerTransaction.CustomerTransactionID DESC;"; Debug.Print(sql); //rs.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText rs = modRecordSet.getRS(ref sql); lvTransaction.Items.Clear(); lblcount.Text = "0 of 0"; System.Windows.Forms.ListViewItem lvItemTran = null; x = 0; lvTransaction.Visible = false; while (!(rs.EOF)) { x = x + 1; if (gLoading) { } else { break; // TODO: might not be correct. Was : Exit Do } lblcount.Text = x + " of " + rs.RecordCount; System.Windows.Forms.Application.DoEvents(); //If rs("CustomerTransaction_Reference") = "Month End" Then if (rs.Fields("TransactionType_Name").Value == "B/Forward") { if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) { } else if ((Information.IsDBNull(rs.Fields("credit").Value) ? 0 : rs.Fields("credit").Value) == rs.Fields("CustomerTransaction_Allocated").Value & (Information.IsDBNull(rs.Fields("credit").Value) ? 0 : rs.Fields("credit").Value) < 0) { } else { lvItemTran = lvTransaction.Items.Add("K" + rs.Fields("CustomerTransactionID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), ""); if (lvItemTran.SubItems.Count > 1) { lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value; } else { lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value)); } if (lvItemTran.SubItems.Count > 2) { lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value; } else { lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value)); } //Select Case rs("Sale_PaymentType") // Case 1 // lvItemTran.SubItems(2) = "Cash" // Case 2 // lvItemTran.SubItems(2) = "CR Card" // Case 3 // lvItemTran.SubItems(2) = "DR Card" // Case 4 // lvItemTran.SubItems(2) = "Cheque" // Case 5 // lvItemTran.SubItems(2) = "Account" // Case 7 // lvItemTran.SubItems(2) = "Split Tender" // Case Else // lvItemTran.SubItems(2) = "Cash" //End Select if (lvItemTran.SubItems.Count > 3) { lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4); } else { lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4))); } if (lvItemTran.SubItems.Count > 4) { lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4); } else { lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4))); } if (lvItemTran.SubItems.Count > 5) { lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4); } else { lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4))); } if (lvItemTran.SubItems.Count > 6) { lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4); } else { lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4))); } } } else if (rs.Fields("TransactionType_Name").Value == "Invoice") { if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) { } else if (rs.Fields("CustomerTransaction_Allocated").Value < 0) { } else { lvItemTran = lvTransaction.Items.Add("K" + rs.Fields("CustomerTransactionID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), ""); if (lvItemTran.SubItems.Count > 1) { lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value; } else { lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value)); } if (lvItemTran.SubItems.Count > 2) { lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value; } else { lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value)); } if (lvItemTran.SubItems.Count > 3) { lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4); } else { lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4))); } if (lvItemTran.SubItems.Count > 4) { lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4); } else { lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4))); } if (lvItemTran.SubItems.Count > 5) { lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4); } else { lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4))); } if (lvItemTran.SubItems.Count > 6) { lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4); } else { lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4))); } } } else if (rs.Fields("TransactionType_Name").Value == "Interest") { if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) { } else if (rs.Fields("CustomerTransaction_Allocated").Value < 0) { } else { lvItemTran = lvTransaction.Items.Add("K" + rs.Fields("CustomerTransactionID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), ""); if (lvItemTran.SubItems.Count > 1) { lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value; } else { lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value)); } if (lvItemTran.SubItems.Count > 2) { lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value; } else { lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value)); } if (lvItemTran.SubItems.Count > 3) { lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4); } else { lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4))); } if (lvItemTran.SubItems.Count > 4) { lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4); } else { lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4))); } if (lvItemTran.SubItems.Count > 5) { lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4); } else { lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4))); } if (lvItemTran.SubItems.Count > 6) { lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4); } else { lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4))); } } } else if (rs.Fields("TransactionType_Name").Value == "Debit Journal") { if ((Information.IsDBNull(rs.Fields("debit").Value) ? 0 : rs.Fields("debit").Value) == rs.Fields("CustomerTransaction_Allocated").Value) { } else { lvItemTran = lvTransaction.Items.Add("K" + rs.Fields("CustomerTransactionID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), ""); if (lvItemTran.SubItems.Count > 1) { lvItemTran.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value; } else { lvItemTran.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value)); } if (lvItemTran.SubItems.Count > 2) { lvItemTran.SubItems[2].Text = rs.Fields("TransactionType_Name").Value; } else { lvItemTran.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value)); } if (lvItemTran.SubItems.Count > 3) { lvItemTran.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4); } else { lvItemTran.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4))); } if (lvItemTran.SubItems.Count > 4) { lvItemTran.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4); } else { lvItemTran.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4))); } if (lvItemTran.SubItems.Count > 5) { lvItemTran.SubItems[5].Text = Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4); } else { lvItemTran.SubItems.Insert(5, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("CustomerTransaction_Allocated").Value, 4))); } if (lvItemTran.SubItems.Count > 6) { lvItemTran.SubItems[6].Text = Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4); } else { lvItemTran.SubItems.Insert(6, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value) < 0 ? 0 - ((rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)) : (rs.Fields("debit").Value - rs.Fields("CustomerTransaction_Allocated").Value)), 4))); } } } rs.moveNext(); } lvTransaction.Visible = true; //lvTransaction.Refresh Cursor = System.Windows.Forms.Cursors.Default; gLoading = false; }
private void loadCompanies() { string locationCompany_Name = null; int locationCompanyID = 0; int x = 0; ADODB.Recordset rs = new ADODB.Recordset(); System.Windows.Forms.ListViewItem lListitem = null; rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient; if (openConnection()) { } rs.Open("SELECT locationCompany.locationCompanyID, location.location_Name, locationCompany.locationCompany_Name, locationCompany.locationCompany_Path FROM location INNER JOIN locationCompany ON location.locationID = locationCompany.locationCompany_LocationID ORDER BY location.location_Name, locationCompany.locationCompany_Name;", cnnDBmaster, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic); //Me.lvLocation.ListItems.Clear if (rs.RecordCount) { x = -1; object[,] gArray = new object[rs.RecordCount, 3]; while (!(rs.EOF)) { x = x + 1; gArray[x, 0] = rs.Fields(locationCompanyID).Value; gArray[x, 1] = rs.Fields(locationCompany_Name).Value + ""; gArray[x, 2] = 0; rs.moveNext(); } rs.Close(); //Do Until rs.EOF // Set lListitem = lvLocation.ListItems.Add(, "k" & rs("locationCompanyID"), rs("locationCompany_Name"), , 2) // lListitem.SubItems(1) = rs("location_Name") & "" // lListitem.SubItems(2) = rs("locationCompany_Path") & "" // If LCase(rs("locationCompany_Path") & "") = LCase(serverPath & "pricing.mdb") Then // lListitem.Selected = True // 'lvLocation_DblClick // End If // rs.moveNext //Loop } }
private void CustomerStatement(ref int id) { ADODB.Recordset rsInterest = default(ADODB.Recordset); ADODB.Recordset rsCustTransCheck = default(ADODB.Recordset); ADODB.Recordset rsTransaction = default(ADODB.Recordset); ADODB.Recordset rsCompany = default(ADODB.Recordset); string lNumber = null; string lAddress = null; ADODB.Recordset rs = new ADODB.Recordset(); string sql = null; //Dim Report As New cryCustomerStatement_AGING 'cryCustomerStatement CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); Report.Load("cryCustomerStatement_AGING.rpt"); System.DateTime lDate = default(System.DateTime); rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth - (this.cmbMonthEnd.SelectedIndex - 1) + "));"); Report.SetParameterValue("txtStatementDate", Strings.Format(rs.Fields("MonthEnd_Date").Value, "dd mmm yyyy")); lDate = rs.Fields("MonthEnd_Date").Value; rs.Close(); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); lDate = System.Date.FromOADate(lDate.ToOADate() + 10); lDate = System.Date.FromOADate(lDate.ToOADate() - DateAndTime.Day(lDate)) + rs.Fields("Company_PaymentDay").Value; if (chkFields.CheckState) { Report.SetParameterValue("txtPaymentDate", " "); Report.SetParameterValue("Text7", " "); } else { Report.SetParameterValue("txtPaymentDate", Strings.Format(lDate, "dd mmm yyyy")); } lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", "); if (Strings.Right(lAddress, 2) == ", ") { lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2); } Report.Database.Tables(1).SetDataSource(rs); Report.SetParameterValue("txtAddress", lAddress); lNumber = ""; if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value)) lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value; if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value)) { if (!string.IsNullOrEmpty(lNumber)) lNumber = lNumber + " / "; lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value)) { if (!string.IsNullOrEmpty(lNumber)) lNumber = lNumber + " / "; lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value; } Report.SetParameterValue("txtNumbers", lNumber); //New banking details if (Information.IsDBNull(rs.Fields("Company_BankName").Value)) { } else { Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName")); } if (Information.IsDBNull(rs.Fields("Company_BranchName").Value)) { } else { Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName")); } if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value)) { } else { Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode")); } if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value)) { } else { Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber")); } //................... rsCompany = new ADODB.Recordset(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.SetParameterValue("txtDaysCurr", "R " + Strings.FormatNumber(rsCompany("Customer_Current"), 2)); Report.SetParameterValue("txtDays30", "R " + Strings.FormatNumber(rsCompany("Customer_30Days"), 2)); Report.SetParameterValue("txtDays60", "R " + Strings.FormatNumber(rsCompany("Customer_60Days"), 2)); Report.SetParameterValue("txtDays90", "R " + Strings.FormatNumber(rsCompany("Customer_90Days"), 2)); Report.SetParameterValue("txtDays120", "R " + Strings.FormatNumber(rsCompany("Customer_120Days"), 2)); Report.SetParameterValue("txtDays150", "R " + Strings.FormatNumber(rsCompany("Customer_150Days"), 2)); rsCompany.Close(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, modRecordSet.cnnDB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(2).SetDataSource(rsCompany); rsTransaction = new ADODB.Recordset(); //rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _ //'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText //changed for OPEN ITEM //rsTransaction.Open "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," & _ //'" TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsCustTransCheck = new ADODB.Recordset(); rsCustTransCheck.Open("SELECT * FROM CustomerTransaction Where CustomerTransaction_CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); if (rsCustTransCheck.Fields.Count <= 12) { rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit, 0, 0, 0 FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + "));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); } else { rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]), CustomerTransaction.CustomerTransaction_Reference & IIf([CustomerTransaction.CustomerTransaction_Allocated]<>[CustomerTransaction_Amount] AND [CustomerTransaction.CustomerTransaction_Allocated]<>0,' (P)',Null), CustomerTransaction.CustomerTransaction_PersonName, TransactionType.TransactionType_Name," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))>0,([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated])),Null) AS debit," + " IIf(([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))<0,([CustomerTransaction_Amount]-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated])),Null) AS credit, CustomerTransaction.CustomerTransaction_Main, CustomerTransaction.CustomerTransaction_Child, CustomerTransaction.CustomerTransaction_Allocated FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + ") AND ((CustomerTransaction.CustomerTransaction_Amount-IIf(IsNull([CustomerTransaction.CustomerTransaction_Allocated]),0,[CustomerTransaction.CustomerTransaction_Allocated]))<>0));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); } //Report.Database.Tables(3).SetDataSource rsTransaction, 3 if (rsTransaction.BOF | rsTransaction.EOF) { rsTransaction = new ADODB.Recordset(); rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(3).SetDataSource(rsTransaction); //Exit Sub } else { Report.Database.Tables(3).SetDataSource(rsTransaction); } if (rsTransaction.BOF | rsTransaction.EOF) { return; } //Set rsInterest = New Recordset //rsInterest.Open ("SELECT * FROM Interest WHERE (((CustomerID)=" & id & ")) and (Debit>0);"), cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsInterest = modRecordSet.getRS(ref "SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);"); //If rsInterest.BOF Or rsInterest.EOF Then if (rsInterest.RecordCount > 0) { Report.ReportDefinition.ReportObjects("Field20").Top = 280; Report.ReportDefinition.ReportObjects("Field21").Top = 280; Report.ReportDefinition.ReportObjects("Field22").Top = 280; Report.ReportDefinition.ReportObjects("Field23").Top = 280; Report.Database.Tables(4).SetDataSource(rsInterest); } else { //Set rsInterest = New Recordset //rsInterest.Open "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsInterest = modRecordSet.getRS(ref "SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;"); Report.ReportDefinition.Sections("Field20").SectionFormat.EnableSuppress = true; Report.ReportDefinition.Sections("Field21").SectionFormat.EnableSuppress = true; Report.ReportDefinition.Sections("Field22").SectionFormat.EnableSuppress = true; Report.ReportDefinition.Sections("Field23").SectionFormat.EnableSuppress = true; Report.Database.Tables(4).SetDataSource(rsInterest); //Exit Sub //Set rsInterest = New Recordset //rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText } if (rs.Fields("Company_DebtorPrintBal").Value == true) { CrystalDecisions.CrystalReports.Engine.TextObject tObj = Report.ReportDefinition.ReportObjects("Text5"); tObj.Color = Color.White; } My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; // frmReportShow.CRViewer1.PrintReport My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); // Unload frmReportShow System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); }
private void imagePanel_DragDrop(object sender, DragEventArgs e) { if (!validData) return; string medrecnum = ""; MemoryStream ms = e.Data.GetData("Synapse.FujiOffset") as MemoryStream; StreamReader sr; if (ms != null) { sr = new StreamReader(ms, Encoding.Unicode); medrecnum = sr.ReadToEnd().TrimEnd('\0'); } ms = e.Data.GetData("UniformResourceLocator") as MemoryStream; if (ms == null) // Not a Synapse drag event { Array data = e.Data.GetData("FileDrop") as Array; if ((data != null) && (data.GetValue(0) is String)) { string dcmfile = ((string[])data)[0]; if (File.Exists(dcmfile)) { getDemographics(dcmfile, medrecnum, true, false); return; } } } if (cbNetwork.Checked) { ms = e.Data.GetData("UniformResourceLocator") as MemoryStream; if (ms != null) { sr = new StreamReader(ms); string rawstring = sr.ReadToEnd().TrimEnd('\0'); //textBoxLine(rawstring); int epath_loc = rawstring.IndexOf("epath="); if (epath_loc != -1) { string epath = DecodeFrom64(rawstring.Substring(epath_loc + 6)); epath = epath.Substring(0, epath.LastIndexOf("&") + 1); epath = epath.Replace("&", "%26"); epath = epath.Replace("%3A", "%253A"); epath = epath.Replace("%2F", "%252F"); rawstring = rawstring.Substring(0, epath_loc) + "path=" + epath; } textLoc.Text = mapToLocation(rawstring); string datasource = Regex.Match(rawstring, @"datasource=(.*?)%26").Groups[1].Value; datasource = datasource.Replace("%253A", ":"); datasource = datasource.Replace("%252F", "/"); Uri uriImageURL = new Uri(datasource); string studyUID = Regex.Match(rawstring, @"studyuid=(\d*)").Groups[1].Value; string imageUID = Regex.Match(rawstring, @"imageuid=(\d*)").Groups[1].Value; string uriBase = uriImageURL.GetLeftPart(UriPartial.Authority); Uri uriFujiRDS = new Uri(uriBase + "/SynapseScripts/fujirds.asp"); string querystr; querystr = @"select p.external_eid as mrn, s.ris_study_euid as accessionnumber, description, study_timedate from patient p, procedure_info pi, study s, image i where pi.id=s.procedure_info_uid and p.id= s.patient_uid and s.id=i.study_uid and i.id=" + imageUID; byte[] result = retrieveRDS(uriFujiRDS, querystr); if (result == null) return; ADODB.Recordset rs = new ADODB.Recordset(); string tempfile = Path.GetTempFileName(); ByteArrayToFile(tempfile, result); rs.Open(tempfile, "Provider=MSPersist", ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 0); string description = rs.Fields["description"].Value.ToString(); DateTime dt = DateTime.Parse(rs.Fields["study_timedate"].Value.ToString()); string mrn = rs.Fields["mrn"].Value.ToString(); rs.Close(); File.Delete(tempfile); textDesc.Text = ""; textStudy.Text = description; textMRN.Text = mrn; textDate.Text = dt.ToString("yyyy-MM-dd"); } } else { string studyUID = ""; ms = e.Data.GetData("Synapse.TC") as MemoryStream; if (ms != null) { sr = new StreamReader(ms, Encoding.Unicode); string[] tcString = sr.ReadToEnd().Trim('\0').Split(','); foreach (string s in tcString) { if (s.StartsWith("S=")) studyUID = s.Substring(2); } } string imageURL = ""; ms = e.Data.GetData("UniformResourceLocator") as MemoryStream; if (ms != null) { sr = new StreamReader(ms); imageURL = sr.ReadToEnd().TrimEnd('\0'); int epath_loc = imageURL.IndexOf("epath="); if (epath_loc != -1) { string epath = DecodeFrom64(imageURL.Substring(epath_loc + 6)); epath = epath.Substring(0, epath.LastIndexOf("&") + 1); epath = epath.Replace("&", "%26"); epath = epath.Replace("%3A", "%253A"); epath = epath.Replace("%2F", "%252F"); imageURL = imageURL.Substring(0, epath_loc) + "path=" + epath; } textLoc.Text = mapToLocation(imageURL); } ArrayList results = WebCacheTool.WinInetAPI.FindUrlCacheEntries(@"/\d\.\d.*\)$"); ArrayList prefixes = new ArrayList(); SortedList<DateTime, String> slist = new SortedList<DateTime, String>(); foreach (WebCacheTool.WinInetAPI.INTERNET_CACHE_ENTRY_INFO entry in results) { DateTime dt = WebCacheTool.Win32API.FromFileTime(entry.LastAccessTime); if ((DateTime.Now - dt) > TimeSpan.FromHours(24)) continue; string fname = entry.lpszLocalFileName; int index = fname.IndexOf('('); if (index > 0) { string prefix = fname.Substring(0, index); if (prefixes.Contains(prefix) || slist.ContainsKey(dt)) continue; prefixes.Add(prefix); slist.Add(dt, fname); } } byte[] readBuffer = new byte[65536]; int bytesRead; byte[] mrn = { 0x10, 0x00, 0x20, 0x00, 0x4c, 0x4f }; byte[] sid = { 0x20, 0x00, 0x0d, 0x00, 0x55, 0x49 }; string test_sid = "not found"; string dicom_mrn = ""; string fname_match = ""; if (studyUID!="") { for (int i = slist.Count - 1; i >= 0; i--) { fname_match = slist.Values[i]; using (Stream s = new FileStream(fname_match, FileMode.Open, FileAccess.Read)) { bytesRead = s.Read(readBuffer, 0, readBuffer.Length); test_sid = getDicomString(readBuffer, sid, bytesRead); if (test_sid == studyUID) break; } } if (test_sid != studyUID) { fname_match = ""; } } if ((fname_match=="") && (medrecnum!="")) { for (int i = slist.Count - 1; i >= 0; i--) { fname_match = slist.Values[i]; using (Stream s = new FileStream(fname_match, FileMode.Open, FileAccess.Read)) { bytesRead = s.Read(readBuffer, 0, readBuffer.Length); dicom_mrn = getDicomString(readBuffer, mrn, bytesRead); if (dicom_mrn == medrecnum) break; } } if (dicom_mrn != medrecnum) { return; } } if (fname_match!="") getDemographics(fname_match, medrecnum, test_sid==studyUID, true); } }
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e) { try { BackgroundParameter bObj = e.Argument as BackgroundParameter; string url = bObj.url; string querySuffix = bObj.querySuffix; Uri uriFujiRDS = new Uri(url); ProgressObject pObj = new ProgressObject(); backgroundWorker.ReportProgress(0, pObj); DateTime dtime = dateTimePickerStart.Value; string dateStart = dtime.ToString("yyyy-MM-dd HH:mm:ss"); string dateEnd = dtime.AddHours(double.Parse(bObj.duration)).ToString("yyyy-MM-dd HH:mm:ss"); //dateTimePickerEnd.Value.ToString("yyyy-MM-dd HH:mm:ss"); string query = String.Format(@"select * from storage s,document d,study_document sd,study st,patient p,procedure_info pi where d.id = sd.document_uid and s.id=d.storage_uid and st.id=sd.study_uid and p.id=st.patient_uid and pi.id=st.procedure_info_uid and d.name='Notes' and d.creation_timedate between to_date('{0}','YYYY-MM-DD HH24:MI:SS') and to_date('{1}','YYYY-MM-DD HH24:MI:SS')", dateStart, dateEnd); query += querySuffix; ADODB.Recordset rs = new ADODB.Recordset(); byte[] result = retrieveRDS(uriFujiRDS, query); if (result == null) return; string tempfile = Path.GetTempFileName(); ByteArrayToFile(tempfile, result); rs.Open(tempfile, "Provider=MSPersist", ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0); string notefile = Path.GetTempFileName(); //** Eliminating this code due to apparent unreliability of WebClient with redirects //WebClient client = new WebClient(); //client.Credentials = myCredentialCache; //client.UseDefaultCredentials = true; dt = new DataTable(); dt.Columns.Add("Name", typeof(string)); dt.Columns.Add("MRN", typeof(string)); dt.Columns.Add("Accession", typeof(string)); dt.Columns.Add("Procedure", typeof(string)); dt.Columns.Add("Study Time", typeof(string)); dt.Columns.Add("Note Time", typeof(string)); dt.Columns.Add("Note", typeof(string)); dt.Columns.Add("Reports", typeof(string)); DateTime lastprogress = DateTime.Now; while (!rs.EOF) { string http_url = rs.Fields["http_url"].Value.ToString(); string https_url = rs.Fields["https_url"].Value.ToString(); string accnum = rs.Fields["ris_study_euid"].Value.ToString(); string proc = rs.Fields["description"].Value.ToString(); string proc_code = rs.Fields["code"].Value.ToString(); string filename = rs.Fields["filename"].Value.ToString(); string mrn = rs.Fields["internal_euid"].Value.ToString(); string lastname = rs.Fields["last_name"].Value.ToString(); string firstname = rs.Fields["first_name"].Value.ToString(); string middlename = rs.Fields["middle_name"].Value.ToString(); string study_time = ((DateTime)rs.Fields["study_timedate"].Value).ToString("s").Replace('T', ' '); string creation_time = ((DateTime)rs.Fields["creation_timedate"].Value).ToString("s").Replace('T', ' '); if (url.StartsWith("https")) { filename = https_url + filename; } else { filename = http_url + filename; } string note = ""; try { downloadFile(filename, notefile); //client.DownloadFile(filename, notefile); note = parseNote(notefile); } catch (Exception ex) { note = String.Format("Error downloading note at {0}: {1}", filename, ex.Message); } string name = (lastname + ", " + firstname + " " + middlename).Trim(); dt.Rows.Add(name, mrn, accnum, proc, study_time, creation_time, note, ""); rs.MoveNext(); pObj.dt = dt; backgroundWorker.ReportProgress(0, pObj); } rs.Close(); File.Delete(tempfile); int totalNotes = dt.Rows.Count; int current = 0; foreach (DataRow dr in dt.Rows) { // Retrieve reports here query = String.Format(@"select * from storage s,document d,study_document sd,study st where d.id = sd.document_uid and s.id=d.storage_uid and st.id=sd.study_uid and d.name='Report' and st.ris_study_euid='{0}' order by d.creation_timedate", dr["Accession"]); //dr["Reports"] = query; result = retrieveRDS(uriFujiRDS, query); if (result == null) return; tempfile = Path.GetTempFileName(); ByteArrayToFile(tempfile, result); rs.Open(tempfile, "Provider=MSPersist", ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, 0); string report = ""; while (!rs.EOF) { string http_url = rs.Fields["http_url"].Value.ToString(); string https_url = rs.Fields["https_url"].Value.ToString(); string doctype = rs.Fields["name"].Value.ToString(); string filename = rs.Fields["filename"].Value.ToString(); if (url.StartsWith("https")) { filename = https_url + filename; } else { filename = http_url + filename; } try { downloadFile(filename, notefile); //client.DownloadFile(filename, notefile); report += parseReport(notefile, url); } catch (Exception ex) { report += String.Format("Error downloading report at {0}: {1}", filename, ex.Message); } rs.MoveNext(); if (!rs.EOF) report += "\r\n\r\n======\r\n\r\n"; } rs.Close(); File.Delete(tempfile); dr["Reports"] = report; current += 1; if ((DateTime.Now - lastprogress).Milliseconds > 50) { backgroundWorker.ReportProgress(current * 100 / totalNotes, pObj); lastprogress = DateTime.Now; } } File.Delete(notefile); pObj.updateDT = true; backgroundWorker.ReportProgress(100, pObj); } catch (Exception ex) { labelStatus.Text = ex.Message; } }
public string getRoadName() { try { Position PoiX = new Position(); PoiX.X = Lat; PoiX.Y = Lon; double tempLen = 1000000; string tempTown = ""; ADODB.Recordset RST = new ADODB.Recordset(); /* string sqlSTR = "SELECT name,the_geom FROM " + this.roadsTableName +" WHERE the_geom && 'BOX3D(" + (Lon - 0.1) + " " + (Lat - 0.1) + "," + (Lon + 0.1) + " " + (Lat + 0.1) + ") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat + ")', -1 ) ) < 0.11"; string sqlSTROther = "SELECT name,the_geom FROM " + this.roadsTableName + " WHERE the_geom && 'BOX3D(" + (Lon - 1) + " " + (Lat - 1) + "," + (Lon + 1) + " " + (Lat + 1) + ") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat + ")', -1 ) ) < 1.1";*/ string sqlSTR = "SELECT rd_name,the_geom FROM kRoads WHERE the_geom && 'BOX3D(" + (Lon - 0.1) + " " + (Lat - 0.1) + "," + (Lon + 0.1) + " " + (Lat + 0.1) + ") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat + ")', -1 ) ) < 0.11"; string sqlSTROther = "SELECT rd_name,the_geom FROM kRoads WHERE the_geom && 'BOX3D(" + (Lon - 1) + " " + (Lat - 1) + "," + (Lon + 1) + " " + (Lat + 1) + ") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat + ")', -1 ) ) < 1.1"; RST.Open(sqlSTR, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0); if (RST.EOF == true) { try { RST.Close(); } catch { } RST.Open(sqlSTROther, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0); } if (RST.EOF == false) { RST.MoveFirst(); while (RST.EOF == false) { /*we are no longer dealing with a single point we are * dealind with a line string*/ string Coord = RST.Fields["the_geom"].Value.ToString(); int Len = Coord.Length; Coord = Right(Coord, (Len - 25)); Len = Coord.Length; Coord = Mid(Coord, 0, (Len - 2)); char[] SepChar = { ',' }; Array pointArray = Coord.Split(SepChar); /*lets loop through the line string*/ for (int p = 0; p < pointArray.Length; p++) { try { char[] pointSep = { ' ' }; Array coordArray = pointArray.GetValue(p).ToString().Split(pointSep); double xlon = Convert.ToDouble(coordArray.GetValue(0).ToString()); double xlat = Convert.ToDouble(coordArray.GetValue(1).ToString()); Position PoiY = new Position(); PoiY.X = xlat; PoiY.Y = xlon; Calculations calc = new Calculations(); double xLen = 10000001; try { xLen = calc.CalculateDistace(PoiX, PoiY); } catch { } calc = null; //MessageBox.Show(xLen.ToString()); if (xLen < tempLen) { tempLen = xLen; if (tempLen > 0.5) { tempTown = " Along Unknown Road"; } else { tempTown = " Along " + RST.Fields["rd_name"].Value.ToString(); } } //PoiY = null; coordArray = null; //Application.DoEvents(); } catch { } } //Application.DoEvents(); RST.MoveNext(); } RST.Close(); RST = null; } if (tempLen != 1000000) { return tempTown; } else { return " "; } } catch (System.Exception qw) { return " "; } }
public static Boolean bExportDataToSql(String sDatabaseLocation, Boolean bDebug) { clsLog clsLog_ = new clsLog(msLogFile, msVersionData); clsError clsError_ = new clsError(); ADODB.Connection conn = new ADODB.Connection(); ADODB.Recordset rs = new ADODB.Recordset(); clsLog_.mLog(Constants.gcInfo, "Exporting data to sql ..."); String sSql; String sSqlfile = sDatabaseLocation + "\\data\\OneLanData.sql"; String sString = null; String sPropertyInsertIntoHeader = null; String sAccomInsertIntoHeader = null; Int16 i = 0; //String sSql = null; // Variable to hold website information - needed to strip any single quotes out String sWebsite = null; if (bDebug) { clsLog_.mLog(Constants.gcInfo, sIndent + "onelan.bExportDataToSql, sSqlfile = " + sSqlfile); } try { // Open connection to database conn.Open(gcDSN + sDatabaseLocation + "\\" + gsOneLanDatabase); // Remove previous versions of the file if (File.Exists(sSqlfile)) { File.Delete(sSqlfile); } // Open streamwriter obbject StreamWriter sw = File.AppendText(sSqlfile); // Set the insert commands sw.WriteLine("-- File : "+ sSqlfile); sw.WriteLine("-- Date : " + DateTime.Now.ToString("dd-MMM-yyyy HH:mm:ss")); sw.WriteLine("-- Author : auto-sgenerated / NRE www.rmount.co.uk"); sw.WriteLine("-- Client : Raeburn Christie, Aberdeen"); sw.WriteLine("-- Purpose : Repopulate Raeburn mysql database of property data, for use in property screens"); sw.WriteLine(""); sw.WriteLine("-- To apply mysql -pusername -ppassword < " + sSqlfile); sw.WriteLine(""); sw.WriteLine("-- Purge accom table ..."); sw.WriteLine("DELETE FROM accom;"); sw.WriteLine("-- Purge Property table ..."); sw.WriteLine("DELETE FROM property;"); sw.WriteLine(""); sw.WriteLine("-- Repopulate Property table ..."); sPropertyInsertIntoHeader = " INSERT INTO `property` (`PropID`, `PropertyAddress1`, `PropertyAddress2`, `PropertyAddress3`, `Price`, `OffersOverEtc`, `ClosingDate`, `UnderOffer`, `PropertyAddress4`, `Postcode`, `RCCWOffice`) VALUES"; sAccomInsertIntoHeader = "INSERT INTO `accom` (`PropID`, `Accom`, `Website`) VALUES"; // Open the recordset //rs.Open("SELECT * FROM property ORDER BY PropID", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1); sSql= "SELECT PropID" + " , PropertyAddress1" + " , PropertyAddress2" + " , PropertyAddress3" + " , Price" + " , OffersOverEtc" + " , Format(ClosingDate,'YYYY-MM-DD HH:NN:SS') as cd" + " , UnderOffer" + " , PropertyAddress4" + " , Postcode" + " , RCCWOffice" + " FROM property" + " ORDER BY PropID"; clsLog_.mLog("INFO", "property extract sql = " + sSql); rs.Open (sSql,conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1); if (!rs.EOF) { sw.WriteLine(sPropertyInsertIntoHeader); while (!rs.EOF) { if (i == 150) { sw.WriteLine(sPropertyInsertIntoHeader); i = 0; } // sString = "(" + rs.Fields["PropID"].Value + ",'" + // rs.Fields["PropertyAddress1"].Value + "','" + // rs.Fields["PropertyAddress2"].Value + "','" + // rs.Fields["PropertyAddress3"].Value + "'," + // rs.Fields["Price"].Value + ",'" + // rs.Fields["OffersOverEtc"].Value + "','" + // rs.Fields["ClosingDate"].Value + "','" + // rs.Fields["UnderOffer"].Value + "','" + // rs.Fields["PropertyAddress4"].Value + "','" + // rs.Fields["Postcode"].Value + "','" + // rs.Fields["RCCWOffice"].Value + "')"; sString = "(" + rs.Fields["PropID"].Value + ",'" + rs.Fields["PropertyAddress1"].Value + "','" + rs.Fields["PropertyAddress2"].Value + "','" + rs.Fields["PropertyAddress3"].Value + "'," + rs.Fields["Price"].Value + ",'" + rs.Fields["OffersOverEtc"].Value + "','" + rs.Fields["cd"].Value + "','" + rs.Fields["UnderOffer"].Value + "','" + rs.Fields["PropertyAddress4"].Value + "','" + rs.Fields["Postcode"].Value + "','" + rs.Fields["RCCWOffice"].Value + "')"; //sString = " i = " + i + ", PropID = " + rs.Fields["PropID"].Value; i++; if (i<150) {sString = sString + ","; } else { sString = sString + ";"; } rs.MoveNext(); // Replace '' with NULL sString = sString.Replace("''", "NULL"); // Force mysql null date format sString = sString.Replace("30/12/1899 00:00:00", "1899-12-30 00:00:00"); if (!rs.EOF) { sw.WriteLine(sString); } // end while } // End if } //sw.Close(); rs.Close(); // Replace last comma with a semi colon // Replace '' with NULL //sString = sString.Replace("''", "NULL"); sString = sString.Remove(sString.Length - 1, 1) + ";"; // Add the last line sw.WriteLine(sString); // // Now do the accom sw.WriteLine(""); sw.WriteLine("-- Purge accom table ..."); sw.WriteLine("DELETE FROM accom;"); sw.WriteLine(""); sw.WriteLine("-- Repopulate accom table ..."); rs.Open("SELECT * FROM accom ORDER BY PropID", conn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockOptimistic, -1); if (!rs.EOF) { sw.WriteLine(sAccomInsertIntoHeader); while (!rs.EOF) { if (i == 150) { sw.WriteLine(sAccomInsertIntoHeader); i = 0; } sWebsite = ""; sWebsite = rs.Fields["Website"].Value; // strip any single quotes off website information sWebsite = sWebsite.Replace("'", ""); sString = "(" + rs.Fields["PropID"].Value + "," + "NULL" + ",'" + sWebsite + "')"; i++; if (i < 150) { sString = sString + ","; } else { sString = sString + ";"; } rs.MoveNext(); // Replace '' with NULL sString = sString.Replace("''", "NULL"); if (!rs.EOF) { sw.WriteLine(sString); } // end while } // End if } //sw.Close(); rs.Close(); // Replace last comma with a semi colon // Replace '' with NULL sString = sString.Replace("''", "NULL"); sString = sString.Remove(sString.Length - 1, 1) + ";"; // Add the last line sw.WriteLine(sString); sw.Close(); return true; } catch (Exception e) { clsError_.mLogError("Problem exporting data to sql", "onelan", "bExportDataToSql", e, msVersionData, msLogFile, false); return false; } // End of export data to ssql }
protected void UpdateEntity(string Entity, string ID, string Field, string Value) { object oMissing = System.Reflection.Missing.Value; //get the DataService to get a connection string to the database Sage.Platform.Data.IDataService datasvc = Sage.Platform.Application.ApplicationContext.Current.Services.Get<Sage.Platform.Data.IDataService>(); ADODB.Connection objConn = new ADODB.Connection(); ADODB.Recordset objRS = new ADODB.Recordset(); string strSQL = "SELECT " + Field + " FROM " + Entity + " WHERE " +Entity + "ID = '" + ID + "'"; try { objConn.Open(datasvc.GetConnectionString(),null, null,0 ); objRS.CursorLocation = ADODB.CursorLocationEnum.adUseClient; objRS.CursorType = ADODB.CursorTypeEnum.adOpenDynamic; objRS.LockType = ADODB.LockTypeEnum.adLockOptimistic; objRS.Open(strSQL, objConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic,-1); if (!objRS.EOF) { //updating try { objRS.Fields[Field].Value = Value; } catch (Exception ex) { } } objRS.Update(oMissing ,oMissing ); objRS.Close(); } catch (Exception ex) { } }
private void CustomerStatement(ref int id) { ADODB.Recordset rsInterest = default(ADODB.Recordset); ADODB.Recordset rsTransaction = default(ADODB.Recordset); ADODB.Recordset rsCompany = default(ADODB.Recordset); int lNumber = 0; string lAddress = null; ADODB.Recordset rs = new ADODB.Recordset(); string sql = null; //Dim Report As New cryCustomerStatement CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument(); Report.Load("cryCustomerStatement.rpt"); System.DateTime lDate = default(System.DateTime); rs = modRecordSet.getRS(ref "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth + "));"); //rs.Open "SELECT MonthEnd.MonthEnd_Date From MonthEnd WHERE (((MonthEnd.MonthEndID)=" & gMonth & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText //Report.txtStatementDate.SetText Format(rs("MonthEnd_Date"), "dd mmm yyyy") Report.SetParameterValue("txtStatementDate", Strings.Format(DateAndTime.Today, "dd mmm yyyy")); lDate = rs.Fields("MonthEnd_Date").Value; rs.Close(); rs = modRecordSet.getRS(ref "SELECT * FROM Company"); lDate = System.Date.FromOADate(lDate.ToOADate() + 10); lDate = DateAndTime.DateSerial(DateAndTime.Year(lDate), DateAndTime.Month(lDate), 1); lDate = System.Date.FromOADate(lDate + rs.Fields("Company_PaymentDay").Value - 1); //Report.txtPaymentDate.SetText Format(lDate, "dd mmm yyyy") lAddress = Strings.Replace(rs.Fields("Company_PhysicalAddress").Value, Constants.vbCrLf, ", "); if (Strings.Right(lAddress, 2) == ", ") { lAddress = Strings.Left(lAddress, Strings.Len(lAddress) - 2); } Report.Database.Tables(1).SetDataSource(rs); Report.SetParameterValue("txtAddress", lAddress); lNumber = ""; if (!string.IsNullOrEmpty(rs.Fields("Company_Telephone").Value)) lNumber = lNumber + "Tel: " + rs.Fields("Company_Telephone").Value; if (!string.IsNullOrEmpty(rs.Fields("Company_Fax").Value)) { if (!string.IsNullOrEmpty(lNumber)) lNumber = lNumber + " / "; lNumber = lNumber + "Fax: " + rs.Fields("Company_Fax").Value; } if (!string.IsNullOrEmpty(rs.Fields("Company_Email").Value)) { if (!string.IsNullOrEmpty(lNumber)) lNumber = lNumber + " / "; lNumber = lNumber + "Email: " + rs.Fields("Company_Email").Value; } Report.SetParameterValue("txtNumbers", lNumber); //New banking details if (Information.IsDBNull(rs.Fields("Company_BankName").Value)) { } else { Report.SetParameterValue("txtBankName", rs.Fields("Company_BankName")); } if (Information.IsDBNull(rs.Fields("Company_BranchName").Value)) { } else { Report.SetParameterValue("txtBranchName", rs.Fields("Company_BranchName")); } if (Information.IsDBNull(rs.Fields("Company_BranchCode").Value)) { } else { Report.SetParameterValue("txtBranchCode", rs.Fields("Company_BranchCode")); } if (Information.IsDBNull(rs.Fields("Company_AccountNumber").Value)) { } else { Report.SetParameterValue("txtAccountNumber", rs.Fields("Company_AccountNumber")); } //................... rsCompany = new ADODB.Recordset(); rsCompany.Open("SELECT * FROM Customer Where CustomerID = " + id, cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(2).SetDataSource(rsCompany); rsTransaction = new ADODB.Recordset(); //rsTransaction.Open "SELECT CustomerTransaction.*, TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText rsTransaction.Open("SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + id + "));", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //Report.Database.Tables(3).SetDataSource rsTransaction, 3 if (rsTransaction.BOF | rsTransaction.EOF) { rsTransaction = new ADODB.Recordset(); rsTransaction.Open("SELECT 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0," + " 0, 0 AS debit, 0 AS credit;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); Report.Database.Tables(3).SetDataSource(rsTransaction); //Exit Sub } else { Report.Database.Tables(3).SetDataSource(rsTransaction); } if (rsTransaction.BOF | rsTransaction.EOF) { return; } rsInterest = new ADODB.Recordset(); rsInterest.Open("SELECT * FROM Interest WHERE (((CustomerID)=" + id + ")) and (Debit>0);", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //If rsInterest.BOF Or rsInterest.EOF Then if (rsInterest.RecordCount > 0) { //Report.Field20.Top = 280 //Report.Field21.Top = 280 //Report.Field22.Top = 280 //Report.Field23.Top = 280 Report.Database.Tables(4).SetDataSource(rsInterest); } else { rsInterest = new ADODB.Recordset(); rsInterest.Open("SELECT 0 AS CustomerID, 0 AS CDate, 0 AS Description, 0 AS Debit, 0 AS Credit, 0 AS SumIntBal ;", cnnDBStatement, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); //Report.Field20.Suppress = True //Report.Field21.Suppress = True //Report.Field22.Suppress = True //Report.Field23.Suppress = True Report.Database.Tables(4).SetDataSource(rsInterest); //Exit Sub //Set rsInterest = New Recordset //rsInterest.Open "SELECT * FROM Interest WHERE (((CustomerID)=" & id & "));", cnnDBStatement, adOpenStatic, adLockReadOnly, adCmdText } //Report.PrintOut(False, 1) Report.PrintToPrinter(1, false, 0, 0); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; }
private void report_WHTransfer(ref string tblName) { ADODB.Recordset rs = default(ADODB.Recordset); string sql = null; CrystalDecisions.CrystalReports.Engine.ReportDocument Report = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); Report.Load("cryWHRecVerify.rpt"); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor; rs = modRecordSet.getRS(ref "SELECT Company.Company_Name FROM Company;"); Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name")); rs.Close(); sql = "SELECT Handheld777_0.HandHeldID, StockItem.StockItem_Name, Handheld777_0.Quantity"; sql = sql + " FROM Handheld777_0 INNER JOIN StockItem ON Handheld777_0.HandHeldID = StockItem.StockItemID;"; rs = modRecordSet.getRS(ref sql); CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = default(CrystalDecisions.CrystalReports.Engine.ReportDocument); ReportNone.Load("cryNoRecords.rpt"); if (rs.BOF | rs.EOF) { ReportNone.SetParameterValue("txtCompanyName", Report.ParameterFields("txtCompanyName").ToString); ReportNone.SetParameterValue("txtTitle", Report.ParameterFields("txtTitle").ToString); My.MyProject.Forms.frmReportShow.Text = ReportNone.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = ReportNone; My.MyProject.Forms.frmReportShow.mReport = ReportNone; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); return; } Report.Database.Tables(1).SetDataSource(rs); //Report.VerifyOnEveryPrint = True My.MyProject.Forms.frmReportShow.Text = Report.ParameterFields("txtTitle").ToString; My.MyProject.Forms.frmReportShow.CRViewer1.ReportSource = Report; My.MyProject.Forms.frmReportShow.mReport = Report; My.MyProject.Forms.frmReportShow.sMode = "0"; My.MyProject.Forms.frmReportShow.CRViewer1.Refresh(); System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default; My.MyProject.Forms.frmReportShow.ShowDialog(); }
static void Main(string[] args) { ADODB.Connection conn = null; ADODB.Recordset rs = null; try { //////////////////////////////////////////////////////////////////////////////// // 连接数据源. // Console.WriteLine("正在连接数据库 ..."); // 获取连接字符串 string connStr = string.Format("Provider=SQLOLEDB;Data Source={0};Initial Catalog={1};Integrated Security=SSPI", ".\\sqlexpress", "SQLServer2005DB"); // 打开连接 conn = new ADODB.Connection(); conn.Open(connStr, null, null, 0); //////////////////////////////////////////////////////////////////////////////// // 编写并执行ADO命令. // 可以是SQL指令(SELECT/UPDATE/INSERT/DELETE),或是调用存储过程. // 此处是一个INSERT命令示例. // Console.WriteLine("将一条记录插入表CountryRegion中..."); // 1. 生成一个Command对象 ADODB.Command cmdInsert = new ADODB.Command(); // 2. 将连接赋值于命令 cmdInsert.ActiveConnection = conn; // 3. 设置命令文本 // SQL指令或者存储过程名 cmdInsert.CommandText = "INSERT INTO CountryRegion(CountryRegionCode, Name, ModifiedDate)" + " VALUES (?, ?, ?)"; // 4. 设置命令类型 // ADODB.CommandTypeEnum.adCmdText 用于普通的SQL指令; // ADODB.CommandTypeEnum.adCmdStoredProc 用于存储过程. cmdInsert.CommandType = ADODB.CommandTypeEnum.adCmdText; // 5. 添加参数 // CountryRegionCode (nvarchar(20)参数的添加 ADODB.Parameter paramCode = cmdInsert.CreateParameter( "CountryRegionCode", // 参数名 ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(20)) ADODB.ParameterDirectionEnum.adParamInput, // 参数类型 20, // 参数的最大长度 "ZZ"+DateTime.Now.Millisecond); // 参数值 cmdInsert.Parameters.Append(paramCode); // Name (nvarchar(200))参数的添加 ADODB.Parameter paramName = cmdInsert.CreateParameter( "Name", // 参数名 ADODB.DataTypeEnum.adVarChar, // 参数类型 (nvarchar(200)) ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向 200, // 参数的最大长度 "Test Region Name"); // 参数值 cmdInsert.Parameters.Append(paramName); // ModifiedDate (datetime)参数的添加 ADODB.Parameter paramModifiedDate = cmdInsert.CreateParameter( "ModifiedDate", // 参数名 ADODB.DataTypeEnum.adDate, // 参数类型 (datetime) ADODB.ParameterDirectionEnum.adParamInput, // 参数传递方向 -1, // 参数的最大长度 (datetime忽视该值) DateTime.Now); // 参数值 cmdInsert.Parameters.Append(paramModifiedDate); // 6. 执行命令 object nRecordsAffected = Type.Missing; object oParams = Type.Missing; cmdInsert.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords); //////////////////////////////////////////////////////////////////////////////// // 使用Recordset对象. // http://msdn.microsoft.com/en-us/library/ms681510.aspx // Recordset表示了数据表中记录或执行命令获得的结果的集合。 // 在任何时候, Recordset对象都指向集合中的单条记录,并将 // 该记录作为它的当前记录。 // Console.WriteLine("列出表CountryRegion中的所有记录"); // 1. 生成Recordset对象 rs = new ADODB.Recordset(); // 2. 打开Recordset对象 string strSelectCmd = "SELECT * FROM CountryRegion"; // WHERE ... rs.Open(strSelectCmd, // SQL指令/表,视图名 / // 存储过程调用 /文件名 conn, // 连接对象/连接字符串 ADODB.CursorTypeEnum.adOpenForwardOnly, // 游标类型. (只进游标) ADODB.LockTypeEnum.adLockOptimistic, // 锁定类型. (仅当需要调用 // 更新方法时,才锁定记录) (int)ADODB.CommandTypeEnum.adCmdText); // 将第一个参数视为SQL命令 // 或存储过程. // 3. 通过向前移动游标列举记录 // 移动到Recordset中的第一条记录 rs.MoveFirst(); while (!rs.EOF) { // 当在表中定义了一个可空字段,需要检验字段中的值是否为DBNull.Value. string code = (rs.Fields["CountryRegionCode"].Value == DBNull.Value) ? "(DBNull)" : rs.Fields["CountryRegionCode"].Value.ToString(); string name = (rs.Fields["Name"].Value == DBNull.Value) ? "(DBNull)" : rs.Fields["Name"].Value.ToString(); DateTime modifiedDate = (rs.Fields["ModifiedDate"].Value == DBNull.Value) ? DateTime.MinValue : (DateTime)rs.Fields["ModifiedDate"].Value; Console.WriteLine(" {2} \t{0}\t{1}", code, name, modifiedDate.ToString("yyyy-MM-dd")); // 移动到下一条记录 rs.MoveNext(); } } catch (Exception ex) { Console.WriteLine("应用程序出现错误: {0}", ex.Message); if (ex.InnerException != null) Console.WriteLine("描述: {0}", ex.InnerException.Message); } finally { //////////////////////////////////////////////////////////////////////////////// // 退出前清理对象. // Console.WriteLine("正在关闭连接 ..."); // 关闭record set,当它处于打开状态时 if (rs != null && rs.State == (int)ADODB.ObjectStateEnum.adStateOpen) rs.Close(); // 关闭数据库连接,当它处于打开状态时 if (conn != null && conn.State == (int)ADODB.ObjectStateEnum.adStateOpen) conn.Close(); } }
public void TestFillDataTableWithADODBRecordSet() { OleDbDataAdapter adapter = new OleDbDataAdapter(); adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; DataSet stuDS = new DataSet(); DataTable stuTable = new DataTable("student"); //stuTable.Columns.Add("id", typeof(int)); //stuTable.Columns.Add("name", typeof(string)); stuDS.Tables.Add(stuTable); //Use ADO objects from ADO library (msado15.dll) imported // as.NET library ADODB.dll using TlbImp.exe ADODB.Connection adoConn = new ADODB.Connection(); ADODB.Recordset adoRS = new ADODB.Recordset(); adoConn.Open("Provider=CUBRIDProvider;Location=test-db-server;Data Source=demodb;User Id=dba;Port=30000", "", "", -1); adoRS.Open("SELECT * FROM student", adoConn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockReadOnly, 1); int fillRowCount = adapter.Fill(stuTable, adoRS); adoRS.Close(); adoConn.Close(); Assert.AreEqual(3, fillRowCount); }
private void cmdShowHistory_Click(System.Object eventSender, System.EventArgs eventArgs) { int i = 0; int x = 0; string sql = null; string databaseName = null; short y = 0; short lMonth = 0; ADODB.Connection cn = default(ADODB.Connection); ADODB.Recordset rs = new ADODB.Recordset(); // ERROR: Not supported in C#: OnErrorStatement if (cmdShowHistory.Text == "&Show Full History") { cmdShowHistory.Text = "&Show Current Month"; } else { cmdShowHistory.Text = "&Show Full History"; cmdsearch_Click(cmdSearch, new System.EventArgs()); return; } if (gLoading) return; gLoading = true; Cursor = System.Windows.Forms.Cursors.WaitCursor; System.Windows.Forms.Application.DoEvents(); y = cmbMonth.Items.Count - 1; lvTransaction.Items.Clear(); lblcount.Text = "0 of 0"; lvTransaction.Visible = false; string lPosString = null; System.Windows.Forms.ListViewItem lvItem = null; //(cmbMonth.ListCount - 1) for (i = 0; i <= y; i++) { lMonth = Convert.ToInt32(cmbMonth.Items[i]); if (lMonth == gMonthEnd) { databaseName = "pricing.mdb"; } else { databaseName = "Month" + lMonth + ".mdb"; } cn = modRecordSet.openConnectionInstance(ref databaseName); if (cn == null) { goto nextMonth; //Exit Sub } //Dim lString As String //Dim lCustomerString As String //Dim lStockString As String if (this.cmbPOS.SelectedIndex) lPosString = " AND (Sale_PosID=" + cmbPOS.SelectedIndex + ")"; sql = "SELECT CustomerTransaction.CustomerTransactionID, CustomerTransaction.CustomerTransaction_CustomerID, CustomerTransaction.CustomerTransaction_TransactionTypeID, CustomerTransaction.CustomerTransaction_DayEndID, CustomerTransaction.CustomerTransaction_MonthEndID, CustomerTransaction.CustomerTransaction_ReferenceID, CustomerTransaction.CustomerTransaction_Date, CustomerTransaction.CustomerTransaction_Description, CustomerTransaction.CustomerTransaction_Amount, CustomerTransaction.CustomerTransaction_Reference, CustomerTransaction.CustomerTransaction_PersonName," + " TransactionType.TransactionType_Name, IIf([CustomerTransaction_Amount]>0,[CustomerTransaction_Amount],Null) AS debit, IIf([CustomerTransaction_Amount]<0,[CustomerTransaction_Amount],Null) AS credit FROM CustomerTransaction INNER JOIN TransactionType ON CustomerTransaction.CustomerTransaction_TransactionTypeID = TransactionType.TransactionTypeID WHERE (((CustomerTransaction.CustomerTransaction_CustomerID)=" + adoPrimaryRS.Fields("CustomerID").Value + ")) ORDER BY CustomerTransaction.CustomerTransactionID DESC;"; Debug.Print(sql); rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText); x = 0; //lvTransaction.Visible = False while (!(rs.EOF)) { x = x + 1; if (gLoading) { } else { break; // TODO: might not be correct. Was : Exit Do } lblcount.Text = x + " of " + rs.RecordCount; System.Windows.Forms.Application.DoEvents(); if (rs.Fields("CustomerTransaction_Reference").Value != "Month End") { lvItem = lvTransaction.Items.Add("K" + rs.Fields("CustomerTransactionID").Value + "_" + databaseName + "_" + rs.Fields("CustomerTransaction_ReferenceID").Value + "_" + rs.Fields("CustomerTransaction_TransactionTypeID").Value, Strings.Format(rs.Fields("CustomerTransaction_Date").Value, "yyyy mmm dd hh:mm"), ""); if (lvItem.SubItems.Count > 1) { lvItem.SubItems[1].Text = rs.Fields("CustomerTransaction_Reference").Value; } else { lvItem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("CustomerTransaction_Reference").Value)); } if (lvItem.SubItems.Count > 2) { lvItem.SubItems[2].Text = rs.Fields("TransactionType_Name").Value; } else { lvItem.SubItems.Insert(2, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("TransactionType_Name").Value)); } if (lvItem.SubItems.Count > 3) { lvItem.SubItems[3].Text = Strings.FormatNumber(rs.Fields("debit").Value, 4); } else { lvItem.SubItems.Insert(3, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("debit").Value, 4))); } if (lvItem.SubItems.Count > 4) { lvItem.SubItems[4].Text = Strings.FormatNumber(rs.Fields("credit").Value, 4); } else { lvItem.SubItems.Insert(4, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, Strings.FormatNumber(rs.Fields("credit").Value, 4))); } } rs.moveNext(); } //lvTransaction.Visible = True rs.Close(); nextMonth: } lvTransaction.Visible = true; Cursor = System.Windows.Forms.Cursors.Default; gLoading = false; return; ErrShowHistory: if (Strings.InStr(Strings.LCase(Err().Description), "not a valid path")) { Interaction.MsgBox(Err().Number + " - " + Err().Description); return; } else { Interaction.MsgBox(Err().Number + " - " + Err().Description); return; } }
public string getAddress() { try { Position PoiX = new Position(); PoiX.X = Lat; PoiX.Y = Lon; double tempLen = 1000000; string tempTown = ""; ADODB.Recordset RST = new ADODB.Recordset(); string sqlSTR = "SELECT name,the_geom FROM mergedpoints WHERE the_geom && 'BOX3D(" + (Lon - 0.5) + " " + (Lat - 0.5) + "," + (Lon + 0.5) + " " + (Lat + 0.5) + ") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat + ")', -1 ) ) < 0.5"; string sqlSTROther = "SELECT name,the_geom FROM mergedpoints WHERE the_geom && 'BOX3D(" + (Lon - 6) + " " + (Lat - 6) + "," + (Lon + 6) + " " + (Lat + 6) + ") '::box3d AND distance( the_geom, GeometryFromText( 'POINT(" + Lon + " " + Lat + ")', -1 ) ) < 7"; RST.Open(sqlSTR, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0); if (RST.EOF == true) { try { RST.Close(); } catch { } RST.Open(sqlSTROther, this.odbcDatabaseConnection, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, 0); } if (RST.EOF == false) { RST.MoveFirst(); while (RST.EOF == false) { string Coord = RST.Fields["the_geom"].Value.ToString(); int Len = Coord.Length; Coord = Right(Coord, (Len - 14)); Len = Coord.Length; Coord = Mid(Coord, 0, (Len - 1)); char[] SepChar = { ' ' }; Array coordArray = Coord.Split(SepChar); double xlon = Convert.ToDouble(coordArray.GetValue(0).ToString()); double xlat = Convert.ToDouble(coordArray.GetValue(1).ToString()); Position PoiY = new Position(); PoiY.X = xlat; PoiY.Y = xlon; Calculations calc = new Calculations(); double xLen = calc.CalculateDistace(PoiX, PoiY); //MessageBox.Show(xLen.ToString()); if (xLen < tempLen) { tempLen = xLen; tempTown = RST.Fields["name"].Value.ToString(); } //PoiY = null; coordArray = null; RST.MoveNext(); } RST.Close(); RST = null; } if (tempLen != 1000000) { string retVal = Decimal.Round((decimal)tempLen, 3).ToString(); return retVal + "Km From " + tempTown; } else { return " "; } } catch (System.Exception qw) { return " "; } }