Hello. First, sorry for the long post. Others have tried to help but I need SPECIFIC instructions of what and where to add code-too confusing!
I have a page that inserts a new record into my Access db. The vehID field is indexed to prevent duplicates. When a user enters a duplicate vehID in my form page, this error is displayed:
--------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship...
--------
What can I do to display a message that the Record Exists? Or, if easier, rather than check the database to verify it's a duplicate vehID, just assume it is and display a this msg on any error?
Below are both pages - vehiclesaddform.asp & it's included file utils.asp that throws the error.
Thanks for any help.
Driving me crazy, last bit left on this site.
THANKS!!!!!!!!!!!
==========
<!-- #include file=utils.asp -->
<html>
<head>
<title>vehicles</title>
<link rel="stylesheet" href="/carlot/ss.css" type="text/css">
</head>
<body bgcolor="#FFFFFF">
<form method= post action=vehiclesaddform.asp onSubmit="return validateForm();" name=vehicles> <center>
<table width=75%>
<!-- #include file ="sec.asp" -->
<tr>
<td colspan=2>
<!--#include virtual="/carlot/adminn/admin_header.asp" -->
</td>
</tr>
<tr>
<td colspan=2>
<div align="center"><b><font size="2" color="#FF0000">Add New Vehicle</font></b></div>
</td>
</tr>
<tr>
<td bgcolor="#E4E4E4">catID</td>
<td bgcolor="#E4E4E4">
<input type="text" name="txtcatID" size="30" maxlength="30">
<font size="1">Choose a category. <font color="#000000">Enter </font><font color="#0000FF">1<font color="#000000">,</font>
2 <font color="#000000">or </font>3 <font color="#000000">only.</font></font><br>
1 Sedans, Coupes<br>
2 Sport Utilities<br>
3 Pickups, Vans </font></td>
</tr>
<tr>
<td bgcolor="#D1D1D1">vehID<font color=red>*</font></td>
<td bgcolor="#D1D1D1">
<input type="text" name="txtvehID" size="30" maxlength="50">
<font size="1">Enter Stock Number using lower case: '<font color="#0000FF">d1292</font>'</font>
</td>
</tr>
<tr>
<td bgcolor="#E4E4E4">year<font color=red>*</font></td>
<td bgcolor="#E4E4E4">
<input type="text" name="txtyear" size="30" maxlength="30">
<font size="1">Enter 4 digit year '<font color="#0000FF">2002</font>'</font>
</td>
</tr>
<tr>
<td bgcolor="#D1D1D1">mfrID<font color=red>*</font></td>
<td bgcolor="#D1D1D1">
<input type="text" name="txtmfrID" size="30" maxlength="30">
<font size="1">Select a <font color="#0000FF">numerical code</font>
for Make.</font><br>
<table width="100%" border="0" cellspacing="2" cellpadding="0">
<tr>
<td><font size="1">1 Acura<br>
2 Audi<br>
3 BMW<br>
4 Buick<br>
5 Cadillac<br>
</tr>
</table>
</td>
</tr>
<tr>
<td bgcolor="#E4E4E4">model<font color=red>*</font></td>
<td bgcolor="#E4E4E4">
<input type="text" name="txtmodel" size="30" maxlength="50">
<font size="1">ie: <font color="#0000FF">Explorer</font></font> </td>
</tr>
<tr>
<td bgcolor="#D1D1D1">color</td>
<td bgcolor="#D1D1D1">
<input type="text" name="txtcolor" size="30" maxlength="50">
<font size="1">ie: <font color="#0000FF">blue</font></font> </td>
</tr>
<tr>
<td bgcolor="#E4E4E4">body</td>
<td bgcolor="#E4E4E4">
<input type="text" name="txtbody" size="30" maxlength="50">
<font size="1">Enter <font color="#0000FF">2 door<font color="#000000">
or </font>4 door</font>. Use this format only. Or, cab/half, etc. can
be used.</font> </td>
</tr>
<tr>
<td bgcolor="#D1D1D1">miles</td>
<td bgcolor="#D1D1D1">
<input type="text" name="txtmiles" size="30" maxlength="30">
<font size="1">ie: <font color="#0000FF">23425<font color="#000000">
-</font></font> Do not enter commas</font> </td>
</tr>
<tr>
<td bgcolor="#E4E4E4">price<font color=red>*</font></td>
<td bgcolor="#E4E4E4">
<input type="text" name="txtprice" size="30" maxlength="8">
<font size="1">Enter as <font color="#0000FF">8995</font> (for $8,995).
Do not enter comma, dollar sign or decimal point. </font> </td>
</tr>
<tr bgcolor="#D1D1D1">
<td>dateentd</td>
<td>
<input type="text" name="txtdateentd" size="30" maxlength="30">
<font size="1">Use default date or enter in format <font color="#0000FF">09/01/2004</font></font></td>
</tr>
<tr>
<td bgcolor="#E4E4E4">sold</td>
<td bgcolor="#E4E4E4">
<input type="checkbox" name="txtsold">
<font size="1">Check to hide temporarily from public. To delete permanently,
click 'Delete' button below.</font></td>
</tr>
<tr bgcolor="#D1D1D1">
<td>descr</td>
<td>
<textarea name="txtdescr" cols="80" rows="2"></textarea>
<br>
<font size="1">Enter a description for more detail if needed. ie: <font color="#0000FF">Eddie
Bauer</font>, one-owner</font> </td>
</tr>
<script language="javascript1.2">
editor_generate('txtdescr');
</script>
<tr>
<td bgcolor="#E4E4E4">photo_f</td>
<td bgcolor="#D1D1D1">
<input type="text" name="txtphoto_f" size="30" maxlength="50">
<font size="1">Full Size Photo - tName photo same as vehID. ie: <font color="#0000FF" size="2">d1200<font color="#FF0000">f</font>.jpg</font></font>
</td>
</tr>
<tr bgcolor="#D1D1D1">
<td>photo_t</td>
<td>
<input type="text" name="txtphoto_t" size="30" maxlength="50">
<font size="1">Thumbnail Photo - Name photo same as vehID. ie: <font color="#0000FF" size="2">d1200<font color="#FF0000">t</font>.jpg</font></font>
</td>
</tr>
<tr>
<td bgcolor="#E4E4E4"></td>
<td bgcolor="#E4E4E4">
<input type="submit" value="Submit" name="submit">
<font size="1" color="red">*</font><font size="1"> required field.</font>
</td>
</tr>
<tr>
<td colspan=2>
<div align="center"> <font size=1><a href="#top"><img src="/carlot/images/top.gif" width="21" height="22" border="0"></a>
</font></div>
</td>
</tr><tr>
<td colspan=2><!--#include virtual="/carlot/adminn/admin_header.asp" --></td>
</tr>
</table>
</center></form></body></html>
<script>
<% 'sql statement that gets passed to xrecord function in utils.asp
sub doRecord()
if request.form("txtdateentd")="" then
sqldateentd="Null"
else
sqldateentd="#" & request.form("txtdateentd") & "#"
end if
if request.form("txtsold")="" then
sqlsold=0
else
sqlsold=request.form("txtsold")
end if
strSql="Insert into [vehicles](catID,vehID,year,mfrID,model,color,body,miles,price,dateentd,sold,descr,photo_f,photo_t) values( " & request.form("txtcatID")& ","& "'" & replace(request.form("txtvehID"),"'","''") & "'," & request.form("txtyear")& "," & request.form("txtmfrID")& ","& "'" & replace(request.form("txtmodel"),"'","''") & "',"& "'" & replace(request.form("txtcolor"),"'","''") & "',"& "'" & replace(request.form("txtbody"),"'","''") & "'," & request.form("txtmiles")& "," & request.form("txtprice")& "," & sqldateentd & "," & sqlsold & ","& "'" & replace(request.form("txtdescr"),"'","''") & "',"& "'" & replace(request.form("txtphoto_f"),"'","''") & "',"& "'" & replace(request.form("txtphoto_t"),"'","''") & "')"
xRecord strSql,"vehicleslistadmin.asp?s=1"
end sub %>
<!-- java script validation -->
<script type="text/javascript">
function validateForm() {
if (document.forms[0].txtcatID.value=="")
{document.forms[0].txtcatID.value=0}
if (isNaN(document.forms[0].txtcatID.value)){self.alert("Please enter a numeric value in catID!");
document.forms[0].txtcatID.focus();
return false; }
if (document.forms[0].txtvehID.value == "")
{self.alert("You must make an entry in vehID!");
document.forms[0].txtvehID.focus();
return false;}
if (document.forms[0].txtyear.value == "")
{self.alert("You must make an entry in year!");
document.forms[0].txtyear.focus();
return false;}
if (document.forms[0].txtyear.value=="")
{document.forms[0].txtyear.value=0}
if (isNaN(document.forms[0].txtyear.value)){self.alert("Please enter a numeric value in year!");
document.forms[0].txtyear.focus();
return false; }
if (document.forms[0].txtmfrID.value == "")
{self.alert("You must make an entry in mfrID!");
document.forms[0].txtmfrID.focus();
return false;}
if (document.forms[0].txtmfrID.value=="")
{document.forms[0].txtmfrID.value=0}
if (isNaN(document.forms[0].txtmfrID.value)){self.alert("Please enter a numeric value in mfrID!");
document.forms[0].txtmfrID.focus();
return false; }
if (document.forms[0].txtprice.value == "")
{self.alert("You must make an entry in Price!");
document.forms[0].txtprice.focus();
return false;}
if (document.forms[0].txtprice.value=="")
{document.forms[0].txtprice.value=0}
if (isNaN(document.forms[0].txtprice.value)){self.alert("Please enter a numeric value in Price!");
document.forms[0].txtprice.focus();
return false; }
if (document.forms[0].txtmodel.value == "")
{self.alert("You must make an entry in model!");
document.forms[0].txtmodel.focus();
return false;}
if (document.forms[0].txtmiles.value=="")
{document.forms[0].txtmiles.value=0}
if (isNaN(document.forms[0].txtmiles.value)){self.alert("Please enter a numeric value in miles!");
document.forms[0].txtmiles.focus();
return false; }
if (document.forms[0].txtsold.value == "")
{self.alert("You must make an entry in sold!");
document.forms[0].txtsold.focus();
return false;}
}</script>
=================================
utils.asp (this is the included file that throws the error at the line "conn.execute(strsql)"
=================================
<%'sets up dsnless connection string
mydsn="DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\") & "\fpdb\inventory.mdb"
'creates ado database objects
set rs=server.createobject("adodb.recordset")
set conn=server.createobject("adodb.connection")
rs.CursorLocation = 3
rs.CursorType = 3
'if the delete button has been pressed the delrecord function is called
'this can be found at the bottom of the admin page
if request.querystring("d")<>"" then delrecord()
'if add or update has been pressed then this calls dorecord function at bottom of admin page
if request.form("submit")<>"" then dorecord()
function getLineBreaks(str)
'take care of null values
on error resume next
'this displays carriage returns!
getLineBreaks=replace(str,chr(10),"<br>")
end function
'this function executes the sql statement passed to it from either dorecord or delrecord
'and then redirect's to the list screen for that table by using the passed gowhere paramater
function xRecord(strSql,gowhere)
conn.open mydsn
conn.execute(strsql)
conn.close
set conn=nothing
response.redirect(gowhere)
end function %>