Solved

ASP & SQL

Posted on 1998-10-15
9
251 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Accessibility and Usability are two concepts that seem to be closely related.  But, too many people seem to have a distorted perception of them. During last five years, those two words have come to the day-to-day work of almost every web develope…
I've been asked to discuss some of the UX activities that I'm using with my team. Here I will share some details about how we approach UX projects.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

932 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

11 Experts available now in Live!

Get 1:1 Help Now