[VB6 RC6 SQLite] How to search and retrieve from recordset using RC6 methods?

[webbiz]

My project's main drawing board is a usercontrol (ucStockChart).

In order to DRAW a TrendLine, for example, and to save and later be able to redraw it, I need to save the X coordinate as a Date and the Y coordinate as a Price.

This is because as each new day adds a new data price record, X/Y locations in relation to the price bars will have change.

Therefore, by referencing the Date (for X) and Price (for Y), as the Date and Price shifts to the left with each new data record, having the Date/Price should be enough to locate where to redraw the original TrendLine.


My method in the uc is...

Public Function GetDateFromX(ByVal X As Long) As String


  If Rs Is Nothing Then: Exit Function
  Dim RowIdx As Long
  
  RowIdx = (XViewPort + CC.Surface.Width - (mRMargin * mBarWidth) - X) \ mBarWidth 'convert the passed param from a Mouse-Coord to a RecordIndex
  GetDateFromX = Rs.ValueMatrix(RowIdx, Rs("tDate").IndexInFieldList)


End Function

This method will return the Date based on the X coordinate of the mouse click. I use this successfully to update the Statusbar date panel as I move the mouse left and right.

But now I need to do the reverse. When I retrieve the x1Date and x2Date from JSON/DB storage, I need to convert these to an actual X location to redraw the line.

Public Function GetXFromDate(ByVal dDate As String) As Long
    If Rs Is Nothing Then: Exit Function
    Dim RowIdx As Long
    
    'Locate record containing dDate
    


End Function

I tried Rs.FindFirst() thinking maybe this looks for the first 'whatever' (I passed a string x1Date) and hoped it would locate the record. Then grab the ID from Rs.AbsolutePosition, but I am clearly misusing these methods.

Do I need to execute a SQL query? Or is there a built-in cRecordSet method to locate a record using a known value and having the recordset return another value from the same record it found?

In my GetDateFromX() method it was easy to grab the Date value using ValueMatrix by knowing the Row index. How about the other way around?

TIA

[VanGoghGaming]

RC6 is the path to the dark side, young padawan!

Recordsets have a "Filter" property that can help you find the records you're looking for! This should work, assuming your date field is called "tDate" like in the sample you posted above:

Dim fld as Field
With rs
    .Filter = "tDate='" & dDate & "'"
    If .RecordCount Then
        For Each fld In .Fields
            Debug.Print fld.Name, fld.Value
        Next fld
    End If
    .Filter = adFilterNone
End With

[Schmidt]

RC6 is the path to the dark side...

???
A true statement would be:
Using RC6 will not require you, to rewrite all your existing code,
when the underlying compiler runs on a different platform.

In other words, not using RC6 will tie you to the Windows-platform forever.

Recordsets have a "Filter" property ...

DAO-Recordsets, as well as RC6.cRecordsets don't.

An RC6.cRecordset offers a Boolean-returning FindFirst-function though...

@Webbiz
In your shoes, I'd use the ID-Field, to store the "x-Position" within your "scrollable Chart-area" -
because your current String-based (localized formatted from real Date-Values) Date-Strings are error-prone
(in Sort-, Compare- and Find-Ops) - and because the Dates of consecutive Records allow "gaps"
(which the "increasing ID-Field, the younger the Records gets" does not suffer from).

If Rs.FindFirst("ID=" & StoredID) Then 'found the Record
  RowIdx = Rs.AbsolutePosition - 1 'convert the found position to a zerobased RowIdx
End If

Olaf

[VanGoghGaming]

Haha, it's just a little StarWars humor mate. I remember at one time you vowed not to help the guy anymore but now it seems you have swayed him to the dark side like a true Sith Lord!

In other words, not using RC6 will tie you to the Windows-platform forever.

Not that it's a bad thing, but as far as I gathered, RC6 is an ActiveX DLL so how is that not tied to the Windows platform?

[-Corso->]

Jedi and rebels are galactic waste. TYD
Total Yoda Death.

[webbiz]

???

@Webbiz
In your shoes, I'd use the ID-Field, to store the "x-Position" within your "scrollable Chart-area" -
because your current String-based (localized formatted from real Date-Values) Date-Strings are error-prone
(in Sort-, Compare- and Find-Ops) - and because the Dates of consecutive Records allow "gaps"
(which the "increasing ID-Field, the younger the Records gets" does not suffer from).

If Rs.FindFirst("ID=" & StoredID) Then 'found the Record
  RowIdx = Rs.AbsolutePosition - 1 'convert the found position to a zerobased RowIdx
End If

Olaf

@Schmidt
LOL! I had to read this over several times. "In your shoes.." I was like "what? My shoes?"

But then I realized you meant "If I were in your shoes...". Different regions?


I didn't think of that about the ID. Of course. The ID never changes. Late nights are getting to me.

Thank you for the redirection.

@VanGoghGaming

Too late Master. I've already crossed over. The dark side is strong with this one.