Type 13 mismatch with ADO, VB6, Win98

We have a program (http://www.Acrocat.com/pdafitness/desktop/pdafitness.htm).

It works on all version of windows with the exception of a select few Win98 SE (and possibly other 98 versions).  I have an on error goto statement in each function that logs the errors and am getting a lot of vb 13 type mismatch erros – but only on these certain machines. Everywhere else is works fine.  What ends up happening if I ake out the on error goto statements is that certain code is not being run:

We have combo boxes that are populated by data from an access db that is connected via DSN and ADO. It seems to get type mismatches on these certain machines on the connect statement.

I am assuming that these machines that it fails on don’t have either the right MDAC or VB runtime files installed or something along those lines.  Please let em know what I can post to make this more clearer.  Your time is much appreciated.

Who is Participating?
MCummings111400Connect With a Mentor Commented:
First, what version of MDAC is being referenced by your appliation, and are you requireing that version be installed at the clients? If you are using only access, then I would suggest setting your reference to MDAC 2.1 and everything should be much better. That way you don't require a new MDAC to be installed.
>> We have combo boxes that are populated by data from an access db that is connected via DSN and ADO. It seems to get type mismatches on these certain machines on the connect statement.<<

It's most likely got nothing to do with the OS.  Those machines probably have null values in some of the fields you are trying to access.  The expression in which you try to use those values is probably expecting a string or integer and null is always a variant, so you get the mismatch error msg.

To help with this, many folks use
  strValue = fieldvalue & "" when getting a string value because the concatenation of the empty string creates a string of zero length - a real string type

Of course you can always trap for this stuff in slightly more complex code:
  If IsNull(myFieldValue) Then
     strValue = ""
     strValue = myFieldValue
  End If

it just takes a little more typing than
   myFieldValue & ""

As you step through your code in he debugger, observe the critical field values and find out if Null values may really be the cause of your problem.

csullinsAuthor Commented:
Hello QJohnson,

I will check the code this evening, but I want to calrify something.

The same database on the Win98 computer does not work, but the 2000/XP comptuer does.  If what you are suggesting is indeed correct, wouldn't it fail on both computers?

What I was thinking was that the 98 computer was somehow being more stringent in the error checking, but that doesn't make sense.

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

My apologies.  I thought I understood that there were several locations, each with their own OS _AND THEIR OWN COPIES_ of the database... in which case the database contents were different for different users.

If you are all using the same database, clearly this isn't possible.

So why do they look different to the boxes that have the problem?

The DSN file may reference drivers that aren't available to those boxes (either physically not avaiable or due to limitations enforced by security).  You could test this on those boxes by trying to create the DSN manually and look in the driver list to see if the source you want is really available.

Other than that, I wouldn't have any recommendation except that you build some logging into the connection routine that shows you the status of your connection at each step and the results of attempts to retrieve data if you get it open.  

You may as well use MAPI and have the log emailed to you automatically so you don't have to keep asking the users to do that while you're debugging.  If the logging and sending logic is in an error handler, the workstations that are running fine will not be bothered by the new logging code.  You'll have to get them off your system to allow you to replace the exe with the version that enables the additional logging, but it should only take a few minutes to make the swap which shouldn't be too gross an incovenience (at least in the evening).

good luck
QJohnson for future reference

        Run-time error '94':
        Invalid use of Null

is the error that would occur

csullins, try setting you connection string into a variable if the error occurs when you try this I have fould that this is usually a result of a missing reference in the project not necessarily MDAC.
csullinsAuthor Commented:
Are you saying that you received an error 94 when running the application? Or was that from previous experience?

I will check the connection string and make sure it is a variable, has this solved a problem before?
I meant that I have got an Type mismatch error on

          for a = 0 to 9

with the 'a' highlited as a result of a missing reference.

   could you post you connection code for us, I think that would help out a bunch. The only Error this question is dealing with now is your 13:Type mismatch.

The comment from Hornet241 relating to a error 94 was refering to the situation described by QJohnson. His description of events would cause a error 94, not a error 13 as you had stated you are receiving. So in essence, we don't need to bother ourselves with error 94, yet ;)
csullinsAuthor Commented:
I can post the code tonight about 8PM Pacific time.  I won't be able to tget to the comtuoer until then.  Thanks for staying so patient guys.

csullinsAuthor Commented:
OK - here is the code.  basically it goes through a table and populates a combo box.  In these Win98 boxes, it doesn't populate the comboboxes at all.

Private Sub PopulateHSUID()
    On Error GoTo PDAFitness_Err
    Dim count As Long
    Dim Index As Integer
    Dim userID As Long
    Dim UserName As String
    Dim itemIndex As Integer
    Index = 0

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sHSUID As String
        ' Open a connection.
        Set conn = New ADODB.Connection
        conn.ConnectionString = "Data Source='PDAFit'"
        conn.ConnectionTimeout = 30
        ' Open the Recordset
        Set rs = conn.Execute("SELECT DISTINCT HotsyncUser from ClientProfileDB ORDER BY HotsyncUser ASC")

         ' see if recordset is empty
        If rs.BOF = True And rs.EOF = True Then
            cmbHSUSER.AddItem "Demo Client"
            GoTo rs_empty
        End If
        ' List the data
        Do While Not rs.EOF
            cmbHSUSER.AddItem rs!HotsyncUser

    Set rs = Nothing
    Set conn = Nothing
    Exit Sub
'print #Filenum, "populatehsuid"
        Call LogPDAFitness(Me.Name, Err.Number, Err.Description, Err.Source)

End Sub
csullinsAuthor Commented:
I should say that the database is Access 2000.  Would that be why Win98 is acting up?
csullinsAuthor Commented:
I have narrowed down the error.

This only occurs on Windows 98 machines.

The error is occuring (Type Mismatch, 13) on the line:

Set rs = conn.Execute("SELECT DISTINCT HotsyncUser from ClientProfileDB ORDER BY HotsyncUser ASC")

Any ideas?  85 points....

csullinsAuthor Commented:
I will also point out that I am not referencing or using DAO in my project - just ADODB.
Have you tried opening the recordset with its own Open method?  Shouldn't be any opportunity for the compiler to get confused about data types that way:

   Set rs = New ADODB.Recordset
   rs.CursorType...etc., etc.

   rs.Open "SELECT DISTINCT HotsyncUser from ClientProfileDB ORDER BY HotsyncUser ASC", conn, , , adCmdText

It sure looks like your code should work.  Your code doesn't specify much about the connection or the recordset and the Connection's Execute method only returns one type of recordset (forward only, read only) - perhaps that looks like the wrong type of recordset to yours?  Doesn't make much sense to me.  Clearly your method call and the variable to which its return value is being set much be the same data type.  So the MS geniuses must have blown it on the error message they generated.  Maybe they just can't force their run time error logic to see the difference between data type mismatch and some property mismatches between recordsets that make them incompatible for assignment purposes.

But this is probably not very useful speculation.  However, if rs.Open works... that's BETTER than speculation about the cause right?  <grin>

Hope it helps.

csullinsAuthor Commented:
I installed MDAC 2.5 and it worked - thanks guys for the help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.