RecordSet problem

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. [b]///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?[/b]

end if

any help, please?


msgbox db.errormessage

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

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


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”

@Marco Winster — 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.

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

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.

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

[code]While not RS.EOF


While not RS2.EOF


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

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


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.

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 ?