Upgrading VB6 VS2005

[Jerome]

Hoi,
Some advice about VS2005 needed
I have a major project written in VB6 where all database interactions are
nicely combined in one ClassModule witch gave
me the opportunity to work with the Connection.Execute method. The
ClassInitialise makes the connection and from then
all works like a charm. The module contains about 250 insert/update/select
subs/functions
Now, when trying to make it inVS2005, the inserts/updates subs makes no
prblem to convert because the string for that
are almost the same:

Sub InstelFaktInitNummer(ByVal pWaarde As Integer, ByVal pCode As String)
Dim strSql As String
strSql = "UPDATE Dagboek SET da_nummer = " & pWaarde & " Where da_code =
'" & pCode & "'"
connection.Execute (strSql)
End Sub

So, making a command object and executenoquery instead of the
connection.execute does the trick, no problem. Altough the connection will
remain open as was in VB6

But...what about a function wich returns a Recordset (VB6) ??
fi:
Function GetArtikelsBeheerMain() As ADODB.Recordset
Dim strSql As String
strSql = "SELECT ar_id, ar_code, ar_naam, ar_aantal, ar_inbestel, ar_pr,
" & _
"ar_pv, ar_min, ar_max, ar_discon, " & _
"ar_lever, ar_maxkort, ar_barcode, ar_datmaak, ar_usermaak,
" & _
"ar_datwijzig, ar_userwijzig FROM Artikel ORDER BY ar_code"
Set GetArtikelsBeheerMain = connection.Execute(strSql)
End Function

Then on a (MDI child) Form i just call
SET MyGrid.Datasource = DbConn.GetArtikelsBeheerMain
(info....DbConn is an instance of the ClassModule)

My question: How to do this last one in VB2005 ? How can i put ALL the
funcions in 1 module for retrieving the database
recordsets (dataset/tableset or whatever they may be called nowadays) and
call/open/close them?

Thanks in advance for all suggestions
Jerome

 

[Cor Ligthert [MVP]]

Jerome,

In my opinion is the first thing you have to investigate the Connected (Ado)
versus the Disconnected way of use (AdoNet), which gives you direct
diferences between pessimistic and optimistic concurrency.

As a sample in this

http://msdn.microsoft.com/library/d...s/cpguide/html/cpconoptimisticconcurrency.asp

I hope that this gives some ideas

Cor

 

[Jerome]

Jerome,

In my opinion is the first thing you have to investigate the Connected
(Ado) versus the Disconnected way of use (AdoNet), which gives you direct
diferences between pessimistic and optimistic concurrency.

As a sample in this

http://msdn.microsoft.com/library/d...s/cpguide/html/cpconoptimisticconcurrency.asp

I hope that this gives some ideas

Cor

Hi Cor,
Thanks for the link but i was already aware of that issue about locks.
That's just one of the points! The record wich is actually on edit schould
be locked to others.
If someone is making an invoice, i certainly do not want others to work on
the same one. Wich is now the
case in NET as everyone has a copy of the same data on his pc, not knowing
that others are perhaps already doing the same.
On the other hand, there is no need to check wether the fields in the dB are
old or modified a few nanoseconds before; Update/Insert/Delete means
update/insert/delete, unconditionaly and then requery the dB to have the new
data to confirm.

Regards and thanks for the thinking
Jerome

 

[William \(Bill\) Vaughn]

Jerome,
This is an involved (but not particularly complex) problem that we've written about for decades. There are several approaches that you can take and ADO.NET supports most of them. What you're describing is creating a pessimistic lock on a specific invoice. This can be done by using transaction isolation. However, in my books I describe a number of scenarios that don't use this approach--they design the system to prevent collisions in the first place. This means we don't permit two clerks to have access to the same customer at once. I describe this in detail in my old Hitchhiker's Guide books and in ADO and ADO.NET Examples and Best Practices.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Jerome]

