Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 263
  • Last Modified:

ASP & SQL

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
oucher
Asked:
oucher
  • 4
  • 2
  • 2
  • +1
1 Solution
 
MasseyMCommented:
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
 
oucherAuthor Commented:
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
 
john_19Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MasseyMCommented:
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
 
oucherAuthor Commented:
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
 
MasseyMCommented:
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
 
sybeCommented:
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
 
sybeCommented:
Oops,
strSQL = strSQL & AND YearOut >=" & Request("yearout")
should be
strSQL = strSQL & "AND YearOut >=" & Request("yearout")


0
 
MasseyMCommented:
There you go... It is with SQL... SYBE is the man.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now