How to filter recordset with two strings/array

Hi.
First of all - I have not been working to much with strings and arrays likt this.
I am trying to filter a recordset with two WHERE conditions that get their values from two strings based on two different recordsets.

I am trying to make this as clear as possible - but fint it difficult so I will try as best as possible.

The situation is like this:

I have e table containing ut to five values (school lessons) based on how many lessons that was selected in the form before.

Like this:
Math  - selected
History - selected
Science - not selected
English - selected
Computer - not selected

The result is like this:
Math - options(1, 2, 3, 4)
History - options(1, 2, 3, 4, 5, 6, 7)
English - options(1, 2, 3)

What I now want to do is to send the selected options value with the lessions value together and filter the next table(recordset) based on the selected lessions and selected options like this:
Recordset shows:
Math - option 2 - values 1, 2, 3, 4
History - option 4 - values 1, 2, 3, 4, 5, 6
English - option 3 - values 1, 2

I am sorry if I havent been clear enough on the problem - please ask me if there is anything not clear enough

Thanks in advace
*********The recordset I want to show at last:**********

<%
Dim merketfag, trinnmerket
merketfag = "0"
if Request.Form("lmamerket_fag") <> "" Then
merketfag = Request.Form("lmamerket_fag")
End if
trinnmerket = "0"
if Request.Form("lmatrinn_velg") <> "" Then
trinnmerket = Request.Form("lmatrinn_velg")
End if
%>
<% Response.Write(Request.Form("lmamerket_fag")) %> 
*** This returns values like 1, 2, 3
<% Response.Write(Request.Form("lmatrinn_velg")) %>
*** This returns values like 1, 2, 3
<% 
Dim Recordset9
Dim Recordset9_cmd
Dim Recordset9_numRows

Set Recordset9_cmd = Server.CreateObject ("ADODB.Command")
Recordset9_cmd.ActiveConnection = MM_JegKanMal_STRING
Recordset9_cmd.CommandText = "SELECT jegkan_id, jegkanfag, jegkantrinn, jegkannr, jegkantekst FROM chlaas_medklin.db_jegkan WHERE jegkanfag IN ("&merketfag&") AND jegkantrinn IN ("&trinnmerket&")"
Recordset9_cmd.Prepared = true

Set Recordset9 = Recordset9_cmd.Execute
Recordset9_numRows = 0
%>



******* The code in the body ********

<input type="hidden" name="lmamerket_fag" id="lmamerket_fag" value="<%=(Recordset8.Fields.Item("vurderingfag_id").Value)%>" />

<select name="lmatrinn_velg" id="lmatrinn_velg">
                    <%
While (NOT Recordset3.EOF)
%><option value="<%=(Recordset3.Fields.Item("jegkan_trinn").Value)%>"><%=(Recordset3.Fields.Item("jegkan_trinn").Value)%></option>

***** The code in the body are inside a repeated region ***

Open in new window

chlaasAsked:
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.

chlaasAuthor Commented:
Sorry, forgot to say one thing. When I now use this code the result is like this:


Math - option 2 - values 1, 2, 3, 4 - option 4 - values 1, 2, 3, 4, 5, 6 option 3 - values 1, 2
History option 2 - values 1, 2, 3, 4 - option 4 - values 1, 2, 3, 4, 5, 6 option 3 - values 1, 2
English option 2 - values 1, 2, 3, 4 - option 4 - values 1, 2, 3, 4, 5, 6 option 3 - values 1, 2

I also upload some pictures to show the pages.

screen-capture-4.png
screen-capture-5.png
0
Arthur_WoodCommented:
In this line

Recordset9_cmd.CommandText = "SELECT jegkan_id, jegkanfag, jegkantrinn, jegkannr, jegkantekst FROM chlaas_medklin.db_jegkan WHERE jegkanfag IN ("&merketfag&") AND jegkantrinn IN ("&trinnmerket&")"

Is the value of merketfag (or trinnmerket)  a single number or a set of several numbers, separated by commas?

AW
0
chlaasAuthor Commented:
If you are reffering to ("&merketfag&") and ("&trinnmerket&") they are in most cases several numbers like this 2, 5, 1, 4 and an equal amount of numbers in trinnmerket - 3, 6, 2, 5

In some cases there will only be one number in both merketfag and trinnmerket.

The amount of numbers are always equal.
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!

Arthur_WoodCommented:
What do the values for "&merketfag&" represent?  Why are there multiple values?

I am still confused about what you want the page to present.

AW
0
chlaasAuthor Commented:
Ok...
Sorry for not being precise enough.

The values of merketfag - represents the lessons from a table -  like - 1 is English, 2 is History etc...
from one table.

The values of trinnmerket represents the different options for each lesson - like - 1 - step 1, 2 - step 2 etc..

But the problem is that each step in trinnmerket also represent many exercises under each step - like 1-step 1 contains exercise 1, 2, 3, 4, 5, 6   etc.

The goal is that I want it to be able to select lessons - you can se how in the first picture - and then select witch steps they want to se in each lesson - as shown in picture 2 - then finally they get to the page where the exercises are shown as according to the lessons and steps like this-
***
English - step 2 - exercise 1
English - step 2 - exercise 2
English - step 2 - exercise 3
History - step 5 - exercise 1
History - step 5 - exercise 2
History - step 5 - exercise 3
History - step 5 - exercise 4
Maths - step 3 - exercise 1
Maths - step 3 - exercise 2
*******
Then they can select the exercises they want with checkboxes and submit the page that results in a new page loading with a report over the exercises they have chosen that will automaticly print.

I am so sorry for not being precise enough - I hope this was clearer.

Thanks for your attention.
0
Arthur_WoodCommented:
try writing your line like this:

Recordset9_cmd.CommandText = "SELECT jegkan_id, jegkanfag,  jegkantrinn, jegkannr, jegkantekst FROM chlaas_medklin.db_jegkan WHERE  jegkanfag IN (" & merketfag & ") AND jegkantrinn IN  (" & trinnmerket & ")"

note the blank spaces around the & signs.  The blanks here ARE important

AW
0
Arthur_WoodCommented:
However, I must point out that the way you are going at this is probably not going to get you what you want

you will get ALL the lessons that are included in ANY combination of the values in the two sets, the selection does NOT pair up the values,

SO you will get

2 -> 3
2 - > 6
2 -> 2
2 -> 5
5 -> 3
 5 - > 6
 5 -> 2
 5 -> 5
1 -> 3
 1 - > 6
 1 -> 2
 1 -> 5
4 -> 3
 4 - > 6
 4 -> 2
 4 -> 5

I suspect that is not what you want to get.

AW
0
chlaasAuthor Commented:
Ok

I got the result you said. Is there any other way to get the result i want to??
0
Arthur_WoodCommented:
You cannot get the result set you want by allowing the user to select all of the lesson, and all of the options at the same time.


One way would be:

You should select a lesson, and then the options for that lesson.  Then select the next lesson, and the options for THAT lesson, and so on.  Each time a lesson has been selected, do not allow that same lesson to be selected again.

AW
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
chlaasAuthor Commented:
Thank you a lot for your help.
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.