Hi Bill,
That's indeed what i taught. As a matter of fact it is what i am doing now with the existing application made in VB6 and access as backend.
The concept is not more than a master-detail concept. The mastertabel already contains a field called "Status" with 3 possibilitys. Lets call it "N" means NEW_editing_allowed, "V" means DONE_no_further_editing_allowed, "X" means EDITING_in_progress_by_another. That way i can block the records that should be left alone. So when a "A" is opened by someone, the Status will be changed to "X" and updated in the dB so no one else can open the same record. Works ok.
The details are made with a Flexgrid from ComponentOne bound to a recset with LockType = adLockBatchOptimistic. This way i can clear the numeric cells when the row only contains a remark. Wich by the grid will automatically translated to the dB as vbNull in the numeric field.
What i am doing for learning VS2005, is started the same project from scratch and with sqlServer So far all readonly datasets and sqlcommands works perfect but that particular piece gives me some headache concerning the binding grid-dataset-databasetable.
This project will never be released however, just for trying VS NET

Regards,
Jerome


"William (Bill) Vaughn" <[email protected]> schreef in bericht Jerome,
This is an involved (but not particularly complex) problem that we've written about for decades. There are several approaches that you can take and ADO.NET supports most of them. What you're describing is creating a pessimistic lock on a specific invoice. This can be done by using transaction isolation. However, in my books I describe a number of scenarios that don't use this approach--they design the system to prevent collisions in the first place. This means we don't permit two clerks to have access to the same customer at once. I describe this in detail in my old Hitchhiker's Guide books and in ADO and ADO.NET Examples and Best Practices.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

 

[Cor Ligthert [MVP]]

Jerome,

This method you tell is as far as I can see the oldest form of a pesimistic lock there was done. It fears direct in one thing, that is if the computer that holds the X goes down, than you have what is called a deadlock. There is no report back from the disconnected dataset that it has happened and the data will only be available again as you set the X back by hand or by a procedure.

AFAIK is not the method from Bill, in Bills method can only an owner process his parts of the data. Therefore there will never be a concurrency problem.

Cor

 

[Jerome]

Yes Cor, this is 200% correct. But one has to make tradeoffs. As the programm is used only by 2 or 3 persons at the max, the risk is very small and on the other hand everybody there is very much acquinted with MSAcces so opening the tabel in Access and change the X in an A will be no great problem. Moreover, everybody who starts the program makes a automatic backup and another one once he/she shuts down the application
Allowing only the author of that part to edit it is no option. It may happen that one person sets up a new sales (read invoice) while another one has to finish and print it after correcting (give discounts) it.

Now...continuing on the possibility of a breakdown of a pc: If one has already made 20 ~ 30 details in the detail table and that breakdown occurs on the NET approach (disconnected datasets) there will be nothing in the database! That would also be a disaster.
So, my point is that there will allways be a potential danger of losing/damaging records.

We will see. Until now it is still a learning cycle for me, altough i doubt if i will ever make a commercial application in NET. Comparing to Vb6 one has to write at least the double of codelines, so it takes the double in time wich will never be paid for in a onetime application build on customers specifications.

Jerome

"Cor Ligthert [MVP]" <[email protected]> schreef in bericht Jerome,

This method you tell is as far as I can see the oldest form of a pesimistic lock there was done. It fears direct in one thing, that is if the computer that holds the X goes down, than you have what is called a deadlock. There is no report back from the disconnected dataset that it has happened and the data will only be available again as you set the X back by hand or by a procedure.

AFAIK is not the method from Bill, in Bills method can only an owner process his parts of the data. Therefore there will never be a concurrency problem.

Cor

 

[William \(Bill\) Vaughn]

Cor is right. The problem (exacerbated with JET/Access) is that race conditions and lazywrites, a dropped connection, a broken app and the phase of the moon can cause the master monitor row to be lost and you're pooched. In SQL Server (which is far more stable), we still don't recommend this approach (although it's been tried many times). It's simply too dangerous.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Jerome,

This method you tell is as far as I can see the oldest form of a pesimistic lock there was done. It fears direct in one thing, that is if the computer that holds the X goes down, than you have what is called a deadlock. There is no report back from the disconnected dataset that it has happened and the data will only be available again as you set the X back by hand or by a procedure.

AFAIK is not the method from Bill, in Bills method can only an owner process his parts of the data. Therefore there will never be a concurrency problem.

Cor