Solved

Search Page To Query Database And Display The Information

Posted on 2003-10-21
22
214 Views
Last Modified: 2008-02-01
what im trying to accomplish is a page where a user can enter 3 search criteria's which are Clients, Run number, and Date.
Our list of clients can be hard coded because theres seldom a new one added also Run Number they stay the same, Date obviously can not be hard coded also im kind of wondering how I can make a date field on a webpage.

  After this information is entered into the form and submitted it needs to filter out the search criteria and display it on another page like a data grid. So im guessing pass the search parameters threw the url have a page accept them and create a SQL query and display it.


Heres the database layout (fox pro)

               Qualdata.dbc  (database container)

               within Qualdata.dbc there are two tables

Qualdetl.dbf  (run detail information)   Qualmast.dbf (run master information)

the fields for Qualdetl.dbf:                     the fields for Qualmast.dbf

pwrunit                                                massacct
driver                                                   empcode
masacct                                               runnumber
runnumber                                           driver
dealercode                                           pwrunit
datein                                                  trailer
timein                                                  starttime
dateout                                                startdate
timeout                                                runendtime
odometer                                             runenddate
boxes                                                  startodo
pallets                                                 endodo
envelopes                                            U_id
cages
totes
returns
retcages
remarks
U_id


so thats what im working with now as you can see by the fields these tables are beeing updated by a tracking system for trucks when a entry is made it enters it into both tables and is joined by the U_id field or Unique ID.


Any help would be greatly apreciated. Thanks










0
Comment
Question by:dragon158
  • 12
  • 10
22 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 9594564
Ok heres the first bit (just a basic page)

<form name=myform action="page2.asp" method="post">
<input type=text name= size="20">
<input type=text name= size="20">
<select size="1" name="D1">
<%for count=1 to 31
response.write "<option>" & count
next %>
</select>
<select size="1" name="D2">
<%for count=1 to 12
response.write "<option>" & monthname(count)
next %>
</select>
<select size="1" name="D3">
<%for count=2003 to 2010
response.write "<option>" & count
next %>
</select>
<input type=submit>
</form>


For the second bit are you displaying all the fields from both tables?
0
 
LVL 58

Expert Comment

by:Gary
ID: 9594648
Forget that one forgot to name them

<form name=myform action="page2.asp" method="post">
<input type=text name=Clients size="20">
<input type=text name=Run size="20">
<select size="1" name="day">
<%for count=1 to 31
response.write "<option>" & count
next %>
</select>
<select size="1" name="month">
<%for count=1 to 12
response.write "<option>" & monthname(count)
next %>
</select>
<select size="1" name="year">
<%for count=2003 to 2010
response.write "<option>" & count
next %>
</select>
<input type=submit>
</form>
0
 
LVL 58

Expert Comment

by:Gary
ID: 9594910
This is the code for the second page, will probably need adjusting.  Is the client list related to U_ID in the db?  Is runnumber just a number?

<%
sqldate=request("month") & "/" & request("day") & "/" & request("year")
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND runnumber='" & request("run") & "' AND u_ID=" & request("clients") & ")
if not rs.eof

response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR>driver<TD></TD><TD>" & rs("driver") & "</TD></TR>"
response.write "<TR>masacct<TD></TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR>runnumber<TD></TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR><TD></TD><TD>" & rs("") & "</TD></TR>"
response.write "<TR><TD></TD><TD>" & rs("") & "</TD></TR>"
response.write "<TR><TD></TD><TD>" & rs("") & "</TD></TR>"
' ... etc you get the idea
response.write "</table>"

set rs=nothing
set cn=nothing

%>
0
 

Author Comment

by:dragon158
ID: 9595732
no the client list is not stored anywhere in the DB and the run number would be like ex: 01P . one more question users would be using those three search fields but how in the select statement could I add other things to be displayed on page2.asp like pallets and totes deleverd. anyways thanks for the quick reply i'll try it out soon as a get a chance and let you know how I make out. Thanks
0
 

Author Comment

by:dragon158
ID: 9601015
I ran it and the error that came up was:

Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/page2.asp, line 7

set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND runnumber='" & request("run") & "' AND u_ID=" & request("clients") & ")

also since client name refers to the field massacct and the account's dont change is there a way to hard code them in the first page? heres some sample massacct entrys TC,EF,BC ect... anyways thanks again for your help!
0
 
