Recordset no me devuelve datos.

[email protected]
05 de Abril del 2005
Buenas, tengo un problemilla, ya que al realizar una conexion contra una bd en oracle, no me devuelve datos (y si los hay).

Os dejo mi codigo para ver si veis donde esta el fallo, o que estoy haciendo mal.

Muchas gracias, antes de nada..

///////////////////////////////////////////////////////////////////////////////////////////



'Dim DB As New ADODB.Connection
Dim DsnPUB As New ADODB.Connection
Dim DsnDST As New ADODB.Connection
Dim DsnBOB As New ADODB.Connection

Dim RS As New ADODB.Recordset
Dim RS_COUNT As New ADODB.Recordset
Dim RS2 As New ADODB.Recordset
Dim RS2_COUNT As New ADODB.Recordset
Dim RS3 As New ADODB.Recordset
Dim RS3_COUNT As New ADODB.Recordset
Dim RS1 As New ADODB.Recordset
Dim RS1_COUNT As New ADODB.Recordset
'MAKE THE REFERENCE TO microsoft excel object 9.0 library

Dim EXAP As Excel.Application
Dim eX_book As Excel.Workbook
Dim i, j, k, Y As Integer
Dim nRegistros As Integer
Dim nCampos As Integer
Dim t, d, m, a As Integer
Dim Fecha As Date

Private Sub Command1_Click()

On erro GoTo Fallo
'CREATE AN OBJECT OF THE COM APPLICATION
Set EXAP = CreateObject("Excel.Application")

Set eX_book = EXAP.Workbooks.Open(App.Path & "" & "PlantillaConsumosCPU-UNIX.xls")

EXAP.Visible = True
'Select the Sheet
EXAP.Sheets("DATA").Select
'Set eX_book = EXAP.Workbooks.Add

'SPOT_US_US -----------------------------------------------------------
EXAP.Cells(1, 2) = "SPOT_US"

EXAP.Cells(2, 2) = "HORA"
EXAP.Cells(3, 2) = "Usuarios" 'DataGrid1.Columns(1).Caption
EXAP.Cells(4, 2) = "Sesiones" 'DataGrid1.Columns(0).Caption

MsgBox (RS_COUNT.EOF)
MsgBox (RS_COUNT.BOF)


nRegistros = RS_COUNT.Fields(0).Value + 2
nCampos = RS.Fields.Count + 1

If chkSPOT.Value = "1" And RS.BOF = False Then

RS.MoveFirst
For i = 3 To nRegistros
k = nCampos - 2
Y = 2
For j = 2 To nCampos

EXAP.Cells(Y, i) = RS.Fields(k).Value
k = k - 1
Y = Y + 1
Next
RS.MoveNext

Next

Else
EXAP.Cells(3, 3) = "NADA " & chkSPOT.Value

End If

'DIST ---------------------------------------------------------
EXAP.Cells(8, 2) = "DIST"

EXAP.Cells(9, 2) = "HORA" 'DataGrid2.Columns(0).Caption
EXAP.Cells(10, 2) = "Usuarios" 'DataGrid2.Columns(1).Caption
EXAP.Cells(11, 2) = "Sesiones"


If chkDIST.Value = "1" And RS2.BOF = False Then

RS2.MoveFirst
nRegistros = RS2_COUNT.Fields(0).Value + 2
nCampos = RS2.Fields.Count + 1

For i = 3 To nRegistros
k = nCampos - 2
Y = 9 'hay que poner donde tiene que empezar a poner datos
For j = 2 To nCampos

EXAP.Cells(Y, i) = RS2.Fields(k).Value
k = k - 1
Y = Y + 1
Next
RS2.MoveNext

Next
Else
EXAP.Cells(9, 3) = "NADA " & chkDIST.Value
End If


'BOB ---------------------------------------------------------
EXAP.Cells(15, 2) = "BOB"

EXAP.Cells(16, 2) = "HORA" 'DataGrid2.Columns(0).Caption
EXAP.Cells(17, 2) = "Usuarios" 'DataGrid2.Columns(1).Caption
EXAP.Cells(18, 2) = "Sesiones"


If chkBOB.Value = "1" And RS3.BOF = False Then

RS3.MoveFirst
nRegistros = RS3_COUNT.Fields(0).Value + 2
nCampos = RS3.Fields.Count + 1

For i = 3 To nRegistros
k = nCampos - 2
Y = 16 'hay que poner donde tiene que empezar a poner datos
For j = 2 To nCampos

EXAP.Cells(Y, i) = RS3.Fields(k).Value
k = k - 1
Y = Y + 1
Next
RS3.MoveNext

Next
Else
EXAP.Cells(16, 3) = "NADA " & chkBOB.Value
End If

