Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mr_blore
Starting Member
7 Posts |
 Posted - 2004-10-20 : 03:17:29
|
| I have a stored proc which loops through and inserts into a table. Whereever an error occurs, I capture it in a comma seperated string @errlist and return it to my view[client] and the client takes care of handling the error.WHILE (@iLoopCount > 0)BEGININSERT INTO table1 (col1, col2) (SELECT col1, col2 FROM table2 WHERE iRowid = @iLoopCount)SET @err = @@ERRORIF @err <> 0 BEGIN print 'An error occured' SET @errList = 4 -- this is an out parameter for my stored proc ENDSET @iLoopCount = @iLoopCount –1ENDGOMy client code looks like this :try{ cmd.ExecuteNonQuery();}catch(Exception err){ MessageBox.Show(p3.Value.ToString()); -- I get the output paramter in my catch block return; }How can this error handling be done better from the client side? |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-20 : 04:18:14
|
| Our SProcs return a unique error number for each possible error location (only the first one is kept) and a concatenated string of "user friendly messages" for all the errors that were encountered. The client can then either react to a [known/expected] error number, or display the "user friendly message" to the operator.Dunno if that helps though?Kristen |
 |
|
|
mr_blore
Starting Member
7 Posts |
Posted - 2004-10-20 : 04:31:08
|
| Kristen,You are right....We are also sending the error number back to the client so that the trnsaction manager in client would react accordingly.My question is "Although am handling the error in the SProc, why should it throw it as an exception in my client"?thanksMeera |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-20 : 05:11:15
|
Ah, I see. I expect that there is something in the ADO errors collection, and that is being interpretted as there being a problem. Does the client treat this is an exception too?SELECT MAX(foo)FROM( SELECT [foo] = CONVERT(int, NULL)) X that will generate a warning (attempting to do MAX on a column that contains NULLs)Kristen |
|
|
|
mr_blore
Starting Member
7 Posts |
Posted - 2004-10-20 : 05:48:02
|
| hi,warnings are not treated as exceptions in the client code.But any valid exceptions are thrown bak to the client.So in my client code :try{ cmd.ExecuteNonQuery(); MessageBox("This msg is not shown in case of an exception!");}catch(Exception err){ MessageBox.Show(p3.Value.ToString()); -- I get the output paramter in my catch block return; }Though am handling the error in SProc and returning the error list as an out parameter, still the exception is thrown to the client and so the messagebox after ExecuteNonQuery() never gets shown! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-10-21 : 01:23:54
|
| Can we be sure because it is because of the presence of data in the ADO collection?Maybe you have to do without the try/catch - although that puts some strain on your SProcs to ALWAYS & Without Fail!! catch errors.Or your CATCH could operate ONLY if you have NO error message from the SProc (i.e. p3.Value = 0/empty/NULL)Kristen |
|
|
|
a_shyam41
Starting Member
9 Posts |
Posted - 2004-10-21 : 03:00:41
|
| Why don't you use "On Error Resume Next" in your client code. That way you can suppress the exception that you get and handle it manually by checking the returned value from the stored procedure. |
|
|
|
|
|
|
|
|
Please Help!!!