Refresh Database

[sotoasty]

If you don't have a way to notify User a when updates are applied, you will need to devise a way yourself. 1 way would be to create another table with a single field and single row. Whenever user B/C/D updates a record in the table User A needs refreshed, update the new table with a value (1 for refresh). You can then use a timer to check the value of this table. If a refresh is required, you can pop up a message box asking User A if he wants to update right now or wait. That way, User A gets to decide if he is in the middle of something important and delay the refresh. If he says no, activate a refresh button on the screen. When a refresh happens, update the new table with a value (say 0 for just refreshed).

[DataMiser]

Yep, that is what I was thinking as well,

[DataMiser]

sory, i mean like this
i have application which using tab pages.
so user can show like 5 report/grid report.
if i use the check database method then i think the server will overload cause
the timer always send check routine and the 5 tab pages opened by user also make 5 timer and 5 check routine.
it will make server cpu usage rise.
thx

You only need 1 timer and 1 check routine. Look at the post by sotoasty. This table could have one record per table with different columns for different users.

Of course any code you add will increase processing but if this is done properly it should be minimal.

[RusdyRIP]

If you don't have a way to notify User a when updates are applied, you will need to devise a way yourself. 1 way would be to create another table with a single field and single row. Whenever user B/C/D updates a record in the table User A needs refreshed, update the new table with a value (1 for refresh). You can then use a timer to check the value of this table. If a refresh is required, you can pop up a message box asking User A if he wants to update right now or wait. That way, User A gets to decide if he is in the middle of something important and delay the refresh. If he says no, activate a refresh button on the screen. When a refresh happens, update the new table with a value (say 0 for just refreshed).

i see,but i have one opinion again..soryy..

let's say timer interval is 5 second to prevent cpu usage rise to high
first A is looking Grid with row 10,
2 second after that B is looking Grid with row 10 ( different computer same application)

then timer check if the table value is 1 or 0,and the value is 0 so there is no pop up asking.

the C is inserting record on database on different computer again.then update the new table value to 1

the grid at A computer will trigger first to pop up cause the value is 1, and asking if he wants to update and it says yes,so the grid is updated and the new table value become 0

the grid at B computer will not trigger because it check the new table value is 0,
i think it will become bugs

thx

sorry bad english


@datamiser

yes i just have 1 timer and 1 check routine,but i have more than 5 grid at different forms,
and i must implement each timer at each form that have grid

thx

[DataMiser]

I think you are seeing more complexity than what is here.

Let's say you have a 10 user setup with 5 tables that may be open and may need to be refreshed. You would want one record per table to monitor with one field per connected pc in each record as well as a field indicating which table you are referring to.

When a user changes data in a table the flag would be set to 1 in that record for the other 9 users but not the user who made the change.

The timer would execute a simple query select TableName from UpdatedDefs where thispc=1. The query would return a list of tables that may need to be refreshed, assuming they were being viewed at the time. If any records are returned then a prompt asking the user if they want to refresh now if so execute a routine that refreshes the proper views that are active and have changed otherwise you could enable a button like suggested which would call the same code.

Basically you are looking at one simple query that at most will return 5 records consisiting of only one field each and if you are doing it every 5 seconds then surely yoru server has the power to handle such a trival transaction load. If not then I feel sorry for the users when they actually need to refresh a large data table.

[DataMiser]

yes i just have 1 timer and 1 check routine,but i have more than 5 grid at different forms,
and i must implement each timer at each form that have grid

thx

You do not need a timer on every form, this can be done with one timer on the main form if done correctly.

[RusdyRIP]

I think you are seeing more complexity than what is here.

Let's say you have a 10 user setup with 5 tables that may be open and may need to be refreshed. You would want one record per table to monitor with one field per connected pc in each record as well as a field indicating which table you are referring to.

When a user changes data in a table the flag would be set to 1 in that record for the other 9 users but not the user who made the change.

The timer would execute a simple query select TableName from UpdatedDefs where thispc=1. The query would return a list of tables that may need to be refreshed, assuming they were being viewed at the time. If any records are returned then a prompt asking the user if they want to refresh now if so execute a routine that refreshes the proper views that are active and have changed otherwise you could enable a button like suggested which would call the same code.

Basically you are looking at one simple query that at most will return 5 records consisiting of only one field each and if you are doing it every 5 seconds then surely yoru server has the power to handle such a trival transaction load. If not then I feel sorry for the users when they actually need to refresh a large data table.

