?
Solved

ASP & SQL

Posted on 1998-10-15
9
Medium Priority
?
260 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 100 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

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

CTAs encourage people to do something specific to show interest in your company, product or service. Keep reading to learn why CTAs should always be thought of as extremely important, albeit small, sections of websites.
Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
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…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
Suggested Courses

719 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