'SET THE RANGE MANUALLY ACCORDING TO YOUR OUTPUT
'ESTILO DE LA HOJA EN DATA
EXAP.Range("B1:Z4").AutoFormat xlRangeAutoFormatClassic2

EXAP.Range("B8:Z11").AutoFormat xlRangeAutoFormatClassic2

EXAP.Range("B15:Z18").AutoFormat xlRangeAutoFormatClassic2

EXAP.Range("B22:Z25").AutoFormat xlRangeAutoFormatClassic2

't = Str(Date - 1)
'd = Mid(t, 1, 2)
'm = Mid(t, 4, 2)
'a = Mid(t, 7, 4)


EXAP.ActiveWorkbook.SaveAs (App.Path & "" & "ConsumoCpu_.xls")
'EXAP.Application.Quit

'EXAP.Visible = True

Fallo:

MsgBox ("Fallo : " & Err.Description)

'Resume salir

End Sub



Private Sub Form_Load()

DsnPUB.Open "Provider=OraOLEDB.Oracle.1;Password=monitor;Persist Security Info=True;User ID=monitor;Data Source=pub02"

DsnBOB.Open "Provider=OraOLEDB.Oracle.1;Password=monitor;Persist Security Info=True;User ID=monitor;Data Source=bob"

DsnDST.Open "Provider=OraOLEDB.Oracle.1;Password=monitor;Persist Security Info=True;User ID=monitor;Data Source=distp"

RS.CursorLocation = adUseClient
RS2.CursorLocation = adUseClient
RS3.CursorLocation = adUseClient



Dim Sql1 As String
Dim Sql2 As String

Sql1 = "SELECT * from MONITOR.USUARIOS Where hora < TO_CHAR(sysdate, 'DD/MM/YYYY') And hora > TO_CHAR(sysdate -1, 'DD/MM/YYYY')"
Sql2 = "SELECT COUNT(*) from monitor.usuarios Where hora < TO_CHAR(sysdate, 'DD/MM/YYYY') And hora > TO_CHAR(sysdate -1, 'DD/MM/YYYY')"


'SPOT----------------------------------------------------------------------
'Sql1 = "SELECT SPOT_US.* From SPOT_US Where hora < date() And hora > date() - 1 order by hora ASC"
RS.Open Sql1, DsnPUB, adOpenStatic, adLockReadOnly
'RS.Open Sql1, DsnPUB, adOpenDynamic, adLockOptimistic
'Sql2 = "SELECT COUNT(*) from SPOT_US Where hora < date() And hora > date() - 1"

RS_COUNT.Open Sql2, DsnPUB, adOpenStatic, adLockReadOnly

'DIST-------------------------------------------------------------------------------

'Sql1 = "SELECT DIST_US.* From DIST_US Where hora < date() And hora > date() - 1 order by hora ASC"

RS2.Open Sql1, DsnDST, adOpenStatic, adLockReadOnly
'Sql2 = "SELECT COUNT(*) from DIST_US Where hora < date() And hora > date() - 1"

RS2_COUNT.Open Sql2, DsnDST, adOpenStatic, adLockReadOnly
'---------------------------------------------------------------------

'BOB-------------------------------------------------------------------------------

'Sql1 = "SELECT BOB_US.* From BOB_US Where hora < date() And hora > date() - 1 order by hora ASC"

RS3.Open Sql1, DsnBOB, adOpenStatic, adLockReadOnly
'Sql2 = "SELECT COUNT(*) from BOB_US Where hora < date() And hora > date() - 1"

RS3_COUNT.Open Sql2, DsnBOB, adOpenStatic, adLockReadOnly

End Sub

Private Sub Form_Unload(Cancel As Integer)
RS.Close
RS2.Close
RS3.Close
DsnBOB.Close
DsnDST.Close
DsnPUB.Close

End Sub

//////////////////////////////////////////////////
Lo que hace esto es coger datos de oracle, y luego pasarlo a una hoja excel (sencillo), pero la cosa es que no me devuelve datos.

Gracias.

Alejandro Tello Busquets
05 de Abril del 2005
Hola, no revisé tu código a detalle, pero me aventuro a decir que el problema son tus consultas, me gustaría saber si ya las probaste en el sqlplus y qué resultado te dan.

Si tienes problema avísame.
www.kognos.com.mx/foro

[email protected]
05 de Abril del 2005
Si, en sqlplus funcionan,..y me dan valores en las dos sqls..no se q puede ser..no paro de mirarlo y no encuentro el fallo,...

Alejandro Tello
05 de Abril del 2005
Hola, en mi foro, existe una sección de oracle, te podría pedir que subas ahí tus consultas para que las emule en mi base de datos. Creo que el problema son los rangos en el where, están invertidos.
www.kognos.com.mx/foro