Microsoft JET Database Engine error '80040e07'

[DoomedVash]

Hello all!

I hope you guys can help me. I have an old ASP-based gaming event system running on my Windows Server 2008 R2 with ASPClassic enabled.

Everything works fine so. Until I try to calculate the statistics for an event.

Then I get this error:

Microsoft JET Database Engine error '80040e07'

Data type mismatch in criteria expression.
/wargames/includes/include.asp, line 3815

This is my code:

function RecalcGameRC(iGameID)
  ' Set up variables and structures
  dim sDebugResults
  dim SQL, rsMatches, rsPlayer, rsPoints, iMaxClubPlayers, iMaxGamesPlayed, iMinGamesHosted, iMaxAttritionRt
  dim iGames, vWarsList, index, iWarID, sMatchList, sPlayerName, iUniquePlayersIndex, vPlayerMatches, iMaxUPP, iMaxhrp, iMaxVictoryRt
  dim iRanking, iMaxPP, iAveragePlayerRanking, iPlayerID, iPoints, iKills, iDeaths, iMaxTotBases, iMaxTeamBases, iMaxAIBonus, iMaxAITeamBonus
  dim iVictoryRT, iAttritionRT, iMaxTeamPlayers, hostedrankingtemp, hostedrankingtemp1, iMatchBonus, iLMatchBonus, iPlayerMatchBonus, iTotAIRT, iTotBaseRT
  dim hrp
  set rsMatches = server.createobject("ADODB.Recordset")
  set rsPlayer  = server.createobject("ADODB.Recordset")
  set rsPoints  = server.createobject("ADODB.Recordset")
  application.lock
    dbConnection.execute "delete from war_playergamerankings where gameid=" & iGameID
    dbConnection.execute "delete from war_teamgamerankings where gameid=" & iGameID
  ' Now calculate the player and club rankings for the overall game
  SQL = "select sum(ranking) as ranking , username, memberid from war_playerwarrankings where warid in (select warid from war_wars where gameid=" & igameid & ") group by username, memberid"
  rsPlayer.open SQL, dbconnection, adopenforwardonly, adlockreadonly
  rsPoints.open "war_playergamerankings", dbconnection, adopenstatic, adlockoptimistic
  do until rsplayer.eof
    rspoints.addnew
    rspoints("gameid") = iGameID
    rspoints("username") = rsPlayer("username")
    rsPoints("memberid") = rsPlayer("memberid")
    rsPoints("ranking")  = rsplayer("ranking")
    rspoints.update
    rsplayer.movenext
  loop
  rsplayer.close
  rspoints.close
  SQL = "select sum(ranking) as ranking , teamname, teamtag, teamid from war_teamwarrankings where warid in (select warid from war_wars where gameid=" & igameid & ") group by teamname, teamid, teamtag"
  rsPlayer.open SQL, dbconnection, adopenforwardonly, adlockreadonly
  rsPoints.open "war_teamgamerankings", dbconnection, adopenstatic, adlockoptimistic
  do until rsplayer.eof
    rspoints.addnew
    rspoints("gameid") = iGameID
    rspoints("teamname") = rsPlayer("teamname")
    rsPoints("teamid")   = rsPlayer("teamid")
    rsPoints("teamtag")  = rsPlayer("teamtag")
    rsPoints("ranking")  = rsplayer("ranking")
    rspoints.update
    rsplayer.movenext
  loop
  rsplayer.close
  rspoints.close
  RecalcGameRC = sDebugResults
  application.unlock
end Function

Please help.

[awasson]

What's on line 3815 of your script?

MS ASP is pretty ancient but it's good at telling you where the trouble is.

My guess is that line 3815 is where a database query is executed. From the looks of that error, there is a problem with the query, ie: a number is treated like a string or vice-versa. The Jet Database engine is particularly fussy about making sure that string items are surrounded in single-quotes ' ' and that numbers are not.

[DoomedVash]

Line 3815 is the second "rspoints.update".

[awasson]

Line 3815 is the second "rspoints.update".

What is the SQL Query?

[DaveMaxwell]

