How does one trap the duplicate key error for SQL server?

kazai · Nov 21, 2001

hi,
I wanted to know how one can trap the duplicate key error for SQL server? I have an insert statement, which throws up an error for duplicate insertions...how do I trap that error and display my own appropriate message....PLS. HELP!!!!! IT's VERY VERY URGENT!!!!!!!!
thanks,
Radhika.

Guest_imported · Nov 21, 2001

Here's how I would go about this:

form.asp
-------------
<%@ Language=VBScript %>
<HTML>
<HEAD>
</HEAD>
<BODY>

<form name="test" action="insert.asp" method="post">
<input type="text" name="pub_id">
<input type="text" name="pub_name">
<input type="submit">
</form>

</BODY>
</HTML>

insert.asp
--------------

<%@ Language=VBScript %>
<HTML>
<HEAD>
</HEAD>
<BODY>

<%
dim ors,oconn,strsql,strconn
strconn = "driver={SQL Server};server=server2000;uid=sa;pwd=;database=pubs"
set oconn = server.CreateObject("ADODB.Connection&quot
oconn.Open strconn
strsql = "Select pub_name from publishers where pub_id = '" & request("pub_id&quot& "'"
set ors = oconn.Execute(strsql)
if not ors.eof then
Response.Write("This id already exists!!!!&quot
else
strsql = "Insert into publishers (pub_id,pub_name) values ('" & request("pub_id&quot& "','" & request("pub_name&quot& "')"
set ors = oconn.Execute(strsql)
if err.number = 0 then Response.Write "Success!"
end if
%>

</BODY>
</HTML>

Of course, this is just a sample, but it works. Just take care of closing and setting objects to nothing after using them.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How does one trap the duplicate key error for SQL server?

kazai

Programmer

Guest_imported

New member

Similar threads

Part and Inventory Search

Sponsor