asked on

Ado recordset search, filter

I am trying to do a select query on an ADO recordset from several tables in my data environment which I am doing using RS.open "Select..." Statement and so far it works. I am now trying to take this recordset and use some sort of RS.filter or rs.find method to find a specific record that meets the filter criteria then go and find another record that meets a second filter criteria and has a field that is the same as the previous record, take these two records and subtract the date fields and create a new reccordset that includes these dates and some other filtered data.
what is the best way to achieve this?
Here is a sample of the code that I am trying to use:

Dim adoRS As ADODB.Recordset
Dim connectString As String
Dim RS1 As Recordset
Dim RS2 As Recordset
Dim RS As Recordset
Dim strcriteria1 As String
Dim strcriteria2 As String

Set adoconn = New ADODB.Connection
Set adoRS = New ADODB.Recordset
Set RS = New ADODB.Recordset
Set RS1 = New Recordset
Set RS2 = New Recordset

connectString = DataEnvironment1.Connection1

adoconn.Open connectString

adoRS.ActiveConnection = adoconn
'DataEnvironment1.Connection1.Open

RS.ActiveConnection = adoconn

RS.Open "SELECT Datevalue(field_log.`log`) AS Log_Date, Timevalue(field_log.`log`) AS Log_Time," _
& " device_brokers.real_hostname AS Device_Broker,devices.device_type AS Device_Type," _
& " property_value_log.val AS Device_Name, faults.nam AS Event,field_log.acknowledged" _
& " FROM devices, field_log, computers, faults, property_value_log," _
& " device_brokers WHERE devices.device_id = field_log.device_id AND" _
& " field_log.computer_id = computers.computer_id AND field_log.field_id = faults.fault_id AND" _
& " devices.device_id = property_value_log.device_id AND field_log.broker_id = device_brokers.broker_id AND" _
& " (field_log.type = 2)", adoconn, adOpenKeyset, adLockOptimistic
Dim strdevice As String
Dim strlogtime As String
Dim strlogtime2 As String

strcriteria1 = "field_log.acknowledged = ""0"""

strcriteria2 = "field_log.acknowledged = ""1"""

Do Until RS.EOF
RS.MoveFirst '
RS.Filter = strcriteria1 ' 1 st filter
strdevice = Property_value_log.Val ' save the device type
strlogtime = field_log.Log
Set RS2 = New Recordset
RS2.AddNew
RS2.Fields.Append "Device", adVarChar, 20
RS2.Fields.Append "Event", adVarChar, 50
RS2.Fields.Append "Fault_Time", adDate
RS2.Fields.Append "Elapsed_Time", adDate
RS.MoveNext
Do Until stcriteria2 & "property_value_log = ""strdevice"""
strlogtime2 = field_log.Log
RS2.AddNew
RS2!device = Property_value_log.Val
RS2!Event = faults.nam
RS2!Fault_Time = field_log.Log
RS2!Elapsed_Time = (strlogtime2 - strlogtime)
Loop

Loop

sql1 = "select * from RS2"
' By device broker
If MainForm.DeviceBrokerCombo.Text <> "All Device Brokers" Then
sql1 = sql1 & " AND device_brokers.hostname = '" & MainForm.DeviceBrokerCombo.Text & "'"
Else
End If

'Set Spreadfrm.fpSpread1.DataSource = Spreadfrm.Adodc1
adoRS.Open sql1, adoconn, adOpenKeyset, adLockOptimistic

'adoRS.Filter = "'Datevalue(command_log.`log`)' = 'Mainform.Devicetypecombo.text'"
Set Spreadfrm.fpSpread1.DataSource = adoRS
Spreadfrm.Adodc1.Visible = False

Spreadfrm.Visible = True
Spreadfrm.Caption = "Device Types"
Spreadfrm.Text1.Text = "System Device Types "
'End If
End Sub

bbekele

ASKER

Edited text of question.

TimCottee

At first glance, the line
'adoRS.Filter = "'Datevalue(command_log.`log`)' = 'Mainform.Devicetypecombo.text'"
should be
adorRS.Filter = "Datevalue(command_Log.'Log') = '" & MainForm.Devicetypecombo.text & "'"