LVL 58

Expert Comment

by:Gary
ID: 9601325
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND runnumber='" & request("run") & "' AND u_ID=" & request("clients"))

There was an extra " at the end

For the hardcoding just change the text box to a drop down and code in the options
<select name="namehere">
<option>option1
<option>option2
<option>option3
...etc
</select>
0
 

Author Comment

by:dragon158
ID: 9607899
I keep getting this error now


Microsoft VBScript compilation error '800a03f9'

Expected 'Then'

/page2.asp, line 8

IF NOT RS.EOF
-------------^
  this is the code I have right now


<%
sqldate=request("month") & "/" & request("day") & "/" & request("year")
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND runnumber='" &

request("run") & "' AND u_ID=" & request("masacct"))
IF NOT RS.EOF
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR>driver<TD></TD><TD>" & rs("driver") & "</TD></TR>"
response.write "<TR>masacct<TD></TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR>runnumber<TD></TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR>boxes<TD></TD><TD>" & rs("boxes") & "</TD></TR>"
response.write "<TR>totes<TD></TD><TD>" & rs("totes") & "</TD></TR>"
response.write "<TR>cages<TD></TD><TD>" & rs("cages") & "</TD></TR>"
response.write "</table>"
set rs=nothing
set cn=nothing
%>

0
 
LVL 58

Expert Comment

by:Gary
ID: 9608042
Amend to this

<%
sqldate=request("month") & "/" & request("day") & "/" & request("year")
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND runnumber='" & request("run") & "' AND u_ID=" & request("masacct"))

IF NOT RS.EOF Then
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR>driver<TD></TD><TD>" & rs("driver") & "</TD></TR>"
response.write "<TR>masacct<TD></TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR>runnumber<TD></TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR>boxes<TD></TD><TD>" & rs("boxes") & "</TD></TR>"
response.write "<TR>totes<TD></TD><TD>" & rs("totes") & "</TD></TR>"
response.write "<TR>cages<TD></TD><TD>" & rs("cages") & "</TD></TR>"
response.write "</table>"

End If
set rs=nothing
set cn=nothing

%>
0
 

Author Comment

by:dragon158
ID: 9609692
Ok now that works but im getting this error


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

[Microsoft][ODBC Visual FoxPro Driver]SQL: Column 'EF' is not found.

/page2.asp, line 11

the address to take a look is http://208.234.3.100/page1.asp

The test data i've been using is Month: Feb Day:08 Year:2003 in the db it displays as 02/08/03
Client: EF and Run: EP

it seems like its looking at EF as a field instead of masacct in qualmast

I dont mean to keep buggin ya but thanks for the help
0
 

Author Comment

by:dragon158
ID: 9609734
This is the code im using right now

<html>
<head>
<title>Web Detail</title>
<body>
<%
sqldate=request("month") & "/" & request("day") & "/" & request("year")
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=C:\Inetpub\wwwroot;SourceType=DBF;" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND 'runnumber'='" &

request("run") & "' AND 'masacct'=" & request("client"))
IF NOT RS.EOF THEN
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR>driver<TD></TD><TD>" & rs("driver") & "</TD></TR>"
response.write "<TR>masacct<TD></TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR>runnumber<TD></TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR>boxes<TD></TD><TD>" & rs("boxes") & "</TD></TR>"
response.write "<TR>totes<TD></TD><TD>" & rs("totes") & "</TD></TR>"
response.write "<TR>cages<TD></TD><TD>" & rs("cages") & "</TD></TR>"
response.write "</table>"
End IF
set rs=nothing
set cn=nothing
%>
</body>
</head>
</html>
0
 
LVL 58

Expert Comment

by:Gary
ID: 9609935
Hmmm you had apostrophes around the field names, that may be screwing it up.  Amended ...

set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE datein='" & sqldate & "' AND runnumber='" &request("run") & "' AND masacct=" & request("client"))
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dragon158
ID: 9610235
the reason I used the apostrophes is it keeps giving this error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Visual FoxPro Driver]RUNNUMBER is not unique and must be qualified.

/page2.asp, line 11
0
 

Author Comment

by:dragon158
ID: 9610652
I came up with this and it also returns no error's But the page remains blank afterwords doesnt grab anything I think it might be the date field  would October 22 2003 pull record out of the db like 10/22/03 not sure if thats a issue.

