It's easier if you do this by using a library. This hides the gory details and allows use to trap errors to prevent revealing underlying database details.
<%
'****************************************************************
'* paramSQL.asp © 2005, 2006 Roderick Divilbiss *
'* http://www.rodsdot.com *
'****************************************************************
'* TERMS OF USE *
'*--------------------------------------------------------------*
'* Except where otherwise noted, this source code and markup is *
'* licensed under a Creative Commons License Creative Commons *
'* License. http://creativecommons.org/licenses/by-nc/2.0/ *
'* *
'* Complete Terms of Use my be found at: *
'* http://www.rodsdot.com/termsofuse.asp *
'* *
'* No part of this application code may be used for commercial *
'* purposes without prior written permission from the author, *
'* Roderick W. Divilbiss of Overland Park, Kansas, United States*
'* of America. *
'* *
'* Non-commercial use of this application code requires this *
'* notice be kept intact. *
'* *
'* © 2000-2005, Roderick W. Divilbiss, All Rights Reserved *
'* Used by permission. *
'* Original source code may be found at www.rodsdot.com. *
'* *
'* The following notice and hyperlink must be included on one *
'* page on the web site accessible to the public and linked *
'* directly to the website's home page. *
'* *
'* This site contains code used by permission of Rod Divilbiss, *
'* http://www.rodsdot.com. *
'* **************************************************************
'****************************************************************
'* GLOBAL VARIABLES *
'****************************************************************
Dim db_cmd, db_conn, db_rs, db_cmdText, numAffected
Sub openCommand(pConnStr,pSource)
if pConnStr="" then
Response.Write "There was an openCommand error: "& pSource &" 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
on error resume next
Set db_cmd = Server.CreateObject("ADODB.Command")
if err then
Response.Write "There was an openCommand error: "& pSource &" 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
Set db_conn = Server.CreateObject("ADODB.Connection")
if err then
Response.Write "There was an openCommand error: "& pSource &" 3.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
db_conn.open pConnStr
if err then
Response.Write "There was an openCommand error: "& pSource &" 4.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
Set db_cmd.ActiveConnection = db_conn
if err then
Response.Write "There was an openCommand error: "& pSource &" 5.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
End Sub
Sub closeCommand()
on error resume next
if IsObject(db_cmd) then
Set db_cmd = nothing
end if
if IsObject(db_conn) then
if db_conn.open then
db_conn.close
end if
Set db_conn = nothing
end if
End Sub
Sub addParam(pName,pType,pDir,pSize,pValue,pSource)
Dim param
on error resume next
Set param = db_cmd.CreateParameter(pname,ptype,pdir,psize,pvalue)
if err then
Response.Write "There was an error creating a database parameter: "& pSource &" 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
db_cmd.Parameters.Append param
if err then
Response.Write "There was an error appending the database parameter: "& pSource &" 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
Set param = nothing
End Sub
Sub getRS(db_rs,db_cmdText,pSource)
on error resume next
Set db_rs = Server.CreateObject("ADODB.Recordset")
if err then
Response.Write "There was an error: "&pSource&" 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
else
db_cmd.CommandText = db_cmdText
if err then
Response.Write "There was an error: "&pSource&" 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
else
' db_rs.Open db_cmd, adOpenStatic, adLockOptimistic
db_rs.Open db_cmd
if err then
Response.Write "There was an error "&pSource&" 3.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
end if
end if
End Sub
Sub execCmd(db_cmdText)
on error resume next
db_cmd.CommandText = db_cmdText
if err then
Response.Write "There was an error setting command text. execCmd 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
else
db_cmd.Execute numAffected,, adExecuteNoRecords
if err then
Response.Write "There was an error executing command. execCmd 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & " Command: " &db_cmdText& "<br>" & vbLF
Response.End
end if
end if
End Sub
Sub closeRS
on error resume next
if IsObject(db_rs) then
db_rs.close
end if
End Sub
%>
© Coyright 2000-2008, Roderick (Rod) W. Divilbiss. Some rights reserved.
Except where otherwise noted, this site, all content, and all source code and markup is licensed under a Creative Commons License
Creative Commons License.
No part of this web site including all application code and examples may be used for commercial purposes without prior written permission from the author,
Roderick W. Divilbiss of Overland Park, Kansas, United States of America.
