Question on passing values into a sql statement from a form?

jpdeveloper · #1 (**permalink**) 02-06-2006, 04:30 PM

I just started using ASP not sure of all the values or if implementing them correctly. I'm having trouble being able to pass my user's selection from two chain drop boxes (which the options/values are displayed by querying the Access database). These parameters are using the method "post" to another .asp file which is then inserted into my sql statement (the where clause of my sql statement.) I have tried the excute (command object) and (connection object) which was still returning all records in the table. I appreciate any help or advice anyone can give.. Thanks in advance.

**brian.mark** · #2 (**permalink**) 02-06-2006, 05:29 PM

Perhaps if you give us a better idea of what your select statement looks like we could help.

As it is now, I'd find it hard to say if your ASP or your SQL Statement is at fault.

Brian.

jpdeveloper · #3 (**permalink**) 02-07-2006, 12:21 PM

Well here are my two files that I'm currently working on and made changes to and again to this morning. My expected results is that I want to have the user choose a month and year then submit it and them to see it the month and year selected and the hyperlink topics that correspond with that. Along with a no results found if their selection has no data. (That piece I didn't put the code in the file yet)

bulletin_dropdown_menu.asp

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit

dim objConn, objRS, objRS2, strSQL

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
Set objRS2 = Server.CreateObject("ADODB.Recordset")

