Best way to handle multi-user environment

[Aaron Smith]

What is the best way to handle data in a multiple user environment? We
have forms that will allow users to add edit and delete data from a
table on SQL server. The data could be edited on multiple machines at
the same time. How do you keep the dataset constantly updated with
changes made to the data? I'm playing with just calling the fill method,
but it seems pretty unstable at times. Especially when deleting records,
they don't seem to remove themselves from the grid when it is deleted
from another form. I am trying to call the fill method on the data
adapter in the Position_Changed event. This seems to work a little bit.
The problem comes in when adding and deleting records. If I go to a new
record and call the fill method, if there is a new record, they get put
in the grid, but then there is no new line to start adding a new record.

As you can see, I'm having multiple problems here.. So how is this
supposed to be done safely? Is there an easy procedure that I am just
not finding?

Aaron

 

[Bernie Yaeger]

Hi Aaron,

The fill method can be used to refresh the data from the datasource. Review
its various arguments.

As for the datagrid, you should try something like this:

MyGrid.DataSource = ds;
MyGrid.DataBind();

HTH,

Bernie Yaeger

 

[Aaron Smith]

Thanks Bernie.. I'm going to give it a shot with that DataBind() method.
I didn't have that in there before. I was just setting the
grid.DataSource = Nothing, then Setting it to the dataset and getting
ticked off that it didn't display anything in the grid. :-/

Hi Aaron,

The fill method can be used to refresh the data from the datasource. Review
its various arguments.

As for the datagrid, you should try something like this:

MyGrid.DataSource = ds;
MyGrid.DataBind();

HTH,

Bernie Yaeger

 

[Jan Hyde]

Aaron Smith <[email protected]>'s wild thoughts
were released on Mon, 29 Nov 2004 16:35:00 GMT bearing the
following fruit:

What is the best way to handle data in a multiple user environment? We
have forms that will allow users to add edit and delete data from a
table on SQL server. The data could be edited on multiple machines at
the same time.

We handle this by only allowing 1 user to edit a record. The
record is locked while a user edits the record, should any
other user attempt to edit the record the database generates
a record locked error and we inform the user that the record
is in use.

J

How do you keep the dataset constantly updated with
changes made to the data? I'm playing with just calling the fill method,
but it seems pretty unstable at times. Especially when deleting records,
they don't seem to remove themselves from the grid when it is deleted
from another form. I am trying to call the fill method on the data
adapter in the Position_Changed event. This seems to work a little bit.
The problem comes in when adding and deleting records. If I go to a new
record and call the fill method, if there is a new record, they get put
in the grid, but then there is no new line to start adding a new record.

As you can see, I'm having multiple problems here.. So how is this
supposed to be done safely? Is there an easy procedure that I am just
not finding?

Aaron

Jan Hyde (VB MVP)

--
The past does not repeat itself, but it rhymes.

Mark Twain

