C# ADODB.Recordset.Open方法代碼示例

 public static ADODB.Recordset getRSwaitron(ref object sql, ref ADODB.Connection cn)
 {
     ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
     functionReturnValue = new ADODB.Recordset();
     functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
     functionReturnValue.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
     return functionReturnValue;
 }

 public static ADODB.Recordset getRS(ref string sql)
 {
     ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
     functionReturnValue = new ADODB.Recordset();
     functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
     functionReturnValue.Open(sql, cnnDB, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic, -1);
     return functionReturnValue;
     //Debug.Print(sql)
 }

        public static ADODB.Recordset getRSreport(ref string sql)
        {
            ADODB.Recordset functionReturnValue = default(ADODB.Recordset);
            string Path = null;
            string strDBPath = null;
             // ERROR: Not supported in C#: OnErrorStatement

            functionReturnValue = new ADODB.Recordset();
            functionReturnValue.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
            Debug.Print(sql);
            functionReturnValue.Open(sql, cnnDBreport, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
            return functionReturnValue;
            getRSreport_Error:

            if (cnnDBreport == null) {
                Interaction.MsgBox(Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + " cnnDBreport object has not been made.");
            } else if (Err().Description == "Not a valid password.") {
                Interaction.MsgBox("Error while getRSreport and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + cnnDBreport.ConnectionString + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBreport.State);

                modRecordSet.cnnDB.Close();
                //UPGRADE_NOTE: Object cnnDB may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
                modRecordSet.cnnDB = null;
                modRecordSet.cnnDB = new ADODB.Connection();
                //UPGRADE_WARNING: Couldn't resolve default property of object strDBPath. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                strDBPath = modRecordSet.serverPath + "pricing.mdb";
                //UPGRADE_WARNING: Couldn't resolve default property of object strDBPath. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                //UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                Path = strDBPath + ";Jet " + "OLEDB:Database Password=lqd";
                //cnnDB.CursorLocation = adUseClient
                //UPGRADE_WARNING: Couldn't resolve default property of object Path. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                modRecordSet.cnnDB.Open("Provider=Microsoft.ACE.OLEDB.12.0;Mode=Share Deny Read|Share Deny Write;Persist Security Info=False;Data Source= " + Path);
                modRecordSet.cnnDB.Execute("ALTER DATABASE PASSWORD Null " + " " + "lqd");
                modRecordSet.cnnDB.Close();
                //UPGRADE_NOTE: Object cnnDB may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'
                modRecordSet.cnnDB = null;
                modRecordSet.openConnection();

            } else {
                Interaction.MsgBox("Error while getRSreport and Error is :" + Err().Number + " " + Err().Description + " " + Err().Source + Constants.vbCrLf + Constants.vbCrLf + cnnDBreport.ConnectionString + Constants.vbCrLf + Constants.vbCrLf + " --- " + cnnDBreport.State);
            }
             // ERROR: Not supported in C#: ResumeStatement

            return functionReturnValue;
        }

        public customer getCustomer(ref int id)
        {
            string sql = null;
            ADODB.Connection cn = default(ADODB.Connection);
            ADODB.Recordset rs = default(ADODB.Recordset);
            customer customer_Renamed = null;
            if (id) {

                cn = modRecordSet.openConnectionInstance();
                if (cn == null) {
                    Interaction.MsgBox("You are not connected to the 4POS BackOffice server! This task can only be performed if you are connected to the server. Please contact your System Administrator.", MsgBoxStyle.Exclamation, "BackOffice Connection Error");
                } else {
                    rs = new ADODB.Recordset();
                    sql = "SELECT Customer.*, [Customer_Current]+[Customer_30Days]+[Customer_60Days]+[Customer_90Days]+[Customer_120Days]+[Customer_150Days] AS balance From Customer WHERE CustomerID=" + id;
                    rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly, ADODB.CommandTypeEnum.adCmdText);
                    if (rs.RecordCount) {
                        customer_Renamed = new customer();
                        customer_Renamed.Key = rs.Fields("CustomerID").Value;
                        customer_Renamed.channelID = rs.Fields("Customer_ChannelID").Value;
                        customer_Renamed.creditLimit = rs.Fields("Customer_CreditLimit").Value;
                        customer_Renamed.department = rs.Fields("Customer_DepartmentName").Value + "";
                        customer_Renamed.fax = rs.Fields("Customer_Fax").Value + "";
                        customer_Renamed.name = rs.Fields("Customer_InvoiceName").Value + "";
                        customer_Renamed.outstanding = rs.Fields("balance").Value;
                        customer_Renamed.person = rs.Fields("Customer_FirstName").Value + " " + rs.Fields("Customer_Surname").Value;
                        customer_Renamed.physical = rs.Fields("Customer_PhysicalAddress").Value + "";
                        customer_Renamed.postal = rs.Fields("Customer_PostalAddress").Value + "";
                        customer_Renamed.signed_Renamed = customer_Renamed.person;
                        customer_Renamed.telephone = rs.Fields("Customer_Telephone").Value + "";
                        customer_Renamed.terms = rs.Fields("Customer_Terms").Value;
                        customer_Renamed.tax = rs.Fields("Customer_VATNumber").Value + "";
                    }
                }
            }
            return customer_Renamed;
        }

        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) {
//.........這裏部分代碼省略.........

        public void ImportSalesOrder()
        {
            string soPath = "http://nwszmail/public/namwah/Sales/Timex/PurchaseOrders/";
            string strSQL = "";

            Dictionary<string, SalesOrder> dictSo = new Dictionary<string, SalesOrder>();

            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rst = new ADODB.Recordset();
            Item item;

            /*
            XPCollection<SalesOrderLine> soLines = new XPCollection<SalesOrderLine>(session);
            session.BeginTransaction();
            session.Delete(soLines);
            session.CommitTransaction();

            return;
            */

            string partNo;
            string soLineIndex;

            CacheItem();
            CacheSalesOrderLine(Namwah.Module.BO.SalesOrder.SalesOrderType.Normal);

            ConnectDB(cnn, soPath);

            strSQL = "SELECT \"nw:partno\" , \"nw:cpo:item:ignore\", \"nw:cpo:item:index\", \"nw:cpo:item:initneeddate\", \"nw:cpo:date\" ";
            strSQL = strSQL + " , \"nw:cpo:item:needqty\", \"nw:cpo:item:needdate\", \"nw:cpo:item:priority\", \"nw:cpo:item:shippedqty\" ";
            strSQL = strSQL + " , \"nw:cpo:item:remark\", \"nw:cpo:no\", \"nw:customer\", \"nw:part:tmxrefno\" ";
            strSQL = strSQL + " FROM \"" + soPath;
            strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false AND \"nw:customer\" = 'Timex' )";
            return ;
            rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);

            session.BeginTransaction();
            DateTime sTime = DateTime.Now;
            string poNo;

            while (!rst.EOF)
            {
                partNo = rst.Fields["nw:partno"].Value.ToString();
                soLineIndex = rst.Fields["nw:cpo:no"].Value.ToString() + "-" + rst.Fields["nw:cpo:item:index"].Value.ToString();
                poNo = rst.Fields["nw:cpo:no"].Value.ToString();

                if (poNo.StartsWith("PoHistory") == false)
                {
                    if (dictItem.ContainsKey(partNo))
                    {
                        if (dictSalesOrderLine.ContainsKey(soLineIndex))
                        {
                            SalesOrderLine soLine = dictSalesOrderLine[soLineIndex];

                            soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
                            soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
                            soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
                            soLine.SetShipQty(float.Parse(rst.Fields["nw:cpo:item:shippedqty"].Value.ToString()));
                            soLine.Save();
                        }
                        else
                        {
                            item = dictItem[partNo];
                            SalesOrder so;

                            if (dictSo.ContainsKey(rst.Fields["nw:cpo:no"].Value.ToString()))
                            {
                                so = dictSo[rst.Fields["nw:cpo:no"].Value.ToString()];
                            }
                            else
                            {
                                so = session.FindObject<SalesOrder>(new BinaryOperator("OrderNo", rst.Fields["nw:cpo:no"].Value.ToString()));

                                if (so == null)
                                {
                                    so = new SalesOrder(session);
                                    so.SetOrderNo(rst.Fields["nw:cpo:no"].Value.ToString());
                                    so.Customer = GetCustomer(rst.Fields["nw:customer"].Value.ToString());
                                    so.Save();
                                }
                                dictSo.Add(rst.Fields["nw:cpo:no"].Value.ToString(), so);
                            }

                            SalesOrderLine soLine = new SalesOrderLine(session);
                            soLine.SalesOrder = so;
                            soLine.Item = item;
                            soLine.SetOrderLineNo(int.Parse(rst.Fields["nw:cpo:item:index"].Value.ToString()));
                            soLine.CustomerItemNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
                            soLine.CustomerOrderNo = rst.Fields["nw:cpo:no"].Value.ToString();
                            if (rst.Fields["nw:cpo:item:ignore"].Value.ToString() != "")
                                soLine.IgnorePlanningReport = bool.Parse(rst.Fields["nw:cpo:item:ignore"].Value.ToString());

                            if (rst.Fields["nw:cpo:item:initneeddate"].Value.ToString() != "")
                                soLine.InitNeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:initneeddate"].Value.ToString());

                            soLine.NeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:needdate"].Value.ToString());
                            soLine.NeedQty = float.Parse(rst.Fields["nw:cpo:item:needqty"].Value.ToString());
                            soLine.PoDate = DateTime.Parse(rst.Fields["nw:cpo:date"].Value.ToString());
                            soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
                            soLine.SetShipQty(float.Parse(rst.Fields["nw:cpo:item:shippedqty"].Value.ToString()));
//.........這裏部分代碼省略.........

        public void ImportSSTK()
        {
            string soPath = "http://nwszmail/public/namwah/Parts2/SafetyStock/";
            string strSQL = "";

            Dictionary<string, SalesOrder> dictSo = new Dictionary<string, SalesOrder>();

            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rst = new ADODB.Recordset();
            Item item;
            string partNo;
            string soLineIndex;

            CacheItem();
            ConnectDB(cnn, soPath);

            strSQL = "SELECT \"nw:cpo:item:initneeddate\", \"nw:cpo:date\" ";
            strSQL = strSQL + " , \"nw:cpo:item:needqty\", \"nw:cpo:item:needdate\", \"nw:cpo:item:priority\", \"nw:part:sstqty\" ";
            strSQL = strSQL + " , \"nw:cpo:no\", \"nw:part:tmxrefno\" ";
            strSQL = strSQL + " FROM \"" + soPath;
            strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false )";

            rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);

            session.BeginTransaction();

            XPCollection<SalesOrderLine> soLines = new XPCollection<SalesOrderLine>(session);
            soLines.Criteria = CriteriaOperator.Parse("SalesOrder.OrderType == SalesOrder.SalesOrderType.SSTK");
            session.BeginTransaction();
            session.Delete(soLines);
            session.CommitTransaction();

            DateTime sTime = DateTime.Now;
            string poNo;

            while (!rst.EOF)
            {
                partNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
                soLineIndex = rst.Fields["nw:cpo:no"].Value.ToString() + "-" + rst.Fields["nw:part:tmxrefno"].Value.ToString();
                poNo = rst.Fields["nw:cpo:no"].Value.ToString();

                if (dictItem.ContainsKey(partNo))
                {
                    item = dictItem[partNo];
                    SalesOrder so;
                    if (dictSo.ContainsKey(rst.Fields["nw:cpo:no"].Value.ToString()))
                    {
                        so = dictSo[rst.Fields["nw:cpo:no"].Value.ToString()];
                    }
                    else
                    {
                        so = session.FindObject<SalesOrder>(new BinaryOperator("OrderNo", rst.Fields["nw:cpo:no"].Value.ToString()));

                        if (so == null)
                        {
                            so = new SalesOrder(session);
                            so.SetOrderNo(rst.Fields["nw:cpo:no"].Value.ToString());
                            so.OrderType = SalesOrder.SalesOrderType.SSTK;
                            so.Customer = GetCustomer("TIMEX");
                            so.Save();
                        }
                        dictSo.Add(rst.Fields["nw:cpo:no"].Value.ToString(), so);
                    }

                    SalesOrderLine soLine = new SalesOrderLine(session);
                    soLine.SalesOrder = so;
                    soLine.Item = item;
                    so.AddLineNo();
                    soLine.SetOrderLineNo(so.LastLineNo);
                    soLine.CustomerItemNo = rst.Fields["nw:part:tmxrefno"].Value.ToString();
                    soLine.CustomerOrderNo = rst.Fields["nw:cpo:no"].Value.ToString();
                    //soLine.InitNeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:initneeddate"].Value.ToString());
                    soLine.NeedDate = DateTime.Parse(rst.Fields["nw:cpo:item:needdate"].Value.ToString());
                    soLine.NeedQty = float.Parse(rst.Fields["nw:part:sstqty"].Value.ToString());
                    soLine.PoDate = DateTime.Parse(rst.Fields["nw:cpo:date"].Value.ToString());
                    soLine.Priority = int.Parse(rst.Fields["nw:cpo:item:priority"].Value.ToString());
                    soLine.ApprovedSalesOrderLine();
                    soLine.Save();
                }
                rst.MoveNext();
            }

            System.Diagnostics.Debug.WriteLine("Start Time : " + sTime.ToString() + ".  End Time : " + DateTime.Now.ToString());
            session.CommitTransaction();
            System.Diagnostics.Debug.WriteLine("Commit Time : " + DateTime.Now.ToString());
        }

        public void ImportWorkOrder()
        {
            string woPath = "http://nwszmail/public/namwah/WorkOrders/";
            string strSQL = "";

            ADODB.Connection cnn = new ADODB.Connection();
            ADODB.Recordset rst = new ADODB.Recordset();
            Item item;
            WorkOrder wo;
            string partNo;
            string woNo;
            session.BeginTransaction();

            CacheItem();
            CacheWo();

            ConnectDB(cnn, woPath);

            strSQL = "SELECT \"nw:partno\" , \"nw:partname\", \"nw:parttype\", \"nw:wo:no\",  ";
            strSQL = strSQL + " \"nw:wo:needqty\", \"nw:wo:needdate\", \"nw:wo:ssblankuseqty\", \"nw:wo:extra\" ";
            strSQL = strSQL + " FROM \"" + woPath;
            strSQL = strSQL + "\" WHERE (\"DAV:ishidden\" = false)";

            rst.Open(strSQL, cnn, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockBatchOptimistic, 1);

            while (!rst.EOF)
            {
                partNo = rst.Fields["nw:partno"].Value.ToString();
                woNo = rst.Fields["nw:wo:no"].Value.ToString();

                if (dictItem.ContainsKey(partNo))
                {
                    if (dictWo.ContainsKey(woNo))
                    {
                        WorkOrder oldWo = dictWo[woNo];
                        if (oldWo.NeedQty != float.Parse(rst.Fields["nw:wo:needqty"].Value.ToString()))
                        {
                            oldWo.NeedQty = float.Parse(rst.Fields["nw:wo:needqty"].Value.ToString());
                            oldWo.Save();
                        }
                    }
                    else
                    {
                        item = dictItem[partNo];
                        wo = new WorkOrder(session, woNo);
                        wo.Item = item;
                        wo.NeedDate = DateTime.Parse(rst.Fields["nw:wo:needdate"].Value.ToString());
                        wo.NeedQty = float.Parse(rst.Fields["nw:wo:needqty"].Value.ToString());

                        if (rst.Fields["nw:wo:extra"].Value.ToString() != "")
                            wo.ExtraQty = float.Parse(rst.Fields["nw:wo:extra"].Value.ToString());

                        if (rst.Fields["nw:wo:ssblankuseqty"].Value.ToString() != "")
                            wo.ssBlankUseQty = float.Parse(rst.Fields["nw:wo:ssblankuseqty"].Value.ToString());
                        wo.Save();
                    }
                }
                rst.MoveNext();
            }

            session.CommitTransaction();
        }

 private void loadCompanies()
 {
     ADODB.Recordset rs = new ADODB.Recordset();
     System.Windows.Forms.ListViewItem lListitem = null;
     rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
     //If openConnection Then
     //End If
     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);
     //If lvLocation.Items.Count <> 0 Then
     //Me.lvLocation.Items.Clear()
     //End If
     if (rs.RecordCount) {
         while (!(rs.EOF)) {
             lListitem = lvLocation.Items.Add("k" + rs.Fields("locationCompanyID").Value, rs.Fields("locationCompany_Name").Value, 2);
             if (lListitem.SubItems.Count > 0) {
                 lListitem.SubItems[0].Text = rs.Fields("location_Name").Value + "";
             } else {
                 lListitem.SubItems.Insert(0, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("location_Name").Value + ""));
             }
             if (lListitem.SubItems.Count > 1) {
                 lListitem.SubItems[1].Text = rs.Fields("locationCompany_Path").Value + "";
             } else {
                 lListitem.SubItems.Insert(1, new System.Windows.Forms.ListViewItem.ListViewSubItem(null, rs.Fields("locationCompany_Path").Value + ""));
             }
             if (Strings.LCase(rs.Fields("locationCompany_Path").Value + "") == Strings.LCase(modRecordSet.serverPath + "pricing.mdb")) {
                 lListitem.Selected = true;
                 lvLocation_DoubleClick(lvLocation, new System.EventArgs());
             }
             rs.MoveNext();
         }
     }
 }

        private void cmdLoad_Click(System.Object eventSender, System.EventArgs eventArgs)
        {
            string sql = null;
            ADODB.Connection lConn = default(ADODB.Connection);
            ADODB.Recordset rs = default(ADODB.Recordset);
            ADODB.Recordset rsPurchase = default(ADODB.Recordset);
            ADODB.Recordset rsSales = default(ADODB.Recordset);
            ADODB.Recordset rsStock = default(ADODB.Recordset);
            ADODB.Recordset rsCompany = default(ADODB.Recordset);
            //Dim Report As New cryIncomeExpense
            CrystalDecisions.CrystalReports.Engine.ReportDocument Report = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
            Report.Load("cryIncomeExpense.rpt");
            //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 (cmbMonthEnd.SelectedIndex) {
                lConn = modRecordSet.openConnectionInstance(ref "month" + gMonth - cmbMonthEnd.SelectedIndex + ".mdb");
                if (lConn == null)
                    return;
                rsStock = new ADODB.Recordset();
                rsStock.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
                sql = "SELECT DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]+[DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS total, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]) AS opening, Sum([DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]) AS sales, Sum([DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]) AS shrink, Sum([DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS grv From DayEndStockItemLnk GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;";

                rsStock.Open(sql, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
                rsSales = new ADODB.Recordset();
                rsSales.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
                //UPGRADE_WARNING: Couldn't resolve default property of object sql. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
                sql = "SELECT [M_DayEnd].[DayEndID], [M_DayEnd].[DayEnd_Date], Sum([Declaration].[Declaration_Total]) AS SumOfSale_Total FROM Declaration INNER JOIN M_DayEnd ON [Declaration].[Declaration_DayEndID]=[M_DayEnd].[DayEndID] GROUP BY [M_DayEnd].[DayEndID], [M_DayEnd].[DayEnd_Date];";

                rsSales.Open(sql, lConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic);
            } else {

                modApplication.modUpdate = 3;

                modApplication.updateStockMovement();

                sql = "SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum(Declaration.Declaration_Total) AS SumOfSale_Total FROM Company, Declaration INNER JOIN DayEnd ON Declaration.Declaration_DayEndID = DayEnd.DayEndID Where (((DayEnd.DayEndID) <> [Company]![Company_DayEndID])) GROUP BY DayEnd.DayEndID, DayEnd.DayEnd_Date Union SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum([SaleItem_Price]*[SaleItem_Quantity]) AS SumOfSale_Total FROM Consignment AS Consignment_1 RIGHT JOIN (Consignment RIGHT JOIN ((Sale INNER JOIN (Company INNER JOIN DayEnd ON Company.Company_DayEndID = DayEnd.DayEndID) ON Sale.Sale_DayEndID = DayEnd.DayEndID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleItem_SaleID) ON Consignment.Consignment_SaleID = Sale.SaleID) ON Consignment_1.Consignment_ReversalSaleID = Sale.SaleID Where (((SaleItem.SaleItem_Revoke) = 0)) GROUP BY Consignment.ConsignmentID, Consignment_1.ConsignmentID, DayEnd.DayEndID, DayEnd.DayEnd_Date  ";
                sql = sql + "HAVING (((Consignment.ConsignmentID) Is Null) AND ((Consignment_1.ConsignmentID) Is Null));";
                rsSales = modRecordSet.getRS(ref sql);

                rsStock = modRecordSet.getRS(ref "SELECT DayEndStockItemLnk.DayEndStockItemLnk_DayEndID, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]-[DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]+[DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS total, Sum([DayEndStockItemLnk_Quantity]*[DayEndStockItemLnk_ListCost]) AS opening, Sum([DayEndStockItemLnk_QuantitySales]*[DayEndStockItemLnk_ListCost]) AS sales, Sum([DayEndStockItemLnk_QuantityShrink]*[DayEndStockItemLnk_ListCost]) AS shrink, Sum([DayEndStockItemLnk_QuantityGRV]*[DayEndStockItemLnk_ListCost]) AS grv From DayEndStockItemLnk GROUP BY DayEndStockItemLnk.DayEndStockItemLnk_DayEndID;");

            }

            rsPurchase = modRecordSet.getRS(ref "SELECT DayEnd.DayEndID, DayEnd.DayEnd_Date, Sum(grvPosted.GRV_InvoiceInclusive) AS SumOfGRV_InvoiceInclusive, ([MonthEnd_BudgetSales]/[MonthEnd_Days]) AS saleBudget, ([MonthEnd_BudgetPurchases]/[MonthEnd_Days]) AS purchaseBudget FROM [SELECT GRV.* From GRV WHERE (((GRV.GRV_GRVStatusID)=3))]. AS grvPosted RIGHT JOIN (DayEnd INNER JOIN MonthEnd ON DayEnd.DayEnd_MonthEndID = MonthEnd.MonthEndID) ON grvPosted.GRV_DayEndID = DayEnd.DayEndID Where (((MonthEnd.MonthEndID) = " + gMonth - cmbMonthEnd.SelectedIndex + ")) GROUP BY DayEnd.DayEndID, DayEnd.DayEnd_Date, ([MonthEnd_BudgetSales]/[MonthEnd_Days]), ([MonthEnd_BudgetPurchases]/[MonthEnd_Days]);");

            rsCompany = modRecordSet.getRS(ref "SELECT MonthEnd.* From MonthEnd WHERE (((MonthEnd.MonthEndID)=" + gMonth - cmbMonthEnd.SelectedIndex + "));");

            rs = modRecordSet.getRS(ref "SELECT * FROM Company");
            Report.SetParameterValue("txtCompanyName", rs.Fields("Company_Name"));
            rs.Close();
            //ReportNone.Load("cryNoRecords.rpt")
            CrystalDecisions.CrystalReports.Engine.ReportDocument ReportNone = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
            ReportNone.Load("cryNoRecords.rpt");
            if (rsPurchase.BOF | rsPurchase.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.SetDataSource(rs)
            Report.Database.Tables(1).SetDataSource(rsPurchase);
            Report.Database.Tables(2).SetDataSource(rsSales);
            Report.Database.Tables(3).SetDataSource(rsStock);
            Report.Database.Tables(4).SetDataSource(rsCompany);
            //UPGRADE_WARNING: Couldn't resolve default property of object Report.VerifyOnEveryPrint. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
            //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();

            if (lConn == null) {
            } else {
                lConn.Close();
            }
        }

		///<summary>
		/// Execute a SQL command
		/// <param name="SQLText">
		/// SQL command to execute
		/// </param>
		/// <param name="rows">
		/// Maximum number of row to extract. If is "0" then all rows are extracted.
		/// </param>
		/// <returns> return a <see cref="System.Data.DataTable">DataTable</see>
		///or a <see cref="System.Data.DataSet">DataSet</see> object.
		/// </returns>
		/// </summary>
		public override object ExecuteSQL(string SQLText, int rows)
		{
			ADODB.Recordset record = new ADODB.Recordset();
			DataTable table = null;

			this.pADOConnection.BeginTrans();

			try
			{
				record.MaxRecords = rows;
				record.Open((object)SQLText,
				            (object)this.pADOConnection,
				            ADODB.CursorTypeEnum.adOpenDynamic,
				            ADODB.LockTypeEnum.adLockPessimistic,
				            (int)ADODB.CommandTypeEnum.adCmdText
				           );

				table = RecordSetToDataTable(record);
			}
			catch (System.Exception)
			{
				this.pADOConnection.RollbackTrans();

				string mes = SQLText + "\n\r";

				foreach (ADODB.Error err in this.pADOConnection.Errors)
				{
					mes += "-----------------\n\r";
					mes += err.Description + "\n\r";
					mes += err.NativeError + "\n\r";
				}
				throw new ExecuteSQLException(mes);
			}
			finally
			{
				this.pADOConnection.CommitTrans();
			}

			return table;
		}

        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
            }
        }

 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);
 }

    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)
        {

        }
    }

        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 " "; }
        }