Hi All New to the forum and need help [Archive] - HTML Forums

View Full Version : Hi All New to the forum and need help

JustXtreme

01-20-2004, 04:26 PM

WHERE TO POST
I have an access data base that i've exported to an asp page, so basiclly i have a asp page with a database in it.
i am tring to make a drop down in an html page that pulls from the database to a table, i haven't had any problems with with pulling one feild of data, but when i try to pull a range it doesn't work, basically i need to know how to pull data in a drop down from a database with a range of fields, i'm sure this doesn't make sense so here's my script
I want florida to pull a range of JOBNUM?
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<TITLE>Comdata Card</TITLE>
<BODY>
<FORM METHOD="POST" ACTION="Comdata Card.ASP">
week
<select name="WEEK">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
<option value="32">32</option>
<option value="33">33</option>
<option value="34">34</option>
<option value="35">35</option>
<option value="36">36</option>
<option value="37">37</option>
<option value="38">38</option>
<option value="39">39</option>
<option value="40">40</option>
<option value="41">41</option>
<option value="42">42</option>
<option value="43">43</option>
<option value="44">44</option>
<option value="45">45</option>
<option value="46">46</option>
<option value="47">47</option>
<option value="48">48</option>
<option value="49">49</option>
<option value="50">50</option>
<option value="51">51</option>
<option value="52">52</option>

</select>
<P> State
<select name="JOBNUM">
<option value=""40000""49999"">Florida</option>
</select>
<P>
<INPUT TYPE="Submit" VALUE="Run Query">
</FORM>
</BODY>
</HTML>

scoutt

01-21-2004, 04:10 PM

if you have a drop down like that it is best just to loop it instead of using the database.

bassrek or putts will be the best people to help as I do not know ASP.

illogique

01-21-2004, 04:21 PM

well your script is just plain html!
no asp, no database.
is that all of it?
pretty hard helping with a query that is not even there...

ps. i don't know about that access "export to asp" thing.

bassrek

01-21-2004, 04:29 PM

It's hard to tell exactly what you're trying to do from your post. Don't know what an Access export to ASP is, either. If you're trying to grab a recordset from your database and stick it into a select box in ASP, here's some generic code

'assuming you've already established a connection called cn
set rs = Server.CreateObject("ADODB.RecordSet")
sql = "SELECT Jobnum FROM Jobs WHERE State = 'FL'"
rs.Open sql, cn, , adLockReadonly

dim jobbox
do while NOT rs.EOF
jobbox = jobbox & "<option value='" & rs("Jobnum") & "'>" & rs("Jobnum") & "</option>"
rs.MoveNext
Loop
rs.Close
'somewhere inside of your form you'd have
resposne.write "<select name='Jobnum' id='Jobnum'>" & jobbox & "</select>"

The first part queries the database and builds the <option> tags. Then inside of your form, you'd create the <select> tags and throw the jobbox variable inside with the database output.

JustXtreme

02-03-2004, 02:38 PM

Here's the Error
If anyone can help e-mail me at justin@threecompanies.com

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Extra ) in query expression '(((CONT1.WEEK)=) AND ((CONT1.JOBNUM) Between '' And ''))'.

/Comdata Card.ASP, line 22

heres the html script
Now i'll i'm trying to do is grab the week

<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<TITLE>Comdata Card</TITLE>
<BODY>
<FORM METHOD="POST" ACTION="Comdata Card.ASP">
week
<select name="WEEK">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
<option value="5">5</option>
<option value="6">6</option>
<option value="7">7</option>
<option value="8">8</option>
<option value="9">9</option>
<option value="10">10</option>
<option value="11">11</option>
<option value="12">12</option>
<option value="13">13</option>
<option value="14">14</option>
<option value="15">15</option>
<option value="16">16</option>
<option value="17">17</option>
<option value="18">18</option>
<option value="19">19</option>
<option value="20">20</option>
<option value="21">21</option>
<option value="22">22</option>
<option value="23">23</option>
<option value="24">24</option>
<option value="25">25</option>
<option value="26">26</option>
<option value="27">27</option>
<option value="28">28</option>
<option value="29">29</option>
<option value="30">30</option>
<option value="31">31</option>
<option value="32">32</option>
<option value="33">33</option>
<option value="34">34</option>
<option value="35">35</option>
<option value="36">36</option>
<option value="37">37</option>
<option value="38">38</option>
<option value="39">39</option>
<option value="40">40</option>
<option value="41">41</option>
<option value="42">42</option>
<option value="43">43</option>
<option value="44">44</option>
<option value="45">45</option>
<option value="46">46</option>
<option value="47">47</option>
<option value="48">48</option>
<option value="49">49</option>
<option value="50">50</option>
<option value="51">51</option>
<option value="52">52</option>

