asp jump menu

[puppetnd]

hi im trying to learn more about forms in asp and would like to know if anyone knows how i can tell a jump menu to change a variable if an option is selected instead of going to a page?
i need to select an option from a list which will change the WHERE statement in my sql so i can choose to look for, say for example Artists, Paintings, Prices, etc from my access database. kind of like the menu at the top of this website where u can choose find a forum or search post and then type in a key word.

thanks

 

[Jeremy21b]

As far as I know it is not possible to change the variable on the fly. An ASP page is processed on the server and sent to the browser as an html file. So to change your SQL query, you'd really have to reload the page so that the server can process the new sql squery. We do exactly what you're looking to achieve on our own website. For us it is not feasible to preload all the possible outcomes of choosing different menu options. Most websites that have two menus that seem to work dynamically together preload all the information. What we do is submit the form to the current page once the select menu changes. If you do this, be wary of what page you autosubmit a form on. Reloading the page so often can use up a lot of bandwidth.

 

[snowboardr]

Well you are going to have to reload the page... so just reload it with a querystring id that maches your category

ie: mypage.asp?cat_id=12


On your drop down you would put this:

<select name="frmSelect" onChange="self.location='mypage.asp?cat_id='+this.options[this.selectedIndex].value">

<option value="12">Painter</option>

etc...


Hope this helps
Jason

www.sitesd.com
ASP WEB DEVELOPMENT

 

[puppetnd]

thanks for the replys but would i have to change the "where" part? its still looking in artist.


WHERE Artist LIKE '%" & searchItem & "%'

search item is just a variable from the text input

 

[snowboardr]

Well you could do this:

Dim queryCategory    '# Category cat_id querystring
Dim sSelectedTable   '# the table to use
Dim sDoRunDatabase   '# true/false run database conn

sDoRunDatabase = True


Select Case queryCategory
   Case "1"
     sSelectedTable = "Artist"
   
   Case "2"
     sSelectedTable = "Painter"

   Case "3"
      sDoRunDatabase = False
 
End Select



If sDoRunDatabase = True
   '# Run db connection etc..
    
     sql = Select column_name FROM table_name WHERE " &sSelectedTable & " LIKE '%" & searchItem & "%'" 
    
      Else
       Response.Write "Invalid category id"

End If

www.sitesd.com
ASP WEB DEVELOPMENT

 

[puppetnd]

ok thanks, so it should look like this?

<%@LANGUAGE="VBSCRIPT"%>
<%
Option Explicit
Response.Expires = 0
Dim conn, SQL, RS, searchItem, DBQ, myScript, queryCategory, sSelectedTable , sDoRunDatabase
%>

<%sDoRunDatabase = true%>

<%
Send "<form method=get action='"& myScript &"'>"
Send "<input type=text name=SEARCH value=Search>"
Send "<select name=SELECTOR onChange=self.location='mypage.asp?cat_id='+this.options[this.selectedIndex].value>"
send "<option>--</option>"
send "<option value=?search=%25>All</option>"
Send "<option value=1>Artist</option>"
Send "<option value=2>Painting</option>"
Send "</select>"
Send "<input type=submit name=submit value=submit></form>"
%>

<%
Select Case querycategory
Case "1"
sSelectedTable = "Artist"
Case "2"
sSelectedTable = "Painting"
Case "3"
sDoRunDatabase = false
End Select
%>

<%SearchItem = trim(request.querystring("SEARCH"))%>
<% IF len(searchItem) = 0 THEN
myScript = Request.ServerVariables("SCRIPT_NAME")


ELSE

Set conn = Server.CreateObject("ADODB.Connection")
DBQ = server.mappath("db1.mdb")
conn.Open = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBQ
SQL="SELECT Title,Painting,Image FROM table1"
SQL=SQL & " WHERE '"&sSelectedTable&"%' LIKE '%"&searchItem&"%' "
SQL=SQL & " ORDER BY Artist"
Set RS = conn.execute(SQL)
Send "<table cellpadding=2>"
WHILE not RS.eof
Send "<tr><td><img src =images/" & RS("Image") & " width=130 height=130></img></td>"
send "<td bgcolor=#D6DBEF width=470>&nbsp;<B><font face=Arial, Helvetica, sans-serif>" & RS ("Painting") & "</b>"
send "<br>&nbsp;" & RS ("Artist") & "</b>"

RS.MoveNext
Wend
Send "</table>"%>

<%RS.close : Set RS = Nothing : conn.close : Set conn = Nothing

End if %>

<%Sub send (html) : response.write html & vbCRLF : end sub %>

sorry about the length of code

 

[snowboardr]

Yes accept you forgot to check if sDoRunDatabase = True if it is false then you will have an error because your Id number didnt match a category in your select case...



www.sitesd.com
ASP WEB DEVELOPMENT

 

[puppetnd]

could i put it with the other IF statement like this?

<% IF len(searchItem) = 0 & sDoRunDatabase = True THEN

 

[snowboardr]

Ya thats fine just dont run the db connection if the value is false because that means the users did something else to the querystring ie: it went to this part of the code:

Ooops

i had a mistake in the code it should be this:

Select Case querycategory
Case "1"
sSelectedTable = "Artist"
Case "2"
sSelectedTable = "Painting"
Case Else
sDoRunDatabase = false
End Select


Case else means if they choose another querystring

other then cat_id=1 or cat_id=2 it will not run the db conenction...

sorry about that.

www.sitesd.com
ASP WEB DEVELOPMENT

 

[puppetnd]

i did it like this but it says page could not be found when i choose an item in the drop down

<%SearchItem = trim(request.querystring("SEARCH"))%>
<% IF len(searchItem) = 0 THEN
myScript = Request.ServerVariables("SCRIPT_NAME")


ELSE
IF sDoRunDatabase = True THEN

Set conn = Server.CreateObject("ADODB.Connection")
DBQ = server.mappath("db1.mdb")
conn.Open = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & DBQ
SQL="SELECT Painting,Artist,Image FROM table1"
SQL=SQL & " WHERE '"&sSelectedTable&"%' LIKE '%"&searchItem&"%' "
SQL=SQL & " ORDER BY Artist"
Set RS = conn.execute(SQL)
Send "<table cellpadding=2>"

ELSE
Response.Write "Invalid category id"
END IF

WHILE not RS.eof
Send "<tr><td><img src =images/" & RS("Image") & " width=130 height=130></img></td>"
send "<td bgcolor=#D6DBEF width=470>&nbsp;<B><font face=Arial, Helvetica, sans-serif>" & RS ("Painting") & "</b>"
send "<br>&nbsp;" & RS ("Artist")

send "</td></tr>"

RS.MoveNext
Wend
Send "</table>"%>

 

[puppetnd]

ok nevermind i went a different route with the search box and it should be working as soon as i fix this next error

the error i get is name redifined but i need to keep the name or else it wouldnt be able to be used in another sub to open the connection. heres my code

SUB DBOpen
SET conn = server.createObject("ADODB.Connection")
DBQ=server.mappath("db1.mdb") 'Open the basic serverpath but add the line to my database
conn.Open = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & DBQ
END SUB

SUB DBClose
RS.close
SET RS = nothing
conn.close
SET conn = nothing
END SUB
SUB send (html)
response.write html & vbCRLF
END SUB

SUB loadRESULTS
DBopen
SET RS=conn.execute(SQL)
WHILE not RS.eof

etc