Translating SQL Server error messages into plain english

  • CanuckBuck

    Hall of Fame

    Points: 3883

    I'm looking for a means to capture error messages coming from SQL server e.g.

    I'd like to replace this message with something that a business user would understand like; "You need to supply a value for each mandatory field before you can save this record."

    I'm using MS Access 2010 and SQL Server 2016

    which I couldn't figure out how to make work (maybe I'm just missing something).

    which, again, I couldn't make work - more so couldn't figure out how to make it work or what it is doing (e.g. me.show 1 and the cmdOK_Click subroutine) or where to put the code.  Additionally, I don't think it will do what I want.  The ability to translate the messages into something user-understandable doesn't seem to be there

    which looks like it should work.  I desperately want for this to work because it looks like it would enable me to customize my messages, but I can't make it go to the error handling part of the code.  This example is straight from Microsoft!

    This appears to be relying on using Windows OS calls, hijacking the error message shown above and overwriting the text with a new message.  Yikes!

    Is what I'm wanting to do really that hard?

    Thanks in advance for any assistance you can provide.

  • yelouati

    SSC Enthusiast

    Points: 108

    I would consider calling a stored procedure. In the stored procedure, have a BEGIN TRY logic. In the CATCH block, feel free to translate the message any which way you want

  • WendellB

    SSCrazy Eights

    Points: 8590

    Are your users getting those kind of ODBC errors from a form where they are adding/editing data on a form?  Or are they adding or editing data at the table or query level  If it's the latter, best practices suggest using a form in all cases where additions or edits are allowed.  On forms, you can set properties to prevent the kind of editing your error message describes.  And at the form level, you can customize the error messages so they make sense to the user.  Hope this helps - if it doesn't, post back with more info.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • WILLIAM MITCHELL

    SSChampion

    Points: 13535

    Instead of trapping the error when it reaches the database, you should consider adding form-level validation. For example if your field MyText is required, in your OK button click you might do this:

    with me
    if isnull(.MyText) then
    msgbox "MyText is a required field"
    else
    ' process the OK button click
    end if

  • WILLIAM MITCHELL

    SSChampion

    Points: 13535

    WILLIAM MITCHELL - Friday, March 22, 2019 10:41 AM

    Instead of trapping the error when it reaches the database, you should consider adding form-level validation. For example if your field MyText is required, in your OK button click you might do this:

    with me
    if isnull(.MyText) then
    msgbox "MyText is a required field"
    else
    ' process the OK button click
    end if
    END WITH

    oops forgot the last line

  • CanuckBuck

    Hall of Fame

    Points: 3883

    Wendell;  thanks for your response.  To answer your question; users are using a form.  The errors mostly occur when a user navigates (accidentally tabbing from the last field onto the next row or clicking back on the main from) off of a from (Subfrom actually) without filling in all of the mandatory fields.  Can you tell me what properties of a form I would set?  Is it the approach that William suggests.

    William;  thank you as well, for your response.  I have considered duplicating all of the database validation and if that's the way it has to be then that's what I'll do but to me that seems like a fail -effectively duplicating code.  I can't believe it's this difficult to trap and translate error messages coming from the server.

  • WendellB

    SSCrazy Eights

    Points: 8590

    Sorry for the delay in responding.  And yes, it is essentially the sort of code that William suggested.  It sounds as though you are dealing with a continuous form that is a subform on a main form.  There is a BeforeUpdate form event that is triggered each time a record is changed (there is also a BeforeInsert event), and you can put code on it to check each field that requires data being entered.  In addition, you can put a constraint on a text box that says an entry must be made, so if a user tabs through a text box leaving it null, the form will stop and require an entry before moving to the next text box.  That doesn't require any code, although you can also put code behind each text box to do checking.  Hope this helps.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

  • CanuckBuck

    Hall of Fame

    Points: 3883

    Wendell;
    Thanks again for your response.  You've helped me in the past and I appreciate it.

    I'm shocked/dismayed/disappointed that there isn't a better/simpler way to leverage the error reporting coming from SQL Server and that the answer seems to be to effectively preempt that validation by duplicating it in MS Access.

  • Chris Hurlbut

    SSCarpal Tunnel

    Points: 4022

    If you don't perform validations on data before you attempt to insert it into SQL you will be shocked/dismayed/disappointed your whole life as a developer.

  • WILLIAM MITCHELL

    SSChampion

    Points: 13535

    Chris Hurlbut - Thursday, March 28, 2019 9:11 AM

    If you don't perform validations on data before you attempt to insert it into SQL you will be shocked/dismayed/disappointed your whole life as a developer.

    +1

  • WendellB

    SSCrazy Eights

    Points: 8590

    CanuckBuck - Thursday, March 28, 2019 6:50 AM

    I'm shocked/dismayed/disappointed that there isn't a better/simpler way to leverage the error reporting coming from SQL Server and that the answer seems to be to effectively preempt that validation by duplicating it in MS Access.

    Actually, the issue comes in large part from the ODBC driver, which "tries" to convert the SQL Server message and doesn't do very well.  It is possible to write some error handling code to convert the message to something more useful, but it is quite a complex bit of coding to deal with all the potential error cases.  Doing it on the form is a relatively simple exercise, and would be desirable even if you were using local Access tables, as the raw error messages from Access aren't always helpful to the end user.  Access is a powerful front-end to SQL Server, where local or in the cloud (SQL Azure) and it allows the developer to quickly implement a verry sophisticated user interface.  Of course its requirement that you have Access licenses for each user is a downside, and there are others such as needing a PC to run the interface on.  

    I should note that there are several other forums where there is much greater activity on Access that you will likely get quicker responses.  Check out Utter Access, Eileen's Lounge, Windows Secrets Lounge, AccessForums.net and several others noted at MS Access Links.  You might also find the FMS Tutorials useful in understanding the issues and advantages of a SQL Server back-end.  Hope this is helpful.

    Wendell
    Colorful Colorado
    You can't see the view if you don't climb the mountain!

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply