Respons.Redirect for Duplicate Data in Access DB 1

[kefi2927]

Hi Hope someone can help me with this.
When I ask to redirect to another page if the data being entered via a particular form field "Centre" to the Access DB is duplicated it will only see the first record and redirect if that is duplicated. If the form field "Centre" data entered into the form is duplicated past the first record i get a "page cannot be displayed" with the message: "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship."

Code is:

<%@LANGUAGE=&quot;VBSCRIPT&quot; CODEPAGE=&quot;1252&quot;%>
<!-- #include file = &quot;../includes/LOGINNOW.ASP&quot; -->
<!--#include file=&quot;../includes/connection.asp&quot; -->
<%


' Declare variables
Dim Conn
Dim RS
Dim SQL
Dim strID



'create connection object
Set Conn=Server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open Openvar


'create recordset object
Set RS=Server.CreateObject(&quot;ADODB.RecordSet&quot

'set and assign structured query statement to variable
SQL=&quot;SELECT * FROM Centre&quot;


'opens the table to view all records and open and lock modes
RS.Open SQL, Conn, 3,3

%>

<%
If Request.Form(&quot;Centre&quot =RS(&quot;CentreName&quot Then
Response.Redirect &quot;duplicate_centredept.asp&quot;
End If
%>


<%
Thisday = CStr(Date) ' Get todays date.
%>

<%




'AddNew method of recordset to add a record
RS.AddNew

'input to recordset from form data
RS(&quot;CentreName&quot = Request.Form(&quot;Centre&quot
RS(&quot;Address&quot = Request.Form(&quot;Address&quot
RS(&quot;City&quot = Request.Form(&quot;City&quot
RS(&quot;Postcode&quot = Request.Form(&quot;Postcode&quot
RS(&quot;TelNo&quot = Request.Form(&quot;TelNo&quot
RS(&quot;FaxNo&quot = Request.Form(&quot;FaxNo&quot
RS.Update


'MoveLast gets the Centre ID number that is automatically generated by database
' A local variable is set so that I can write out the ID number from the database
RS.MoveLast
strID = RS(&quot;CentreName&quot

'Code for assigning variable to ASP date
thisDay = CStr(Date)
%>

Cheers for any help.

 

[mwolf00]

This line

<%
If Request.Form(&quot;Centre&quot =RS(&quot;CentreName&quot Then
Response.Redirect &quot;duplicate_centredept.asp&quot;
End If
%>

only looks at the first record in the recordset. To change this, you'd have to loop through the whole recordset first. I recommend replacing the above code with this (and placing it before you execute your recordset...)


SQL=&quot;SELECT CentreName FROM Centre where centreName = '&quot; & Request.Form(&quot;Centre&quot & &quot;'&quot;

RS.Open SQL, Conn, 3,3

If not RS.EOF then
Response.Redirect &quot;duplicate_centredept.asp&quot;
'you may want to use server.transfer here rather than response.redirect...
End If

'now load you recordset and handle it....

BTW - I recommend against the constant opening and closing of the server-side delimiters (<%..%>) - it just makes more work for the server...
Get the Best Answers! faq333-2924
Is this an asp FAQ? faq333-3048
Tek-Tips Best Practices: FAQ183-3179

 

[kefi2927]

Hey mwolf00
thanks for your help. I have modified it slightly.

SQL=&quot;SELECT CentreName FROM Centre where centreName = '&quot; & Request.Form(&quot;Centre&quot & &quot;'&quot;

RS.Open SQL, Conn, 3,3

If not RS.EOF then

loaded recordset and handled

End If

Also thank for the advice on constant opening and closing of the server-side delimiters (<%..%>)

Cheers