Solved

ASP & SQL

Posted on 1998-10-15
9
245 Views
Last Modified: 2013-12-25
I need some help figuring out how to make my ASP pass SQL to my database.

My high school alumni database has fields YearIn and YearOut; when someone enters a record they are required to supply the year they began and they year graduated.  I would like to make an ASP that allows users to enter two years and see the names of all alumni who were in school during those years.  For instance, if the database contained these records:

Name    YearIn    YearOut
_________________________
Aaron    1989     1992    
Beth     1990     1993
Chris    1991     1994
Dan      1992     1995
Emily    1993     1996
Frank    1994     1997
Greg     1995     1998
Heather  1996     1999

, then a query for 1994 through 1995 should return Chris, Dan, Emily, Frank and Greg.

Is there an elegant way to make an ASP generate the proper SQL to satisy this type of query?  I prefer JScript over VBScript, but whatever works works.
0
Comment
Question by:oucher
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 10

Expert Comment

by:MasseyM
ID: 1855752
your SQL would be something link this

SELECT * from [YourTable]

You will select all the records and then parse through them...

<%
For Year = request.form("YearInInput") to request.form("YearOutInput")
  If rs.Fields("YearIn") or rs.Fields("YearOut") = Year then response.write rs.Fields("Name")
Next
%>

This will simply go through each year they entered... ie
input 1991 and 1995 ... if will go through 1991, 1992, 1993, 1994, and 1995... If the YearIn or YearOut in the database equals the "Year" that we are parsing through, it iwll display it...

This is the easiest way I can think of off the top of my head...

You will have to add error checking (such as if they entered a year as 95 rather than 1995... etc...

0
 

Author Comment

by:oucher
ID: 1855753
Thanks; looking at your code helped me work this out in my head.  However, I think I see a problem:

What if a user requests 1995 through 1996?  The results should include Frank, since he was here both years (he came in 1994 and left in 1997), but neither his YearIn or YearOut is 1995 or 1996, so it seems to me he would be left out.

Also, is there a more efficient way than returning all the records?
0
 

Accepted Solution

by:
john_19 earned 50 total points
ID: 1855754
Have two textboxes(txt_yrin & txt_yrout) on your ASP page.
Use a recordset & set it to fire a stored procedure (Use Visual Interdev 6.0 design-time control).

stored proc : Select primarykey(reqd. for updating records),yrin,yrout
from Table where yrin =@yrin and yrout = @yrout

This will return reqd. records in your recordset.

To update textboxes :
1) You could databind the textboxes (use Visual Interdev design-time textbox controls)
2) You could do : txt_box1.value = rs_record.fields.getvalue("yrin")
similar for yrout

0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1855755
My idea will work... I just forgot one thing... after i logged out last night I remembered it... Sorry, but I had to wok all day and this is the first chance I get...

You  need another additional loop outside my function looping through the years the person was there...

But, it is not very efficient... So... You see, the hard thing is the fact that you have someone that spans a period of years... If you could make a database with say 6 fields like year1 year2 year3 year4 year5 year6, then you could run a SQL query against each field... WHERE YearInInput = year1 ... you get the idea... the problem lies in the 1994 to 1996 (you will miss 1995) ...

I am sorry if this doesn;t make sense... I tried to explain it the best way I could... sorry..
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:oucher
ID: 1855756
It seems to me your suggestion is like the previous one, and would leave some records out (i.e. 1995-96, Frank).  Or maybe I just don't understand what you're proposing well enough.  Could you provide some example code to help me see just what you're saying?

Thanks!
0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1855757
Sorry, I cannot... I don't have the time right now...

But, just create two loops...
1st loop through the years they enter.
   2nd loop through the years of the individul

     if withing that timeframe, print it.

   end loop 2
end loop 1
0
 
LVL 28

Expert Comment

by:sybe
ID: 1855758
Put the years in dropdownboxes in the form, it will prevent you from doing a lot of validation. When you use textfields for input, you'll have to check if the values are valid years.

Make a sql statement like this:

strSQL = SELECT * from [YourTable] "
strSQL = strSQL & "WHERE YearIn <=" & Request("yearin") & " "
strSQL = strSQL & AND YearOut >=" & Request("yearout")

0
 
LVL 28

Expert Comment

by:sybe
ID: 1855759
Oops,
strSQL = strSQL & AND YearOut >=" & Request("yearout")
should be
strSQL = strSQL & "AND YearOut >=" & Request("yearout")


0
 
LVL 10

Expert Comment

by:MasseyM
ID: 1855760
There you go... It is with SQL... SYBE is the man.
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

Title # Comments Views Activity
Website like ebay or amazon 3 56
WCAG audit tools 1 51
How to parametrize bing map on webbrowser control 6 58
Video on my site 4 27
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Why do we like using grid based layouts in website design? Let's look at the live examples of websites and compare them to grid based WordPress themes.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…

757 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

22 Experts available now in Live!

Get 1:1 Help Now