ASP/SQL/database: random record among TOP 100?

Discussion in 'Software' started by BFprout, May 17, 2005.

  1. BFprout

    BFprout Private E-2

    I'm using ASP and a CSV file for my database.

    It has 4 colums: date, time, name, message

    I want to sort the records by date and time (I managed to do that), but then I don't know how I can pick one random record among the TOP 100 most recent.

    Does anyone have an idea? Any help would be much appreciated.
     
  2. goldfish

    goldfish Lt. Sushi.DC

    if it were me I'd have an ID column as well, and simply generate a random number, using SQL statement to drag out the record with that random ID. Somthing like

    Code:
    "SELECT * FROM table_name WHERE id = " & id & " LIMIT 1"
    
    But that might not be an option... you could try accessing it directly from a recordset but I'm so rusty with ASP I wouldn't know how! Oy KODO! :D

    Some links about recordsets
    http://www.shocknet.org.uk/defpage.asp?pageID=16
    http://www.w3schools.com/ado/ado_ref_recordset.asp
     
  3. BFprout

    BFprout Private E-2

    The auto-incrementing id could be an option if only I had found someone to tell me how to do it with ASP and CSV files.
    The only tutorials I have found were for Access databases.

    This is why I now want to do it with dates.
     
  4. goldfish

    goldfish Lt. Sushi.DC

    Thing is, dates arn't unique values, so you can't pick out a unique record at random.

    You could write a fairly simple script to read your CSV files into an Access database, with auto incrementing ID. Its quite a simple script to write, really.

    However, thinking about it, if it was a CSV file, you could use the same method I used for random quotes. All you do is generate a random number, and round it (don't forget that), then loop a function that many times to skip a line, then read the data from the line that you're sitting at after the loop.

    Not at all efficient, but it works.
     
  5. Kodo

    Kodo SNATCHSQUATCH

    pull the csv data out and manually create a recordest. Then you'll be able to access the recordset in memory like you would a database. Then make a random function (remember to call randomize) and use the array of Auto ID's as your values to randomize on.. from n-start to n-end. If a number doesn't exist, then run the function again. Don't have to go into any more.. gotta get back to work.
     
  6. BFprout

    BFprout Private E-2

    Thank you Goldfish and Kodo.
    What you are both saying sounds very complicated to me. I'm really not a programmer.

    I thought if I'd managed to gather the 100 records I'm interested in, it would be easy to pick one randomly.

    For the date, I was thinking of ordering by year, then month, then day, then hour, then minute. Is it stupid? In the end each record would have a unique value, right?

    About creating manually a recordset, I have to go back to my ASP book because I have no idea how to do that.
     
  7. goldfish

    goldfish Lt. Sushi.DC

    If you use the date you are not garunteed to have a unique value for every entry. With an ID, it would be completly impossible to have 2 identical IDs.

    Read up about recordsets, if you input the CSV data into a recordset you would really simplify the problem. The only thing you'd need to worry about would be the random number and draging out the data depending on that random number.
     
  8. BFprout

    BFprout Private E-2

    I don't really get how the recordset object could help me. I'm currently trying to understand.
    But it's not the recordset that could help me do this id column is it? How can I actually get this auto-incrementing id?

    And if I had it, it would be easy to select the 100 highest ids but then, how would I pick one randomly? Because I can't know in advance how high the numbers will get, they could be really big numbers.

    I'm really confused...
     
  9. Kodo

    Kodo SNATCHSQUATCH

    Manually creating a recordset (disconnected) http://www.4guysfromrolla.com/webtech/071799-1.2.shtml

    Random numbers http://www.asp101.com/articles/stephen/randomnumbers/default.asp

    This will allow you to create a recordset in memory and you should be able to sort it on a field if you wish. The important thing is that you'll be able to add records via a loop with sequential increments of ID's that you can use in your random function. Look up ADO recordsets to get more information on how to use this better.

    IMO, I would do what you're doing in ASP.NET. It's quite a bit easier.
     
  10. BFprout

    BFprout Private E-2

    thanks for your help. I'm exploring a few solutions at the moment.
    I can't use ASP.NET for this project. It seems much better though
     

MajorGeeks.Com Menu

Downloads All In One Tweaks \ Android \ Anti-Malware \ Anti-Virus \ Appearance \ Backup \ Browsers \ CD\DVD\Blu-Ray \ Covert Ops \ Drive Utilities \ Drivers \ Graphics \ Internet Tools \ Multimedia \ Networking \ Office Tools \ PC Games \ System Tools \ Mac/Apple/Ipad Downloads

Other News: Top Downloads \ News (Tech) \ Off Base (Other Websites News) \ Way Off Base (Offbeat Stories and Pics)

Social: Facebook \ YouTube \ Twitter \ Tumblr \ Pintrest \ RSS Feeds