Archive

Archive for the ‘Visual Basic 6’ Category

SQLDMO for older programs


Sometimes i must maintain ancient software, Visual Basic 6 applications of the last millennium.
I still wondering why opening an Visual Basic 6 project after some years, i remember that was all ok and i’m the only one in the world that opens this ancient vm, magically some DLL reference is no more working.
So some code that was using SQL-DMO was causing the error “can’t create the object”.
In a folder there was a sqldmo.dll, no way to register/unregister, delete and the add the reference.
Fortunately i found this download, after the install pointing the dll reference to c:\Programs\Microsoft SQL Server\80\Tools\binn\sqldmo.dll (is a Windows XP vm…) my program can run; by the way connecting to SQL Server 2016 Developer inside a Docker Windows container.

Advertisements
Categories: SQL Server, Visual Basic 6

Vb6 software on Azure VM

There are still customers that uses old, aged software: for example, i have customers using my old program for truck transports, that uses a Microsoft Access file as database.
As i wrote in this post, this software in installed on a on-premise server, which was tipically Windows server 2003.
Now, these old servers (often recycled) are dying, the hardware today is more cheaper than in the past but an Windows Server 2012 license is still expensive for a small office.
So the idea: let’s try to migrate to Azure!
So i began an incredible amount of try & catch (errors..)
The application requires to send emails via CDO , with an attached pdf file generated from the Crystal Reports engine (used from the app for the printing activities).
In order to print via CDO is required a working email client; in the old Windows 2003 there was by default Outlook Express, instead from 2008 version in Windows Server there is no more an email client: this can be resolved installing Windows Mail, but it requires .NET 3.5
First error: using an Windows Server 2012 DataCenter NOT R2.
The main problem with the “normal” version is, IMHE (In My Humble Experience) that .NET 3.5 is not installable because you can’t use the downloadable 3.5 installer and this must be done from the Server Manager that complains about missing sources (for example see here, but i was not able to achieve the same result) : could be that attaching an ISO file (of Windows Server 2012) it works.
But i lose no more time and try creating instead an Windows Server 2012 R2 vm: same complain about the missing sources, but this time .NET 3.5 installed without issues.
The installation of Windows Mail was without problems and so for my app.
The only problem is the calendar for date input: my vb6 app uses mscal.ocx (perhaps a wrong choice) which is problematic.
For example in the setup.lst file generated from the vb6 package installer wizard the mscal is generated as

File10=@MSCAL.OCX,$(WinSysPath),$(DLLSelfRegisterEx),$(Shared),5/7/98 12:00:00 AM,90112,8.0.0.5007

But the DLLSelfRegisterEx must be changed in DLLSelfRegister otherwise the setup is not successful.
And in every case the problem is that the calendar was not displayed (interface in Italian):


The solution was to recreate from scratch the VM and create an vb6 installer WITHOUT mscal.ocx: it is already present in the Azure vm and trying to install another mscal.ocx causes big troubles in the registry; now the mscal.ocx is working ok.
Another curious thing was that in the vb6 code

With Flds
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = boolUseSsl
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = intSmtpAuth
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = strUser
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = strPwd
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strSmtpSvr
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = intSendUsing
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = intSvrPort
    .Update
End With

“microsoft” was accidentally written with the initial “m” uppercase, so the email sending was not working in the new 2012 R2 server (instead in Windows Server 2003 yes!); after the correction, emails sent.

Old Crystal Tricks

Many years ago i developed a software for transports by truck (that i should rewrite, i’m thinking to a Metro App) , in this blog i already wrote a post about.

In these days the problem: for another customer i have prepared a near identical pc, with the same Windows 2003 standard 32 bit , and surprise , the reports using UFL functions are not working : trying to launch them = crash , the program exits without errors ; in the event viewer only a generic message that my program crashed.

What is a UFL function ? In old Crystal Reports (i don’t know for the latest release, my program uses reports that was developed with Crystal 9 , Service pack 7) you can use custom function implemented in Visual Basic DLL; these functions are visible in Crystal if the name begins with “CRUFL” , so i have developed a dll named CRUFLWtk


That gives the cities reached from a truck in a trip : my program has a Trips archives where for every Trip there is a list of Cities reached in the Trip .

Compiling the DLL you can see in Crystal the new functions available for a formula, in my case


These functions does nothing else to give a list of cities separated from a comma, for example “Torino, Genova,Milano”

