RecordSet problem

  1. 3 months ago
    Edited 3 months ago

    Hello,
    I am stuck and don't known while the "temp"-variable in the rs2 while loop only returns the first temp entry (temp I a global variable)

    rs= db.SQLSelect("SELECT * FROM `schedule` WHERE `FromDT` < "+seconds.ToText+"")
    rs2= db.SQLSelect("SELECT * FROM `campaign`")

    If rs <> Nil then //return 2 entries
    While Not rs.EOF
    temp= rs.Field("CampaignID").StringValue
    msgbox temp //showing first entry and after rs.movenext showing second entry, which is right...

    While Not rs2.EOF
    msgbox temp. ///always showing only first entry, if rs2 has e.g. 5 entries, it must be showing 5 times the temp variable from the first rs temp and 5 times the second rs temp, or am I wrong?
    ...
    ...
    rs2.MoveNext
    wend
    rs.MoveNext
    Wend
    rs.Close
    rs2.close
    end if

    any help, please?

    marco

  2. Jean-Yves P

    Apr 13 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 months ago
    msgbox db.errormessage

    one after rs= db... and one after rs2=db....
    what does the msgbox tell you ?

    also
    what are rs.recordcount and rs2.recordcount after the two sqlselect ?

  3. Edited 3 months ago

    hello,

    msgbox db.errormessage is return nothing

    rs is returning 2 (values: 95 and 93) and rs2 is returning 23.

    While Not rs.EOF
    temp= rs.Field("CampaignID").StringValue
    msgbox temp // this is "95" and after rs.movenext it is "93"
    ...

    but in
    While Not rs2.EOF
    msgbox temp // is always only "95"
    ...

  4. @Marco W — First error is that you try to parse RS2 several times. With SQLite, you can use RS2.MoveFirst to reset parsing but it is not allowed for many DB formats. You should parse each RecordSet only once.

  5. Edited 3 months ago

    ok, and how? store the recordset in an array and parse it then?

  6. Jean-Yves P

    Apr 13 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    movenext is allowed everywhere, but don't use movefirst more than once.
    if not possible , make the same select query again.
    or like you said, store the result in an array and use the array as you want.

  7. @Marco W — Just separate your While...Wend loops. Your SQL statement for RS2 does not depend on RS anyway, so their content is independent.

    While not RS.EOF
    ...
    Wend
    
    While not RS2.EOF
    ...
    Wend

    But if your goal is to retrieve the "campaign" value for each matching row in "schedule" based on its "campaignID", then you might consider:
    • Using only one complex SQL statement which does the whole stuff (but I am not qualified for that)
    • Or build a Dictionary from RS2 (key = campaignID, value = campaign) so you can easily determine the "campaign" value for each row in RS

  8. But if your goal is to retrieve the "campaign" value for each matching row in "schedule" based on its "campaignID", then you might consider:

    yes, indeed in need recordsets from the first query to get recordsets from the second query.

    I guess I will store them like you said in a dictionary

    Thanks!

  9. Jay M

    Apr 13 Pre-Release Testers, Xojo Pro NC, USA

    Are you really getting 12 message boxes? The way your code is written I would expect you to only get 7. The rs2 loop only executes once since you don't reset it to the beginning for each rs loop. You need to move the "rs2 = db.SQLSelect...." line to inside the rs loop so that the query is executed for each rs record.

    But, if the two queries are related, then why not make SQL do the work for you? Use a join query to get results from both tables in one query. From what I can glean from your code, the query would probably be something like

    "Select * from schedule join campaign on schedule.CampaignID = campaign.CampaignID
    where schedule.FromDT < " + seconds.ToText

    Then you only need one loop to process the values.

  10. Jean-Yves P

    Apr 13 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    yes like Jay says : use a join query to have one query that does it all.
    what are the tables, what are the datas you want to retrieve ?

or Sign Up to reply!