Solved

ASP & SQL

Posted on 1998-10-15
9
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
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
 

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

Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
When crafting your “Why Us” page, there are a plethora of pitfalls to avoid. Follow these five tips, and you’ll be well on your way to creating an effective page.
This tutorial demonstrates how to identify and create boundary or building outlines in Google Maps. In this example, I outline the boundaries of an enclosed skatepark within a community park.  Login to your Google Account, then  Google for "Google M…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

617 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