|
 |
asp_databases thread: Error Handling on SQL Server Database
Message #1 by "Hong, Alan" <AHong@w...> on Thu, 28 Dec 2000 14:59:50 +1030
|
|
Hi all,
I need some assistance with how to handle errors with insert and update for
SQL Server. Basically I want to trap for duplicate row error in order to
display a user friendly error message and stop the page from abending. I
tried to check for errors after the execution of the SQL string but still am
not able to trap the errors. Any help is very much appreciated. Below is a
sample of my code and the error message encountered.
Sample code:
....
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = strDSN
objConn.Open
objConn.BeginTrans()
' Save Job_Option row
strSQL = "insert " _
& " into Job_Option " _
& " ( Job_Option_Val " _
& " ) "_
& "values " _
& " ( '" & DublQ(Request.Form("Job_Option_Val2")) & "' " _
& " ) "
objConn.Execute(strSQL)
If objConn.Errors.Count > 0 Then
For Each error in objConn.Errors
Response.Write("Error Number: " & error.Number &
"<BR>")
Response.Write("Error Description: " &
error.Description & "<BR>")
Response.Write("Error Source: " & error.Source &
"<BR>")
Response.Write("Error SQLState: " & error.SQLState &
"<BR>")
Next
objConn.Errors.Clear
End If
' Commit the entire insert process
objConn.CommitTrans()
Error Run Time Message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Violation of PRIMARY KEY constraint 'PK_Job_Option'. Cannot insert duplicate
key in object 'Job_Option'.
/RISEJobLeads/code/JobOptionDetails.asp, line 89
Regards,
Alan Hong
****************************************************************************
This e-mail is intended for the use of the addressee only. It may contain
information that is protected by legislated confidentiality and/or is
legally privileged. If you are not the intended recipient you are prohibited
from disseminating, distributing or copying this e-mail. Any opinion
expressed in this e-mail may not necessarily be that of the WorkCover
Corporation of South Australia. Although precautions have been taken, the
sender cannot warrant that this e-mail or any files transmitted with it are
free of viruses or any other defect.
If you have received this e-mail in error, please notify the sender
immediately by return e-mail and destroy the original e-mail and any copies.
****************************************************************************
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #2 by Stephane_Dattenny@D... on Thu, 28 Dec 2000 02:44:20 -0600
|
|
Because ADO raise an error when SQL server is not able to execute your
query, when you use
< If objConn.Errors.Count > 0 Then >, the ASP processor stops.
Try to add:
On error resume next
...at the beginning of your asp page.
Best regards / Cordialement
Stephane Dattenny
Dell Computers Corporation - EMEA Services IT - Windows DNA developer
Phone: +33 (0)4 99 75 49 88
Email: stephane_dattenny@d... <mailto:stephane_dattenny@d...>
-----Original Message-----
From: Hong, Alan [mailto:AHong@w...]
Sent: 28 December 2000 16:24
To: ASP Databases
Subject: [asp_databases] Error Handling on SQL Server Database
Hi all,
I need some assistance with how to handle errors with insert and update for
SQL Server. Basically I want to trap for duplicate row error in order to
display a user friendly error message and stop the page from abending. I
tried to check for errors after the execution of the SQL string but still am
not able to trap the errors. Any help is very much appreciated. Below is a
sample of my code and the error message encountered.
Sample code:
....
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.ConnectionString = strDSN
objConn.Open
objConn.BeginTrans()
' Save Job_Option row
strSQL = "insert " _
& " into Job_Option " _
& " ( Job_Option_Val " _
& " ) "_
& "values " _
& " ( '" & DublQ(Request.Form("Job_Option_Val2")) & "' " _
& " ) "
objConn.Execute(strSQL)
If objConn.Errors.Count > 0 Then
For Each error in objConn.Errors
Response.Write("Error Number: " & error.Number &
"<BR>")
Response.Write("Error Description: " &
error.Description & "<BR>")
Response.Write("Error Source: " & error.Source &
"<BR>")
Response.Write("Error SQLState: " & error.SQLState &
"<BR>")
Next
objConn.Errors.Clear
End If
' Commit the entire insert process
objConn.CommitTrans()
Error Run Time Message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Violation of PRIMARY KEY constraint 'PK_Job_Option'. Cannot insert duplicate
key in object 'Job_Option'.
/RISEJobLeads/code/JobOptionDetails.asp, line 89
Regards,
Alan Hong
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #3 by Gregory_Griffiths@c... on Thu, 28 Dec 2000 08:47:01 +0000
|
|
You could try to query the database for a key field e.g.
SELECT job_number FROM Job_Option WHERE job_number=1
If that returns EOF then you don't have a record in there, otherwise
the row exists.
> -----Original Message-----
> From: AHong@w... [mailto:AHong@w...]
> Sent: 28 December 2000 15:24
> To: asp_databases@p...
> Cc: AHong@w...
> Subject: [asp_databases] Error Handling on SQL Server Database
>
>
> Hi all,
>
> I need some assistance with how to handle errors with insert
> and update for
> SQL Server. Basically I want to trap for duplicate row error
> in order to
> display a user friendly error message and stop the page from
> abending. I
> tried to check for errors after the execution of the SQL
> string but still am
> not able to trap the errors. Any help is very much
> appreciated. Below is a
> sample of my code and the error message encountered.
>
> Sample code:
> ....
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.ConnectionString = strDSN
> objConn.Open
> objConn.BeginTrans()
>
> ' Save Job_Option row
> strSQL = "insert " _
> & " into Job_Option " _
> & " ( Job_Option_Val " _
> & " ) "_
> & "values " _
> & " ( '" &
> DublQ(Request.Form("Job_Option_Val2")) & "' " _
> & " ) "
>
> objConn.Execute(strSQL)
>
> If objConn.Errors.Count > 0 Then
> For Each error in objConn.Errors
> Response.Write("Error Number: " & error.Number &
> "<BR>")
> Response.Write("Error Description: " &
> error.Description & "<BR>")
> Response.Write("Error Source: " & error.Source &
> "<BR>")
> Response.Write("Error SQLState: " &
> error.SQLState &
> "<BR>")
> Next
> objConn.Errors.Clear
> End If
> ' Commit the entire insert process
> objConn.CommitTrans()
>
> Error Run Time Message:
> Microsoft OLE DB Provider for SQL Server error '80040e14'
>
> Violation of PRIMARY KEY constraint 'PK_Job_Option'. Cannot
> insert duplicate
> key in object 'Job_Option'.
>
> /RISEJobLeads/code/JobOptionDetails.asp, line 89
>
>
> Regards,
>
> Alan Hong
>
>
>
---
NEED TECHNICAL TIPS, TOOLS, AND INSIGHTS? Is FREE okay?
Visit EarthWeb for the latest in IT Management, Software Development,
Web Development, Networking & Communications, and Hardware & Systems.
Click on http://www.earthweb.com for FREE articles, tutorials,
and discussions from the experts.
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
Message #4 by "jignesh gandhi" <jignesh@h...> on Thu, 28 Dec 2000 14:28:09 +0530
|
|
This is a multi-part message in MIME format.
------=_NextPart_000_002A_01C070DA.66ABF220
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
hi
try this way
dim joboption
joboption =3D DublQ(Request.Form("Job_Option_Val2")
before inserting just do this
set rscheck =3D conn.execute("select 'x' from Job_Option where
Job_Option_Val =3D '" & joboption & "'")
if rscheck.eof
insert the data
else
data already exists
end if
j b gandhi
----- Original Message -----
From: Hong, Alan
To: ASP Databases
Sent: Thursday, December 28, 2000 8:54 PM
Subject: [asp_databases] Error Handling on SQL Server Database
Hi all,
I need some assistance with how to handle errors with insert and
update for
SQL Server. Basically I want to trap for duplicate row error in order
to
display a user friendly error message and stop the page from abending.
I
tried to check for errors after the execution of the SQL string but
still am
not able to trap the errors. Any help is very much appreciated. Below
is a
sample of my code and the error message encountered.
Sample code:
....
Set objConn =3D Server.CreateObject("ADODB.Connection")
objConn.ConnectionString =3D strDSN
objConn.Open
objConn.BeginTrans()
' Save Job_Option row
strSQL =3D "insert " _
& " into Job_Option " _
& " ( Job_Option_Val " _
& " ) "_
& "values " _
& " ( '" & DublQ(Request.Form("Job_Option_Val2")) & "'
" _
& " ) "
objConn.Execute(strSQL)
If objConn.Errors.Count > 0 Then
For Each error in objConn.Errors
Response.Write("Error Number: " & error.Number &
"<BR>")
Response.Write("Error Description: " &
error.Description & "<BR>")
Response.Write("Error Source: " & error.Source &
"<BR>")
Response.Write("Error SQLState: " & error.SQLState &
"<BR>")
Next
objConn.Errors.Clear
End If
' Commit the entire insert process
objConn.CommitTrans()
Error Run Time Message:
Microsoft OLE DB Provider for SQL Server error '80040e14'
Violation of PRIMARY KEY constraint 'PK_Job_Option'. Cannot insert
duplicate
key in object 'Job_Option'.
/RISEJobLeads/code/JobOptionDetails.asp, line 89
Regards,
Alan Hong
*************************************************************************
***
This e-mail is intended for the use of the addressee only. It may
contain
information that is protected by legislated confidentiality and/or is
legally privileged. If you are not the intended recipient you are
prohibited
from disseminating, distributing or copying this e-mail. Any opinion
expressed in this e-mail may not necessarily be that of the WorkCover
Corporation of South Australia. Although precautions have been taken,
the
sender cannot warrant that this e-mail or any files transmitted with
it are
free of viruses or any other defect.
If you have received this e-mail in error, please notify the sender
immediately by return e-mail and destroy the original e-mail and any
copies.
*************************************************************************
***
---
FREE SOFTWARE DEVELOPMENT CODE, CONTENT, AND
INSIGHTS IN YOUR INBOX!
Get the latest and best C++, Visual C++, Java, Visual Basic, and XML
tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb?s
FREE IT newsletters at http://www.earthweb.com today!
leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
---
FREE WEB DEVELOPMENT CODE, CONTENT, AND INSIGHTS
IN YOUR INBOX!
Get the latest and best HTML, XML, and JavaScript tips, tools, and
developments from the experts. Sign up for one or more of EarthWeb's
FREE IT newsletters at http://www.earthweb.com today!
---
You are currently subscribed to asp_databases as: $subst('Recip.EmailAddr')
To unsubscribe send a blank email to leave-asp_databases-$subst('Recip.MemberIDChar')@p2p.wrox.com
|
|
|
View Cart
