Recordset no me devuelve datos.
N@ska
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.
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
Si tienes problema avÃsame.
www.kognos.com.mx/foro
N@ska
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
www.kognos.com.mx/foro