| Author |
Topic |
|
cosku
Starting Member
4 Posts |
 Posted - 2002-01-10 : 05:29:24
|
| Why can't I capture the raised error when I execute conn.Execute("select n=1 into #t; raiserror('t',16,1)")orconn.Execute("insert into t values(1); raiserror('t',16,1)")--where t is a table having a single integer columnorconn.Execute("insert into t values(1); insert into t values('a')")--again where t is a table having a single integer columnfrom an VB Application using ADO 2.7, SQL Server 2000 SP2 |
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-01-10 : 08:22:33
|
| I believe you'd have to use ADO's error collection to return the error code back to your page.Mike "A program is a device used to convert data into error messages." |
 |
|
|
cosku
Starting Member
4 Posts |
Posted - 2002-01-10 : 08:32:31
|
| conn.Errors.count returns to be zero after the execution of sql statementsthat's why I can't capture the errorthat is to say I can capture the error if I writeconn.Execute("insert into t values('a')")but cannot if i writeconn.Execute("insert into t values(1); insert into t values('a')")thx |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-01-10 : 14:16:07
|
| I'm stuck on time so I can't try this out myself yet, but what are the results of the infomessages event of the connection object?----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
cosku
Starting Member
4 Posts |
Posted - 2002-01-11 : 07:50:09
|
| infomessages event is not fired... |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-01-11 : 08:02:22
|
| A few things here, they may or may not be totally relevant (I'm tired)1. You will only be able to see errors for the last statement. So if you fire one that fails, but follow it up with a good one, you will not be able to see the error.2. I belive the SQLOLEDB provider doesn't properly report all error descriptions properly.3. To do what you want to do, I suggest wrapping the operation up in a stored proc with a transaction. Then you can guarantee that if one fails, it all will be rolled back.Damian |
|
|
|
cosku
Starting Member
4 Posts |
Posted - 2002-01-11 : 08:53:20
|
| in fact my problem is reverse of first situation in my case first statement is a good one and the second fails but still i cannot capture the errorsecondly even if the transaction is rolled back user will be unaware of the error occured which is a fatally undesirable situation |
|
|
|
mfemenel
Professor Frink
1421 Posts |
Posted - 2002-01-11 : 09:29:43
|
| What about returning a value of 1 or 0 back from your procedure. If it runs through normally return a 1 else 0. Might be a viable work around.Mike "A program is a device used to convert data into error messages." |
|
|
|
Lavos
Posting Yak Master
200 Posts |
Posted - 2002-01-12 : 02:51:35
|
| Unfortunately, I don't have the documentation with me so let me ignore my first response. I know most of the occassions when informessages gets called, I guess I misremembered errors being one of those times.Anyway, I tried your examples, and they worked just fine for me.For instance:Private Sub Command1_Click() con.Open Debug.Print con.Errors.Count con.Execute "select n=1 into #a; raiserror('t',16,1)" Debug.Print con.Errors.Count con.CloseEnd SubProduced exactly what it should've produced in the immediate window. I didn't have the exact same layout as you, (VB, tried with ADO 2.0, 2.5, and 2.6) with sql server 2000 (I don't know the service pack number off hand and can't check from here.) I tried it with your other examples, and I tried it with all three examples in sequence and checking con.Errors.Count between each call to Execute. It always reported 1 error after the statement executed.It looks like there is either a bug in 2.7, or your code is wrong somehow. Are you checking Errors.Count immediately after the call to Execute, or at least before using any other ADO objects/methods?----------------------"O Theos mou! Echo ten labrida en te mou kephale!" |
|
|
|
|
Error handling using ADO