I need the code to create a server behavior that will first check the username against the MS Access database and then check the password separately against the MS Access database so it doesn't try to write a duplicate to the database. I am using the User Authentication Server Behavior Check New Username for the username and the form works perfectly to allow users to create a username and login, and prevents new users from creating a duplicate username. However, if they attempt to create a password that already exists the following error occurs:

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. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

/registration/index.asp, line 54

All I need is the same redirection action for a duplicate password field that works for the username field. I don't think this should be this difficult of a fix since it only requires duplicating the server behavior that works for the check Username but for a different field in the form.

Thank you for the help, I have attached the code below to ensure the usernames in the table match the appropriate script to fix the issue. Code:

<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../Connections/login_dbase.asp" -->
<%
Dim MM_editAction
MM_editAction = CStr(Request.ServerVariables("SCRIPT_NAME")
If (Request.QueryString <> "" Then
MM_editAction = MM_editAction & "?" & Server.HTMLEncode(Request.QueryString)
End If

' boolean to abort record edit
Dim MM_abortEdit
MM_abortEdit = false
%>
<%
' *** Redirect if username exists
MM_flag = "MM_insert"
If (CStr(Request(MM_flag)) <> "" Then
Dim MM_rsKey
Dim MM_rsKey_cmd

MM_dupKeyRedirect = "www.robvazquez.com/registration/user_name_taken.asp"
MM_dupKeyUsernameValue = CStr(Request.Form("UserName")
Set MM_rsKey_cmd = Server.CreateObject ("ADODB.Command"
MM_rsKey_cmd.ActiveConnection = MM_login_dbase_STRING
MM_rsKey_cmd.CommandText = "SELECT Username FROM db1 WHERE Username = ?"
MM_rsKey_cmd.Prepared = true
MM_rsKey_cmd.Parameters.Append MM_rsKey_cmd.CreateParameter("param1", 200, 1, 50, MM_dupKeyUsernameValue) ' adVarChar
Set MM_rsKey = MM_rsKey_cmd.Execute
If Not MM_rsKey.EOF Or Not MM_rsKey.BOF Then
' the username was found - can not add the requested username
MM_qsChar = "?"
If (InStr(1, MM_dupKeyRedirect, "?" >= 1) Then MM_qsChar = "&"
MM_dupKeyRedirect = MM_dupKeyRedirect & MM_qsChar & "requsername=" & MM_dupKeyUsernameValue
Response.Redirect(MM_dupKeyRedirect)
End If
MM_rsKey.Close
End If
%>
<%
If (CStr(Request("MM_insert") = "loginform" Then
If (Not MM_abortEdit) Then
' execute the insert
Dim MM_editCmd

Set MM_editCmd = Server.CreateObject ("ADODB.Command"
MM_editCmd.ActiveConnection = MM_login_dbase_STRING
MM_editCmd.CommandText = "INSERT INTO db1 ([First Name], [Last Name], Email, Username, Password) VALUES (?, ?, ?, ?, ?)"
MM_editCmd.Prepared = true
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param1", 202, 1, 50, Request.Form("FirstName") ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param2", 202, 1, 50, Request.Form("LastName") ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param3", 202, 1, 50, Request.Form("Email") ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param4", 202, 1, 50, Request.Form("UserName") ' adVarWChar
MM_editCmd.Parameters.Append MM_editCmd.CreateParameter("param5", 202, 1, 50, Request.Form("Password") ' adVarWChar
MM_editCmd.Execute
MM_editCmd.ActiveConnection.Close

' append the query string to the redirect URL
Dim MM_editRedirectUrl
MM_editRedirectUrl = "/robvazquez/registration/registration_complete.asp"
If (Request.QueryString <> "" Then
If (InStr(1, MM_editRedirectUrl, "?", vbTextCompare) = 0) Then
MM_editRedirectUrl = MM_editRedirectUrl & "?" & Request.QueryString
Else
MM_editRedirectUrl = MM_editRedirectUrl & "&" & Request.QueryString
End If
End If
Response.Redirect(MM_editRedirectUrl)
End If
End If
%>
<%
Dim rs_register
Dim rs_register_cmd
Dim rs_register_numRows

Set rs_register_cmd = Server.CreateObject ("ADODB.Command"
rs_register_cmd.ActiveConnection = MM_login_dbase_STRING
rs_register_cmd.CommandText = "SELECT * FROM db1"
rs_register_cmd.Prepared = true

Set rs_register = rs_register_cmd.Execute
rs_register_numRows = 0
%>
<html>
<head>
<meta name="keywords" content="Rob Vazquez, Kari Vazquez, Steven Vazquez, Melanie Vazquez">
<meta name="description" content="This site is dedicated to providing information about the family that is worthy, or at least decent enough, for public consumption. Its ongoing purpose will be to provide updated information on the family and provide the wife and kids with their own web pages, and perspectives. ">
<title>Rob Vazquez User Registration</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
@import url("../images/headermenu.css";

a:link { font-family: "Century Gothic"; color: #003366; font-weight: normal; background-position: center center}
a:visited { font-family: "Century Gothic"; color: #003366; font-weight: normal}
a:hover { font-family: "Century Gothic"; font-style: normal; font-weight: bold; color: #000000; list-style-image: none; list-style-type: none; clip: rect( )}
a:active { font-family: "Century Gothic"; font-weight: normal; text-transform: none}
body,td,th {
font-family: Century Gothic;
color: #003366;
}
.style1 {
font-family: "Comic Sans MS";
font-weight: bold;
font-size: 12px;
}
body {
margin-left: 0px;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 0px;
}
.style2 {font-size: 12px}
a {
font-family: Century Gothic;
}
.style5 {
font-family: "Comic Sans MS";
font-weight: bold;
}
.style7 {font-size: 16px}


-->
</style>
<script language="JavaScript1.2" type="text/javascript" src="../images/mm_css_menu.js"></script>
<script type="text/JavaScript">
<!--
function MM_findObj(n, d) { //v4.01
var p,i,x; if(!d) d=document; if((p=n.indexOf("?")>0&&parent.frames.length) {
d=parent.frames[n.substring(p+1)].document; n=n.substring(0,p);}
if(!(x=d[n])&&d.all) x=d.all[n]; for (i=0;!x&&i<d.forms.length;i++) x=d.forms[i][n];
for(i=0;!x&&d.layers&&i<d.layers.length;i++) x=MM_findObj(n,d.layers[i].document);
if(!x && d.getElementById) x=d.getElementById(n); return x;
}

function MM_validateForm() { //v4.0
var i,p,q,nm,test,num,min,max,errors='',args=MM_validateForm.arguments;
for (i=0; i<(args.length-2); i+=3) { test=args[i+2]; val=MM_findObj(args[i]);
if (val) { nm=val.name; if ((val=val.value)!="" {
if (test.indexOf('isEmail')!=-1) { p=val.indexOf('@');
if (p<1 || p==(val.length-1)) errors+='- '+nm+' must contain an e-mail address.\n';
} else if (test!='R') { num = parseFloat(val);
if (isNaN(val)) errors+='- '+nm+' must contain a number.\n';
if (test.indexOf('inRange') != -1) { p=test.indexOf(':');
min=test.substring(8,p); max=test.substring(p+1);
if (num<min || max<num) errors+='- '+nm+' must contain a number between '+min+' and '+max+'.\n';
} } } else if (test.charAt(0) == 'R') errors += '- '+nm+' is required.\n'; }
} if (errors) alert('The following error(s) occurred:\n'+errors);
document.MM_returnValue = (errors == '');
}
//-->
</script>
</head>

<body bgcolor="#FFFFFF">
<table width="1000" border="0" align="center">
<tr>
<td height="23" align="left" valign="top"><table width="5">
<tr>
<td> </td>
</tr>
</table></td>
<td height="23" colspan="3" align="left" valign="top"><img src="../images/home_headermenu.png" width="967" height="65" longdesc="www.robvazquez.com"></td>
<td width="14" height="23" align="left" valign="top"><table width="5">
<tr>
<td> </td>
</tr>
</table></td>
</tr>
<tr>
<td height="23" align="left" valign="top"> </td>
<td width="2" align="left" valign="top"> </td>
<td width="504" height="23" align="left" valign="top"> </td>
<td width="453" height="23" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"> </td>
</tr>
<tr>
<td height="23" align="left" valign="top"> </td>
<td height="23" colspan="4" align="left" valign="top"><div align="center"><span class="style5"><u>USER REGISTRATION</u></span> </div></td>
</tr>
<tr>
<td height="23" align="left" valign="top"> </td>
<td height="23" colspan="4" align="left" valign="top"> </td>
</tr>
<tr>
<td height="23" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"> </td>
<td height="23" colspan="2" align="left" valign="top"><span class="style7">Create a new User Account on robvazquez.com.  All fields must be completed for the registration process to be completed. </span></td>
<td height="23" align="left" valign="top"> </td>
</tr>

<tr>
<td height="248" align="left" valign="top"> </td>
<td height="248" colspan="4" align="center" valign="top"><table width="950">
<tr>
<td colspan="4" align="center"><form ACTION="<%=MM_editAction%>" METHOD="POST" name="loginform" id="loginform" onSubmit="MM_validateForm('FirstName','','R','Email','','RisEmail','UserName','','R');return document.MM_returnValue">
<table width="536">
<tr>
<td width="140">First Name:</td>
<td width="304"><input name="FirstName" type="text" id="FirstName" size="50"></td>
<td width="76"> </td>
</tr>
<tr>
<td>Last Name: </td>
<td><input name="LastName" type="text" id="LastName" size="50"></td>
<td> </td>
</tr>
<tr>
<td>Email:</td>
<td><input name="Email" type="text" id="Email" size="50"></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>User Name: </td>
<td><input name="UserName" type="text" id="UserName" size="50"></td>
<td> </td>
</tr>
<tr>
<td>Password:</td>
<td><input name="Password" type="password" id="Password" size="50"></td>
<td> </td>
</tr>
<tr>
<td>Verify Password: </td>
<td><input name="VerifyPassword" type="password" id="VerifyPassword" size="50"></td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td>
<div align="center">
<input name="Submit" type="submit" class="style1" value="Register">
</div></td>
<td> </td>
</tr>
</table>













<input type="hidden" name="MM_insert" value="loginform">
</form>
</td>
</tr>

</table></td>
</tr>

<tr>
<td height="23" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"> </td>
<td height="23" colspan="2" align="left" valign="top"><font face="Century Gothic"><span class="style7">If you have any questions, or difficulty creating an account, please send me an email at <a href="mailto: "> </a>.</span></font></td>
<td height="23" align="left" valign="top"> </td>
</tr>
<tr>
<td height="23" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"> </td>
<td height="23" colspan="2" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"> </td>
</tr>
<tr>
<td width="14" height="23" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"><p><br>
</p> </td>
<td height="23" colspan="2" align="left" valign="top"> </td>
<td height="23" align="left" valign="top"> </td>
</tr>

<tr bgcolor="#006699" align="center">
<td height="38" valign="middle" bgcolor="#FFFFFF"> </td>
<td height="38" colspan="3" valign="middle" bgcolor="#003366"><span class="style2"><font face="Lucida Sans" color="#CCCCCC">®
All trademarks are property of their respective holders at the time of this
publication. </font><font face="Lucida Sans" color="#666666"><br>
<font color="#CCCCCC">Copyright © 2004 by Rob Vazquez</font></font></span></td>
<td height="38" valign="middle" bgcolor="#FFFFFF"> </td>
</tr>
</table>
</body>
</html>
<%
rs_register.Close()
Set rs_register = Nothing
%>