Generate an error if number is repeated in a form

Question

I have a web form for admin purposes where the user can change the order that a group of records is shown on a webpage.

For example: A table (tblStuff) in a database has three fields:

ContentID, Content, RecordPosition

The table has, say, four records:

1, Guess what, 1
2, More stuff, 2
3, Some stuff, 3
4, That's right, 4

The SQL code is:

SELECT * FROM tblStuff ORDER BY RecordPosition ASC

The user can use the form to change the RecordPosition number so that the order can read:

3, Some stuff, 1
2, More stuff, 2
1, Guess what, 3
4, That's right, 4

So... How can I validate the form so that the same number isn't entered twice into the RecordPosition field?

Hope this makes sense.

Here's the whole page

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include virtual="/Connections/ENG.asp" -->
<%
' *** Restrict Access To Page: Grant or deny access to this page
MM_authorizedUsers=""
MM_authFailedURL="../default.asp"
MM_grantAccess=false
If Session("MM_Username") <> "" Then
  If (true Or CStr(Session("MM_UserAuthorization"))="") Or _
         (InStr(1,MM_authorizedUsers,Session("MM_UserAuthorization"))>=1) Then
    MM_grantAccess = true
  End If
End If
If Not MM_grantAccess Then
  MM_qsChar = "?"
  If (InStr(1,MM_authFailedURL,"?") >= 1) Then MM_qsChar = "&"
  MM_referrer = Request.ServerVariables("URL")
  if (Len(Request.QueryString()) > 0) Then MM_referrer = MM_referrer & "?" & Request.QueryString()
  MM_authFailedURL = MM_authFailedURL & MM_qsChar & "accessdenied=" & Server.URLEncode(MM_referrer)
  Response.Redirect(MM_authFailedURL)
End If
%>
<%
If Request.Form("action")="update" Then
  'Set variables for update
  Dim updateSQL, i
  Dim cContentID, cPositionNumber

'Loop through records on screen and update
For i = 1 To fFormat(Request.Form("counter")) 

'Create the proper field names to reference on the form
cContentID = "ContentID" & CStr(i)
cPositionNumber = "PositionNumber" & CStr(i)

'Create the update sql statement
updateSQL = "UPDATE tblContent SET PositionNumber=" & fFormat(Request.Form(cPositionNumber)) & " WHERE ContentID=" & fFormat(Request.Form(cContentID))

'Run the sql statement
Call sRunSQL(updateSQL)
Next

'Refresh page
Response.Redirect("record-order-modify-updated.asp")
End If

Function fFormat(vText)
  fFormat = Replace(vText, "'", "''")
End Function

Sub sRunSQL(vSQL)
  set cExecute = Server.CreateObject("ADODB.Command")
  With cExecute
    .ActiveConnection = MM_ENG_STRING
    .CommandText = vSQL
    .CommandType = 1
    .CommandTimeout = 0
    .Prepared = true
    .Execute()
  End With
End Sub
%>
<%
Dim rsCharityDetails
Dim rsCharityDetails_cmd
Dim rsCharityDetails_numRows

Set rsCharityDetails_cmd = Server.CreateObject ("ADODB.Command")
rsCharityDetails_cmd.ActiveConnection = MM_ENG_STRING
rsCharityDetails_cmd.CommandText = "SELECT * FROM tblCharityDetails" 
rsCharityDetails_cmd.Prepared = true

Set rsCharityDetails = rsCharityDetails_cmd.Execute
rsCharityDetails_numRows = 0
%>
<%
Dim rsNavBar
Dim rsNavBar_cmd
Dim rsNavBar_numRows

Set rsNavBar_cmd = Server.CreateObject ("ADODB.Command")
rsNavBar_cmd.ActiveConnection = MM_ENG_STRING
rsNavBar_cmd.CommandText = "SELECT * FROM tblMainMenu WHERE MainMenuID <6  OR MainMenuID >7" 
rsNavBar_cmd.Prepared = true

Set rsNavBar = rsNavBar_cmd.Execute
rsNavBar_numRows = 0
%>
<%
Dim rsContent__smID
rsContent__smID = "1"
If (Request.QueryString("smID")   <> "") Then 
  rsContent__smID = Request.QueryString("smID")  
End If
%>
<%
Dim rsContent
Dim rsContent_cmd
Dim rsContent_numRows

