Debugging and Error Messages - Page 3

Unless you're a flawless coder, your hand-coded pages are likely to bring up a few error messages drawing attention to your syntax errors and occasional lapses of logic. Some error messages are very explicit and will lead you straight to the problem, but some aren't.

Two useful sources for error information are Macromedia's UltraDev support pages and Microsoft's ASP support pages.

Let's take the exaggerated example of an appalling, entirely hand-coded page created late on a Friday afternoon after a long lunch, and work through the many error messages.

Here's how the important central part of the page should look.

It contains many recordsets. One to populate each of the three selection menus, one to create the main data for the table and one for a total line that's way down at the bottom and not shown in the picture. And there are two more, associated with the inner workings of the file, making seven recordsets in total.

Here's the first error, as seen in Dreamweaver's Live Data window.

And here's the same error (as text) when viewing the file in a regular Internet Explorer window, using PWS as a server.

Microsoft VBScript compilation error '800a0409'
  Unterminated string constant
  /kingston/version11/referrals/special.asp, line 118
  Fperiod.Source = "SELECT FinPeriod,PNames FROM
  ----------------------------------------------
  epscons_EPS_OP_REF,epscons_EPS_PNames where FinPeriod =
    PeriodNo and
  ----------------------------------------------
  RPeriod = '" + REF_VarRP + "' group by FinPeriod,PNames
    order by
  ----------------------------------------------
  FinPeriod desc
  --------------^

[The colored lines above are one line. They have been split for formatting purposes.]

When the Live Data window is scrolled to the right and shows its full content, it turns out to be identical to the message created by PWS. You can use either for debugging. Ultimately, it's slightly better to use Internet Explorer than Live Data, because you get to see your HTML rendered by a real browser.

The error message is very helpful (in this case) and takes you straight to the problem. The SQL statement has not been terminated by a double quote. This is clearly shown by the arrow at the end of the string of hyphens.

It should end:

order by FinPeriod desc"

With this fault fixed, and the corrected page "put" to the server, we move on to the next one:

  Microsoft VBScript runtime error '800a000d'
  Type mismatch: 'DoCaseM'
  /kingston/version11/referrals/special.asp, line 64

This is less helpful. First thing we need to do is find line 64. A good tip here, if you're not familiar with debugging, is to open the ASP file in Wordpad or some other text editor, copy it, and paste the contents into the first cell of a new Excel spreadsheet. Excel automatically adds line numbers to the file so you don't have to count manually. This doesn't always work, especially if Excel gets hold of a block of HTML and starts rendering it, but most of the time it's very useful. Here's the relevant part of the page in Excel:

And here's the whole of line 64:

sqlstmnt = "SELECT epscons_EPS_OP_REF.DoHCode, " + DoCaseM("MCnt")
  + ", sum(MCnt) as sumYCnt, DoHCodeDesc FROM epscons_EPS_OP_REF,
    swanson_DoHDesc
  WHERE epscons_EPS_OP_REF.DoHCode = swanson_DoHDesc.DoHCode
    and RPeriod <=
  '" + REF_varRP + "' and FinPeriod between '" + FCEB__varP1 +
  "' and '" + FCEB__varFP + "'"

[The colored lines above are one line. They have been split for formatting purposes.]

It's a fairly complicated start to an SQL statement, but there are no obvious problems. As part of our detective work we look further down the page to check the function DoCaseM. We find it's entirely missing. What a Friday that was! That's why the system returned a type mismatch, because it couldn't find the function.

We add in the missing function and "put" the page to the server, then carry on. You'll notice that the first errors we've encountered are all to do with specifying recordsets. This is typical. Later we might hit HTML and VBScript errors in the body of the page, but recordset errors generally come first.

Here's the next error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
  [Microsoft][ODBC Microsoft Access Driver] Syntax error
    in GROUP BY clause.
  /kingston/version11/referrals/special.asp, line 78

[The colored lines above are one line. They have been split for formatting purposes.]

Slipping back to look at the file in Excel shows:

Line 78 is a rather standard and innocuous part of a recordset. Again the error message needs some interpretation here. It isn't really line 78 that's flawed, it's (yet again) part of the SQL statement a few lines above it - and this is a typical interpretation required with error messages. As the message suggests, it's actually our GROUP BY clause that's wrong.

Here it is:

sqlstmnt = sqlstmnt & " group by epscons_EPS_OP_REF.DoHCode, ORDER
  BY epscons_EPS_OP_REF.DoHCode"

When a GROUP BY clause is wrong, it often means that a field has been included in the recordset that isn't aggregated so must be part of the GROUP BY clause. Anything else will produce an error. Hunting through the split SQL statement we find:

sqlstmnt = "SELECT epscons_EPS_OP_REF.DoHCode, "
  + DoCaseM("MCnt")
  + ", sum(MCnt) as sumYCnt, DoHCodeDesc FROM…

[The colored lines above are one line. They have been split for formatting purposes.]

So there's our non-aggregated field that we've forgotten to deal with - DoHCodeDesc. Our GROUP BY must be changed to:

group by epscons_EPS_OP_REF.DoHCode,DoHCodeDesc ORDER BY
  epscons_EPS_OP_REF.DoHCode

[The colored lines above are one line. They have been split for formatting purposes.]

Ok, that's fixed. What's next?

Case and Iif - Page 2
Hand-Coded SQL for UltraDev ASP Pages
Beyond Recordset Errors - Page 4