Response.Write out the following fields. One of them has character values or is null, but the field is supposed to be numeric.

iGameID
rsPlayer("teamname")
rsPlayer("teamid")
rsPlayer("teamtag")
rsplayer("ranking")

[awasson]

Response.Write out the following fields. One of them has character values or is null, but the field is supposed to be numeric.

That's what I was thinking.

[DoomedVash]

Thanks for your replies, guys.

I tried it with following code:

Response.Write "iGameID: " & iGameID & "<br />"
Response.Write "teamname: " & rsPlayer("teamname") & "<br />"
Response.Write "teamid: " & rsPlayer("teamid") & "<br />"
Response.Write "teamtag: " & rsPlayer("teamtag") & "<br />"
Response.Write "ranking: " & rsplayer("ranking") & "<br />"
Response.End

This is what I get:

iGameID: 13
teamname: -]Rbln[-
teamid: 573
teamtag: -]Rbln[-
ranking: 94.06

What now?

[awasson]

I wonder if the code expects ranking (94.06) to be an integer but instead it gets a float?

[DoomedVash]

How can I check that?

[awasson]

I'm assuming with a Jet Error you've got an MS Access Database. Open the Database and in the 'war_teamwarrankings' table (I think) look at the 'ranking' field in Design View. The Data Type should be a number but not the default which is long integer.... You want Decimal.

[DoomedVash]

I checked that: "Data Type" is set on "Number" and the "Field Size" is "Decimal".

[awasson]

Hmmm, Well it's got to be a data type issue somewhere.

If the error is being reported at the second rspoints.update which is in a loop, I suspect at some point in the loop it's running into a null or wrongly formatted piece of data. I'd test by temporarily preventing the update from running and instead get it to print out the fields so I can look through the loop of results for the problem. Something like this:

do until rsplayer.eof
    'rspoints.addnew
    'rspoints("gameid") = iGameID
    'rspoints("teamname") = rsPlayer("teamname")
    'rsPoints("teamid")   = rsPlayer("teamid")
    'rsPoints("teamtag")  = rsPlayer("teamtag")
    'rsPoints("ranking")  = rsplayer("ranking")
    'rspoints.update
    'rsplayer.movenext

   ' Print each of the updates in the loop into unordered lists to inspect
   Response.Write "<ul>"
   Response.Write "<li>gameid = " & iGameID
   Response.Write "<li>teamname = " & rsPlayer("teamname")
   Response.Write "<li>teamid = " & rsPlayer("teamid")
   Response.Write "<li>teamtag = " & rsPlayer("teamtag")
   Response.Write "<li> ranking = " & rsplayer("ranking")
   Response.Write "</ul>" & VBCRLF

  loop

[DoomedVash]

I'm really bad at ASP.... can you please tell me what to do?

Shall I just replace this:

do until rsplayer.eof
    rspoints.addnew
    rspoints("gameid") = iGameID
    rspoints("teamname") = rsPlayer("teamname")
    rsPoints("teamid")   = rsPlayer("teamid")
    rsPoints("teamtag")  = rsPlayer("teamtag")
    rsPoints("ranking")  = rsplayer("ranking")
    rspoints.update
    rsplayer.movenext
  loop

By this:

do until rsplayer.eof
    'rspoints.addnew
    'rspoints("gameid") = iGameID
    'rspoints("teamname") = rsPlayer("teamname")
    'rsPoints("teamid")   = rsPlayer("teamid")
    'rsPoints("teamtag")  = rsPlayer("teamtag")
    'rsPoints("ranking")  = rsplayer("ranking")
    'rspoints.update
    'rsplayer.movenext

   ' Print each of the updates in the loop into unordered lists to inspect
   Response.Write "<ul>"
   Response.Write "<li>gameid = " & iGameID
   Response.Write "<li>teamname = " & rsPlayer("teamname")
   Response.Write "<li>teamid = " & rsPlayer("teamid")
   Response.Write "<li>teamtag = " & rsPlayer("teamtag")
   Response.Write "<li> ranking = " & rsplayer("ranking")
   Response.Write "</ul>" & VBCRLF

  loop

Or what exactly do you mean?

[awasson]

Yes, just until you track down the bug. Then you can put it back to where it was. All that block of code does is print out bullet-ed lists of every update that the system is supposed to run in the loop and since the update has been commented out, it won't hit that error.

[awasson]

Oops... there's an error in my code. I didn't move the recordset forward each time.

It should be:

  do until rsplayer.eof
    'rspoints.addnew
    'rspoints("gameid") = iGameID
    'rspoints("teamname") = rsPlayer("teamname")
    'rsPoints("teamid")   = rsPlayer("teamid")
    'rsPoints("teamtag")  = rsPlayer("teamtag")
    'rsPoints("ranking")  = rsplayer("ranking")
    'rspoints.update
    'rsplayer.movenext

   ' Print each of the updates in the loop into unordered lists to inspect
   Response.Write "<ul>"
   Response.Write "<li>gameid = " & iGameID
   Response.Write "<li>teamname = " & rsPlayer("teamname")
   Response.Write "<li>teamid = " & rsPlayer("teamid")
   Response.Write "<li>teamtag = " & rsPlayer("teamtag")
   Response.Write "<li> ranking = " & rsplayer("ranking")
   Response.Write "</ul>" & VBCRLF
   rsplayer.movenext

  loop

[DoomedVash]

Ah okay I see.

This is what I get:

    gameid = 13
    teamname = -]Rbln[-
    teamid = 573
    teamtag = -]Rbln[-
    ranking = 94.06

    gameid = 13
    teamname = ~Tactical Battle Squad~
    teamid = 498
    teamtag = ~TbS~
    ranking = 111.43

    gameid = 13
    teamname = =SS=Star Soldiers
    teamid = 154
    teamtag = =SS=
    ranking = 69.83

    gameid = 13
    teamname = >]VF[< clan
    teamid = 471
    teamtag = >]VF[<
    ranking = 0

    gameid = 13
    teamname = 124th Regiment
    teamid = 201
    teamtag = *RASR*
    ranking = 38.48

    gameid = 13
    teamname = 7 Deadly Sins
    teamid = 359
    teamtag = («7DS»)
    ranking = 80.28

    gameid = 13
    teamname = Alpha Strike Force
    teamid = 29
    teamtag = ASF
    ranking = 16.14

    gameid = 13
    teamname = Assault Force of the Rebellion
    teamid = 345
    teamtag = {AfoR}
    ranking = 0

    gameid = 13
    teamname = Berserker Kindred
    teamid = 22
    teamtag = BK
    ranking = 53.17

    gameid = 13
    teamname = Burn clan
    teamid = 66
    teamtag = B.U.R.N_
    ranking = 97.96

    gameid = 13
    teamname = Clankillerz
    teamid = 200
    teamtag = [Ç.K.Ž]
    ranking = 103.33

    gameid = 13
    teamname = Confederacy of Independant Gamers
    teamid = 218
    teamtag = -[CIG]-
    ranking = 23.5

    gameid = 13
    teamname = Conquering Mercenaries
    teamid = 160
    teamtag = {CM}
    ranking = 41.95

    gameid = 13
    teamname = Dark avengers
    teamid = 620
    teamtag = <{Da}
    ranking = 0

    gameid = 13
    teamname = Dark Entity
    teamid = 257
    teamtag = -=DE=-
    ranking = 65.83

    gameid = 13
    teamname = Defenders of Sovereignty
    teamid = 46
    teamtag = DS
    ranking = 770.41

    gameid = 13
    teamname = Deutsche Baller Armada.
    teamid = 705
    teamtag = -=DBA=-
    ranking = 8.6

    gameid = 13
    teamname = Disciples of the Sith
    teamid = 127
    teamtag = DOTS
    ranking = 0

    gameid = 13
    teamname = Dragon Warriors
    teamid = 130
    teamtag = -(DW)-
    ranking = 78.46

    gameid = 13
    teamname = DuelPunks
    teamid = 383
    teamtag = ->DP<-
    ranking = 0

    gameid = 13
    teamname = Elite Marine Corps.
    teamid = 632
    teamtag = [[EMC]]
    ranking = 0

    gameid = 13
    teamname = Elites
    teamid = 137
    teamtag = -^$RESF$^-
    ranking = 82.35

    gameid = 13
    teamname = European Gamers
    teamid = 23
    teamtag = EuG
    ranking = 23.2

    gameid = 13
    teamname = Forces of Solidarity
    teamid = 563
    teamtag = =FS=
    ranking = 295.66

    gameid = 13
    teamname = Galactic Guardians
    teamid = 491
    teamtag = -}GG{-
    ranking = 58.07

    gameid = 13
    teamname = Gametoast Modders
    teamid = 270
    teamtag = [GT]
    ranking = 99.06

    gameid = 13
    teamname = International Space Defense Force
    teamid = 465
    teamtag = [ISDF]
    ranking = 0

    gameid = 13
    teamname = Kamikazigaming
    teamid = 747
    teamtag = =)KG(=
    ranking = 0

    gameid = 13
    teamname = Kamino Elite
    teamid = 152
    teamtag = -=[/<£]=-
    ranking = 45.65

    gameid = 13
    teamname = Last Clan Republic Commando
    teamid = 219
    teamtag = -{{Lcrc}}-
    ranking = 37.15

    gameid = 13
    teamname = Legendary Black Forces
    teamid = 789
    teamtag = ·×lBƒ×·
    ranking = 0

    gameid = 13
    teamname = Loner - The Only Survivor
    teamid = 348
    teamtag = :]LoneR[:
    ranking = 0

    gameid = 13
    teamname = Loner - The Only Survivor
    teamid = 683
    teamtag = :]LoneR[:
    ranking = 0

    gameid = 13
    teamname = Long Range Recon Patrol
    teamid = 117
    teamtag = LRRP
    ranking = 219.01

    gameid = 13
    teamname = Lost Descendants
    teamid = 621
    teamtag = (·LD·)
    ranking = 83.17

    gameid = 13
    teamname = Mechanically Engineered Heroes
    teamid = 159
    teamtag = [MEH]
    ranking = 85

    gameid = 13
    teamname = Mercenaries of the Republic
    teamid = 126
    teamtag = {MotR}
    ranking = 776.93

    gameid = 13
    teamname = Mercinary band
    teamid = 217
    teamtag = [%merc]
    ranking = 12.68

    gameid = 13
    teamname = Ministry of Chaos
    teamid = 75
    teamtag = -=MoC=-
    ranking = 73.7

    gameid = 13
    teamname = Most Wanted Killas
    teamid = 341
    teamtag = -]MwK[-
    ranking = 0

    gameid = 13
    teamname = Night Troopers
    teamid = 149
    teamtag = =[NT]=
    ranking = 36.16

    gameid = 13
    teamname = RagE MultiGaming Clan
    teamid = 115
    teamtag = RagE
    ranking = 27.75

    gameid = 13
    teamname = rage of the ewoks
    teamid = 120
    teamtag = roe
    ranking = 60.33

    gameid = 13
    teamname = Rebel Squadrons
    teamid = 65
    teamtag = RS
    ranking = 0

    gameid = 13
    teamname = RedCell
    teamid = 134
    teamtag = -]RC[-
    ranking = 582.34

    gameid = 13
    teamname = revange of republic
    teamid = 262
    teamtag = -=)RR(=-
    ranking = 110.73

    gameid = 13
    teamname = Revenge of the Republic
    teamid = 202
    teamtag = -=}RR{=-
    ranking = 18.21

    gameid = 13
    teamname = Revived Clan
    teamid = 530
    teamtag = {Revived}
    ranking = 34.11

    gameid = 13
    teamname = Shoot to Kill
    teamid = 810
    teamtag = [StK]
    ranking = 0

    gameid = 13
    teamname = Soul Reapers
    teamid = 247
    teamtag = <}sr{>
    ranking = 50.24

    gameid = 13
    teamname = Soul Reapers
    teamid = 478
    teamtag = <}sr{>
    ranking = 166.81

    gameid = 13
    teamname = Star Wars Republic Coammando
    teamid = 199
    teamtag = [SWRC]
    ranking = 26.27

    gameid = 13
    teamname = Tactical Assassin Squad
    teamid = 462
    teamtag = {TAS}
    ranking = 0

    gameid = 13
    teamname = TBS
    teamid = 443
    teamtag = =TBS=
    ranking = 0

    gameid = 13
    teamname = Team3P
    teamid = 805
    teamtag = T.3P
    ranking = 0

    gameid = 13
    teamname = TeamXtreme
    teamid = 493
    teamtag = ]-TX-[
    ranking = 199.68

    gameid = 13
    teamname = The Elites
    teamid = 321
    teamtag = {ELITE}
    ranking = 0

    gameid = 13
    teamname = The Ghost Force
    teamid = 216
    teamtag = |GHOST|
    ranking = 36.33

    gameid = 13
    teamname = The Resistance
    teamid = 580
    teamtag = TR
    ranking = 0

    gameid = 13
    teamname = Time Splitters
    teamid = 467
    teamtag = -]TS[-
    ranking = 8.39

    gameid = 13
    teamname = Troops of the Absolutistic Guild
    teamid = 357
    teamtag = TAG
    ranking = 71.35

    gameid = 13
    teamname = Unreal Wookies
    teamid = 207
    teamtag = WKU
    ranking = 87.66

    gameid = 13
    teamname = Wrath of the Republic
    teamid = 139
    teamtag = ~{WR}~
    ranking = 26.75

But where is the error?

[awasson]

Well it could be that Access isn't happy with values that begin with mathematical operators but I don't know for sure.

I've got an access DB that accepts decimals. It's a number field, Field Size is Double and Decimal Places is Auto. Default Value is 0, Required is No and Indexed is No. Everything else is blank.

To get more info out of the Error you can try some Error Trapping. Something like this:

  do until rsplayer.eof
    
    rspoints.addnew
    rspoints("gameid") = iGameID
    rspoints("teamname") = rsPlayer("teamname")
    rsPoints("teamid")   = rsPlayer("teamid")
    rsPoints("teamtag")  = rsPlayer("teamtag")
    rsPoints("ranking")  = rsplayer("ranking")
    
    on error resume next
    rspoints.update
    if err.number<>0 then
        Response.Write "<p><strong>Warning:</strong> An error occured during this database transaction."
        Response.Write "<p>" & err.number & "<br />" & err.description & "</p>"
        err.Clear
    end if
    
    rsplayer.movenext
    
  loop

[DoomedVash]

I really appreciate your help btw!

That's what your new code gives me:

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

Warning: An error occured during this database transaction.

-2147217913
Data type mismatch in criteria expression.

[awasson]

Wow... So it looks like every transaction is breaking.

Did you try changing the ranking field as follows:
Number field,
Field Size is Double
Decimal Places is Auto.
Default Value is 0
Required is No
Indexed is No
Everything else is blank.

[DoomedVash]

Yea I tried that. I changed the 'ranking' field in the table 'war_teamwarrankings' to everything you wrote. I still get the same error. :-(

[DaveMaxwell]

I'm wondering if that field really is the one causing you the issue. Try this:

1. Comment out all the rspoints(" lines (put a single quote (') in front of the line).
2. Now uncomment the first one and the rspoints.update statement. Execute it.
3. If step two works, uncomment the next statement and repeat until you hit the one causing the error.

[DoomedVash]

I tried that and found that this line causes the error:

rsPoints("ranking")  = rsplayer("ranking")

The error is gone when this line is commented out.

[DaveMaxwell]

You could try rsPoints("ranking") = cDbl(rsplayer("ranking"))

Either that or change the AS ranking in the SQL statement to AS rankingSum then rsPoints("ranking") = rsplayer("rankingSum")

If the first doesn't work, the 2nd probably won't either....

[DoomedVash]

No, that doesn't work either.

[DaveMaxwell]

OK, add this line instead of the update and show us the result.

Response.Write "RSPoints = " & VarType(rspoint("ranking")) & ", RSPlayer = " & varType(rsplayer("ranking")) & vbCrLf