subscript out of range error on one workstation 2

[kskinne]

I have an app written in VB6 that we have deployed in an exe file. It uses ADO to connect to a database and open recordsets to either read data from or write data back to the database, based on user input in our front end.

This seems to work just file from everyone's workstation except one workstation, where it gives a runtime error 9: subscript out of range error message when it tries to open and return data from the database.

Any ideas what is causing this? Since they are running this from the exe file I cannot debug and troubleshoot from this workstation, because it does not have vb6?

If more information is needed please let me know.

Thank you,
Kevin

 

[Bluejay07]

Hi Kevin,

Usually the error 'Subcript Out Of Range' is a result of a change to either the database or the program and the corresponding indexno longer matches.

This usually occurs when accessing an array of somesort. Since this error seems to be localized to an individual machine, I would suggest the following:
- ensure the connection is looking at the correct database;
- adjust ODBC connections if necessary;
- is there a local copy of a database being referenced and not a global copy?

That is just a few suggestions as quick fixes. You may also want to have the developer create a new build (a new .exe version) of the program and try the new version of the program.

 

[kskinne]

Hi Bluejay thanks for the reply - I did try re-compiling the project and re-creating the exe file to no avail. After re-compiling I tried from my workstation and it worked fine, however it still will not run correctly from the other workstation. Still gets the same run time error. I cannot get this error to repeat on anyone else's machine.

Not sure if this helps or not but here is the code that is causing the problem:

Private Sub Form_Load()
Adodc2.ConnectionString = glbConn1
MySqlStrPart = "SELECT TimeClock.ID, TimeClock.EmployeeID, Employee.Name, TimeClock.TimeIn, TimeClock.TimeOut, TimeClock.TimeInRound, TimeClock.TimeOutRound FROM TimeClock INNER JOIN Employee on TimeClock.EmployeeID = Employee.ID"
MySqlStr = MySqlStrPart & " ORDER BY TimeClock.ID"
Adodc2.RecordSource = MySqlStr
Adodc2.Refresh
Set glbADORS5 = New ADODB.Recordset
glbSqlStr5 = "SELECT DISTINCT EmployeeID FROM TimeClock ORDER BY EmployeeID"
glbADORS5.Open glbSqlStr5, glbADOConn1, adOpenForwardOnly, adLockReadOnly
cboViewID.Clear
cboEditID.Clear
glbADORS5.MoveFirst
Adodc2.Recordset.MoveFirst
Do Until glbADORS5.EOF = True
  cboViewID.AddItem glbADORS5.Fields("EmployeeID")
  cboEditID.AddItem glbADORS5.Fields("EmployeeID")
  glbADORS5.MoveNext
Loop

dgResults.Columns(3).NumberFormat = "mm/dd/yy hh:mm AM/PM"
dgResults.Columns(4).NumberFormat = "mm/dd/yy hh:mm AM/PM"
dgResults.Columns(5).NumberFormat = "mm/dd/yy hh:mm AM/PM"
dgResults.Columns(6).NumberFormat = "mm/dd/yy hh:mm AM/PM"

Set glbADORS5 = Nothing

If glbEmployeeID = 52 Then
  cmdPrint.Visible = True
Else
  cmdPrint.Visible = False
End If

If glbEmployeeID = 52 Or glbEmployeeID = 81 Then
  cmdDelete.Visible = True
Else
  cmdDelete.Visible = False
End If

End Sub

Any help would be greatly appreciated. Thank you,
Kevin

 

[jadams0173]

Kevin,

While I really have no idea as to why your code doesn't work on one machine, maybe knowing the exact line of code that is causing the error would help. I would suggest to download MZ Tools which is a VB6 add in. Using MZ Tools you can add line numbers and an error trap to your code. In the error handler you can put something like

MsgBox "An error has occured while trying to process your request." & vbCrLf _
              & "Error Line: " & Erl & vbCrLf _
              & "Error Number: " & Err.Number & vbCrLf _
              & "Error Description: " & Err.Description, vbCritical, "Error Processing Request"