sorry i don't really understand it.
10 user (1 to 10)
5 Table(A,B,C,D,E,F)

table i must have :
Name:RefreshTable
GridTableName|Flag
A |0
B |0
C |0
D |0
E |0
F |0

if 1 is inserting table A then he changed RefreshTable,record A will update the flag to 1.
after the user say yes to update the record A will update flag to 0

is that right?looks like this will gonna work,but still think that different time will make bugs like i said first,cause timer at user 2 different from user 1 and when user 1 is updated,user 2 check that flag is 0

thx




You do not need a timer on every form, this can be done with one timer on the main form if done correctly.

you are right,i think i can set the timer on mainform.

[dglienna]

You really have to learn about table locking and concurrency. You can lock for Update, or Exclusive access. If you have two users, SQL Server takes care of the locks and the uses do their thing.

TransA: get U lock on row
TransB: wants U lock on row, has to wait
TransA: convert U lock to X, does the work and (at some time) move on
TransB: convert U to X lock (A has now released its lock), does the work an move on

[DataMiser]

It seems that something is being missed here with the talk of record locking. User A is viewing the data. User B changes the data. User A will not see the changes until User A does a refresh. The lock for edit does nothing in the way of notifing User A that the data in the table has been changed by another user.

The locking will stop 2 people from making changes to the same record at the same time or table but when several users are viewing and one makes a change the lock does nothing for the visibility unless the data is refreshed on the other machines afterwards.

[DataMiser]

As for the table layout. Lets say we have 5 Tables A,B,C,D,E and 10 Pcs Named PC1 - PC10.

Lets say the table name is RefreshMe and the table columns are tblName | PC1 | PC2 | PC3 ..........

If the user on PC3 needs to check the changed tables the query would be select tblName from RefreshMe where PC3=1

Now if tables B and D have been changed we will get 2 records back B and D. After the refresh those records can be set to 0 but only in the column for PC3 so the other pcs will still be able to run thier own query on thier field and refresh as needed. There may be a better way to do this but I do not see a problem here from a logical standpoint other than you must account for each pc that will be connected by having the proper field in the table.

On the pc that does the edit/insert it would update all the columns on the record for the table in question except the one for the PC doing the edit.

Does that make sense to you?

[RusdyRIP]

As for the table layout. Lets say we have 5 Tables A,B,C,D,E and 10 Pcs Named PC1 - PC10.

Lets say the table name is RefreshMe and the table columns are tblName | PC1 | PC2 | PC3 ..........

If the user on PC3 needs to check the changed tables the query would be select tblName from RefreshMe where PC3=1

Now if tables B and D have been changed we will get 2 records back B and D. After the refresh those records can be set to 0 but only in the column for PC3 so the other pcs will still be able to run thier own query on thier field and refresh as needed. There may be a better way to do this but I do not see a problem here from a logical standpoint other than you must account for each pc that will be connected by having the proper field in the table.

On the pc that does the edit/insert it would update all the columns on the record for the table in question except the one for the PC doing the edit.

Does that make sense to you?

i understand
i think like this :

"Now if tables B and D have been changed we will get 2 records back B and D. After the refresh those records can be set to 0 but only in the column for PC3 so the other pcs will still be able to run thier own query on thier field and refresh as needed"

if the table B or D have been inserted/updated ,ie on PC4 then the flag on other pc will set to 1 (just table B or D).
if the other pc is viewing before updated/inserted ,it's okay

but if the other pc is viewing after updated(i mean like pc2 first don't open,then he open after updated)
don't it will be strange,because when the pc2 open, the timer also trigger to check the flag and found that the flag is 1 then pop up asking him to refresh.

sorry bad english

[DataMiser]

I would of course set it to 0 when I open the table

[RusdyRIP]

I would of course set it to 0 when I open the table

nice,i think that complete all my question

really thank you

[dglienna]

So, you finish everything up (about 300 lines of code) that works perfectly.'

What happens when they add a field or another user?

Look up locking. What would you do with 500 concurrent users? (besides lock up the server)

[DataMiser]

It was a simple suggestion that would work on a small scale multiuser application. Large scale would require different methods but that is beside the point.

Could you explain how locking a record for edit will help anyone using the system see the modified data if they were already viewing the data in a grid display? If there is a way that this would work it is one that I am unaware of. At first I thought you simply missunderstood the question but now it is hard to believe that you do not understand, either you know something I do not or ...