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

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 2

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.