ASP SQL Error Handling

Question

Hello,

I am using Classic asp and SQL Server 2005.

This is code that works (Provided by another member of Stack Overflow):

 sqlStr = "USE "&databaseNameRecordSet.Fields.Item("name")&";SELECT permission_name FROM fn_my_permissions(null, 'database')"

This code checks what permissions I have on a given database - the problem being - if I dont have permission it throws an error and doesn't continue to draw the rest of my page.

Anyone got any ideas on remedying this?

Many Thanks, Joel

Answer 1

Since VBScript doesn't support the On Error GoTo <label> handling you'll have to use On Error Resume Next

hasPermission = CheckPermission()

Function CheckPermission()
    On Error Resume Next
    '--- here goes your database query

    If Err.Number = 0 Then
        CheckPermission = True
    Else
        CheckPermission = False
    End If
End Function

If you are using JScript you'd have try/catch available.

Answer 2

Its a long while since I wrote any ASP but one route would be to handle the error. You could treat any error as being the result of no permissons, or preferably handle just the error code returned for this circumstance.

On Error Resume Next

sqlStr = "USE "&databaseNameRecordSet.Fields.Item("name")&";SELECT permission_name FROM fn_my_permissions(null, 'database')"

rs.Open sqlStr, connection

If Err.Number <> 0 Then
    Response.Write "No Permissions"
Else
    ' Your code to display or process permissions
End If

On Error Goto 0