Creating a Server Component with VB - Redesigned - Part 2[10]

we're ready to combine the error code we explored previously with the database code we just developed. again, this code example will demonstrate how to use transactional processes by "rolling back" all sql statements when an error occurs in any one sql statement. of course, we're only using a single select sql statement, but the process is the same for including multiple sql statements that necessitate an "all or nothing" database commitment.

we'll also use the error processing to send a customized error message back to the calling code if our sql statement happens to be invalid because an incorrect noteid was sent as a method parameter. this is important because we really don't have any control over what noteid value might be sent to our method code and we want to let the calling code deal with an erroneous noteid in its own way - since it's the code that sent it.

adding error handling to database code   
public function shownotes(byval lngnoteid as long, _
	optional byval intstyleid as integer = 0, _
	optional byval strurl as string = "noteexample.asp", _
	optional byval strdbconnectionstring as string = "notesdsn") as string

    '///// error code //////////////////////////////////////////////
    on error goto errorcode

    '///// db connectivity variables
    dim sql as string
    dim rs as new adodb.recordset
    dim objcmd as new adodb.command
    dim objconn as new adodb.connection

    '///// db value storage variables
    dim strnotetext as string

    '///// open database ///////////////////////////////////////////
    objconn.open strdbconnectionstring
    objconn.begintrans

    '///// get selected note text from database ////////////////////
    sql = "select notetext from notetable where noteid = " & lngnoteid
    objcmd.commandtext = sql
    objcmd.commandtype = adcmdtext
    set objcmd.activeconnection = objconn
    rs.open objcmd
  

    '///// check if any data was returned
    if rs.eof = true and rs.bof = true then
        '///// no data returned, set error information, raise error
        err.number = 40001
        err.source = "noteproject::noteclass::shownotes"
        err.description = "00000002<br>possible invalid lngnoteid entered as method value"
        err.raise err.number, err.source, err.description
    else
        strnotetext = rs.fields("notetext")
    end if
    

    '--> any other database code goes here


    '///// finish and close up database processes //////////////////
    objconn.committrans
    objconn.close
    set rs = nothing
    set objcmd = nothing
    set objconn = nothing


    '--> other method code goes here


    '///// send back note text from database
    shownotes = strnotetext

    '///// exit function if no error raised ////////////////////////
    exit function
    
    '///// code if error occurs ////////////////////////////////////
errorcode:

    '///// rollback, close db references, and send back raised error
    objconn.rollbacktrans
    if isobject(rs) then set rs = nothing
    if isobject(objcmd) then set objcmd = nothing
    if isobject(objconn) then set objconn = nothing
    err.raise err.number, err.source, err.description

end function

    '///// check if any data was returned
    if rs.eof = true and rs.bof = true then