Set rsContent_cmd = Server.CreateObject ("ADODB.Command")
rsContent_cmd.ActiveConnection = MM_ENG_STRING
rsContent_cmd.CommandText = "SELECT tblContent.*, tblMainMenu.MainMenuName, tblSubMenu.SubMenuName, tblSubMenu.SubMenuID FROM (tblContent LEFT JOIN tblMainMenu ON tblContent.MainMenuID = tblMainMenu.MainMenuID) LEFT JOIN tblSubMenu ON tblContent.SubMenuID = tblSubMenu.SubMenuID WHERE tblContent.SubMenuID = ? AND tblContent.DisplayRecord =1 ORDER BY tblContent.PositionNumber" 
rsContent_cmd.Prepared = true
rsContent_cmd.Parameters.Append rsContent_cmd.CreateParameter("param1", 5, 1, -1, rsContent__smID) ' adDouble

Set rsContent = rsContent_cmd.Execute
rsContent_numRows = 0
%>
<%
Dim rsMenuList
Dim rsMenuList_cmd
Dim rsMenuList_numRows

Set rsMenuList_cmd = Server.CreateObject ("ADODB.Command")
rsMenuList_cmd.ActiveConnection = MM_ENG_STRING
rsMenuList_cmd.CommandText = "SELECT tblMainMenu.MainMenuID, tblMainMenu.MainMenuName, tblSubMenu.SubMenuID, tblSubMenu.SubMenuName FROM tblMainMenu INNER JOIN tblSubMenu ON tblMainMenu.MainMenuID = tblSubMenu.MainMenuID WHERE tblSubMenu.SubMenuID <> 6 AND tblSubMenu.SubMenuID <16 OR tblSubMenu.SubMenuID >19" 
rsMenuList_cmd.Prepared = true

Set rsMenuList = rsMenuList_cmd.Execute
rsMenuList_numRows = 0
%>
<%
Dim rsHeaderImage
Dim rsHeaderImage_cmd
Dim rsHeaderImage_numRows

Set rsHeaderImage_cmd = Server.CreateObject ("ADODB.Command")
rsHeaderImage_cmd.ActiveConnection = MM_ENG_STRING
rsHeaderImage_cmd.CommandText = "SELECT MainMenuImage, MainMenuID FROM tblMainMenu" 
rsHeaderImage_cmd.Prepared = true

Set rsHeaderImage = rsHeaderImage_cmd.Execute
rsHeaderImage_numRows = 0
%>
<%
Dim navBar__numRows
Dim navBar__index

navBar__numRows = -1
navBar__index = 0
rsNavBar_numRows = rsNavBar_numRows + navBar__numRows
%>
<%
Dim rptContent__numRows
Dim rptContent__index

rptContent__numRows = -1
rptContent__index = 0
rsContent_numRows = rsContent_numRows + rptContent__numRows
%>
<%
Dim Repeat_MenuList__numRows
Dim Repeat_MenuList__index

Repeat_MenuList__numRows = -1
Repeat_MenuList__index = 0
rsMenuList_numRows = rsMenuList_numRows + Repeat_MenuList__numRows
%>
<!DOCTYPE HTML>
<html>
<head>
<meta charset="iso-8859-1">

<!-- disable iPhone inital scale -->
<meta name="viewport" content="width=device-width; initial-scale=1.0">

<title><%=(rsCharityDetails.Fields.Item("CharityName").Value)%> | English Website Administration</title>
<!-- main css -->
<link href="../../scripts/mfm-standard-stylesheet.css" rel="stylesheet" type="text/css">

<!--[if lt IE 9]>
<link href="../scripts/mfm-standard-stylesheet_ie.css" rel="stylesheet" type="text/css">
<![endif]-->

<!-- Admin css -->
<link href="../scripts/mfm-admin-stylesheet.css" rel="stylesheet" type="text/css">

<script src="../../scripts/jquery-1.7.2.min.js"></script>
<!-- jQuery NailThumb Plugin - any image to any thumbnail Examples and documentation at: http://www.garralab.com/nailthumb.php -->
<script src="../../scripts/jquery.nailthumb.1.1.js"></script>
<!-- Lightbox2 v2.51 by Lokesh Dhakar For more information, visit: http://lokeshdhakar.com/projects/lightbox2/ -->
<script src="../../scripts/lightbox.js"></script>
<!-- Lightbox css -->
<link href="../../scripts/lightbox.css" rel="stylesheet" type="text/css" media="screen" />
<script src="../tiny_mce/tiny_mce.js"></script>
<script src="../tiny_mce/tiny-mce-mfm.js"></script>

<!-- html5.js for IE less than 9 -->
<!--[if lt IE 9]>
<script src="http://html5shim.googlecode.com/svn/trunk/html5.js"></script>
<![endif]-->

