Debug SQL Easily by Charles Carroll

该博客展示了如何格式化SQL语句以定位常见错误。通过封装ADO错误捕获,将SQL语句各组件分行显示,使调试更简单。文中给出了更新和插入数据的SQL示例,还提供了处理错误报告的库代码,帮助开发者排查问题。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

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
%>

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值