Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 11 of 11
  1. #1
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Trying to duplicate check user input field

    We have a single table database, called invoices.

    Now the original programmer did not set invoice number as primary key, so users have been entering duplicate invoice numbers.

    I'm not familar with programming, but I have tried to insert a rule that tries to check if the invoice number exists, but the code doesn't work.

    Can someone help me out. thanks

    Code:
    <!--#include file ="adovbs.inc" -->
    <%	strConn = "dev_invoice"
    	Set objConn = Server.CreateObject("ADODB.Connection")
    	Set rsResults = Server.CreateObject("ADODB.Recordset")
    
    	objConn.Open strConn
    	
    	Set objConn2 = Server.CreateObject("ADODB.Connection")
           Set objrs = Server.CreateObject("ADODB.Recordset")
    	objConn2.Open objrs
    
    	supplier=Request.QueryString("supplier")
    	thedate=Request.QueryString("thedate")
    	invoicedate=Request.QueryString("invoicedate")
    	invoicenumber=Request.QueryString("invoicenumber")
    	invoiceamount=Request.QueryString("invoiceamount")
    	passedto=Request.QueryString("passedto")
    
    
    objrs = "SELECT * FROM invoice WHERE InvoiceNumber=invoicenumber"
    
    	
    	'Open new editable recordset
    	rsResults.Open "invoice", objConn, adOpenKeyset, adLockOptimistic, adCmdTable
    	
    	rsResults.AddNew
    	rsResults("supplier") = supplier
    	
    
    if thedate<>"" then
    	rsResults("date") = thedate
    	else
    	end if
    	
    
    if invoicedate<>"" then
    	rsResults("invoicedate") = invoicedate
    	else
    	end if
    
           
    
            If Not objrs.EOF Then
    
    	 Response.Write "That invoice number already exists in the database<hr>"
            else
            end if
    
    
    	rsResults("invoicenumber") = invoicenumber
    
    	if invoiceamount<>"" then
    
    	rsResults("amount") = invoiceamount
    	else
    	end if
    
    	rsResults("passedto") = passedto
    
    	rsResults("queried") = "No"
    
    	rsResults.Update
    	
    	'Get new id
    	strID = rsResults("ID")
    	
    	rsResults.Close
    	
    	
    	'Close and release recordset and connection
    	objConn.Close
    	Set rsResults = Nothing
    	Set objConn = Nothing
    	Response.redirect("add.asp?id=" & strid)
    	%>

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,565
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    So what do you want to do if the invoice number already exists?

    Just print an error message and quit? Or assign a new invoice number? Or what?

    Your code is badly broken, but even if it worked all you would do is display the error message but then continue on and add the record anyway!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thanks for your reply
    Yes, you are absolutely correct…. I managed to get the code to produce an error when duplicate was entered, however it was still creating the record. I couldn’t figure out why, though it was a problem with the condition or the recordset doing a lookup for all invoice numbers that match the form input. In the end I broke the code trying to figure this out.

    The ideal solution would be to throw an error message (Duplicate invoice number, please check ), on the page where the user entered the data into the form. But I think this is technically very difficult, as I have no idea how to pass the error back to that page , and display the error. I have attached the code for that page also (add.asp), so you see what I mean.

    If this is not possible, then just display an error on screen, and so the user can back and check.



    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>
    
    <head>
    <title>Add Invoice to Register</title>
    <link href="style.css" rel="stylesheet">
    </head>
    
    <body>
    <!--#include file="header.inc"-->
    <h2>Add a new Invoice</h2>
    <%If Request.QueryString("id")<>"" then
    Response.Write("<table border=""1"" bordercolor=""green"" cellpadding=""3"" cellspacing=""0""><tr><td><strong>R ref of the <font color=""red"">previous</font> R ref was <font color=""blue"">" & Request.QueryString("id") & "</font></strong></tr></td></table>")
    end if%>
    <form action="addupdate.asp">
      <table border="0" cellpadding="2" cellspacing="4"><tr><td colspan="2"><hr></td></tr>
        <tr>
          <td>Supplier</td>
          <td><input type="text" name="supplier" size="20"></td>
        </tr>
        <tr>
          <td>Date</td>
          <td><input type="text" name="thedate" size="20" value="<%Response.write(date)%>"></td>
        </tr>
        <tr>
          <td>Invoice Date</td>
          <td><input type="text" name="invoicedate" size="20"></td>
        </tr>
        <tr>
          <td>Invoice Number</td>
          <td><input type="text" name="invoicenumber" size="20"></td>
        </tr>
        <tr>
          <td>Invoice Amount</td>
          <td><input type="text" name="invoiceamount" size="20"></td>
        </tr>
        <tr>
          <td>Passed to</td>
          <td><input type="text" name="passedto" size="20"></td>
        </tr>
    <tr><td colspan="2"><hr></td></tr>
    
      </table>
      <p><input type="Submit" value="Submit"><input type="Reset" value="Reset"></p>
    </form>
    <!--#include file="footer.inc"-->
    </body>
    </html>

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,565
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Not too hard to do.

    The "trick" is to *NOT* send the code to another page when the form is first submitted. So change your <form> to *NOT* have any action=

    And then put the check code just ahead of the <form>.

    Something like this:
    Code:
    <body>
    <!--#include file="header.inc"-->
    <h2>Add a new Invoice</h2>
    <%
    If Request("POSTBACK") = "YES" Then
        Set conn = Server.CreateObject("ADODB.Connection")
        conn.Open "...your connection string..."
        SQL = "SELECT COUNT(*) FROM invoice WHERE InvoiceNumber=" & CLNG(Request("invoicenumber"))
        Set RS = conn.Execute( SQL )
        count = RS(0)
        RS.Close
        conn.Close
        If count = 0 Then
            ' number does not already exist...everything okay
            Server.Transfer "addupdate.asp" ' transfer to original target page
            ' note that when you transfer, all your Request variables transfer too!!
        End If
        Response.Write "<h3 style=""color: red;"">That invoice number already exists!</h3><br/>"
    End If
    <form>
    <input type="hidden" name="POSTBACK" value="YES"/>
    ...
          <td><input type="text" name="supplier" value="<%=Request("supplier")%>" size="20"></td>
          
    ...
          <td><input type="text" name="invoicedate" value="<%=Request("invoicedate")%>" size="20"></td>
    ... 
         similarly for the other form fields...copy the value form request into the field ...
    You see it?

    Now if the invoice exists, you won't leave this page. You'll give the message and you will use value="<%=Request("...")%>" to remember the value they gave the field, so they don't have to type it again.

    And this all works nicely because Server.Transfer carries along all the Request.QueryString, Request.Form, etc., values to the transferred-to page.

    You can use essentially this same technique to validate the form entries. That is, if there are any blank (or invalid fields) you can display a message, *NOT* transfer to the update page, and retain the prior values for convenience.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    unslog (10-27-2011)

  • #5
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi
    Many thanks for your great reply

    I'm receiving this error on this line;

    SQL = "SELECT COUNT(*) FROM invoice WHERE InvoiceNumber=" & CLNG(Request("invoicenumber"))



    Error Type:
    Microsoft VBScript runtime (0x800A000D)
    Type mismatch: 'CLNG'
    /dev_invoiceregister/add.asp, line 19


    Note, the invoicenumber field inthe access database is set as TEXT , because it is alphanumeric input. is this the cause of the error?

  • #6
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Managed to get help from another forum on the SQL statement, which appears to work

    SQL = "SELECT COUNT(*) FROM invoice WHERE InvoiceNumber= '"&Request("invoicenumber")&"'"

    Just need to add some validation to the form, so that some of the fields are manditory

    Can you advise my how I ensure Supplier field on the field is not blank when clicking submit, produce similar message on page e.g. Supplier must be entered.

    thank you

  • #7
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    On further testing, it doesn't appear to storing any new records , if the invoice number doesn't exist. Is it becuase of where i positioned the if statement?

    Code:
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    <html>
    <head>
    <title>Add Invoice to Register</title>
    <link href="style.css" rel="stylesheet">
    </head>
    <body>
    <!--#include file="header.inc"-->
        <h2>Add a new Invoice</h2>
        <%
    If Request("POSTBACK") = "YES" Then
        Set conn = Server.CreateObject("ADODB.Connection")
        
    	conn.Open "invoice"
    
    
    SQL = "SELECT COUNT(*) FROM invoice WHERE InvoiceNumber= '"&Request("invoicenumber")&"'" 
    
    
    
    
        Set RS=conn.Execute(SQL)
        count = RS(0)
        RS.Close
        conn.Close
        If count = 0 Then
    	
            ' number does not already exist...everything okay
            Server.Transfer "addupdate.asp" ' transfer to original target page
            ' note that when you transfer, all your Request variables transfer too!!
        End If
        Response.Write "<h3 style=""color: red;""> invoice number '"&Request("invoicenumber")&"' already exists!</h3><br/>"
    End If
    
    
    
    
    
    If Request.QueryString("id")<>"" then
    Response.Write("<table border=""1"" bordercolor=""green"" cellpadding=""3"" cellspacing=""0""><tr><td><strong>R ref of the <font color=""red"">previous</font> invoice was <font color=""blue"">" & Request.QueryString("id") & "</font></strong></tr></td></table>")
    end if%>
        <form>
          <input type="hidden" name="POSTBACK" value="YES"/>
          <table border="0" cellpadding="2" cellspacing="4">
            <tr>
              <td colspan="2"><hr></td>
            </tr>
            <tr>
              <td>Supplier</td>
              <td><input type="text" name="supplier" size="20"></td>
            </tr>
            <tr>
              <td>Date</td>
              <td><input type="text" name="thedate" size="20" value="<%Response.write(date)%>"></td>
            </tr>
            <tr>
              <td>Invoice Date</td>
              <td><input type="text" name="invoicedate" size="20"></td>
            </tr>
            <tr>
              <td>Invoice Number</td>
              <td><input type="text" name="invoicenumber" size="20"></td>
            </tr>
            <tr>
              <td>Invoice Amount</td>
              <td><input type="text" name="invoiceamount" size="20"></td>
            </tr>
            <tr>
              <td>Passed to</td>
              <td><input type="text" name="passedto" size="20"></td>
            </tr>
            <tr>
              <td colspan="2"><hr></td>
            </tr>
          </table>
          <p>
            <input type="Submit" value="Submit">
            <input type="Reset" value="Reset">
          </p>
        </form>
        <!--#include file="footer.inc"-->
    </body>
    </html>

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,565
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    Looks right to me. Are you sure you are *getting* to the "addupdate.asp" page?

    Put a tiny bit of debugging on that page to be sure.

    Maybe do this at the top of the page:
    Code:
    <%
    Response.Write "Dumping query string values:<br/>" & vbNewLine
    For Each name In Request.QueryString
        Response.Write name & ": " & Request(name) & "<br/>" & vbNewLine
    Next
    Response.Write "<hr>"
    ....
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Here is the dumped data when the invoice number doesn't exist, and should be writing the form data to the database

    Dumping query string values:
    id: 119206

    Here is the dumped data when the invoice number exists , and returns warning to form page

    Dumping query string values:
    POSTBACK: YES
    supplier: Oakland
    invoiceamount:
    thedate: 27/10/2011
    invoicedate:
    invoicenumber: UNS_OS_0088
    passedto:

  • #10
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,565
    Thanks
    80
    Thanked 4,620 Times in 4,583 Posts
    ??? Which page did you put that code in?

    Looks to me like you put it in the <form> page, not the "addupdate.asp" page.

    And where does id come from? You don't even have a form field by that name!

    In any case, note that the dump is *NOT* showing all the values it should. Where is the value for invoiceamount/invoicedate.

    Something seems really wrong.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #11
    New Coder
    Join Date
    May 2010
    Posts
    10
    Thanks
    2
    Thanked 0 Times in 0 Posts
    it is working. It was my stupid mistake, i pointed to the wrong database.

    I should have pointed to dev_invoice , instead I was pointing to invoice.

    sorry


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •