I have an application in which a user has to enter a purchase order number to write their order to the database. The logic is if a user has used a purchase order before, it returns them to the form and says, "That purchase order has already been used" and if it doesn't exist then write everything to the database. Very simply it compares the form data against the db which is set to VarChar(50).

Everything works great if the purchase order is numeric only (i.e - 12345), however, when it is alpha numeric (i.e - AB12345) or just alpha charcters (i.e - ABCDEF), it bypasses the conditional statement.

<%
IF (not rstComplete.BOF) and (not rstComplete.EOF) then
   IF rstComplete("purchaseOrder") = Request.Form("purchaseOrder") Then
   Response.Redirect "/automotiveapplication/shoppingCartApp.asp?poNumber=Exists"
   End IF
End IF
%>

Thank you.

Hi there,

How does your WHERE clause look like for the SQL statement you're passing to the database?

Can you post some more code? That helps in diagnosing the problem.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Here is the form:

<input type="text" name="purchaseOrder" class="regText"><br><br>
     <% IF Request.Querystring("poNumber") = "False" Then
             Response.Write "Please enter purchase order."
        ElseIF Request.Querystring("poNumber") = "Exists" Then
             Response.Write "The purchase order you entered has already been used for this" &_
                    " dealership. Please select another."
        Else
        End IF%>


     <br><br><input type="submit" name="Submit" value="Check Out" class="regText">

Here is the dbConnection<% Sub completeOrder

If Request.Form("purchaseOrder") = "" Then
        Response.Redirect "/automotiveapplication/shoppingCartApp.asp?poNumber=False"
    End IF

    Dim cnnComplete
    Dim rstComplete
    Dim strSQLCheckPO

    Set cnnComplete = Server.CreateObject("ADODB.Connection")

    cnnComplete.Open "Provider=SQLOLEDB;Data Source=XXX.XXX.XXX.XXX;" _
    & "Initial Catalog=XXXXXXXXX;User Id=XXXXXXXXX;Password=XXXXXXXX;" _
    & "Connect Timeout=15;Network Library=dbmssocn;"

        strSQLCheckPO = "SELECT purchaseOrder FROM autoAppProductInvoice WHERE dealershipID='" & Request.Cookies("dealershipID") & "'"

        Set rstComplete = cnnComplete.Execute (strSQLCheckPO)

        IF (not rstComplete.BOF) and (not rstComplete.EOF) then
            IF rstComplete("purchaseOrder") = Request.Form("purchaseOrder") Then
                Response.Redirect "/automotiveapplication/shoppingCartApp.asp?poNumber=Exists"
            End IF
        End IF

Thank you for your interest.

I am missing the part where you're passing purchaseOrder to the database.