<!-- css3-mediaqueries.js for IE less than 9 -->
<!--[if lt IE 9]>
<script src="http://css3-mediaqueries-js.googlecode.com/svn/trunk/css3-mediaqueries.js"></script>
<![endif]-->

</head>

<body id="other">

<div id="wrapper">
<header class="innerWidth">
<!--#include file="includes/header-modify-record.asp" -->
</header>

<nav class="innerWidth">
<!--#include file="includes/navbar-modify-record.asp" -->
</nav>

<!-- pageContent -->
<div id="content" class="innerWidth">

<!-- Aside -->
<aside>

<h3>Record Order</h3>

<ul>
<% 
Dim txtOldHeading
txtOldHeading = ""
    While ((Repeat_MenuList__numRows <> 0) AND (NOT rsMenuList.EOF)) 
If txtOldHeading = rsMenuList.Fields.Item("MainMenuName").Value Then
Else
txtOldHeading = rsMenuList.Fields.Item("MainMenuName").Value
%>
<li class="menuHeading"><%=(rsMenuList.Fields.Item("MainMenuName").Value)%></li>
<%
END IF
%>
 <li class="menuList">
 <% If (rsMenuList.Fields.Item("SubMenuID").Value) = "3" Then %>
     <a href="our-development-partners-record-order-modify.asp"><%=(rsMenuList.Fields.Item("SubMenuName").Value)%></a>
 <% ElseIf (rsMenuList.Fields.Item("SubMenuID").Value) = "15" Then %>
     <a href="shop-record-order-modify.asp"><%=(rsMenuList.Fields.Item("SubMenuName").Value)%></a>
 <% ElseIf (rsMenuList.Fields.Item("SubMenuID").Value) = "20" Then %>
     <a href="friends-of-mfm-record-order-modify.asp"><%=(rsMenuList.Fields.Item("SubMenuName").Value)%></a>
 <% Else %>
     <a href="record-order-modify.asp?smID=<%=(rsMenuList.Fields.Item("SubMenuID").Value)%>"><%=(rsMenuList.Fields.Item("SubMenuName").Value)%></a>
     <% End If %>
 </li>
 <% 
 Repeat_MenuList__index=Repeat_MenuList__index+1
 Repeat_MenuList__numRows=Repeat_MenuList__numRows-1
 rsMenuList.MoveNext()
Wend
%>
</ul>
</aside>
<!-- /Aside -->

<!-- Article -->
<article>
<% IF Request.ServerVariables("QUERY_STRING") <> "" THEN %>
<h3><span style="font-size:small">Order/Re-order records for: </span><%=(rsContent.Fields.Item("SubMenuName").Value)%></h3>
<% 
Dim counter
While ((rptContent__numRows <> 0) AND (NOT rsContent.EOF)) 
counter = counter + 1
%>
<form action="record-order-modify.asp" method="post" class="recordPosition">
  <table width="100%">
    <tr>
      <td align="left" valign="top" name="ContentTitle" colspan="2"><h2><%=(rsContent.Fields.Item("ContentTitle").Value)%></h2><input type="hidden" value="<%=(rsContent.Fields.Item("ContentID").Value)%>" name="ContentID<%=counter%>"></td>
      </tr>
    <tr>
      <td align="left" valign="top" name="ContentData"><%
    Dim tmp
    tmp = rsContent.Fields.Item("ContentData").Value
    %>
        <% =LEFT(tmp, INSTR((tmp & "."), ".")) %>..
       </td>
      <% IF (IsNull(rsContent.Fields.Item("ContentImage").Value)) THEN %>
      <td width="140" align="center" valign="top" name="ContentImage"><img src="../images/system_images/red-x.png"></td>
      <% ELSE %>
      <td width="140" align="center" valign="top" name="ContentImage"><div class="nailthumb-container">
        <!-- Thumbnail Container -->
        <img src="<%=(rsContent.Fields.Item("ContentImage").Value)%>"> </div></td>
      <% END IF %>
      </tr>
    <tr>
      <td align="left"><label>Current Record Position: <small class="brown" style="text-transform:none">(You may change it here)</small></label>&nbsp;&nbsp;<input type="text" name="PositionNumber<%=counter%>" tabindex="<%=counter%>" value="<%=(rsContent.Fields.Item("PositionNumber").Value)%>"></td>
      </tr>
  </table>
  <hr>
  <% 
  rptContent__index=rptContent__index+1
  rptContent__numRows=rptContent__numRows-1
  rsContent.MoveNext()
