473,834 Members | 1,399 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

handling errors from stored procs

Questions toward the bottom of the post.

Situation is this:

1) Access 97
2) SQL Server 2000
3) The Access app:
a) sets up pass-thru query
b) .SQL property of querydef is a string, the contents of which
comprise the call to a stored proc
c) in the Open event of the Access report object, sets
the .RecordSource property of the report object to the name of the
querydef
4) the stored proc:
a) accepts the parameters passed to it:
b) creates a string, the contents of which is a SQL SELECT
statement...the parameters passed to the stored proc comprise the the
WHERE clause
c) after creating the string (4b) do an exec() on the string; this
produces a result set that gets returned back to calling app
5) the result set produced by the stored proc populates the controls
in the Access report object

The situation described above is working

I would like to make the overall process more robust; to handle the
possibility that an error could occur when
the stored proc gets run...if so, it would be nice if the Access app
could display the message that describes
the error that happened when the stored proc was run.

For example, I deliberately set up the stored proc in such a way that
it would be syntactically correct, but when
it ran, it would produce an error (reminder: the sproc creates dynamic
SQL, then runs that SQL...so, in the
dynamically-created SQL, I deliberately misspelled a reference to a
column in a table. When I use the Query
Analyzer tool in SQL Server to run this stored proc, I see a
meaningful error message...a message along the
lines of "this column does not exist". When I run the exact same
stored proc through the Access app, the
result I see in Access is:

Reserved Error (-7711); there is no message for this error

I'm guessing that what's going on is:
a) the Access app was expecting a result set to get returned
b) the error in the stored proc means that the stored proc did not
return a result set
c) in the absence of a result set getting returned, Access issued the
-7711 error.

It would be great if I could set things up such that:
a) if there were no errors in stored proc, it would return the typical
result set
b) if there were errors in the stored proc, it would return the text
of the error message (e.g. the message "this
column does not exist")
c) the Access app would be smart enough to know when a result set got
returned, and it would then populate the
controls in the report
d) the Access app would be smart enough to recognize that what got
returned from stored proc was an error, and it would then report that
error

I realize that this posting contains some SQL Server elements and some
MS Access elements, and I realize that this is a group that deals with
MS Access-related topics. The questions below attempt to focus on
the MS Access stuff. As you can tell, the issue described above spans
the MS Access and SQL Server environments, so I'm hoping I'm not
violating posting protocol by referring to SQL Server in this post.

Questions:
1) Is it possible to set up set up an Access 97 app combined with a
SQL Server 2000 stored proc in such a way that:
- when stored proc runs without error, the result set that it
returns feeds RecordSource of Access report object
- when stored proc encounters error, it returns that error message
to Access app, and Access app displays that error message
?
2) If the answer to #1 is yes, how does one make the Access app smart
enough to distinguish between when a legitimate result set is getting
returned from stored proc vs when the stored proc is reporting an
error message?
3) If the answer to #1 is yes, is it likely that the stored proc will
have to employ an output parameter and make use of that parameter in
the instances when an error occurs while running the stored proc?

Thank you.
Jul 19 '08 #1
0 1991

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
2960
by: Jon LaRosa | last post by:
Hi all - I have a web application and I want to be able to do some basic error handling. For example, here is one error I would like to catch and display in a useful way for the user: ----------------- Microsoft OLE DB Provider for ODBC Drivers error '80040e14' UPDATE statement
1
460
by: LineVoltageHalogen | last post by:
Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects. Background: 1.) Simple Application that uses sql server for backened db. 2.) The application is only inserting and selecting data from the db. 3.) The developers want to use sp_executesql for simple selects and the dba's want to use a stored proc. >From my reading it seems that sp_executesql has a...
2
5130
by: xAvailx | last post by:
I have a requirement that requires detection of rows deleted/updated by other processes. My business objects call stored procedures to create, read, update, delete data in a SQL Server 2000 data store. I've done a fair amount of research on concurrency handling in newsgroups and other resources. Below is what I've come up as a standard for handling concurrency thru stored procedures. I am sharing with everyone so I can get some comments...
7
1727
by: trint | last post by:
My boss wants me to use stored procedures, but I thought somehow that using c# that ADO.Net was better. Any help is appreciated. Thanks, Trint
45
3426
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
1
1719
by: kentk | last post by:
Is there a difference in how SQL Server 7 and SQL 2000 processes SQL passed from a program by an ADO command object. Reason I ask is I rewrote a couple applications a couple years ago were the SQL statements were inline. I basically took the SQL statements and put them into stored procs, were there were variables in the code I used SQL parameters in the stored procs. I got some amazing performance results by switching to stored procs. ...
15
2290
by: Burt | last post by:
I'm a stored proc guy, but a lot of people at my company use inline sql in their apps, sometimes putting the sql in a text file, sometimes hardcoding it. They don't see much benefit from procs, and say anyway they're are db specific- what if we change from SQL Server to Oracle one day? What say you experts? Is this one of those "no right answer" questions? Thanks,
8
2636
by: Frank Calahan | last post by:
I've been looking at LINQ and it seems very nice to be able to make queries in code, but I use stored procs for efficiency. If LINQ to SQL only works with SQL Server and stored procs are more efficient, what use is LINQ to SQL, other than to have a simpler way to call my stored proc?
37
1918
by: Sweetiecakes | last post by:
Hello I'm a bit confused on how one should handle exceptions. I'm currently building an ADO.NET Windows Forms application. I've built a class for manipulating data within the database in question. This class is used from forms. Let's assume that there is an SQL error: this will throw an exception. Where should I handle it? How should I handle it? What to show when giving an error message? Is there any "centralized" way of handling...
0
9796
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9643
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10790
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10214
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9329
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6952
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5624
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4425
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3976
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.