Can each dealer just have one purchase order? The way you have set up things now, all you do is retrieve all purchase orders for a dealer (WHERE dealershipID='" & Request.Cookies("dealershipID") & "'")

Most likely, your recordset contains multiple records. Can it be a coincidence that when you pass a numeric value that the first record in the recordset matches that number, while the alpha numeric records are placed later in your recordset so the second nested If doesn't fire?

I think you'll need to extend your WHERE clause so it filters for the purchaseOrder as well.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

I apologize if my connections seem redundant and code is sloppy.

I didn't want to use seesion variables so I am using a table in SQL DB to write everything to and then transfer it over to a "Final table" when the user checks out.

To answer your question about the recordset, I don't think that is what is happening. Here is a screen shot of the table:

http://216.247.69.249/image/dbscreenshot.gif

Here is the code from teh if statement on -->

        IF (not rstComplete.BOF) and (not rstComplete.EOF) then
            IF rstComplete("purchaseOrder") = Request.Form("purchaseOrder") Then
                Response.Redirect "/automotiveapplication/shoppingCartApp.asp?poNumber=Exists"
            End IF
        End IF





'This part generates productInvoiceID in the autoAppProductInvoice table

Dim cnnCreateInvoice ' ADO connection
Dim rstCreateInvoice ' ADO recordset
Dim purchaseOrder

Set cnnCreateInvoice = Server.CreateObject("ADODB.Connection")

cnnCreateInvoice.Open "Provider=SQLOLEDB;Data Source=XXX.XXX.XXX.XXX;" _
    & "Initial Catalog=XXXXXXX;User Id=XXXXXX;Password=XXXXX;" _
    & "Connect Timeout=15;Network Library=dbmssocn;"


purchaseOrder = Request.Form("purchaseOrder")

SQLCreateInvoice = "INSERT INTO autoAppProductInvoice (clientID, dealershipID, purchaseOrder)" &_
                    " VALUES (" &_
        "'" & Request.Cookies("clientID") & "'," &_
        "'" & Request.Cookies("dealershipID") & "'," &_
        "'" & purchaseOrder & "')"


Set rstCreateInvoice = cnnCreateInvoice.Execute(SQLCreateInvoice)

Response.Write "SQLCreateInvoice = " & SQLCreateInvoice & "<br><br>"


'This part opens the productInvoiceID for writing to the final products table



Dim cnnWriteInvoiceID ' ADO connection
Dim rstWriteInvoiceID ' ADO recordset
Dim SQLWriteInvoiceID

Set cnnWriteInvoiceID = Server.CreateObject("ADODB.Connection")

cnnWriteInvoiceID.Open "Provider=SQLOLEDB;Data Source=XXX.XXX.XXX.XXX;" _
    & "Initial Catalog=XXXXXXX;User Id=XXXXXX;Password=XXXXX;" _
    & "Connect Timeout=15;Network Library=dbmssocn;"


SQLWriteInvoiceID = "Select * FROM autoAppProductInvoice WHERE purchaseOrder='" & purchaseOrder & "'"

Set rstWriteInvoiceID = cnnWriteInvoiceID.Execute(SQLWriteInvoiceID)

Response.Write "SQLWriteInvoiceID = " & SQLWriteInvoiceID & "<br><br>"

'This part writes all product inormation to the autoAppShoppingCartFinal table


Dim cnnAutomotiveApp5 ' ADO connection
Dim rstAutomotiveApp5 ' ADO recordset
Dim SQL

Set cnnAutomotiveApp5 = Server.CreateObject("ADODB.Connection")

cnnAutomotiveApp5.Open "Provider=SQLOLEDB;Data Source=XXX.XXX.XXX.XXX;" _
    & "Initial Catalog=XXXXXXX;User Id=XXXXXX;Password=XXXXX;" _
    & "Connect Timeout=15;Network Library=dbmssocn;"


    For I = 0 to 999

    getClientID = Request.Cookies("clientID")
    getDealershipID = Request.Cookies("dealershipID")

    If Request("productUpdate_" & I) <> "" Then
        SQL = "INSERT INTO autoAppShoppingCartFinal" &_
        " (productQty, productSKU, productName, productDesc, productSize, productColor, productLogo, productCost, purchaseOrder, orderDate, productInvoiceID, dealershipID, clientID)" &_
        " VALUES (" &_
        "'" & Request("productUpdate_" & I) & "'," &_
        "'" & Request("productSKU_" & I) & "'," &_
        "'" & Request("productName_" & I) & "'," &_
        "'" & Request("productDesc_" & I) & "'," &_
        "'" & Request("productSize_" & I) & "'," &_
        "'" & Request("productColor_" & I) &"'," &_
        "'" & Request("productLogo_" & I) & "'," &_
        "'" & Request("productCost_" & I) & "'," &_
        "'" & purchaseOrder & "'," &_
        "'" & Date & "'," &_
        "'" & rstWriteInvoiceID("productInvoiceID") & "'," &_
        "'" & getDealershipID & "'," &_
        "'" & getClientID & "')"

    Set rstAutomotiveApp5 = cnnAutomotiveApp5.Execute(SQL)

    Response.Write "SQL = " & SQL & "<br><br>"

    End If

    Next

    Call deleteAllProducts

Thanks.

How many records do you expect to be returned from this statement? The screenshot of your table seems to indicate that lots of records can be returned. So, the check in your nested If statement just looks at the first record. Is that your intention? Or is the screen shot from a different table then the one you mentioned in the WHERE clause.

Another thing that might cause a problem: VB Script is not case sensitive, but comparing string is. So "ABCD" = "abcd" will return false.

Your example indicate AB12345 while the screenshot of the database table has ab12345.....

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

IF (not rstComplete.BOF) and (not rstComplete.EOF) then
            IF rstComplete("purchaseOrder") = Request.Form("purchaseOrder") Then

I thought the above code ran through all the records. Please correct me if I am mistaken.

As far as the ab vs. AB, I know I need to add a UCase. I am in the process of doing error handling now and that’s when I ran across the issue we are discussing.


This part is not getting any information from the DB (all request Objects). I did this because I need to generate unique ID for the invoice number. If you are wondering about all the clientID and dealershipID = 1 in the screenshot, that’s because I have only been testing with one dealershipID.

SQLCreateInvoice = "INSERT INTO autoAppProductInvoice (clientID, dealershipID, purchaseOrder)" &_
                    " VALUES (" &_
        "'" & Request.Cookies("clientID") & "'," &_
        "'" & Request.Cookies("dealershipID") & "'," &_
        "'" & purchaseOrder & "')"


Set rstCreateInvoice = cnnCreateInvoice.Execute(SQLCreateInvoice)

Response.Write "SQLCreateInvoice = " & SQLCreateInvoice & "<br><br>"

You're mistaken ;)

All this code does, is check whether the Recordset contains one or more records. If it's not "End Of File" and not "Beginning Of File", the recordset contains at least one record. But the check in the second If block just checks the first record.

You need a loop to check each individual record in the recordset: This code loops through all the records in the recordset, and then compares the value for the purchaseOrder with the value from the form.

I am sure this confusion has caused the problem.....

HtH,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Thank you, Imar, it works like a charm now. Thank you so much for your help, now I can finish plowing through my book only to move on to ASP.NET.

Kudos!

Hi again,

Even if it works correctly, I suggest you do put in the WHERE clause for the purchaseOrder.

Suppose a specific dealer ID has 100 orders. If it's the last order you're after, you'll need to loop through 99 records to get the order you need. Besides this looping, you also need to retrieve those 100 records from the database. So you're wasting valuable bandwith and processing time. Here's the alternative: Using this code, at the most one record is retrieved. If the recordset is empty, there is no duplicate PurchaseOrder. Otherwise, you can do your redirect to shoppingCartApp.asp.

Cheers,

Imar


---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.