Wend
%>
<table align="center" class="positionButtons">
<tr>
<td width="50%" align="right"><input name="Submit" type="submit" value="Update Positions" tabindex="<%=counter%>"></td>
<td width="50%" align="left"><input name="Reset" type="reset" value="Reset All Changes" tabindex="<%=counter%>"></td>
</tr>
</table>
<input type="hidden" name="action" value="update">
<input type="hidden" name="counter" value="<%=counter%>">
</form>
<% ELSE %>
<h3>Select a listing to order/re-order using the list on the left.</h3>
<% END IF %>
</article>
<!-- /Article -->

<script type="text/javascript">
    jQuery(document).ready(function() {
        jQuery('.nailthumb-container').nailthumb({width:125,height:125,fitDirection:'top center'});
    });
</script>

</div>
<!-- /pageContent -->

<div class="push"></div>
</div>
<!-- #wrapper -->

<footer class="innerWidth">
<!--#include file="includes/footer.asp" -->
</footer>

</body>
</html>
<%
rsCharityDetails.Close()
Set rsCharityDetails = Nothing
%>
<%
rsNavBar.Close()
Set rsNavBar = Nothing
%>
<%
rsContent.Close()
Set rsContent = Nothing
%>
<%
rsMenuList.Close()
Set rsMenuList = Nothing
%>
<%
rsHeaderImage.Close()
Set rsHeaderImage = Nothing
%>

As requested by Allende, here's the generated form code.

<form action="record-order-modify.asp" method="post" class="recordPosition">

  <table width="100%">
    <tr>
      <td align="left" valign="top" name="ContentTitle" colspan="2"><h2>Investing in people and the environment</h2><input type="hidden" value="15" name="ContentID1"></td>
      </tr>
    <tr>
      <td align="left" valign="top" name="ContentData"><p>Madagascar is an environmental hotspot...
       </td>

      <td width="140" align="center" valign="top" name="ContentImage"><div class="nailthumb-container">
        <!-- Thumbnail Container -->
        <img src="/images/framed-images/mfm-website-(26).jpg"> </div></td>

      </tr>
    <tr>
      <td align="left"><label>Current Record Position: <small class="brown" style="text-transform:none">(You may change it here)</small></label>&nbsp;&nbsp;<input type="text" name="PositionNumber1" tabindex="1" value="1"></td>
      </tr>
  </table>
  <hr>

  <table width="100%">
    <tr>
      <td align="left" valign="top" name="ContentTitle" colspan="2"><h2>The next generation</h2><input type="hidden" value="16" name="ContentID2"></td>
      </tr>
    <tr>
      <td align="left" valign="top" name="ContentData"><p>Teaching Malagasy children to respect and nurture their environment is critical to the survival of Madagascar's biodiversity...
       </td>

      <td width="140" align="center" valign="top" name="ContentImage"><div class="nailthumb-container">
        <!-- Thumbnail Container -->
        <img src="/images/framed-images/mfm-website-(292).jpg"> </div></td>

      </tr>
    <tr>
      <td align="left"><label>Current Record Position: <small class="brown" style="text-transform:none">(You may change it here)</small></label>&nbsp;&nbsp;<input type="text" name="PositionNumber2" tabindex="2" value="2"></td>
      </tr>
  </table>
  <hr>

  <table width="100%">
    <tr>
      <td align="left" valign="top" name="ContentTitle" colspan="2"><h2>Recognition for our work</h2><input type="hidden" value="17" name="ContentID3"></td>
      </tr>
    <tr>
      <td align="left" valign="top" name="ContentData"><p>Our work over 2 decades with 73 villages surrounding the Reserve of Betampona recently gained recognition at an international conference held at the University of East Anglia...
       </td>

      <td width="140" align="center" valign="top" name="ContentImage"><div class="nailthumb-container">
        <!-- Thumbnail Container -->
        <img src="/images/framed-images/mfm-website-(56).jpg"> </div></td>

      </tr>
    <tr>
      <td align="left"><label>Current Record Position: <small class="brown" style="text-transform:none">(You may change it here)</small></label>&nbsp;&nbsp;<input type="text" name="PositionNumber3" tabindex="3" value="3"></td>
      </tr>
  </table>
  <hr>

  <table width="100%">
    <tr>
      <td align="left" valign="top" name="ContentTitle" colspan="2"><h2>Adding value by adding forests</h2><input type="hidden" value="18" name="ContentID4"></td>
      </tr>
    <tr>
      <td align="left" valign="top" name="ContentData"><p>Often the best way to protect an old forest is to plant a new one...
       </td>

      <td width="140" align="center" valign="top" name="ContentImage"><div class="nailthumb-container">
        <!-- Thumbnail Container -->
        <img src="/images/framed-images/mfm-website-(217).jpg"> </div></td>

      </tr>
    <tr>
      <td align="left"><label>Current Record Position: <small class="brown" style="text-transform:none">(You may change it here)</small></label>&nbsp;&nbsp;<input type="text" name="PositionNumber4" tabindex="4" value="4"></td>
      </tr>
  </table>
  <hr>

