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

bbekeleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bbekeleAuthor Commented:
Edited text of question.
0
TimCotteeHead of Software ServicesCommented:
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 & "'"
0
grumpy147Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

bbekeleAuthor Commented:
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.
0
grumpy147Commented:
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
0
bbekeleAuthor Commented:
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.
0
bbekeleAuthor Commented:
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.
0
bbekeleAuthor Commented:
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.
0
bbekeleAuthor Commented:
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
0
grumpy147Commented:
Hi bbekele,

Sorry, I got my single and double quote ASCII values wrong;
use single quotes (chr(39)) with Date fields
use double quotes (chr(34)) with string fields
use no quotes with numeric fields

I have amended your code below - note this construction for Filter property has only been tested with MS Access

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(34)& "0" & chr(34)

    'GRUMPY - what does this following line do ??
    RS1.Filter = RS1.fields(8).value & " = " chr(34)& 0 & chr(34)    

Do Until RS.EOF
    'GRUMPY - check that field has not already been used
    'GRUMPY - you may also need to put a similar check in the RS1.Filter string
    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 = RS.Fields(5).Name & "=" & Chr(34) & strdevice & Chr(34) & " AND " & RS1.Fields("Date_Time") & "<>" & Chr(39) & strlogtime & Chr(39)

    'GRUMPY - check we found a match
    if( rs1.bof) and (rs1.eof) then
    else
      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
    end if
     
    Debug.Print RS1.AbsolutePosition
   
    'GRUMPY - flag that record in RS has been used

    'GRUMPY - store where we are
    bmk = rs.bookmark
    'GRUMPY - move to record found in rs1
    rs.bookmark = rs1.bookmark
    'GRUMPY - flag the record somehow - set some field to Null ?

    'GRUMPY - go back to where we were
    rs.bookmark = bmk

    RS.MoveNext
Loop


Hope this helps somewhat
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bbekeleAuthor Commented:
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.
0
grumpy147Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.