objConn.ConnectionString = Server.MapPath("bulletindb.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open

Sub showDropDownList(c, r, table)
	
	strSQL = "SELECT DISTINCT(month) FROM " & table & " ORDER BY month"
	Set r = c.Execute(strSQL)
	if r.eof = false then
	Response.Write "<form name=""form1"" action=""test_post.asp"" method=""post"">"
	Response.Write "<select name=""dropdownlist"">" & vbCrLf
	Response.Write "<option selected=""selected"" value="""">Select a Month:</option>"
		While r.EOF = false
		if r ("month") = Request.Form ("dropdownlist") then 
          Response.Write " selected=""selected""" 
     end if 
			Response.Write "<option value=""bulletin_dropdown_menu.asp"">" _
			& r ("month") & "</option>" & vbCrLf
		r.movenext
		Wend
	end if
	Response.Write "</select></form>" & vbCrLf
	
End Sub

Sub showDropDownList2(c, r, table)

	strSQL = "SELECT DISTINCT(year) FROM " & table & " ORDER BY year ASC"
	Set r = c.Execute(strSQL)
		if r.eof = false then
	Response.Write "<form name=""form2"" action=""test_post.asp"" method=""post"">"
	Response.Write "<select name=""dropdownlist2"">" & vbCrLf
	Response.Write "<option selected=""selected"" value="""">Select a Year:</option>"
		While r.EOF = false
			if r ("year") = Request.Form ("dropdownlist2") then 
          Response.Write " selected=""selected""" 
     end if 
			Response.Write "<option value=""bulletin_dropdown_menu.asp"">" _
			& r ("year") & "</option>" & vbCrLf
		r.movenext 
		Wend
	end if
	Response.Write "<input type=""submit"" name=""bulletinsearch"" value=""Search"">" & vbCrLf
	Response.Write "</select></form>" & vbCrLf
	
End Sub
%>

<html>
<body>



First choose the bulletin month and the select the year</p>
<table width="200">
<tr> 
<td>Select Month:</td>
<td>Select Year:</td>
</tr><tr>
<td><%call showDropDownList(objConn, objRS, "bulletintable")%></td>


<td><%call showDropDownList2(objConn, objRS2, "bulletintable")%></td></tr>
</table>

</body>
</html>

test_post.asp

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit

dim objConn, objRS3, strSQL

Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS3 = Server.CreateObject("ADODB.Recordset")

objConn.ConnectionString = Server.MapPath("bulletindb.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open

strSQL = "select month, year, link_name, link_filename, post_desc "
strSQL = strSQL & "from bulletintable"
strSQL = strSQL & "where month='"& r (month) &"' and year='"& r (year) &"'"

objRS3.Open strSQL, objConn

response.write("" & objRS3("month") & "&nbsp"  )
response.write("" & objRS3("year") & "
" )

objRS3.movefirst
do while not objRS3.eof

response.write "<a href=""" & objRS3("link_filename") & " target=""_self"">"
response.write objRS3("link_name") & "</a>" & "" 
response.write(" - "& objRS3("post_desc") & "
")

objRS3.movenext

on error resume next

if the_year <> clng(objRS3("year")) then

act.close

end if
loop

set objRS3=nothing
objRS3.close
%>
<html>

</html>

Any advice or direction? I'm kinda at a stand still on this. Thanks in advance.[/code]

webmaster@pinecone.com · #4 (**permalink**) 02-07-2006, 05:23 PM

I see a couple of problems in your code. First, you have two forms created....you should only submit a single form. You are also not referencing the form fields correctly.

Here's a skeleton example. You can fill in the blanks since it is not complete nor optimized, but it should give you a general idea of how to proceed...

bulletin_dropdown_menu.asp

<form name="bulletin_form" method="post" action="test_post.asp">

<%
open DB
do you query
If not r.eof Then
%>
<select name="month_selected">
<%
Do while not r.eof
%>
<option value="<%=r(month)%>"><%=r("month")%></option>
r.movenext
Loop
%>
</select>
<%
End If
%>

<%
close your recordset
do your second query
If not r.eof Then
%>
<select name="year_selected">
<%
Do while not r.eof
%>
<option value="<%=r(year)%>"><%=r("year")%></option>
r.movenext
Loop
%>
</select>
<%
End If
%>

<input type="submit" value="submit">
</form>

<%
close the recordset
close the DB connection
%>

test_post.asp

<%
Dim strSql
strSQL = "select month, year, link_name, link_filename, post_desc "
strSQL = strSQL & "from bulletintable"
strSQL = strSQL & "where month='"& request("month_selected") &"' and year='"& request ("year_selected") &"'"

ADAM Web Design · #5 (**permalink**) 02-07-2006, 06:50 PM

That ASP sample pinecone posted isn't bad. Start with it.

(I'd come up with one of my own, but why duplicate a solid effort?)

alienzhavelanded · #6 (**permalink**) 02-07-2006, 09:46 PM

I would also suggest using something other than Access. It has limitations in number of connections and when it begins reaching a certain number of records or a large size. There are much more robust databases out there made primarily for dynamic web apps...however, this is good to sink your teeth into. You should plan on graduating to something else eventually.

TheGarty · #7 (**permalink**) 02-07-2006, 10:12 PM

Quote:

Originally Posted by alienzhavelanded

I would also suggest using something other than Access

There is nothing wrong with Access... simple to start with, and unless you are doing heaps of traffic, you will never reach the limitations.

The good thing about using Access if you are new to it, is that there is heaps of support online for it in relation to developing in relation to ASP VBScript.

If you were using PHP, i would suggest you use mySQL simply from a learning POV (availability of support on the web)... but for starting in ASP VBScript, stick with Access.

My vote... for you... is to stick with Access, there is absolutely no need to use anything else... yet.

ADAM Web Design · #8 (**permalink**) 02-07-2006, 11:58 PM

Agreed with TheGarty. I've built websites that have reached in the six digits of page views per month with an Access backend.

The problem is that most people forget to close and destroy all connection and recordset objects first and rely on the web servers to do it for them.

colr · #9 (**permalink**) 02-08-2006, 04:37 AM

You should be made aware, if you're not already, that the code you've posted will be vulnerable to SQL injection attacks.

Is this for a public facing site? You need to strip out illegal characters.

colr__

jpdeveloper · #10 (**permalink**) 02-08-2006, 09:44 AM

http://www.webproworld.com/viewtopic...=283320#283320 Here was my latest files that I was working on yesterday. Which thanks to Adam, gave me very helpful hints and when I was using "View Source" I then realized that my naming of values were wrong and did not match up as I thought that they were. But of course I still couldn't get it to work. I used two forms b/c I thought I had to for the way I wanted the dropdown boxes to be displayed with the separate queries I was running. Even though I knew that you don't have to do that normally but that was how I was being lead at first until I posted here.

--colr..This is not a public site..it is all internal so I lucked out with that one.

--TheGarty..That is good to hear that I have the correct combination..b/c I have no idea how to use mySQL or how different it is. This is the first time I'm not working with an oracle database so I'm thrown off a little here.

--pinecone..lol I knew I had a lot of issues with my code but had no direction so it was very hard when I started to change values, objects, etc.. the errors just got worse which forced me to go back to the beginning. But I’m learning a lot here by messing up the code so I guess that is one good thing..lol Thank you for the advice/direction. I thought it was easier to do it in sub's to have it show up correctly in the html part was I wrong on that part? Also b/c I had two separate forms going on with queries was that one of the reasons (if I had my values correct) that I was having trouble passing it through to the other file?

Thank you all for your advice and help on this one for me. Let me see how well I can do today with this..
Thanks again..-jp

webmaster@pinecone.com · #11 (**permalink**) 02-08-2006, 05:33 PM

Hi jp,

You can still use a subroutine to help build your form if you want. Just make sure that you submit a single form and use the correct field names to reference the data you want to pull out of the form. Skeleton example (not complete nor optimized) follows...

<%
Sub build_month_select_list(conn, query)

End Sub

Sub build_year_select_list(conn,query)

End Sub
%>

<form method=post action="yourfile.asp">
<%
dim conn,query

set conn=createobject(...)
conn = Open_DB_Connection()

query = "select month from month_table"
build_month_select_list (conn, query)

query = "select year from year_table"
build_year_select_list(conn, query)

conn.close
set conn=Nothing
%>
<input type="submit" value=" do it ">
</form>

jpdeveloper · #12 (**permalink**) 02-08-2006, 09:04 PM

It finally WORKS!! I was able to pass the values from my form to my select statement. Thank you for all the direction you gave me, you guys kept me on target with this project.

However I have another question, not sure if anyone can help so here it goes..

Is there an ASP command that will give me the ability to alert the user if they choose a month and a year combination that does not exist in the database?

Here is the code I have now..

bulletin_dropdown_menu.asp

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>

<%  
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString = Server.MapPath("bulletindb.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
%>
<html>
<head>
<link href="style_bulletin.css" rel="stylesheet" type="text/css" />
</head>
<body>
<div id="mainblank">
  <div class="style1" id="nav">

    <span class="style2">

   The Bulletin's Homepage</span></div>
  <div id="undernav"></div>
  <div id="padding4">
    <div id="padding3" class="style3">
            

Select the Bulletin "Month" and the corresponding 4 digit "Year" that you would like to view.</p>
<form name="bulletin_form" method="post" action="bulletin_post.asp">  
  <% 
            objConn.Open
            strSQL = "SELECT DISTINCT(month) FROM bulletintable ORDER BY month"
            Set r = objConn.Execute(strSQL)
            If not r.eof Then
                        %>
            <select name="month_selected">
                        <option>Select a Month</option>
                        <% 
                                    Do while not r.eof 
                                    %>
                        <option value="<%=r("month")%>"><%=r("month")%></option>
                        <%
                                    r.movenext 
                                    Loop
                                    %>
             </select>
  <% 
            End If 
            %>
  <%
            r.close
            %>
<%
            strSQL = "SELECT DISTINCT(year) FROM bulletintable ORDER BY year ASC"
            Set r = objConn.Execute(strSQL)
            If not r.eof Then 
            %>
            <select name="year_selected">
            <option>Select a Year</option>
                        <% 
                                    Do while not r.eof 
                                    %>
            <option value="<%=r("year")%>"><%=r("year")%></option>
                        <%
                                    r.movenext 
                                    Loop 
                                    %>
  </select>
  <% 
            End If 
            %>
  <input type="submit" value="Submit">
</form>
</div>
</div>
</div>
</body>
</html>
<% 
r.close
objConn.close
%>

bulletin_post.asp

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<% Option Explicit
dim objConn, objRS, strSQL, r
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
objConn.ConnectionString = Server.MapPath("bulletindb.mdb")
objConn.Provider = "Microsoft.Jet.OLEDB.4.0"
objConn.Open
strSQL = "select month, year, link_name, link_filename, post_desc " 
strSQL = strSQL & "from bulletintable " 
strSQL = strSQL & "where month='"& request("month_selected") &"' and year='"& request ("year_selected") &"'"
Set r = objConn.Execute(strSQL)
response.write("" & r ("month") & "&nbsp"  )
response.write("" & r ("year") & "
" )
r.movefirst
do while not r.eof
response.write "<a href=""" & r("link_filename") & " target=""_self"">"
response.write r("link_name") & "</a>" & "" 
response.write(" - "& r("post_desc") & "
")
r.movenext
end if
loop
set r=nothing
r.close
objConn.close
%>

webmaster@pinecone.com · #13 (**permalink**) 02-09-2006, 08:47 PM

One easy thing to do would be to test for eof in your bulletin_post.asp file and if eof is true, display a message to the user that no entries were found.

jpdeveloper · #14 (**permalink**) 02-10-2006, 02:54 PM

--pinecone--

I took you suggestion and I believe I have everything written correctly but I keep getting an error:
Microsoft VBScript runtime error '800a01a8'

Object required: ''
********

This is the section I added to the existing code..

Code:

Set r = objConn.Execute(strSQL)

if r.eof is NULL then 
Response.Write "Sorry, no records were found for "& r("month") & "" & r("year") & ""
else

I changed up the connections/objects but always receive that error. Would I have to create two different recordsets for this to work?

webmaster@pinecone.com · #15 (**permalink**) 02-10-2006, 06:10 PM

Try using....

"If Not r.eof Then"... or
"If rs.eof Then"

Instead of "If r.eof = NULL". I don't think checking against NULL works.

jpdeveloper · #16 (**permalink**) 02-15-2006, 09:53 AM

pinecone-- your right that did work. Thanks...