<html>
<head>
<title>Web Detail</title>
<body>
<%
sqldate=request("month") & "/" & request("day") & "/" & request("year")
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=C:\Inetpub\wwwroot;SourceType=DBF;" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE startdate='" & sqldate & "' AND Qualmast.runnumber'='" &request("run") & "' AND Qualmast.masacct='" &request("client") &"' ")
IF NOT RS.EOF THEN
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR>driver<TD></TD><TD>" & rs("driver") & "</TD></TR>"
response.write "<TR>masacct<TD></TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR>runnumber<TD></TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR>boxes<TD></TD><TD>" & rs("boxes") & "</TD></TR>"
response.write "<TR>totes<TD></TD><TD>" & rs("totes") & "</TD></TR>"
response.write "<TR>cages<TD></TD><TD>" & rs("cages") & "</TD></TR>"
response.write "</table>"
End IF
set rs=nothing
set cn=nothing
%>
</body>
</head>
</html>
0
 
LVL 58

Accepted Solution

by:
Gary earned 500 total points
ID: 9610711
I'm not sure of the correct formats for dates in FoxPro
Some examples show

...startdate = CTOD('" & sqldate & "' AND ...

Maybe try that.  Do you have a query analyser in FoxPro?  Could try running the sql directly to check records do get returned
0
 

Author Comment

by:dragon158
ID: 9611754
Thanks Gary, I will give it a try I havent finished my project yet I might have a few more questions but I awarded you the points because you deserve them on this question. Thanks dude
0
 

Author Comment

by:dragon158
ID: 9614880
Just so I understnad the SQL command what do the & and '" "' stand for?
0
 
LVL 58

Expert Comment

by:Gary
ID: 9614906
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE startdate='" & sqldate & "' AND Qualmast.runnumber'='" &request("run") & "' AND Qualmast.masacct='"

The & just concatenates the string, says add this to the end of the string, just like using + but for strings we use & (you can use + but not recommended)
The ' (apostrophe) must be used to enclose strings that are being sent to the database.
0
 

Author Comment

by:dragon158
ID: 9616828
It still doesn’t return anything; you think it needs a trim or u case somewhere. Something else interesting is that the Qualmast table for instance has on one unique id for each record Qualdetl on the other hand has multiple entrees all with the same unique id because when a driver delivers something it writes a record in Qualdetl for every stop and delivery meanwhile Qualmast only has the one because it hold the master information. Ok so when we do a JOIN on U_id = U-id and finds one in Qualmast and possibly 4 in qualdetl is there a way to handle this? Thanks again
0
 
LVL 58

Expert Comment

by:Gary
ID: 9617117
Ok just to check - just had one question where the date field was actually text - the date field is a Date type?
The 1 record to many is fine thats how the inner join works.
(if you want and can - email the pages and a sample db - may find the error quicker, see profile)
0
 

Author Comment

by:dragon158
ID: 9634812
Ok im still getting a blank page when I submit the data from page1 to page2. Just to see something I made a third page with static information and it returns everything just fine heres the code for page 3 thats works

<html>
<head>
<title>Web Detail</title>
<body>
<%
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=C:\Inetpub\wwwroot;SourceType=DBF;" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE startdate='10/22/03' AND Qualmast.runnumber ='01P' AND Qualmast.masacct='EC' ORDER by Qualmast.masacct")
IF NOT RS.EOF THEN
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR><TD>startdate</TD><TD>" & rs("startdate") & "</TD></TR>"
response.write "<TR><TD>masacct</TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR><TD>runnumber</TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR><TD>boxes</TD><TD>" & rs("boxes") & "</TD></TR>"
response.write "<TR><TD>totes</TD><TD>" & rs("totes") & "</TD></TR>"
response.write "<TR><TD>cages</TD><TD>" & rs("cages") & "</TD></TR>"
response.write "</table>"
End IF
set rs=nothing
set cn=nothing
%>
</body>
</head>
</html>


Ok now heres page 1

