asked on

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

chlaas

ASKER

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

Arthur_Wood

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

chlaas

ASKER

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.

Arthur_Wood

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

chlaas

ASKER

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.

Arthur_Wood

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

Arthur_Wood

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

chlaas

ASKER

Ok

I got the result you said. Is there any other way to get the result i want to??

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chlaas

ASKER

Thank you a lot for your help.