What is a better way to implement a simple database select/update program in vb6?

Question

ok here's the rhubarb - I'm working on an application that has been written in VB6 (in which I have less than 1 month's experience), and the gist of the application is it's a simple database select/update type of app.

Basically all it does is you can search for employees (selecting the record from the database), and edit their data (and update the database).

Well, the way this is done in the codebase I inherited is through an ungodly number of ungodly large SQL query strings. Here's a not-really-made-up-but-changed-to-protect-the-innocent example of what I'm talking about:

'Post Transaction to Database
If actionToDo = "Change" Then
    Set coffeeTableRS = jawsTheSQL.Execute("Update coffeeTable set Name = '" & txtName.text & "', OriginalName = '" & MOriginalName & "', Prefix = '" & txtPrefix.text & "', FName = '" & txtFName.text & "', MName = '" & txtMName.text & "', LName = '" & txtLName.text & "', Suffix = '" & txtSuffix.text & "', Relationship = '" & txtRelationship.text & "', " & _
      "Addr1 = '" & txtAddr1.text & "', Addr2 = '" & txtAddr2.text & "', StreetNumber = '" & txtStreetNumber.text & "', StreetName = '" & txtStreetName.text & "', City = '" & cboCity.text & "', State = '" & ChkNull(cboState.text) & "', ZipCode = '" & ChkNull(txtZipCode.text) & "', ZipCode2 = '" & ChkNull(txtZipCode2.text) & "', " & _
      "Birthdate = " & MBirthdate & ", SSN = '" & Trim(txtSSN1.text & txtSSN2.text & txtSSN3.text) & "', OccuCode = '" & currentOccupationCode & "', OccuValue = " & currentOccupationValue & ", ChangeDate = '" & Format(MDate, "yyyy/mm/dd hh:mm:ss") & "', Active = '" & IIf(chkActive.Value = vbChecked, "", "I") & "'" & _          
      " where IDnumber = '" & txtIDNumber.text & "'")

And there is one of these for each action that we can take (add, edit, delete, etc.)

So in other words, when I was asked to add a simple checkbox control to the main form that handles all of this, I had to add it in about 15 different places. I had to add it to all these queries to make sure it was being retrieved on record retrieval and set on record set, as well as setting the flag to compare the data to "true" when the checkbox status didn't match the record's status.

So I'm looking at all of this thinking "there has GOT to be a better/easier/more maintainable way to do this."

I know virtually nothing about VB6, but is there a way to make a simple select/update database app with a nice looking GUI (i.e. we can't just hand the client an editable DataGrid and say "here you go, it has everything you need") where the controls are automagically bound to the database (I'm guessing via fields of the same name), and the update can be handled easily as well (since it's basically just "populate control name foo with value of database field named 'foo')?

What is the VB6 best practice way to do something like this? Is it significantly more easily done in a language like C#?

Thanks in advance

Edit: updated specifications based on answers given

• must be a standalone .exe

Answer 1

MS Access is perfect for this kind of application as long as there will be a relatively low number of concurrent users.

Answer 2

I'm guessing you don't want to rewrite the application? The query as it is written is written poorly. Not sure why the developer wrote it that way. It should use parameters. But, any "good" developer can write bad code in any language.

Answer 3

To be honest you need to do 2 things.

1. Inform those the have ownership that this application is completely unmaintainable. Make any changes needed as carefully but as expediently as you can. Do not attempt to refactor it to a better approach.

2. Inform those that have ownership that they need to commision a re-write (in which case you should move away from VB6).

If these actions are not taken by management continue to do your best with the current app but do not refactor, instead update your CV, you know the rest.

Answer 4

I would move to a newer technology stack; but hey if thats not possible... since it looks like you're already using ADO, you'd want to use disconnected recordsets.

In general what you want to do is open the recordset with a connection, set the recordsets connection to nothing. Edit the recordset (it can hang around for any length of time) then get a new connection and set the recordsets connection to it. Then you Update the batch.

This is a good starting point: http://www.devguru.com/features/tutorials/DisconnectedRecordsets/tutDisconnRS.asp

Edit: I'm pretty sure you can bind that recordset to a datagrid, but hey it's been a while :)

Answer 5

There's a lot of different approaches, however from where you are right now here is what I would do:

1) For each form, make a single routine that will load that form based on key information passed to it. 1b) Change the embedded loading code to call these routines instead.

2) Do the same for Form actions to Create new records (Inserts) and to Delete Records.

3) Do the same for Updates, except that there can be more than one kind of update, so you may need more than one per Form/Table. These now constitute your Data-access routines for a Form/Table: Load(Select), Add(Insert), Remove(Delete) and ChangeX(Update (x)). Gather all of these into their own module or class: {form}_DBAccess. At this point there should be no embedded SQL left in any of the forms or business-logic modules.

4) Concurrent with this, change your data-access routines to use stored procedures instead or embedded SQL. Just make a corresponding stored procedure (QueryDef for Access DB) and copy the embedded SQL login in. Then change the vb6 code to call the stored procedure instead.

Answer 6

Shortly said, apply MVC, and in this case: split view from data.

I would do an Employee class holding all that fields of the table along with an update, insert and delete method (something like an Entity in Hibernate/JPA). Then in the form you would have 2 functions for loading and saving the GUI fields from/to the class fields/properties (as you wish). Each user action (add,edit,delete,...) would then change that Employee object through the GUI saving function and invoke update, insert or delete. Maybe you will need some other special updating functions, I don't know.

I'd say it shouldn't take more than 1 day to refactor the employee part this way and you'll get a clean separation of GUI and data. You will be also free of the dependencies of the GUI elements in your SQL and maybe some other logic involved.

I'm using this approach as well and it's flexible and stable enough.

Answer 7

Even a Jet MDB will support parameterized stored procedures from ADO. Barring that you can often use prepared queries as well. Both of these techniques allow you to separate the SQL out from the procedural activity in your program logic, as well as reducing the headache of SQL injection (intentional or accidental).

You might also consider using databound controls which would be much more like an Access application, but that has its weaknesses and would no doubt be a major rewrite.

The MVC approach has its good points, but can be overkill and often leads to a large, confusing, and difficult to maintain object model. It often has to be done in an impure manner where methods calls have side effects in other objects through Friend properties or methods. This usually occurs because once cast in stone it is hard to change as users refine the business rules over time and the object model no longer models the business properly. For applications with a short life cycle it might be the way to go though.

It can also work well where there is no single user interface, i.e. perhaps a Web UI, a desktop UI, and "alternative service delivery" UIs (IVR, public-facing Web applications, etc.). I have an application where I wish I had taken an MVC approach right now for this reason: a GUI program that must now also be able to run in a batch or command line mode.