#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    142
    Rep Power
    8

    Multiple-step OLE DB operation generated errors


    Hi,

    I'm trying to write an Excel 2010 x64 add-in with VSTO and C#. Currently, my code is below. The way it is now, it runs well and has partial functionality. However, the block of code in the comment (denoted by "ERROR OCCURS WHEN THIS BLOCK IS UNCOMMENTED") completes the functionality, but causes an error when it's uncommented. The error, when clicked in Excel, is
    Multiple-step OLE DB operation generated errors
    and the block of code in question (also below) is
    Code:
    while (!rds.EOF)
                {
                    if (loanDate >= DateTime.Parse(rds.Fields["startdate"].Value.ToString()) && loanDate < DateTime.Parse(rds.Fields["maturitydate"].Value.ToString()))
                        rds.Fields["inCash"].Value = rds.Fields["notional"].Value.ToString();
                    rds.MoveNext();
                }
    I'm not sure if it's my syntax or something, but could anyone point out why I get that error?

    Code:
    using System;
    using System.Collections.Generic;
    using Microsoft.Office.Tools.Excel;
    using System.Linq;
    using System.Text;
    using System.Data.SqlClient;
    using System.Data.Odbc;
    using System.Data;
    using System.Windows.Forms;
    using System.Runtime.InteropServices;
    using Excel = Microsoft.Office.Interop.Excel;
    using Range = Microsoft.Office.Interop.Excel.Range;
    using Worksheet = Microsoft.Office.Interop.Excel.Worksheet;
    
    // Fred Liu. Thursday June 14, 2012-Friday June 15, 2012
    
    namespace Fills2010
    {
        public class Loans
        {
            public Loans(BookRangeSettings sett, SqlDataTools sqltools)
            {
                try
                {
                    m_xl = sett;
                    m_dataTools = sqltools;
                    printLoans("abc", "1");
                    printLoans("abc", "2");
                    printLoans("abc", "3");
                    printLoans("abc", "4");
                }
                catch (Exception exp)
                {
                    MessageBox.Show(exp.Message + "\n Stack: " + exp.StackTrace);
                }
            }
    
            private void printLoans(string book, string accountBlock)
            {
                Range loansRange = m_xl.NamedRange("Loan_Output_" + accountBlock);
                Range accountRange = m_xl.NamedRange("Account_" + accountBlock);
                loansRange.ClearContents();
    
                DateTime loanDate = DateTime.Parse(m_xl.NamedRange("Loans_Date").Text.ToString());
    
                // Get the account # from the account ID
                string query = "select * from accounts"
                    + " where accountname = '" + accountRange.Value2.ToString() + "'";
                ADODB.Recordset rds = m_dataTools.Query_SF(query);
    
                /* Book is found by trial and error with the existing data and SQL Server since the old C++ fills
                 * code does not compile and it's impossible to find the values
                 
                 The 3 columns from the right (PnL receive, in cash, and realized interest) are pre-populated with 0s
                 * here and then replaced with their appropriate values if necessary */
                query = "select startdate, maturitydate, counterparty, comment, daycount, notional, interestrate, 0 PnLReceive, 0 inCash, 0 realizedInterest"
                    + " from loans l, counterparty c"
                    + " where maturitydate >= '" + loanDate.ToString("MM/dd/yyyy") + "'"
                    + " and accountId = '" + rds.Fields["AccountId"].Value.ToString() + "'"
                    + " and book = '" + book + "'"
                    + " and c.counterpartyid = l.counterpartyid"
                    + " order by maturitydate";
    
                rds = m_dataTools.Query_SF(query);
    
                // Replace the last 3 columns with non-zero values if necessary
    
                /* ERROR OCCURS WHEN THIS BLOCK IS UNCOMMENTED
                while (!rds.EOF)
                {
                    if (loanDate >= DateTime.Parse(rds.Fields["startdate"].Value.ToString()) && loanDate < DateTime.Parse(rds.Fields["maturitydate"].Value.ToString()))
                        rds.Fields["inCash"].Value = rds.Fields["notional"].Value.ToString();
                    rds.MoveNext();
                } */
    
                ((Range)loansRange.Cells[1, 1]).CopyFromRecordset(rds, Type.Missing, Type.Missing);
                rds.Close();
    
                // Format the columns as the appropriate date, rows 40 to 1000
                for (int i = 40; i < 1000; i++)
                {
                    Range dateColumn = (Range)(loansRange.get_Range("A1", "B500"));
                    dateColumn.NumberFormat = "ddd d-MMM-yyyy";
                }
            }
    
            private BookRangeSettings m_xl;
    
            private SqlDataTools m_dataTools;
        }
    }
    Cheers,
    Fred Liu
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2006
    Posts
    142
    Rep Power
    8
    Bump
    Cheers,
    Fred Liu

IMN logo majestic logo threadwatch logo seochat tools logo