Debug SQL Easily by Charles Carroll

最新推荐文章于 2023-06-15 14:53:18 发布

原创最新推荐文章于 2023-06-15 14:53:18 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #components #debugging #library #insert #file

ASP/.Net专题专栏收录该内容

21 篇文章

订阅专栏

该博客展示了如何格式化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 " & howmany & " records "
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 " & howmany & " records "
Call SQLerrorreport(SQL,conn)

Conn.Close
set conn=nothing
%>
</body></html>

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="    "
 lb=" " & vbcrlf
 comma=""
 squote=""
 response.write "ADO Error(s) executing: "
 for counter= 0 to HowManyErrs-1
 errornum=parm_conn.errors(counter).number
 errordesc=parm_conn.errors(counter).description
 response.write pad & "Error#=" & errornum & " "
 response.write pad & "Error description="
 response.write errordesc & ""
 next
 SQLstmt=parmSQL
 SQLstmt=replace(SQLstmt,",", "" & comma & "," & lb)
 SQLstmt=replace(SQLstmt,"'","" & squote & "'")
 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: "
 response.write SQLstmt & " "

END SUB
%>