After some attempt (also the install of Crystal Reports on the server…) there were 2 options:

  1. Make a image of the working server done with Vmware vCenter Converter, then launch on the other customer server a VmWare Player using this vm
  2. Search an alternative to UFL functions, because these functions in this case does nothing of very special

The UFL reports (launched from the vb6 program) are perfectly working on every Windows XP on the earth i have tried, so i assume that there are problems with Windows 2003, and the fact that for a customer all is working under Windows 2003 is a strange miracle that i have no time to investigate; finally for this software there are no future developments (as vb6 version).

I have decided for the second option thinking to the old good WhilePrintingRecords that i have used a lot in the past.

The first step was to insert a subreport , with a ADO source, but i was having the problem that also in Crystal there was authentication troubles, the database password was not recognized.

Here the strange discovery : in order to pass the database password of a Microsoft Access file to a subreport (the Access file is password protected, ok is a weak protection but best than nothing) there is a curious procedure (discovered after a last desperate attempt………):

  1. Insert a subreport in Crystal


  1. Choose every time (do not reuse previous connections: apparently does not work !) to create a new ADODB connection


  1. Choose the OLE DB 4 provider, BUT click on “use a connection link file” and select a udl file prepared first (sorry for the Italian language..the XP vm is in Italian because the customers are tipically Italians)


  1. For to create a udl file right clic somewhere (better if in the program folder where are the reports) and create a file with .udl extension, open with double click
  2. Compile for a connection to a Access file (in my case) and make sure to write the db password, and save the udl with the password enclosed


 
 


Doing this , finally when i have defined the link between the main and the subreport no password problem when i was trying the report preview.


At this point how to obtain all the cities touched from our truck drivers, or the start and last cities ?

In my program we have an archive of steps, with a start and a end for every step:


Well, we can define our subreport with these formulas in order to obtain a list of all cities

INIT

WhilePrintingRecords;
StringVar ConcatenatedID := "";

RUNNING

WhilePrintingRecords;
StringVar ConcatenatedID := ConcatenatedID + {GetSintesiPercorsoViaggioCrystal.SiglaPartenza} + "," + {GetSintesiPercorsoViaggioCrystal.SiglaArrivo} + ",";

SHOW

WhilePrintingRecords;
StringVar ConcatenatedID;

GetSintesiPercorsoViaggioCrystal in my sample is a view without parameters that gives a table as


With the subreport defined as


We have a result as (not the illustrated case)


 
 

The subreport in enclosed without borders, with a height equal to the one of the other normal fields.

For the other case where we need the start city of first trip and the end city of the last trip we must change our formulas:

INIT

>WhilePrintingRecords;
StringVar strStart := {GetSintesiPercorsoViaggioCrystal.SiglaPartenza};
StringVar strEnd := "";

RUNNING

WhilePrintingRecords;

SHOW

WhilePrintingRecords;
StringVar strStart ;
StringVar strEnd := {GetSintesiPercorsoViaggioCrystal.SiglaArrivo};
strStart + " --- " + strEnd

The result:


The only problem is that specifying”Can grow” in the subreport and in the main report the textbox does not grow if the text field is larger than the subreport: so beware of this.

For now is fine, i’m out of the Windows 2003 UFL reports nightmare.

Vb6 software on 64 bit adventures

Many years ago i wrote an Visual Basic 6 program for truck transports management , named WinTruck (poor fantasy…)

This is using an Microsoft Access MDB file because at the time the SQL Server 97 MSDE was reserved, if i remember well, to MSDN subscribers (and i wasn’t at this time an MSDN subscriber) and anyway i was reading , at the time, of some problems (i don’t remember.. it was the year 1999…).

So the decision of to use Access, decision unlucky: there are still some customers that use this program ! why unlucky?

Until the customers was using this program in a simple network of 32 bit computers with Windows 2000-XP no problem: 10 years with some minor fix.

Now these customers has renewed the networks , and begins to buy 64 bit machines: a nightmare.

The first attempt was to directly install on the 64 bit pc the software client: every sort of strange errors , and at the time of first install i have copied the database on an my virtual XP 32 bit Vm , deleted older entries from an Access 2000 and compacted the file : after some time in production the deleted records … newly visible !

Then i have tried to use an microsoft virtual pc instance, no more “ghost” records, but every week the database was corrupted, fortunately copying this db on my pc , open the db with Access 2000 and then compacted resolves the issue.

