• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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

0
chlaas
Asked:
chlaas
  • 5
  • 5
1 Solution
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now