Debug SQL Easily by Charles Carroll

原创 2005年05月20日 09:58:00

This page demonstrates how you can format a SQL statement to help pinpoint common errors. Common errors are discussed @

Common SQL Errors
http://www.learnasp.com/learn/dbtroubleshoot2.asp

Syntax Error in SQL Statement
http://www.learnasp.com/learn/FAQdbSQLSyntax.asp

by encapsulating ADO error trapping and display the SQL in an attractive form that separates components onto separate lines and makes debugging simpler (missing commas, single-quotes and like).

<Test Script Below>

<html><head>
<title>debug2.asp</title>
</head><body bgcolor="#FFFFFF">
<%
on   error resume next
myDSN = "DSN=Student;uid=student;pwd=magic"
Set Conn = Server.CreateObject("ADODB.Connection")
conn.open myDSN

SQL="update test set fname='Ted',lname='Wilson',city='Rockville',state='MD',zip='20849',rank=7,datehire='1/15/92', SSN='219-92-2677' WHERE personid=7"
Conn.Execute SQL,howmany
IF  howmany="" THEN
   howmany=0
END IF
response.write "Affected <b>" & howmany & "</b> records<br>"
Call SQLerrorreport(SQL,conn)

SQL="Insert Into junk (fname,lname,city,state,zip,rank,datehire,ssn) VALUES ('ted','wilson','rockville', 'md', '20849',7,'1/15/92','219-92-2677')"
Conn.Execute SQL,howmany
IF  howmany="" THEN
   howmany=0
END IF
response.write "Affected <b>" & howmany & "</b> records</b><br>"
Call SQLerrorreport(SQL,conn)

Conn.Close
set conn=nothing
%>

</body></html>
<!--#include file="lib_debug2.asp"-->

The library that does the work:

<%
SUB SQLErrorReport(parmSQL,parm_conn)
   HowManyErrs=parm_conn.errors.count
   IF HowManyErrs=0 then
      exit sub
   END IF
   pad="&nbsp;&nbsp;&nbsp;&nbsp;"
   lb="<br>" & vbcrlf
   comma="<font color=red>"
   squote="<font color=blue>"
   response.write "ADO Error(s) executing:<br>"
   for counter= 0 to HowManyErrs-1
      errornum=parm_conn.errors(counter).number
      errordesc=parm_conn.errors(counter).description
      response.write pad & "Error#=<b>" & errornum & "</b><br>"
      response.write pad & "Error description=<b>"
      response.write errordesc & "</b><p>"
   next
   SQLstmt=parmSQL
   SQLstmt=replace(SQLstmt,",", "<b>" & comma & ",</b></font>" & lb)
   SQLstmt=replace(SQLstmt,"'","<b>" & squote & "'</b></font>")
   SQLstmt=replace(SQLstmt,"(",lb & "(")
   SQLstmt=replace(SQLstmt,"set",lb & "set" & lb)
   SQLstmt=replace(SQLstmt,"SET",lb & "SET" & lb )
   SQLstmt=replace(SQLstmt," where ",lb & " where " & lb)
   SQLstmt=replace(SQLstmt," WHERE ",lb & " WHERE " & lb )
   
   SQLstmt=replace(SQLstmt,")",")" & lb)
         response.write "SQL statement attempted:<br>"
         response.write SQLstmt & "<br>"

END SUB
%>

How to create DB2 user function easily by DB Query Analyzer 6.03

This essay gives a demonstration of creating DB2 user function easily by DB Query Analyzer. And also...

操作系统实验-by Charles Wenchy

  • 2013年04月06日 17:57
  • 583KB
  • 下载

SharePoint debug - Unhandled exception was thrown by the sandboxed code ...

今天打算使用Microsoft Dynamics CRM list component for Microsoft SharePoint Server 2013
  • shrenk
  • shrenk
  • 2014年11月12日 01:39
  • 1094

adb(android debug bridge)工具的使用方法&#160;&#160;-&#160;&#160;棒槌网@Android开发论坛 - Powered by phpwind

adb(android debug bridge)工具的使用方法: cr&sI=i  常用命令: i"#36CVT~  1.列出查找到的android phone CPw=? adb devi...

DEBUG -- CLOSE BY CLIENT STACK TRACE

第一次写博文,完全是属于菜鸟级别,写的不好之处还望各路大神多多指教,小子感激不尽。 最经开发一个项目使用了C3P0数据库连接池,发现了有这样一个Exception: java.lang.Excep...

Debug Native c/c++ Application for Android(Step by Step)

原文地址::http://www.cppblog.com/luonjtu/archive/2009/02/19/74310.html      调试Android上的c/c++程序一直是个难题,...

How to debug QEMU (by quqi99)

**作者:张华 发表于:2016-08-11 版权声明:可以任意转载,转载时请务必以超链接形式标明文章原始出处和作者信息及本版权声明 ( http://blog.csdn.net/quqi99 ...
  • quqi99
  • quqi99
  • 2016年08月11日 15:14
  • 1318
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Debug SQL Easily by Charles Carroll
举报原因:
原因补充:

(最多只允许输入30个字)