×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Trapping error codes from SQL server??

Trapping error codes from SQL server??

Trapping error codes from SQL server??

(OP)
OK, here is the problem.

I have some VBA CODE that verifies internal tracking numbers, but for some unknown reason it is not catching ALL mis-input numbers.
Granted a 300% improvement from the original CODE (not mine) is good but my boss would like to still eliminate the remaining errors.

Here is a before insert handler for filenumbers that works but, (and not sure why) it still allow from 3 to 9 errors through each month.


CODE

Private Sub FileNumber_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_HandlerDim TrackingDate As Date
Dim dl As String
dl = vbNewLine & vbNewLineMe.TrackingDate = NowDim strFileNum As String
strFileNum = Me.FileNumberDim strFilePrefix As String
strFilePrefix = GetfileNumPrefix(strFileNum)Dim strFileSuffix As String
strFileSuffix = Mid(strFileNum, Len(GetfileNumPrefix(strFileNum)) + 1)If UCase(strFileNum) = UCase(".box.end.") Then
    Exit Sub
End IfIf IsNumeric(strFileNum) = False Or UCase(strFileNum) = UCase(".box.end.") ThenElse
    Cancel = True
    BeepWhirl
    strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
               "The File Number is all Numeric and does not have a valid file prefix." & dl & dl & _
               "Please Correct the File Number ...", _
                vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
               "ERROR!")
        Select Case strResult
            Case vbOK, vbRetry, vbYes, vbNo
                Me.Undo
                Exit Sub
            Case vbCancel, vbAbort, vbIgnore
                Exit Sub
            Case Else
                Exit Sub
        End Select