<INPUT TYPE="Submit" VALUE="Run Query">
</FORM>
</select></BODY>
</HTML>

Here is the ASP Page

<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html;charset=windows-1252">
<TITLE>Comdata Card</TITLE>
</HEAD>
<BODY>
<%
If IsObject(Session("Web_conn")) Then
Set conn = Session("Web_conn")
Else
Set conn = Server.CreateObject("ADODB.Connection")
conn.open "Web","",""
Set Session("Web_conn") = conn
End If
%>
<%
If IsObject(Session("Comdata_Card_rs")) Then
Set rs = Session("Comdata_Card_rs")
Else
sql = "SELECT CONT1.DID, CONT1.STORE, Count(CONT1.CNUM) AS CountOfCNUM, DEM.NAME, DEM.DSC, DEM.DEMO, DEM.ADD1, DEM.ADD2, DEM.CITY, DEM.ST, DEM.ZIP, CONT1.WEEK, CONT1.JOBNUM FROM CONT1 LEFT JOIN DEM ON CONT1.DID = DEM.DID GROUP BY CONT1.DID, CONT1.STORE, DEM.NAME, DEM.DSC, DEM.DEMO, DEM.ADD1, DEM.ADD2, DEM.CITY, DEM.ST, DEM.ZIP, CONT1.WEEK, CONT1.JOBNUM, CONT1.COMPANY HAVING (((CONT1.WEEK)=" & Request.QueryString("week") & ") AND ((CONT1.JOBNUM) Between '" & Request.QueryString("start") & "' And '" & Request.QueryString("end") & "')) "
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, conn, 3, 3
End If
%>
<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=0><FONT FACE="Arial" COLOR=#000000><CAPTION><B>Comdata Card</B></CAPTION></FONT>

<THEAD>
<TR>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>DID</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>STORE</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>CountOfCNUM</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>NAME</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>DSC</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>DEMO</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>ADD1</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>ADD2</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>CITY</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>ST</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>ZIP</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>WEEK</FONT></TH>
<TH BGCOLOR=#c0c0c0 BORDERCOLOR=#000000 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000>JOBNUM</FONT></TH>

</TR>
</THEAD>
<TBODY>
<%
On Error Resume Next
rs.MoveFirst
do while Not rs.eof
%>
<TR VALIGN=TOP>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("DID").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("STORE").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("CountOfCNUM").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("NAME").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("DSC").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("DEMO").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ADD1").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ADD2").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("CITY").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ST").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("ZIP").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ALIGN=RIGHT><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("WEEK").Value)%><BR></FONT></TD>
<TD BORDERCOLOR=#c0c0c0 ><FONT style=FONT-SIZE:10pt FACE="Arial" COLOR=#000000><%=Server.HTMLEncode(rs.Fields("JOBNUM").Value)%><BR></FONT></TD>

</TR>
<%
rs.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>
</BODY>
</HTML>

bassrek

02-03-2004, 02:47 PM

In your sql variable, you are looking for querystring values when you should be looking for form values. As a result, it's passing a blank value where it's expecting a numeric value causing the query to break.

'abbreviated...
'the query should be

HAVING (((CONT1.WEEK)=" & Request.form("week") & ") AND ((CONT1.JOBNUM) Between '" & Request.form("start") & "' And '" & Request.form("end") & "')) "

Also, on the code you provided, there are no form elements for start and end. Since you have single quotes around those, the query will not break, but it might not bring back the expected results.