[Abolish the TV License - http://www.tvlicensing.biz/]

 

[Cor Ligthert]

Jan,

We handle this by only allowing 1 user to edit a record. The
record is locked while a user edits the record, should any
other user attempt to edit the record the database generates
a record locked error and we inform the user that the record
is in use.

Using AdoNet?

Cor

 

[Cor Ligthert]

Aaron,

Can you have a look at these pages

http://msdn.microsoft.com/library/d...us/vbcon/html/vboridataupdatesconcurrency.asp

It is not easy stuff however I hope it helps?

Cor

 

[Aaron Smith]

Thanks, Cor ... Looks like more than "light" reading. I will have to sit
down and read through those, I think they will help. The main problem I
was having I think I have resolved. Although I don't know if it's the
"proper" way to do it.

I have a PositionChanged event that calls a save/refresh routine.

In the save/refresh routine, I turn off the handler for the
positionchanged event. I did this because when you do an EndCurrentEdit
or CancelCurrentEdit (in case a new record has started), you will get an
error if you try to do an update. I then check to see if we are adding a
new record, if we are, I save true to a boolean variable. Then I do the
cancel or end edit depending on the new record boolean variable. Then I
do an update on all the datasets if there are changes. Then I do a fill
on all dataadapters, and then rebind the grid controls. Once all this is
complete, I check to see if we were adding a new record and then do an
AddNew on the binding context and then add the Handlers for the position
changed event back in.

It seems pretty complicated to do one simple thing, but I haven't found
a way to get around doing an update if the cursor is in a new record on
the grid control. When the user moves from row to row, even if it's from
an existing row or a new one, we have to check to see if there is any
new data on the SQL server, and if there is, we need to display it in
the grid. Otherwise, things could get messy with the users. A refresh
button is ugly to us, and a timed event would not work in the
environment that we want to put this software in. Concurrency really
isn't the problem, I guess, it's more of data being displayed properly.
Saving it is really easy and the chances of two people putting the same
data in at the same time is non-existant. I followed the rules on the
autoincrement fields, so that shouldn't be a problem either. We also
need to be updating the SQL Server with every change to a row, hence
doing a save and not just a refresh of the data.

This seems to work, with the exception that custom scroll bars for my
grid seems to be disappearing now, so I have to look into that....

Anyone have any other suggestions to make this easier? The only problem
with this approach is I have to add this code to EVERY form we have..
I'd like to make it some sort of a function that we could just call..
But that would probably take a lot of work too...

Aaron

Aaron,

Can you have a look at these pages

http://msdn.microsoft.com/library/d...us/vbcon/html/vboridataupdatesconcurrency.asp

It is not easy stuff however I hope it helps?

Cor

 

[Cor Ligthert]

Aaron,

That is a lot of text, the only thing I will say about concurrency is: take
a lot of attention to it, it is important and there are articles enough,
however you have yourself to look what is the best policy for you. About
security and concurrency I give not any advise or whatever in this
newsgroup, beside distributing that link that I showed you.

About the refreshment I think that there can be two things that can help
you.

The currencymanager and the dataview.find.

Have a look at this snippet and look what you can do with it. (You have in
my opinion to save the last key)

\\\
Dim dv As New DataView(dtVBreg)
dv.Sort = "Name,Country"
Dim findObject(1) As Object
findObject(0) = "Terry Burns"
findObject(1) = "EU"
Dim cm As CurrencyManager = _
CType(BindingContext(dv), CurrencyManager)
cm.Position = dv.Find(findObject)
DataGrid1.DataSource = dv
///

I hope this gives some ideas?

Cor

Thanks, Cor ... Looks like more than "light" reading. I will have to sit
down and read through those, I think they will help. The main problem I
was having I think I have resolved. Although I don't know if it's the
"proper" way to do it.

I have a PositionChanged event that calls a save/refresh routine.

In the save/refresh routine, I turn off the handler for the
positionchanged event. I did this because when you do an EndCurrentEdit or
CancelCurrentEdit (in case a new record has started), you will get an
error if you try to do an update. I then check to see if we are adding a
new record, if we are, I save true to a boolean variable. Then I do the
cancel or end edit depending on the new record boolean variable. Then I do
an update on all the datasets if there are changes. Then I do a fill on
all dataadapters, and then rebind the grid controls. Once all this is
complete, I check to see if we were adding a new record and then do an
AddNew on the binding context and then add the Handlers for the position
changed event back in.

It seems pretty complicated to do one simple thing, but I haven't found a
way to get around doing an update if the cursor is in a new record on the
grid control. When the user moves from row to row, even if it's from an
existing row or a new one, we have to check to see if there is any new
data on the SQL server, and if there is, we need to display it in the
grid. Otherwise, things could get messy with the users. A refresh button
is ugly to us, and a timed event would not work in the environment that we
want to put this software in. Concurrency really isn't the problem, I
guess, it's more of data being displayed properly. Saving it is really
easy and the chances of two people putting the same data in at the same
time is non-existant. I followed the rules on the autoincrement fields, so
that shouldn't be a problem either. We also need to be updating the SQL
Server with every change to a row, hence doing a save and not just a
refresh of the data.

This seems to work, with the exception that custom scroll bars for my grid
seems to be disappearing now, so I have to look into that....

Anyone have any other suggestions to make this easier? The only problem
with this approach is I have to add this code to EVERY form we have.. I'd
like to make it some sort of a function that we could just call.. But that
would probably take a lot of work too...

Aaron

 

[Aaron Smith]

When binding that currencymanager like that, will it automatically
update the datagrid if the dataset changes on a DataAdapter.Fill() ?? Or
would I have to recreate the DataView and then reset the DataGrid binding?

Aaron

 

[Cor Ligthert]

Aaron,

This questions goes far outside your subject of the message.

I think that it is better to make a new message and when you are using a
newsreader to sent the message crossposted (one message in one time to more
newsgroups) to the newsgroups.

microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.languages.vb

(Not for me I am active in both, although here the most).

In my opinion is the newsgroup as well to get diffent ideas from more
people.

Cor

"Aaron Smith" <[email protected]>>

When binding that currencymanager like that, will it automatically

 

[Aaron Smith]

I think I'll read that article you sent me yesterday first.

Aaron,

This questions goes far outside your subject of the message.

I think that it is better to make a new message and when you are using a
newsreader to sent the message crossposted (one message in one time to more
newsgroups) to the newsgroups.

microsoft.public.dotnet.framework.adonet
microsoft.public.dotnet.languages.vb

(Not for me I am active in both, although here the most).

In my opinion is the newsgroup as well to get diffent ideas from more
people.

Cor

"Aaron Smith" <[email protected]>>

When binding that currencymanager like that, will it automatically