Where Erl is the offending line number.

Maybe then we can focus on that one line of code. Hope this helps.

 

[kskinne]

THanks for the tip on MZ Tools, that worked great. I added an error handler and here's what it found. The subscript out of range error is occurring on line 190 of my code. Here is the code with line numbers included:

Private Sub Form_Load()
10    On Error GoTo Form_Load_Error
20    Adodc2.ConnectionString = glbConn1
30    MySqlStrPart = "SELECT TimeClock.ID, TimeClock.EmployeeID, Employee.Name, TimeClock.TimeIn, TimeClock.TimeOut, TimeClock.TimeInRound, TimeClock.TimeOutRound FROM TimeClock INNER JOIN Employee on TimeClock.EmployeeID = Employee.ID"
40    MySqlStr = MySqlStrPart & " ORDER BY TimeClock.ID"
50    Adodc2.RecordSource = MySqlStr
60    Adodc2.Refresh
70    Set glbADORS5 = New ADODB.Recordset
80    glbSqlStr5 = "SELECT DISTINCT EmployeeID FROM TimeClock ORDER BY EmployeeID"
90    glbADORS5.Open glbSqlStr5, glbADOConn1, adOpenForwardOnly, adLockReadOnly
100   cboViewID.Clear
110   cboEditID.Clear
120   glbADORS5.MoveFirst
130   Adodc2.Recordset.MoveFirst
140   Do Until glbADORS5.EOF = True
150     cboViewID.AddItem glbADORS5.Fields("EmployeeID")
160     cboEditID.AddItem glbADORS5.Fields("EmployeeID")
170     glbADORS5.MoveNext
180   Loop
190   dgResults.Columns(3).NumberFormat = "mm/dd/yy hh:mm AM/PM"
200   dgResults.Columns(4).NumberFormat = "mm/dd/yy hh:mm AM/PM"
210   dgResults.Columns(5).NumberFormat = "mm/dd/yy hh:mm AM/PM"
220   dgResults.Columns(6).NumberFormat = "mm/dd/yy hh:mm AM/PM"
230   Set glbADORS5 = Nothing
240   If glbEmployeeID = 52 Then
250     cmdPrint.Visible = True
260   Else
270     cmdPrint.Visible = False
280   End If
290   If glbEmployeeID = 52 Or glbEmployeeID = 81 Then
300     cmdDelete.Visible = True
310   Else
320     cmdDelete.Visible = False
330   End If
340   On Error GoTo 0
350   Exit Sub
Form_Load_Error:
360   MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Load of Form frmTimeClockRecords - Error Line: " & Erl
End Sub

dgresults is the name of a datagrid in the form. Any ideas why this would be erroring out on this one workstation? I cannot get this error to repeat on any other workstations.

Thanks for your help,
Kevin

 

[jadams0173]

I would check the versions of MSADODC.OCX and MSDATGRD.OCX on the computer with the error in comparison with another PC that works.

 

[kskinne]

I checked the versions and this computer has version 6.1.97.82 for both of those OCX files. That is the same version as on my workstation where it successfully connects to the database without the error.

Just to test it, I copied my versions of the following files from my workstation over to the other:

MSADODC.DEP
MSADODC.OCA
MSADODC.OCX
MSADOX.DLL
MSDATGRD.DEP
MSDATGRD.OCA
MSDATGRD.OCX
MSDATGRD.SRG

Then I used regsvr32 to re-register the two ocx files on that computer. After doing that I tested and it still does not work, getting the same error, on the same line number.

Any other possible ideas?

Thank you,
Kevin

 

[kskinne]

And regarding my previous post, these are the OCX files that are in the C:\WINDOWS\System32 folder. We are using XP professional SP 3, just fyi

Thanks,
Kevin

 

[jadams0173]

Could it be a permission issue where the user does not have access to the system32 folder?