grumpy147

When you want the record that satisfies the second filter criteria, does it also satisfy the first criteria ?

If you open your recordsets with cursortype "adKeySet", then you can create cloned recordsets and the apply the different filter(s) on the clone(s).

Pseudo code follows

Private Sub Clone

Private cn As ADODB.Connection
Private rs1 As ADODB.Recordset
Private rs2 As ADODB.Recordset

Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Password="""";Persist Security Info=True;Data Source=D:\Work\matrix\Transfer\Docs\MTX3.0.0.mdb"
Call cn.Open

Set rs1 = New ADODB.Recordset
Call rs1.Open("select * from filetable", cn, adOpenKeyset, adLockOptimistic)
If ((rs1.BOF) And (rs1.EOF)) Then
Else
set rs2 = rs1.clone
rs1.Filter = strFilter1
rs2.Filter = strFilter2

do until rs1.eof
call rs2.movefirst
do until rs2.eof
'do comparisons / calculations or creation of new 'records/recordsets in here
call rs2.movenext
loop
call rs1.movefirst
loop

End Sub

I do not know if this is what you want; but it may help you on your way.

bbekele

ASKER

My filter will look at the first record, look at field2 then find the next record that has the same field2, then take the field4 of those two records and do a calcultation to find the "elapsed time" ...then generate another recordset that includes the calculated value as a new field.

Ben.

grumpy147

Hi.

Will you be updating the recordset that you are creating ?
How many records from recordset "RS" will you be using
to compare/generate a new recordset - (all or just one) ?

You also need to add to your created recordset those fields
which you want to filter on and insert the relevant values

You can still use the idea of "clones"

set rs1 = cn.open(sqlstring, adopenkeyset)

set rs3 = new adodb.recordset
RS3.Fields.Append "Device", adVarChar, 20
RS3.Fields.Append "Event", adVarChar, 50
RS3.Fields.Append "Fault_Time", adDate
RS3.Fields.Append "Elapsed_Time", adDate

strlogtime2 = field_log.Log
Loop

if( rs1.bof) and(rs1.eof) then
else
set rs2 = rs1.clone

'you may need to set a filter here
call rs1.movefirst
do until rs1.eof
rs2.filter = rs1.fields(2).name & "=" & Chr(39) & rs1.fields(2).value & Chr(39) & " AND " & secondFilter
rs2.movefirst
'check if it is the same record as rs1 points at
'rs2.movenext

        RS3.AddNew
        RS3!device = Property_value_log.Val
        RS3!Event = faults.nam
        RS3!Fault_Time = field_log.Log
        RS3!Elapsed_Time = (rs2.fields(4).value - rs1.fields(4).value)
RS3.Update

call rs1.movenext
loop

'apply a filter to RS3 based on BROKER and COMMAND_LOG
set adodc1.datasource = rs3
end if

set rs2 = nothing
set rs1 = nothing

set

bbekele

ASKER

I will pick the first entry in the table and use that to do the comparison with the rest of the entries in the database until I find a match based on one or two fields. Then I do the elapsed time calculation and enter that record into the new recordset. These two matched records will be deleted or ignored and I will go to the next entry and do a search to find its match and go down the recordset until EOF. I may save the recordset later on but now it just goes to the flexgrid.

Ben.

bbekele

ASKER

bbekele

ASKER

bbekele

ASKER

to Grumpy,
I have tried to us the filter and I keep getting argument are of the wrong type or out of range error..

connectString = DataEnvironment1.Connection1
adoconn.Open connectString

adoRS.ActiveConnection = adoconn
'DataEnvironment1.Connection1.Open

RS.ActiveConnection = adoconn
Debug.Print adoconn
' Get all the records that have faults...type = 2
RS.Open "SELECT Distinct MVP_LOG_FIELD_LOG.log as Date_Time, Datevalue(MVP_LOG_FIELD_LOG.`log`) AS Log_Date, Timevalue(MVP_LOG_FIELD_LOG.`log`) AS Log_Time," _
& " MVP_LOG_DEVICE_BROKERS.real_hostname AS Device_Broker,MVP_LOG_DEVICES.device_type AS Device_Type," _
& " MVP_LOG_PROPERTY_VALUE_LOG.val AS Device_Name, MVP_LOG_FAULTS.nam AS Fault_Event,MVP_LOG_FIELD_LOG.lev as Event_Level" _
& " FROM MVP_LOG_DEVICES, MVP_LOG_FIELD_LOG, MVP_LOG_COMPUTERS, MVP_LOG_FAULTS, MVP_LOG_PROPERTY_VALUE_LOG," _
& " MVP_LOG_DEVICE_BROKERS WHERE MVP_LOG_DEVICES.device_id = MVP_LOG_FIELD_LOG.device_id AND" _
& " MVP_LOG_FIELD_LOG.computer_id = MVP_LOG_COMPUTERS.computer_id AND MVP_LOG_FIELD_LOG.field_id = MVP_LOG_FAULTS.fault_id AND" _
& " MVP_LOG_DEVICES.device_id = MVP_LOG_PROPERTY_VALUE_LOG.device_id AND MVP_LOG_FIELD_LOG.broker_id = MVP_LOG_DEVICE_BROKERS.broker_id AND" _
& " (MVP_LOG_FIELD_LOG.type = 2)and (MVP_LOG_FIELD_LOG.lev <> 0)", adoconn, adOpenKeyset, adLockOptimistic

Dim strdevice 'As strig
Dim strlogtime 'As Variant
Dim strlogtime2 'As Variant
Dim Elapsed_Time 'As Variant
Dim Tot_Elapsed_Time 'As Variant
' create a new recordset to add the new data
Set RS2 = New ADODB.Recordset
RS2.Fields.Append "Device", adVarChar, 20
RS2.Fields.Append "Event_Name", adVarChar, 50
RS2.Fields.Append "Fault_Time", adDate
RS2.Fields.Append "Outage_Time", adDate

If ((RS.BOF) And (RS.EOF)) Then
Else
Set RS1 = RS.Clone ' Clone the first recordset
RS.MoveFirst
RS.Filter = "RS.fields(8).value & " <> " & chr(39)& '0'& chr(39)&"
RS1.Filter = "RS1.fields(8).value & " = " & '0'"
Do Until RS.EOF
RS.MoveFirst
RS1.MoveFirst
' Save the Device name and the date in a variable
strdevice = RS.Fields("Device_Name").Value
strlogtime = RS.Fields("Date_Time").Value
' find a record with same device name and event with a cleared alarm
RS1.Filter = RS1.Fields(5).Name & "=" & Chr(39) & strdevice & Chr(39) & " AND " & RS1.Fields("Date_Time") & "<>" & Chr(39) & strlogtime & Chr(39)
strlogtime2 = RS1.Fields("Date_Time")

'calculate the elapsed time
'Tot_Elapsed_Time = DateDiff(n, strlogtime, strlogtime2)
Tot_Elapsed_Time = strlogtime2 - strlogtime
RS2.AddNew 'add it into the recordset
RS2!Device = RS.Fields("Device_Name")
RS2!Event_Name = RS.Fields("Event_Name")
RS2!Fault_Time = RS.Fields("Date_Time")
RS2!Outage_time = strlogtime2 - strlogtime
RS2.Update

Debug.Print RS1.AbsolutePosition
'RS1.MoveNext
'Loop
RS.MoveNext
Loop

ASKER CERTIFIED SOLUTION

grumpy147

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bbekele

ASKER

Thanks Grumpy . I tried it several ways to get the syntax working but I can not get it. I loaded MDAC 2.1 and that does not seem to help either. I will have to try something else. Thanks for the help. You should get all the points. I have learned a lot...

Thanks,

Ben.

grumpy147

Hi Ben,

Thanks for awarding me the points - sorry I was not able to solve your problem completely.
Let me know when you do figure out a method.

Laurence