End IfSelect Case Len(strFilePrefix)
    Case 1
        If IsNull(DLookup("[FileNumPrefix]", "tblFileNumPrefix", "[FileNumPrefix]='" & _
         strFilePrefix & "'")) = False Then
            If IsNumeric(strFileSuffix) = True And _
            (Len(strFileSuffix) = 8 Or Len(strFileSuffix) = 9) Then
            Else
            Cancel = True
            BeepWhirl
            strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
                       "The File Number does not have a valid numeric file suffix." & dl & dl & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Me.Undo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
            End If
        Else
            Cancel = True
            BeepWhirl
            strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
                       UCase(strFilePrefix) & " is not a valid Alien File prefix." & dl & dl & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Me.Undo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
        End If
    Case 2
        Cancel = True
        BeepWhirl
        strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
                   "File Numbers do not have 2 character alpha prefixes." & dl & dl & _
                   "Please Correct the File Number ...", _
                    vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
                   "ERROR!")
            Select Case strResult
                Case vbOK, vbRetry, vbYes, vbNo
                    Me.Undo
                    Exit Sub
                Case vbCancel, vbAbort, vbIgnore
                    Exit Sub
                Case Else
                    Exit Sub
            End Select
    Case 3
        If IsNull(DLookup("[FileNumPrefix]", "tblFileNumPrefix", "[FileNumPrefix]='" & _
         strFilePrefix & "'")) = False Then
            If IsNumeric(strFileSuffix) = True Or _
            (Len(strFileSuffix) = 10 And Len(strFileSuffix) = 11) Then
            ' Continue
            Else
            Cancel = True
            BeepWhirl
            strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
                       "The File Number does not have a valid 10 or 11 digit file suffix." & dl & dl & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Me.Undo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
            End If
        Else
            Cancel = True
            BeepWhirl
            strResult = MsgBox(Me.FileNumber & " is not a Valid File Number" & dl & _
                       UCase(strFilePrefix) & " is not a valid Reciept File prefix." & dl & dl & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Me.Undo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
        End IfEnd Select
    
If DCount("*", "tblTrackingTable", "FileNumber='" & Me!FileNumber & "' AND BoxNumber='" & Me!BoxNumber & "'") >= 1 Then
    Cancel = True
    BeepWhirl
            strResult = MsgBox("You have attempted to enter a duplicate " & _
                        "File Number for " & Me.BoxNumber.Value & dl & dl & _
                       "Please Correct the File Number ...", _
                       vbExclamation + vbRetryCancel + vbDefaultButton1 + vbMsgBoxSetForeground + vbSystemModal, _
                       "ERROR!")
                Select Case strResult
                    Case vbOK, vbRetry, vbYes, vbNo
                        Me.Undo
                        Exit Sub
                    Case vbCancel, vbAbort, vbIgnore
                        Exit Sub
                    Case Else
                        Exit Sub
                End Select
End Ifendit:
Exit SubErr_Handler:
If StandardErrors(Err) = False Then
    BeepWhirl
    MsgBox Err & ": " & Err.Description
End If
Resume enditEnd Sub

What I want to do is change the error handler to catch the error coming from SQL server and display either a custom message from the client (MS Access 2003) or the message from the SQL Server itself.

I have added some custom messages at the SQL server.


CODE

/******************************************************
Custom File Number error messages. (505XX)
*******************************************************/
--sp_dropmessage @msgnum = 50500
--go
--sp_dropmessage @msgnum = 50501sp_addmessage @msgnum = 50500,
@severity = 16,
@msgtext = N'The File Number you have entered does not meet existing criteria and will not be Accepted.'Gosp_addmessage @msgnum = 50501,
@severity = 16,
@msgtext = N'The File Number you have entered does not begin with a correct PREFIX and will not be Accepted.'

So essentially I need to know what CODE I can use to catch the error messages from SQL.

I am planning to change the main imput form from a bound form to an unbound main/sub form tied to a uSP (user stored procedure) and have the stored procedure do the validation rather than the VBA CODE.

 

Thanks

John Fuhrman
 

RE: Trapping error codes from SQL server??

CODE --> vba

Option Compare Database
Option Explicit
Public Cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Function InitializeAdo()
If Cnn.State = adStateClosed Then
    Cnn.ConnectionTimeout = 0
    Cnn.Open CurrentProject.Connection
End If
End Function

Function ExecuteAdoOutput(AdoString As String, adoCommandType As Integer, ParamArray AdoPrams()) As ADODB.Command
'AdoPrams must have at least 1 value for the return value of a SP
Dim Prams As Integer
Dim A As Integer
InitializeAdo
cmd.CommandText = AdoString
Set cmd.ActiveConnection = Cnn
cmd.CommandType = adoCommandType
cmd.CommandTimeout = 0
For Prams = 0 To UBound(AdoPrams)
    cmd.Parameters(Prams) = AdoPrams(Prams)
Next Prams
cmd.Execute
Set ExecuteAdoOutput = cmd
End Function

Sub SQlOutput()
Dim Output As ADODB.Command
Set Output = ExecuteAdoOutput("z", 4, 0)
if Output("@output")>"" then

MsgBox Output("@output")
else
MsgBox "no errors"

end if
End Sub

CODE --> for sp

Alter proc z

@Output varchar(250) output

As

Select *
from Students


if @@rowcount <100
begin
Select @output ='Less then 100 Students'
end

Return

RE: Trapping error codes from SQL server??

(OP)
Thanks PWise,

I think I get the jist of this, but could you please explain its use?

Thanks

John Fuhrman
 

RE: Trapping error codes from SQL server??

I would use Try-Catch.
Set a varchar variable to the string you want, then just raiserror(@Variable, 16,1)

Then, in the Catch block:
select @Variable=Error_Message()
raiserror(@Variable, 16,1)

Something like:

CODE

Begin Try
--code to check certain conditions
select @Variable = 'Whatever text you like here'
raiserror(@Variable, 16,1)
End Try

Begin Catch
select @Variable=error_message()
raiserror(@Variable, 16,1)
End Catch

This way, the client (VBA) will get the error you want through ADO.

HTH
 

pipe
Daniel Vlas
Systems Consultant

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close