Hi people!
Can anyone help me with this one? Using SQL Server 2000 and VB5,
calling a stored procedure from VB code. When a message occurs like:
Server: Msg 515, Level 16, State 2, Procedure Ve_KarticeIsplate, Line
235
Cannot insert the value NULL into column 'konto', table
'ibis.dbo.promkred'; column does not allow nulls. INSERT fails.
The statement has been terminated.
VB freaks out, looses the connection to server and stops the execution.
I want to be either be able to disable this message or to ignore it
from client app (using ADO for connection)...
(Simply to ignore the error)Hi VesnaSA,
Here are two excellent articles on error handling in SQL server:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
A lot of reading but it's worth it.
"VesnaSA" <VesnaVujovicSA@.gmail.com> wrote in message
news:1132235726.691296.264240@.g14g2000cwa.googlegroups.com...
> Hi people!
> Can anyone help me with this one? Using SQL Server 2000 and VB5,
> calling a stored procedure from VB code. When a message occurs like:
> Server: Msg 515, Level 16, State 2, Procedure Ve_KarticeIsplate, Line
> 235
> Cannot insert the value NULL into column 'konto', table
> 'ibis.dbo.promkred'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> VB freaks out, looses the connection to server and stops the execution.
> I want to be either be able to disable this message or to ignore it
> from client app (using ADO for connection)...
> (Simply to ignore the error)
>|||> Cannot insert the value NULL into column 'konto', table
> 'ibis.dbo.promkred'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> VB freaks out, looses the connection to server and stops the execution.
> I want to be either be able to disable this message or to ignore it
> from client app (using ADO for connection)...
> (Simply to ignore the error)
>
it would be better though not to pass NULL value to this column? or drop the
constraint from database, though i assume that having nulls in konto column
might be somewhat confusing for people working with account related
application
peter|||Thanx but i handle those transactions with errors in another way, by
placing them in another table,
the point is not to prevent it but to disable the message so that the
procedure can go on for the correct
ones and just skip the ones with errors... To prevent VB to loose the
connection when error happens...
But thanx anyway...|||Thanx Reymond, I'll dig into it, it seams that there is no way to
prevent server from sending the message, thw question is now if the
recordset in ADO can be adjusted so it doesn't report the message?|||I'm not a VB programmer but maybe this will help:
(this is script but you can probably adapt it to VB)
ON ERROR RESUME NEXT
--execute your stored procedure Ve_KarticeIsplate here
If you wish, you can view the errors:
If objConn.errors.count > 0 then
Response.write(objConn.errors.count) & " = " &
objConn.errors(0).description
For counter= 0 to objConn.errors.count
Response.write(objConn.errors(counter).number)
Response.write(objConn.errors(counter).description)
Next
end if
If you're still having problems, post in one of the VB newsgroups.
"VesnaSA" <VesnaVujovicSA@.gmail.com> wrote in message
news:1132238362.365549.85690@.z14g2000cwz.googlegroups.com...
> Thanx Reymond, I'll dig into it, it seams that there is no way to
> prevent server from sending the message, thw question is now if the
> recordset in ADO can be adjusted so it doesn't report the message?
>|||shouldn't you do it in the stored procedure then? using db to handle and
send errors might be expensive, and ADO is not best on handling errors.
also, I assume that account number shouldn't be empty and this is a business
rule, so why don't you check it before you call db? this would save you the
problem. exceptions should not be the way you control flow of your program.
Peter
"VesnaSA" <VesnaVujovicSA@.gmail.com> wrote in message
news:1132237902.217152.65360@.g14g2000cwa.googlegroups.com...
> Thanx but i handle those transactions with errors in another way, by
> placing them in another table,
> the point is not to prevent it but to disable the message so that the
> procedure can go on for the correct
> ones and just skip the ones with errors... To prevent VB to loose the
> connection when error happens...
> But thanx anyway...
>|||"Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
news:%23GEb9u46FHA.2716@.TK2MSFTNGP11.phx.gbl...
> shouldn't you do it in the stored procedure then? using db to handle and
> send errors might be expensive, and ADO is not best on handling errors.
> also, I assume that account number shouldn't be empty and this is a
> business rule, so why don't you check it before you call db? this would
> save you the problem. exceptions should not be the way you control flow of
> your program.
> Peter
I agree with you on this one.
Error handling has to be done in the database.
The parameters must be validated client side.
But (I'm sure that you saw the BUT coming) error handling also has to be
done in VB.
Imagine these cases:
-the connexion to the database is lost
-someone changed something in the database
-some unforseen error occors in the SP|||First, resolve the bug in the stored procedure which is trying to insert a
NULL into a non-nullable column.
There are techniques for implementing error handling in T-SQL, but this is
only useful for basic things like rolling back transactions and process
flow. The last line of defense is error handling at the application level,
and based on your description of the "VB freaks out", it sounds like perhaps
there no module or function level handling in the application, and the error
is being escalated up the call stack until becomes unrecoverable.
Handling Errors in Visual Basic
Error Handling Hierarchy
http://msdn.microsoft.com/library/d...nghierarchy.asp
Handling Errors and Messages in ADO
http://msdn.microsoft.com/library/d...
9pr.asp
http://msdn.microsoft.com/library/d...rorhandling.asp
"VesnaSA" <VesnaVujovicSA@.gmail.com> wrote in message
news:1132235726.691296.264240@.g14g2000cwa.googlegroups.com...
> Hi people!
> Can anyone help me with this one? Using SQL Server 2000 and VB5,
> calling a stored procedure from VB code. When a message occurs like:
> Server: Msg 515, Level 16, State 2, Procedure Ve_KarticeIsplate, Line
> 235
> Cannot insert the value NULL into column 'konto', table
> 'ibis.dbo.promkred'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> VB freaks out, looses the connection to server and stops the execution.
> I want to be either be able to disable this message or to ignore it
> from client app (using ADO for connection)...
> (Simply to ignore the error)
>|||True, it is important to implement T-SQL error handling so that the
transaction is rolled back, but any advanced error handling like retry logic
or logging errors is best implemented at the application level.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:e2XAz%2346FHA.3660@.TK2MSFTNGP09.phx.gbl...
> "Rogas69" <rogas69@.no_spamers.o2.ie> wrote in message
> news:%23GEb9u46FHA.2716@.TK2MSFTNGP11.phx.gbl...
> I agree with you on this one.
> Error handling has to be done in the database.
> The parameters must be validated client side.
> But (I'm sure that you saw the BUT coming) error handling also has to be
> done in VB.
> Imagine these cases:
> -the connexion to the database is lost
> -someone changed something in the database
> -some unforseen error occors in the SP
>
