0 Replies Latest reply on Jul 12, 2013 4:11 PM by Octopus Rex

    Query results are not written into worksheet

    Octopus Rex

      I tried to find a better place to pose this question but i could not find one. I'm developing an Excel 2007 Comm Addin with the following code:




      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
                  Dim ConnectionString As String
                  Dim OracleCommand As New OracleCommand
                  Dim OraLeitor As OracleDataReader
                  Dim xl As Microsoft.Office.Interop.Excel.Application
                  xl = New Microsoft.Office.Interop.Excel.Application
                  Dim wBook As Microsoft.Office.Interop.Excel.Workbook
                  wBook = xl.Workbooks.Add
                  Dim wSheet As Microsoft.Office.Interop.Excel.Worksheet
                  Dim i As Integer = 1
                  ConnectionString = "User Id = APPS;Password = APPS; Data Source = PRODL"
                  Dim ConnectionOracle As New OracleConnection(ConnectionString)
                  OracleCommand.Connection = ConnectionOracle
                  OracleCommand.CommandText = "SELECT i.invoice_date, i.description," _
                    & " SUM (alb.accounted_cr) - SUM (alb.accounted_dr) remaining_amount" _
                    & " FROM   ap_liability_balance alb, ap_invoices_all i" _
                    & " WHERE i.vendor_id =19241 AND alb.ORG_ID =  155  and 1=1" _
                    & " AND trunc(alb.accounting_date) <= :P1 and i.invoice_id = alb.invoice_id" _
                    & " GROUP BY  i.description, i.invoice_date" _
                    & " HAVING SUM(accounted_cr) <> SUM(accounted_dr)"
                  OracleCommand.CommandType = Data.CommandType.Text
                  OracleCommand.Parameters.Add("P1", Me.DateTimePicker1.Value.Date)
                  OraLeitor = OracleCommand.ExecuteReader()
                  If OraLeitor.HasRows() Then
                      wSheet = wBook.ActiveSheet
                      While OraLeitor.Read()
                          wSheet.Cells(i, 1) = OraLeitor("INVOICE_DATE")
                          wSheet.Cells(i, 2) = OraLeitor("DESCRIPTION")
                          wSheet.Cells(i, 3) = OraLeitor("REMAINING_AMOUNT")
                          i = i + 1
                      End While
                      MsgBox("Não existem dados", vbOKOnly)
                      Exit Sub
                  End If
              Catch ex As Exception
              End Try
          End Sub


      The code works fine, no errors occur. However, the results (and i know the query is returning data) are not written into Sheet1 of the open Worksheet.

      Can someone please let me know what am i doing wrong?

      Thanks a lot in adavnce for any kind help.