update data control

I am slowly getting my old vb 5 program updated to run in xojo… but I don’t know how to reproduce this activity:

My user is shown a form with client information (frmClients). The use can click a ‘grid’ command and it opens a second form with a listbox populated with a list of clients. the user clicks on one client, and clicks on a button that is supposed to update frmClients to show the selected client. However, my code produces a new frmClient form that is blank. The current code attempt is below: (the MsgBox is only to see if the SQLQuery is correct).

tFileName = Listbox1.Cell(Listbox1.ListIndex, 0)
frmCLIENTS.Data1.MoveLast 'trying to get the fields to refresh and show the new data
frmCLIENTS.txtFields(0).Refresh 'trying to get the fields to refresh and show the new data


I assume that your VB5 program was using an Access database or something similar. If so, then I would recommend to use ADO for data access. You can use ADO with Xojo. You don’t have a data control such as was found in VB5. I was never a big fan of the data control anyway… I always used ADO (and DAO before that.) It takes just a bit more work, but in the end it allows better control over the data and it is portable! You will have to define your ADO objects as OLEObjects. Here is an example of a method with 2 separate database actions. Bear in mind that I am using SQL Server with stored procedures, but you can get the general idea. This method is still being worked on, so there is still a bit of debug code in it. It is not finished, but it works. I also changed the IP address, username and password of the database for obvious reasons. I have various properties holding some of the connexion information. This is not going to work for you if you copy it as is. This is from a short track speed skating club management program that I am porting from VB6 to Xojo (web target).

Dim CN as new OLEObject( “ADODB.Connection” )
dim RS1 as new OLEObject( “ADODB.recordset” )
Dim adoCmd as new OLEObject( “ADODB.Command” )
Dim adoCmd2 as new OLEObject( “ADODB.Command” )
dim myDatabaseName as string
dim RetVal as Variant
Static Essais as integer

myDatabaseName = “GCPV_”
myDatabaseName = mydatabasename + me.IAM.Environnement +"_2"
cn.CursorLocation = adUseClient
cn.Open “Provider=SQLOLEDB; Data Source=zzz.aaa.bbb.ccc; Initial Catalog=” + mydatabasename + “; User ID=xyzxyz; Password=aaabbbcccddd;”
adoCmd.activeconnection = CN
adoCmd2.activeconnection = CN

’ vrification de l’usager
adoCmd.CommandText =“AuthenticateUser”
adoCmd.CommandType = adCmdStoredProc
adocmd.parameters.append adocmd.createparameter("@Club", adchar, adparaminput, 3)
adocmd.parameters.append adocmd.createparameter("@PUser", advarchar, adparaminput, 25)
adocmd.parameters ("@Club") = iam.Club
adocmd.parameters ("@PUser") = iam.User
RS1 = adocmd.execute

'paramtres pour la mise jour des essais et du statut de connexion de l’usager
adoCmd2.CommandText =“AuthUpdate”
adoCmd2.CommandType = adCmdStoredProc
adocmd2.parameters.append adocmd.createparameter("@Club", adchar, adparaminput, 3)
adocmd2.parameters.append adocmd.createparameter("@PUser", advarchar, adparaminput, 25)
adocmd2.parameters.append adocmd.createparameter("@Lock", adboolean, adparaminput, 1)
adocmd2.parameters.append adocmd.createparameter("@Fail", adinteger, adparaminput, 4)

if rs1.recordcount >0 then 'User exists in the system for the club
if Essais =0 then
Essais = rs1.fields(“Failures”).value
end if
if rs1.fields(“lock”).value = true then ’ User has been locked
msgbox (appmsg(10))
rs1 = nil
CN = nil
if TestCode = rs1.fields(“Checksum”).value then
’ loginscreen.txtUserName.text = “”
’ loginscreen.txtpw.text = “”

    'remettre les essais  0
    adocmd2.parameters ("@Club") = iam.Club
    adocmd2.parameters ("@PUser") = iam.User
    adocmd2.parameters ("@Lock") = False
    adocmd2.parameters ("@Fail") = 0
    session.Database = myDatabaseName
    RetVal = adocmd2.execute
    return true
    Essais = Essais +1
    adocmd2.parameters ("@Club") = iam.Club
    adocmd2.parameters ("@PUser") = iam.User
    adocmd2.parameters ("@Fail") = Essais
    'loginscreen.txtUserName.text = ""
   ' loginscreen.txtpw.text = ""
    if Essais >= 3 then
      ' lock user out
      adocmd2.parameters ("@Lock") = True
      RetVal = adocmd2.execute
      'enregistrer le nombre d'essais
      adocmd2.parameters ("@Lock") = False
      RetVal = adocmd2.execute
    end if
    return false
  end if
end if

msgbox appmsg(6)
'loginscreen.txtUserName.text = “”
’ loginscreen.txtpw.text = “”
return false
end if

rs1 = nil
'adoCmd = nil
'adoCmd2 = nil
CN = nil

Actually, in the process of converting, I went to mysql on a separate server as opposed to access (I am writing it to run in Linux).

I only use the data control for a few forms where it was easier to have it populate the fields on the form. The rest of the code does not use the controls.

In this instance, I just want to update the display on the original window, which was easy to do in the old vb program (yes, using access), but I don’t seem to have the syntax down to do it in Xojo.