Record Locking

[daves]

Hi,

I'm an Oracle guy and just started using VB. I've developed on Access for many years.

I'm confused with this new way of loading all the data into a form (object) and then writing it all out.

What happens in multiuser applications where mulitiple people update the same data? If 2 people have the employee table open and make changes, (even to different records). Doesn't one over write the other?

I've been looking around for days for this answer. Do you know if there are any sample menuing systems out there?

Thanks,

 

[kulrom]

This question sounds very familiar to me ... have you asked this somewhere else maybe?
However with good planning and architecture of the application you will avoid such odd situation(s) where two people can update/delete the same data in the same DB. If you have something precisely i am willing to provide much explanation about it ... regards

 

[kulrom]

Hey, wellcome to the forum ...

 

[daves]

Hi,

I don't think it is that unusual...

here is the seniario.

you have 2 data entry clerks,
1 employee maint program.

you do a fill to an dataadapter. the table has 100 rows.

clerk 1 updates emp number 1 and clerk 2 updates emp number 2
but, the record set that clerk 1 has does not know aobut the updates to emp 2 done by the other clerk.

clerk 2 sends the updates to the database,
clear 1 sends the updates (anytime after clerk 2).

would not the second update clobber clerk 2's changes?

Also, a second way to look at it, both clerks want to update employee number 100. with the way that I understand ADO, they could both query the record and do the changes and send the changes back. The disconnect (bring all the data to the client) and then send the updates back does not allow for the database to know that two people are trying to update the same row.

In access, even in grid view, if you begin to change the record, Access blocks other people from changing it..

Does that make sense?

thank you for your reply!

Dave

 

[kulrom]

I cannot to resist to say that your question is very confusing ... however, this would be my answer:
Q: would not the second update clobber clerk 2's changes?
A: Yes! so what? Are you expecting something else?

Anyway, with good strategy and architecture you will never end up with such issue. Btw, are we talking about Web or Win programming here? What kind of application is that when you allow the users to perform update against the data so frequently?

I don't think it is that unusual...

That's unusuall indeed !

Regards

 

[daves]

Hi, Thank you again for your input.

It is a payroll application with over 600 employees and growing. The employees are being paid on commission from various sources and as such there are numerous people updating the data.

What was confusing about my post? Please give the chance to explain.

It will be a windows application.

Another issue around downloading all the data to the client, what if the table has thousands of rows? There will be allot of data.

How would you design the arichtecture? Record locking has been in every product that I've ever developed in from Oracle to Access. As soon as a user begins to edit data, the database locks that record.

Thank you

 

[kulrom]

Then for god's sake do not use dataadapter/dataset objects but rather datareader ... i have no idea what could be your problem with that. Otherwise i have made many server/client apps which are using MSAccess that is never been locked if you want to update it from more than one computer. Why it would at all?

 

[daves]

Thank you,

Problem is that I'm new to Visusal Basic and the documentation talks about dataadapters but not readers all that much.

Would you mind telling me a bit about datareaders and the difference in implimentation? Any doco and or samples where I could start to look at?

How does the reader object affect the 'binding adapter'?

thank you

 

[daves]

Hi,

I read the msdn articles on data reader. data reader has to do with selecting rows in code and cycling through them. This helps thank you.

But what about data bound controls and forms..

Thanks

 

[kulrom]

Sure Daves,
But if you want more concrete help please be more precise in the next post. What exactly do you want? To put data in which control(s)? Attach an example DB and i'll show you how to populate i.e. comboBox, listBox, listView etc.
Regards

 

[daves]

Thank you.

To keep it simply, say I have a form. Say only text boxes.

I want to have a combo box control that I can use to select the employee that I am looking for. Then, I want to be able to bring up this record on the screen. If the user starts to edit the record, it would be nice if no one else could try to update the record.

Also, I said only text boxes, maybe 1 other combo box?

The data source is sql server 2005.

If I get one good sample on how to do this, then I'm off and running.

Also, I will be writing programs to do batch updates (pay calcs) so for that forms I will use ado or some other form of SQL UPDATE to the database...

thank you again for your time and effort.

Dave

 

[daves]

reading a config file

Hi,

Since I'm going to deploy at the client's site, would you have any sample code on reading a config file to build a connect string on the fly?

That way I have a config file for remote development and another for the end user.

thank you.

 

[kulrom]

May i suggest you that you 1st take a look at this thread (there is a very useful attachment you will find it easy) http://www.vbdotnetforums.com/showthread.php?t=9975

Then back with something more concrete if you need an additional help ...
Regards

 

[daves]

thank you. live got the list control attachment and will look at it in detail.

one question, why did you use OLEDB rather then sqldata reader. Are you suggesting that I should use OLEDB rather then SQLDataReader?

take care

 

[kulrom]

Well, it is cuz i am using MSAccess as backend there ... if you are using MSSQL then just replace those oledb objects (connection, reader, command etc.) with SQL ones. Open the module where they are declared and replace with i.e. OleDb.OledbCommand with SqlClient.SqlCommand and so on ..
Regards