0 Replies Latest reply: Jul 12, 2013 11:11 AM by Octopus Rex RSS

    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
              Try
                  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)
                  ConnectionOracle.Open()
                  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
                  Else
                      MsgBox("Não existem dados", vbOKOnly)
                      Exit Sub
                  End If
              Catch ex As Exception
                  MsgBox(ex.Message).ToString()
              End Try
              Me.Hide()
          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.

      Octavio