2. ASPRunnerPro General questions
  3. Select before Listing a page
This topic is locked

Select before Listing a page

 		7/3/2008 5:49:39 AM
ASPRunnerPro General questions
jfr author

Hello,
I have a report which contains about 350 lines.

These are all articles.

These articles have 3 Article groups.
I.e. Services, Products & Combinations.
I would like to offer the user, before the reports lists, a choice;
Services

Products

Combinations

All
I know how to build the SQL around it, but am not familiar enough yet with the Events.
Any help is appreciated.
Thanks,

John
jfr author 7/5/2008

Hello,
Thinking I found the solution with the following post, I started working with this. My code looks like this;

'********** Custom code ************

' put your custom code here
dim rs

FieldName = "Productsoort"

set rs = server.createobject("ADODB.Recordset")

rs.open "Select " & FieldName & " from " & strTableName, dbConnection
Response.Write "<script>" & vbcrlf

Response.Write "function gopage(theLink) { " & vbcrlf

Response.Write "if (document.frmAdmin.theLink.value != '') {" & vbcrlf

Response.Write "location.href = 'Prijslijst_list.asp?a=search&value=1&SearchFor=' + document.frmAdmin.theLink.value + '&SearchOption=Equals&SearchField=" & FieldName & "'; } }" & vbcrlf

Response.Write "</script>" & vbcrlf
Response.Write "<select name='theLink' onchange='gopage(this)'>" & vbcrlf

Response.Write "<option selected value=''></option>" & vbcrlf


while not rs.eof

Response.Write "<option value='" & rs(FieldName)& "'>" & rs(FieldName)& "</option>" & vbcrlf

rs.MoveNext

wend

Response.Write "</select>"


I put this code on the ListOnLoad page.
Although it is doing something, the end result is looking like;


In other words, what am I forgetting.
Thanks a lot,

John
Sergey Kornilov admin 7/5/2008

Instead of

Response.Write "&lt;script>" & vbcrlf


use

Response.Write "<script>" & vbcrlf
jfr author 7/5/2008

Hi Sergey,
Thanks for the prompt answer, I had just copied and pasted the code of the post and changed only the Fieldname and location without having checked the rest. Sorry..
OK, the code itself doesn't give an error message anymore.

'********** Custom code ************

' put your custom code here
dim rs

FieldName = "Productsoort"

set rs = server.createobject("ADODB.Recordset")

rs.open "Select " & FieldName & " from " & strTableName & " group by " & Fieldname , dbConnection
Response.Write "<script>" & vbcrlf

Response.Write "function gopage(theLink) { " & vbcrlf

Response.Write "if (document.frmAdmin.theLink.value != '') {" & vbcrlf

Response.Write "location.href = 'Prijslijst_list.asp?a=search&value=1&SearchFor=' + document.frmAdmin.theLink.value + '&SearchOption=Equals&SearchField=" & FieldName & "'; } }" & vbcrlf

Response.Write "</script>" & vbcrlf
Response.Write "<select name='theLink' onchange='gopage(this)'>" & vbcrlf

Response.Write "<option selected value=''></option>" & vbcrlf


while not rs.eof

Response.Write "<option value='" & rs(FieldName)& "'>" & rs(FieldName)& "</option>" & vbcrlf

rs.MoveNext

wend

Response.Write "</select>"


The page looks like this afterwards, where the Choose shows in the left upper corner.


And maybe I am completely misunderstanding, I click on a groep, but nothing happens.
Once more thx for the help.
John
jfr author 7/7/2008

Solved <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=30831&image=1&table=forumreplies' class='bbc_emoticon' alt=':rolleyes:' />
I had left also the original code in, so that it doubled up.

Took out original, replaced with snipped, and like sunshine it works.
John
jfr author 7/8/2008

It's still working, but I want to add one additional SQL comment.
SQL looks like

SELECT Productsoort

FROM Prijslijst

WHERE (Productsoort <> N'Inactieve producten')

GROUP BY Productsoort


I tried the following in the events, but that didn't go through;

'********** Custom code ************

' put your custom code here
dim rs

FieldName = "Productsoort"

set rs = server.createobject("ADODB.Recordset")

rs.open "Select " & FieldName & " from " & strTableName & " where " & Fieldname & "<> "Inactieve Producten" " & " group by " & Fieldname , dbConnection
Response.Write "<script>" & vbcrlf

Response.Write "function gopage(theLink) { " & vbcrlf

Response.Write "if (document.frmAdmin.theLink.value != '') {" & vbcrlf

Response.Write "location.href = 'Prijslijst_list.asp?a=search&value=1&SearchFor=' + document.frmAdmin.theLink.value + '&SearchOption=Equals&SearchField=" & FieldName & "'; } }" & vbcrlf

Response.Write "</script>" & vbcrlf
Response.Write "<select name='theLink' onchange='gopage(this)'>" & vbcrlf

Response.Write "<option selected value=''></option>" & vbcrlf


while not rs.eof

Response.Write "<option value='" & rs(FieldName)& "'>" & rs(FieldName)& "</option>" & vbcrlf

rs.MoveNext

wend

Response.Write "</select>"


I am sure that I am forgetting something, but what?
Thx a lot,

John
J
 		Jane 7/9/2008

Hi,
try to use following query:

...

rs.open "Select " & FieldName & " from " & strTableName & " where " & Fieldname & "<>""Inactieve Producten"" " & " group by " & Fieldname , dbConnection

...
jfr author 7/9/2008

Hi Jane,
That didn't do it.
John
J
 		Jane 7/9/2008

To debug code print all queries before executing:

str = "Select " & FieldName & " from " & strTableName & " where " & Fieldname & "<>""Inactieve Producten"" " & " group by " & Fieldname

response.write str
jfr author 7/9/2008

Jane,
This is the problem, it's looking for the column name Inactieve producten.

This where it should look for the column name Productsoort.


P.s. I tried;

rs.open "Select " & FieldName & " from " & strTableName & " where " & "dbo.Prijslijst.Productsoort" & "<> ""Inactieve Producten"" " & " group by " & Fieldname , dbConnection



Gave the same error.
We're getting further <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=30921&image=2&table=forumreplies' class='bbc_emoticon' alt=':)' />
John
J
 		Jane 7/10/2008

Sorry for my fault.

Here is the correct code:

rs.open "Select " & FieldName & " from " & strTableName & " where " & "dbo.Prijslijst.Productsoort" & "<> 'Inactieve Producten' " & " group by " & Fieldname , dbConnection
jfr author 7/10/2008

Great Jane, works!!
Thx,

John
jfr author 7/10/2008

Now that this works, I want more <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=30945&image=1&table=forumreplies' class='bbc_emoticon' alt=':P' />
Would it be possible to integrate this select also into a Report?
I can hardly imagine it is not possible.
Thanks a lot,
John