<html>
<head>
<title>Web Detail</title>
<center><h3><i>Please Select Run Information</h3></i></center>
<form name=myform action="page2.asp" method="post">
Month:<select size="1" name="month">
<%for count=1 to 12
response.write "<option>" & count
next %>
</select>
Day:<select size="1" name="day">
<%for count=1 to 31
response.write "<option>" & count
next %>
</select>
Year:<select size="1" name="year">
<option>03
<option>04
</select>
<br>
<br>
Client:<select name="client">
<option>EF
<option>NF
<option>NG
<option>EC
<option>NC
<option>NT
<option>BC
<option>TC
<option>FC
<option>F2
<option>VC
</select>
Run:<input type=text name="run" size="5">
<input type=submit>
<br>
<b><i>Account Codes:</b></i>
<br>EF = Chrysler Orlando
<br>NF = Nissan Jacksonville
<br>NG = Nissan Greenville
<br>EC = Mercedes Maryland
<br>NC = Nissan Somerset
<br>NT = Nissan Memphis
<br>BC = BMW
<br>TC = Toyota
<br>FC = Ford
<br>F2 = Ford
<br>VC = Volvo
</form>
</head>
</html>

and page 2


<html>
<head>
<title>Web Detail</title>
<body>
<%
sqldate=request("month") & "/" & request("day") & "/" & request("year")
set cn = Server.CreateObject("ADODB.Connection")
cn.provider = "MSDASQL.1"
connstr = "Driver={Microsoft Visual FoxPro Driver};UID=;SourceDB=C:\Inetpub\wwwroot;SourceType=DBF;" & server.mappath("Qualdata.dbc")
cn.Open connstr
set rs = cn.Execute("SELECT * FROM Qualmast INNER JOIN Qualdetl ON Qualmast.U_id = Qualdetl.U_id WHERE startdate='" & sqldate & "' AND Qualmast.runnumber ='" & request("run") & "' AND Qualmast.masacct='" & request("client") &"' ORDER by Qualmast.masacct  ")
IF NOT RS.EOF THEN
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("pwrunit") & "</TD></TR>"
response.write "<TR><TD>startdate</TD><TD>" & rs("startdate") & "</TD></TR>"
response.write "<TR><TD>masacct</TD><TD>" & rs("masacct") & "</TD></TR>"
response.write "<TR><TD>runnumber</TD><TD>" & rs("runnumber") & "</TD></TR>"
response.write "<TR><TD>boxes</TD><TD>" & rs("boxes") & "</TD></TR>"
response.write "<TR><TD>totes</TD><TD>" & rs("totes") & "</TD></TR>"
response.write "<TR><TD>cages</TD><TD>" & rs("cages") & "</TD></TR>"
response.write "</table>"
End IF
set rs=nothing
set cn=nothing
%>
</body>
</head>
</html>


Thanks again
0
 

Author Comment

by:dragon158
ID: 9637252
OK seems I have gotten it to work my only problem now seems that it will join qualmast and qualdetl corectly,  like I said before the master run information in qualmast is only entered once but in qualdetl it is entered multiple times depending on how many stops that driver made that day. When it is joining it will get the qualmast info but in qualdetl its only taking the first record it matches meanwhile theres like 4 other records with the same U_id in qualmast it ignores and doesnt retrieve them. Is there any way to acomplish this?
0
 
LVL 58

Expert Comment

by:Gary
ID: 9637716
The sql should be pulling all records back where Qualmast.U_id = Qualdetl.U_id even if there is multiple records in Qualdetl - but you need to loop through the recordset.  So information from Qualmast will appear on every row of the recordset, i.e. the Qualmast.U_id will be available as you move through the records whereas the records form Qualdetl will be unique with each record.  So the scenario would be do as you do above but then

IF NOT RS.EOF THEN
' Run through the first row in the recordset printing out the information from the Qualmast table e.g.
response.write rs("pwrunit")
response.write rs("driver")
response.write rs("masacct")
response.write rs("runnumber")
response.write rs("dealercode")
....etc
' Then we print out the details from the Qualdetl table.  We are already on the first row of the recordset so we just run a loop
Do While Not Rs.Eof
response.write "<table>"
response.write "<TR><TD>pwrunit</TD><TD>" & rs("empcode") & "</TD></TR>"
response.write "<TR><TD>startdate</TD><TD>" & rs("trailer") & "</TD></TR>"
response.write "<TR><TD>startdate</TD><TD>" & rs("starttodo") &
"</TD></TR>"
' etc with the rest of the fields from the Qualdetl  table
response.write "</table>"
Loop
End IF


Probably as clear as mud :o)  Let me know if it doesn't make sense
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now