That's probably a long shot but could be a possibility.

 

[kskinne]

From their computer under their login I can access the system32 folder and view its contents, also copied/pasted the files there from a shared network location under their user login, so don't think that can be the case.

I am going to comment out the line(s) that are causing this error, since all they do is format the datagrid columns anyway, then see if it throws up any other errors or not. I'll post back with those results.

Thanks,
Kevin

 

[kskinne]

When I comment out lines 190 through 220, the error message disappears, but when the form opens there is no data in the datagrid. I run the same code (with the lines commented out) on my workstation, and the datagrid is populated with data results.

My code actually populates an ADO data control object on the form with data from a query, and then the datagrid is supposed to be populated with the records in the adodc recordset.

I have discovered, there is a combobox on the form, that is populated with values from one of the fields in the ADO data control recordset - the same recordset that is supposed to populate the datagrid. This combobox is getting populated with the correct values even on the problem workstation. There are also some other fields on the form that, upon opening the form, are getting populated with data from the first record in the ADO data control recordset.

So it seems able to connect to database and open the recordset on this workstation, and pull data from the ADO data control into other objects on the form, but for some reason the datagrid is not getting populated with the recordset data, that problem definitely seems to be isolated to the datagrid.

Any other reasons why this workstation would have these problems? fyi, it is using the same version of Windows XP as the other computers.

Appreciate the help,
Kevin

 

[HughLerwill]

Something must be different on the host machine;

You have apparently checked;
(All?) dependent file versions
Operating system version (fully updated or updated to the same level)

How about Regional settings?

 

[kskinne]

Hugh, for operating system, all the workstations are on Windows XP SP 3.

For the file version, we are all accessing the same .exe file on the shared network drive, through a shortcut, so we are all using the same version of the app.

Do you mean the regional options tab under regional and language options in the control panel? There you can change language, location, and formats for number, currency, time, short date and long date. If so which one of these should I be comparing?

Thank you,
Kevin

 

[HughLerwill]

>same .exe file ...

AND ALL the files it is dependent on.

>If so which ...

Basically any which are different. Dates, Times and Decimal character are usually worth a first look at. Could be I'm wrong in suggesting Regional settings but it is worth trying if you are really stuck.

 

[gmmastros]

there is no data in the datagrid

This is sounding more and more like a data issue. If you are using SQL Server, then I recommend you use profiler to capture the queries that are sent to the database. If you've never used profiler before, the following site gives a good explanation of how to set it up.

http://www.developer.com/db/article.php/3482216

Profiler will slow down your database. I recommend you start it, then open the form on the computer that is misbehaving, and then turn off profiler. You should not run profiler for a prolonged period of time, especially on a busy database that is known to have performance issues. I'm not saying your database is like that, but it's good for you to know that you should not leave it running for a long time.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kskinne]

Hugh, I checked the regional options and they are identical between the workstations. For the dependencies, I double-checked the file versions for all of the project references and components, and they match as well.

George, I ran profiler and didn't see anything abnormal there in the trace. As I said, the ADO data control has data in it. I know it is able to connect to the database and open the recordset and retrieve data from it, because I can make that data show in the userform in other objects, like a label or text field, etc., just not in the datagrid. I can scroll through the ADO data control records and see the data change in the other objects in my form that I have set up to show data from the data control. This seems to be limited to just the datagrid itself, and not the underlying data.

Thanks,
Kevin

 

[kskinne]

Hugh, I take that back - the MSBIND.DLL file was actually not even in this workstation's WINDOWS\SYSTEM32\ folder, and the mscomct2.ocx was a different version number.

I copied those files over to the affected workstation, re-registered the files, and that was all it took - it is working. If I experience any other unforeseen problems though, I will post back.

Thank you very much for the tip, and thanks to everyone else for your help with this.

Kevin

 

[HughLerwill]

Very pleased to hear it is working now. I should have mentioned previously that the MS/ System Internals Process Explorer can be very handy in such circumstances.