Error in VBA fetching the recordset form Oracle

[PreetiB]

I am using VBA macro to fetch data from oracle and getting the error

"Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record."

I am able to fetch the column names but data is not displayed

Following is the code:

Public fromdate As Date
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim prm1 As ADODB.Parameter
Dim prm2 As ADODB.Parameter

Dim cmdTrans As New ADODB.Command
Dim col As Integer
Dim row As Integer
Dim strConn As String
Public todate As Date
Public Sub Analyze()

''''Input date from user
UserForm1.Show

strConn = "User ID=UserID ; Password=pswd ; Data Source=dsn_name ; Provider=provider"
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.CursorLocation = adUseServer
conn.Open strConn

Set cmdTrans.ActiveConnection = conn
Set cmdTrans = New ADODB.Command
cmdTrans.CommandText = "SELECT d.STAT_ID,process_name,message_name,from_app,to_app,sum(count)from eai_stats_detail d,eai_stats_master m where d.stat_id=m.id and d.start_time between to_date(?,'dd-Mon-yyyy') and to_date(?,'dd-Mon-yyyy') group by stat_id,process_name,message_name,from_app,to_app"
cmdTrans.CommandType = adCmdText
cmdTrans.Prepared = True


Set prm1 = cmdTrans.CreateParameter("From_Date", adDate, adParamInput, 10, fromdate)
cmdTrans.Parameters.Append prm1
Set prm2 = cmdTrans.CreateParameter("To_Date", adDate, adParamInput, 10, todate)
cmdTrans.Parameters.Append prm2
rs.ActiveConnection = conn
cmdTrans.ActiveConnection = conn
Set rs = cmdTrans.Execute

col = 0
'First Row: names of columns
Do While col < rs.Fields.Count
Cells(1, col + 1) = rs.Fields(col).Name
col = col + 1
Loop

'Now actual data as fetched from select statement
row = 1
Do Until rs.EOF
row = row + 1
col = 0
While col < rs.Fields.Count
Cells(row, col + 1) = rs.Fields(col).Value
col = col + 1
Wend
row = row + 1
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
End Sub


UserForm is used to get date from user.Here is the code for form:

Private Sub Calendar1_Click()
TextBox1.Value = Calendar1.Value
End Sub
Private Sub Calendar3_Click()
TextBox2.Value = Calendar3.Value
End Sub
Public Sub GetData_Click()
Dim Start_date As Date
Dim End_date As Date
Start_date = Trim(TextBox1.Text)
End_date = Trim(TextBox2.Text)
fromdate = Format(Start_date, "yyyy/mm/dd")
todate = Format(End_date, "yyyy/mm/dd")
Me.Hide
End Sub

Private Sub UserForm_Initialize()
Set cmdTrans = New ADODB.Command
If IsDate(TextBox1.Value) Then
Calendar1.Value = DateValue(TextBox1.Value)
Else
Calendar1.Value = Date
End If
If IsDate(TextBox2.Value) Then
Calendar3.Value = DateValue(TextBox2.Value)
Else
Calendar3.Value = Date
End If
End Sub

Please suggest me the solution for this error.I have to submit tomorrow to my manager.

 

[Stormseed]

Did you check out Google ?

http://www.google.co.in/search?hl=e...ted+operation+requires+a+current+record&meta=

 

[PreetiB]

Yes..i chkd out..but cud not find a solution...is there any problem in this code??

 

[Blade Hunter]

That looks like a lot of code to pull some data from the DB.

Something along the line of this should work:

Public fromdate As Date
Public todate As Date
Public Sub Analyze()
''''Input date from user
UserForm1.Show
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim sqlText As String
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim P As Long
    Call Get_User
    Set Data = Sheets("DataMonth") 'This is the name of the sheet you want to return your results to
    Data.Select
    Cells.ClearContents
    Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=dsn_name;USER ID=UserID;PASSWORD=pswd"
    Cmd.ActiveConnection = Conn
    Cmd.CommandType = adCmdText
    sqlText = "SELECT d.STAT_ID,process_name,message_name,from_app,to_app,sum(count)from eai_stats_detail d,"
    sqlText = sqlText & " eai_stats_master m where d.stat_id=m.id and d.start_time between to_date(?,'dd-Mon-yyyy')"
    sqlText = sqlText & " and to_date(?,'dd-Mon-yyyy') group by stat_id,process_name,message_name,from_app,to_app"
    Cmd.CommandText = sqlText
    Set RS = Cmd.Execute
    For P = 0 To 1 ' This should equal number of columns being returned minus 1
        Data.Cells(1, P + 1) = RS.Fields(P).Name
    Next
    Do While Not RS.EOF
       Row = Row + 1
       For Findex = 0 To RS.Fields.Count - 1
         Data.Cells(Row + 1, Findex + 1) = RS.Fields(Findex).Value
       Next Findex
       RS.MoveNext
    Loop
End Sub

You will need to change:

Sheets("DataMonth") 'This is the name of the sheet you want to return your results to

to the sheet you want to return your results to.

Also, make sure your ODBC driver is configured

Edit: Oh Silly me, I missed this one. the Question marks in your PL/SQL code. Just close your quotes and ampersand in the date direct from the form.

Cheers

Dan

 

[Stormseed]

Madam, you would not get or receive exact answers to your problem if you perform a Search. If any member on this board knows and if he comes across your question then it may get answered exactly. However, till that moment of happiness you can try different ways to resolve the issue at your end...like this:

http://www.google.co.in/search?hl=e...res+a+current+record+-+Oracle+VBA+Error&meta=

 

[Blade Hunter]

Sorry PreetiB, I got trigger happy and didn't read your verification part. So you are trying to update a record set and need to verify your written data and also make sure no dupes are parsed in correct?

I have to leave work now but if this is unanswered tomorrow I will try and help you out.

 

[PreetiB]

I am trying to fetch the records from database and user is inputting the date through a form.

 

[Blade Hunter]

Oh in that case the code I posted before should work for you.

Cheers

Dan