Another attempt was to use vmware player : still corruptions, even worst because the mdb was so badly trashed that only Access 2010 was able to recover the mdb, with some loss of data and some primary key on tables lost : it was needed to open the mdb in Access 2000 (by precaution) and modify the table structure in order to redefine the primary key .

I think the problem was that the user goes away and when the computer goes in standby this causes damages, but the users (two) was absolutely sure that they exit from the program when they got away from the pc. so ?

The drastic solution : an old server , absolutely 32 bit only, with windows 2003.

In Windows 2003 i have installed my vb6 program , and the two customers uses the program via remote desktop.

I have defined two user; logging into windows with the customer profile i have defined the private work folder in the registry (my software for every user has a private folder for local files) , and all works now without issues, with more speed than before.

The only problem is that my program requires an administrator user otherwise there are errors about perms, and the program executable is added on the Data Execution Prevention (DEP) exclusion list (control panel->advanced->Performance Settings->DEP tab) : i think because some avi animations (used in the program) are in the resource file and copied into user private windows folder when needed, but anyway sometime this temporary file is generated on c:\

I should investigate because this happens : if i could define every user as non admin i can have more than 2 users (the windows 2003 limit for administrative users) but in this case there are just 2 users and i don’t worry about , for the moment.

Categories: Visual Basic 6, VmWare

ADO SQL strings automation

I was working on a old vb6 program, and was trying to optimize the speed.

In this old program many Sql strings was obtained by concatenating strings, exposing the program at an heavy risk from the point of view of Sql injection; and using parameterized calls the sql engine is able to construct an execution plan.

A tipical sample was:

    Set rsGiorni = New ADODB.Recordset
    With rsGiorni
        Set .ActiveConnection = ocLogin.cnApp
        .Open sSql, , adOpenStatic, adLockReadOnly, adCmdText
        If Not (.BOF And .EOF) Then
            sSql = "INSERT ContrGiorni (cgg_IdContr,cgg_Giorno,cgg_Inizio,cgg_Frequenza,"  _
                    "cgg_Fine,cgg_Visite,cgg_Note,cgg_dtModifica,cgg_uteModifica,cgg_dtIns,cgg_uteIns, cgg_CodStudio)"  _
                    " VALUES(?,?,?,?,?,?,?,?,?,?,?,?)"
            Set cmdGiorni = New ADODB.Command
            Set cmdGiorni.ActiveConnection = ocLogin.cnApp
            cmdGiorni.CommandText = sSql
            cmdGiorni.CommandType = adCmdText
            Do Until .EOF
                cmdGiorni.Parameters.Refresh
                cmdGiorni.Parameters(0).value = GetFields(Rs, "ctr_Id")
                cmdGiorni.Parameters(1).value = GetFields(rsGiorni, "cgg_giorno")
                cmdGiorni.Parameters(2).value = GetFields(rsGiorni, "cgg_Inizio")
                cmdGiorni.Parameters(3).value = GetFields(rsGiorni, "cgg_frequenza")
                cmdGiorni.Parameters(4).value = GetFields(rsGiorni, "cgg_Fine")
                cmdGiorni.Parameters(5).value = GetFields(rsGiorni, "cgg_Visite")
                cmdGiorni.Parameters(6).value = GetFields(rsGiorni, "cgg_Note")
                cmdGiorni.Parameters(7).value = dtNow
                cmdGiorni.Parameters(8).value = ocLogin.UserName
                cmdGiorni.Parameters(9).value = dtNow
                cmdGiorni.Parameters(10).value = ocLogin.UserName
                cmdGiorni.Parameters(11).value = GetFields(rsGiorni, "cgg_CodStudio")
                cmdGiorni.Execute
                .MoveNext
            Loop
        End If
    End With

The list of fields was easily obtained with SQL Management Studio (and converted on a single line with Notepad++) , but there were tables with many fields , and the generation of a sequence of “?” with a “,” as separator , in a number equal to the number of fields was a nightmare.

But i thinked: i’m a programmer … So with a regular expression i have instantly generated the list of “?”

My expression was:

[\[\w\]]+

Because my field list is generated from SQL management studio surrounded with brackets [] .

In Notepad ++ :


The same operation done in C#:

System.Text.RegularExpressions.Regex.Replace("[mac_IDMov] ,[mac_DataDal] ,[mac_OraDal] ,[mac_DataAl] ,[mac_OraAl] ,[mac_Note] ,[mac_dtIns] ,[mac_uteIns]", @"[\[\w\]]+", "?")

Categories: Visual Basic 6