locked
Can I SELECT records being viewed right now? RRS feed

  • Question

  • User-1221895677 posted

    Hi All,

     Many thanks in advance. 

    I'm using an MS Access Database and an <asp:AccessDataSource> in my page (I know, I know.).  I'd like to know if its possible to write a statement that would retrieve a real-time dataset of records that are currently being viewed elsewhere on the website.

    Any suggestions?

     Thank you!

     

    Wednesday, January 28, 2009 2:00 PM

Answers

  • User-821857111 posted

    Oh. My. Gawd.

    If security of access to records is the issue, upgrade to SQL Server and implement a role-based authorisation system with auditing. Anyone found sharing their login with anyone else after it has been implemented should be fired.

    Job done.

    If you have existing Access forms etc, they can still be used, and SQL Server Express is free.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 29, 2009 2:44 PM

All replies

  • User2130758966 posted

     I am not aware of this feature being built in although you could create a table which you log entries to.

    Another option is to use the Trace class to log the requests in the format of your choice. It depends if you need this for error logging, security audits, or a public interface?

    Wednesday, January 28, 2009 2:13 PM
  • User-1221895677 posted

    Its primarily going to be public interface.  I'm working on a dashboard tool for management and they requested to be able to see (in real time) which records were being accessed (and also WHO they're being accessed by...but that's an entirely different situation because there isn't a user logon sys in place yet).

    Wednesday, January 28, 2009 2:23 PM
  • User-821857111 posted

    Real Time??? The management need something better to do with their time than gawp at a screen all day to check up on their employees.  People management courses is one option that springs to mind....

    Anyway, you could put a meta tag in the page to refresh it every 60 seconds, or use AJAX tpo provide a smoother appearance and thereby increase the update frequency.  "Real Time" just isn't really workable in a web app.

    You would of course need some way of logging user activity within the database so that it can be queried.

     

    Wednesday, January 28, 2009 4:02 PM
  • User-1221895677 posted

    Agreed, Mike. (By the way, your website is AWESOME.  It's saved me from being ASP.SCREWED quite a few times!).

    So here's what I've come up with so far.

    In the database, I've added 2 columns to the table being accessed: 'Currently_Being_Viewed' & 'User_Viewing_Record' .

    1. Column 'Currently_Being_Viewed' is a Yes/No value that gets updated with a 'Yes' every time a user pulls up the record in their browser on the Page Load event.
    2. Column 'User_Viewing_Record' also gets updated in the Page Load event with the user's Net ID.

    So up to that point, I'm champ.  However, I'm stuck trying to reset the values back to the original when the user either leaves the page or closes the browser window.  Any suggestions?

     

    Wednesday, January 28, 2009 7:15 PM
  • User-1199946673 posted

    In the database, I've added 2 columns to the table being accessed: 'Currently_Being_Viewed' & 'User_Viewing_Record' .

    1. Column 'Currently_Being_Viewed' is a Yes/No value that gets updated with a 'Yes' every time a user pulls up the record in their browser on the Page Load event.
    2. Column 'User_Viewing_Record' also gets updated in the Page Load event with the user's Net ID.

    And when do you set 'Currently_Being_Viewed' back to 'No'. And what if more than 1 user is viewing the same record?

    I think you would better of creating a new table with the following fields:

    TableName
    PrimaryKeyFieldName
    PrimaryKeyFieldValue
    UserID
    Timestamp

    When a user is viewing a record in a table, you add a record in this table, and then you'll have all the data you'll need

    Wednesday, January 28, 2009 7:31 PM
  • User-1221895677 posted

    Right...but I'm still stuck with the same problem: what do I do when the user leaves the page?  How do I reflect in the database that the user isn't viewing the record once he's left the page or closed his window?

    Wednesday, January 28, 2009 7:44 PM
  • User-821857111 posted

    I haven't given this a huge amount of thought in detail but I think I would be looking to see if I could record the SQL or the procedure that the user causes to be executed.  If you are using Access (which is the forum you have posted to), you can create saved queries and call those within the app as if they were SQL Server stored procs.  Every time one gets run, you save the name or id of the user that runs it, the time and the name of the proc.  You would also need to save the parameter values.  That way, you could create a report that actually re-runs the query and displays the results, so that the "managers" can see who was looking at which records and at what time, and for how long.

    There may be better ways than doing this, but I've never been hired by the KGB before....

    Incidentally, what is the purpose of the report?  I mean, why do the managers want to see this activity? I ask this partly out of morbid curiousity, but also to see if the requirement can be fulfilled in an easier way.  Are they checking the workrate of people?  Making sure they only look at records they are supposed to?

     

    Thursday, January 29, 2009 2:33 AM
  • User-1199946673 posted

    Right...but I'm still stuck with the same problem: what do I do when the user leaves the page?  How do I reflect in the database that the user isn't viewing the record once he's left the page or closed his window?

    Mike already pointed that out:

    Anyway, you could put a meta tag in the page to refresh it every 60 seconds, or use AJAX tpo provide a smoother appearance and thereby increase the update frequency.  "Real Time" just isn't really workable in a web app.

    But like Mike I'm very curious why it's so important to know how long somebody is watching a particular record/dataset?

    Thursday, January 29, 2009 6:56 AM
  • User-1221895677 posted

    Hi Mike,

    Man, I like the way you think;  you've nailed the absurdity of what's going on. 

    Long story short, we had an unauthorized employee view records in the Access Database (easy to do, its on a shared drive with no security permissions).  Management caught wind, situation exploded in the boardroom, buzzwords like ".NET" and "Ajax" were thrown around, and now we're here.  (In my defense, I wasn't in the meeting to suggest another course of action).  They're grande plan is to have a web-front end for users, and a separate "monitoring" front-end for managers.  I'll let your imagination take it from there: In the last 3 days, I've experienced serious scope creep, ridiculous deadlines, and just about every other symptom of poor dev planning.

    I can't consider myself completely unlucky, though -- I get to learn more about how all this fancy-shmancy .NET stuff works. ;)

    Thursday, January 29, 2009 12:33 PM
  • User-821857111 posted

    Oh. My. Gawd.

    If security of access to records is the issue, upgrade to SQL Server and implement a role-based authorisation system with auditing. Anyone found sharing their login with anyone else after it has been implemented should be fired.

    Job done.

    If you have existing Access forms etc, they can still be used, and SQL Server Express is free.

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, January 29, 2009 2:44 PM
  • User-1221895677 posted

    Just wanted to post a follow up - Here's what I ended up doing.

    Because I couldn't capture how long each user spent at a record, I decided to capture the changes the user made to each record and when.  (Needless to say, a number of queries were developed so that management could look at this data in a variety of ways). It sufficed enough and the big wigs signed off on it.

    I'd like to visit this topic again at some point.  Every time I go to youtube.com, I'm mocked by the "Videos Being Watched Right Now" banner at the top of the page. 

    Thanks for all the input.  It is truly appreciated.

    Wednesday, February 4, 2009 1:23 PM
  • User-821857111 posted

    Every time I go to youtube.com, I'm mocked by the "Videos Being Watched Right Now" banner at the top of the page. 
     

    What?  And you believe what that tells you??!?

    [;)] 

     

    Wednesday, February 4, 2009 4:17 PM