<table align="center" class="positionButtons">
<tr>
<td width="50%" align="right"><input name="Submit" type="submit" value="Update Positions" tabindex="4"></td>
<td width="50%" align="left"><input name="Reset" type="reset" value="Reset All Changes" tabindex="4"></td>
</tr>
</table>
<input type="hidden" name="action" value="update">
<input type="hidden" name="counter" value="4">
</form>

Answer 1

If you want to prevent duplicate values in RecordPosition no matter how you insert/update them you can create a unique constraint this column

CREATE UNIQUE INDEX uq_idx_RecordPosition ON tblStuff(RecordPosition);

Here is SQLFiddle demo

Answer 2

If you're trying to do some client-side validation, you'd have to build an array that contains all the RecordPosition values.

Once you have that, you can check the array for duplicates. This has been asked a couple of times on SO: Easiest way to find duplicate values in a JavaScript array

Unfortunately I can't help any more than that because you don't include any code that shows how this is structured on your web page

Answer 3

Check before inserting data in to data base

ex: recordposition value 3 --> 1 then pass value 1

SELECT * FROM tblStuff Where RecordPosition=1

if record exist then give message to user this position is exist

Answer 4

Let's suppose you have form like this (notice all the inputs has the same class):

<form id="myForm" method="POST" action"someUrl">
    <input type="text" class="recordPosition"></input>
    <input type="text" class="recordPosition"></input>
    <input type="text" class="recordPosition"></input>
    <input type="text" class="recordPosition"></input>
</form>

You could do with jQuery something like this:

 $(document).ready(function(){

 $(".recordPosition").on("blur", function(){
     var allFieldsForOrder = $('.recordPosition');
     var count = 0;
     var i=0
    //console.log(allFieldsForOrder.length );
      while((i<allFieldsForOrder.length) && (count < 2)){
             if ($(allFieldsForOrder[i]).val()===$(this).val())
            {
             count++                 
            }
            i++;
     } 

     if (count==2){
        alert("A duplicated value"); return false;}
    }); 
 });

For the html you posted you can use this: Notice I don't store the position of the duplicated the value.

$(document).ready(function(){
    //console.log($("input[type='text'][name^='PositionNumber'").length);
    $("input[type='text'][name^='PositionNumber'").each(function(){
         $(this).on("blur", function(){

         var allFieldsForOrder = $("input[type='text'][name^='PositionNumber'");
         var count = 0;
         var i=0

         while((i<allFieldsForOrder.length) && (count < 2)){
                if ($(allFieldsForOrder[i]).val()===$(this).val())
                {
                 count++                 
                }
                i++;
         }

         if (count==2){
            alert("A duplicated value"); 
         }

       });
    });
  });

For the code above we assume you want to check for all the fields where the attribute name starts with the string "PositionNumber"

I will try to reduce the code later, I think there's a shortest way to check if is duplicated the "RecordPosition" value, but need to test some ideas.

This will be your solution (one of them):

  $(document).ready(function(){


    $('form').on("submit",function(){
            var tempArray=[];
            $("input[type='text'][name^='PositionNumber'").each(function(){
                tempArray.push($(this).val());
            });

            var i=0;
            var duplicated=false;
            var currentElement;
            while((tempArray.length >= 0) && (duplicated==false)){                  
                //pop it out from the array
               currentElement = tempArray.pop();
               duplicated = tempArray.indexOf(currentElement)          

            }

            //after you can use "duplicated" to cancel the submit
            if (duplicated){                    
                alert("duplicated value:" + currentElement);
                return false;
            }

    });
  });

I shorter version:

$(document).ready(function(){           
 $('form').on("submit",function(){
         var tempArray=[];            
        var exists=0;
         $("input[type='text'][name^='PositionNumber'").each(function(){
             exists = tempArray.indexOf($(this).val());             
             if (exists>=0){                    
                 return false;//break the loop
             }
             tempArray.push($(this).val());                  
         });

         //after you can use "exist" to check if duplicated and retrieve the value to cancel the submit
         if (exists>=0){                   
             alert("duplicated value:" + tempArray[exists]);                
         } else{
             alert("no duplicated value:");
         }

        return false;            
 }); 
});