Continually poll db for changes and update display as nec.

[MikeL04]

Here is the problem.

I have an enhancement that I need to make to a security system in which a vb, or even an asp app if possible, will display an employee's picture and access point upon scanning their security card at a door. On the scan, a database is queried and a row is inserted to a history table with the employee's id etc. This is the event in which I need to invoke the action of updating the display of the app to show the user who has just scanned their card.

I am used to web application development so this concept is giving me some trouble. I need to continually poll the table for inserts and on an insert display the updated information in the VB app. I am not sure if this is the best way to tackle this, the other would probably be to use a trigger on the table to tell the application to refresh it's display. Either way however, I am not sure how to accomplish this. With the first, how would you poll a db for changes continuously in VB and use this to invoke an event? If I were to use a trigger, how could you get the trigger to invoke an event in a VB app?

Thanks,
Mike

 

[vb5prgrmr]

My advice is not to poll the database even if you have a high powered DB and very few stations that poll it. This causes undue stress not only to the server but to the network also.

Being that you are using a reader whether magnetic or barcode (or fingerprint/retnal) you should be able to interface with that reader (if you have not already and from the sounds of it you have).

So from the sound of it you have a program that reads from the reader and updates a database. Why could you not modify that program so that it will send a message to another program (the display program) that is listening on a port?

Just a thought

Good Luck

 

[MikeL04]

Thanks for the reply. This will not work in my case because the application which interfaces with the scanners is third party. That is, the security company has their software already in place which is interfaced with the scanners, and making modifications to it is not an option. However now, there is an internal request to add the ability I mentioned above leaving myself access to only the database.

Any ideas?

 

[vb5prgrmr]

Ok, three more thoughts and I'm done.

1. Is it possible to monitor the port that the device is on and not interrupt the other program?

2. If you are using SQL server as the back end then you could put a trigger on the table that calls an exe/dll that will send the message to the display program for it to refresh itself.

3. Taking that this is a security matter is it possible to put a button on a form that the guard could press to poll the database after the swipe of the card?

Good Luck

Anybody else have any thoughts???

 

[SemperFiDownUnda]

You can use extended stored procedures (which are just normal functions in DLLs) and call this from a INSERT TRIGGER. You could also use some of the system stored proceedures to help you "xp_cmdshell" which lets a SQL Server procedure call a operating system call. This call could be a small VB program that makes a brocast to your security workstation to have them refresh their display.

How you get that app to talk to your security app is up to you but you can use COM, Pipes, TCP/IP. What ever floats your boat.

 

[MikeL04]

Thanks for all the replies and ideas. Sorry it took me so long to reply but I was off on vacation for a bit.

Anyways,I have gone ahead and prototyped an extended stored proc that acts as a TCP/IP client app that connects to a server app on the same machine and sends the EventId which inturn is brodcast by the server app to any other connected clients. Since I used TCP/IP in an extended proc I wanted to keep the overhead low and as much transmission out of the server process space so I envision having the client apps, on recieving an EventId, to query the database by that EventId and retrieve the persons picture and display it. This way the majority of the load is in the clients' process space.

Anyone see any problems with what I envision as a solution or have I missed something. Suggestions for a better way?

Thanks all.

Mike

 

[vb5prgrmr]

I think you have a viable solution that will also allow for expansion in the future.

Good Luck