Set Variable From IF Statement

Hi have have the following code...

<%
intTmpOrderID = Request.QueryString("OrderID")

IF IsNull(intTmpOrderID) THEN
      intPurchaseOrderID = Request.Form("intPurchaseOrderID")
ELSE
      intPurchaseOrderID = intTmpOrderID
END IF

Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = " & intPurchaseOrderID
RS.Open SQL,Connection,3,3
%>
net-workxAsked:
Who is Participating?
 
peterxlaneCommented:
I would suggest going with something like this:

<%
Function GetPurchaseOrderID
      intTmpQSOrderID = Request.QueryString("OrderID")
      intTmpFormOrderID = Request.Form("OrderID")
      If intTmpQSOrderID <> "" Then
            intPurchaseOrderID = intTmpQSOrderID      
      Else
            intPurchaseOrderID = intTmpFormOrderID
      End If
      GetPurchaseOrderID = intPurchaseOrderID
End Function

If GetPurchaseOrderID <> "" Then
      Set RS = Server.CreateObject("ADODB.Recordset")
      SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = " & intPurchaseOrderID
      RS.Open SQL,Connection, 3, 3
Else
      Response.Write "Do Not Continue Processing"
End If
%>

That way you don't even open the recordset if you know that intPurchaseOrderID does not contain a value
0
 
net-workxAuthor Commented:
Sorry, submitted to early!!

it should also say that when i return the the page using the QueryString everything works.... but when i go to the page from the request.form it fails on:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'intOrderNumber ='.
/admin/includes/pages/retail/stock/check_expected_stock.asp, line 14

so its not assigning the value from request.form to the variable by the look at it so there is something wrong with the if statement....

Any ideas?

Cheers,
0
 
net-workxAuthor Commented:
The form is submitting on though as on the page cannot be displayed this is shown....

POST Data:
intPurchaseOrderID=116182&Submit=Recieve+This+Delivery

Thanks
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
peterxlaneCommented:
I am not understanding that last post...
0
 
net-workxAuthor Commented:
sorry to explaining that it is not a problem with the posting form as the whole error message i get is:

Technical Information (for support personnel)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'intOrderNumber ='.
/admin/includes/pages/retail/stock/check_expected_stock.asp, line 14


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)

Page:
POST 54 bytes to /admin/page.asp

POST Data:
intPurchaseOrderID=116182&Submit=Recieve+This+Delivery

Time:
13 April 2006, 21:04:11

SO I KNOW THE DATE IS BEING SENT TO THE PAGE OK!

SORRY FOR CONFUSION!!
0
 
peterxlaneCommented:
If the value in your database is not a number, then it would need to be enclosed in single quotes.

SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = '" & intPurchaseOrderID & "'"
0
 
net-workxAuthor Commented:
No its deffo a number......

Have replaced with your code but now it fails on this line...
*********************************************************************************************
<%
Set RSOrderDetails = Server.CreateObject("ADODB.Recordset")
SQLOrderDetails = "SELECT * FROM tblPurchaseOrdersProducts WHERE intPurchaseOrderNumber = " & intPurchaseOrderID
RSOrderDetails.Open SQLOrderDetails,Connection,3,3
%>
*********************************************************************************************

with the error....
*********************************************************************************************
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'intPurchaseOrderNumber='.
/admin/includes/pages/retail/stock/check_expected_stock.asp, line 44
*********************************************************************************************

hmmmm, the intPurchaseOrderID is set by your function so im stumped as to why it wont use it on the SQLOrderDetails statement?!!!!!

Carl
0
 
peterxlaneCommented:
Setting your page up like this could help you further track down the error and be able to have it display friendly error messages to the user when there are problems...

<%
Function GetPurchaseOrderID
     intTmpQSOrderID = Request.QueryString("OrderID")
     intTmpFormOrderID = Request.Form("OrderID")
     If intTmpQSOrderID <> "" Then
          intPurchaseOrderID = intTmpQSOrderID    
     Else
          intPurchaseOrderID = intTmpFormOrderID
     End If
     GetPurchaseOrderID = intPurchaseOrderID
End Function

Sub DisplayResults
      On Error Resume Next
      If GetPurchaseOrderID <> "" Then
           Set RS = Server.CreateObject("ADODB.Recordset")
           SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = '" & intPurchaseOrderID & "'"
           RS.Open SQL,Connection, 3, 3
           'The rest of your page here
      Else
           Response.Write "Do Not Continue Processing"
      End If
      If Err.number <> 0 Then
            Response.Write "ERROR: " & Err.Description
      End If
End Sub


Call DisplayResults
%>
0
 
peterxlaneCommented:
and what happens when you replace this:

SQLOrderDetails = "SELECT * FROM tblPurchaseOrdersProducts WHERE intPurchaseOrderNumber = " & intPurchaseOrderID


with:

SQLOrderDetails = "SELECT * FROM tblPurchaseOrdersProducts WHERE intPurchaseOrderNumber = '" & intPurchaseOrderID & "'"
0
 
net-workxAuthor Commented:
I have done what you said but it just says "do not continue processing" but with no error???????

Some reason it is now not keeping the intPurchaseOrderID

Thanks,
Carl
0
 
peterxlaneCommented:
Because intPurchaseOrderID is being populated by a function which is now in a subroutine, your other code must be in that subroutine to be able to get the value of intPurchaseOrderID.  Can you post the code of your entire page?  
0
 
net-workxAuthor Commented:
No Problem..... Here you go...

Have checked the DB to, all fields are numbers not text so that eliminates the ' ' marks!

Thanks,
Carl

<!--#include virtual="/admin/includes/connection_string.asp"-->

<%
Function GetPurchaseOrderID
     intTmpQSOrderID = Request.QueryString("OrderID")
     intTmpFormOrderID = Request.Form("OrderID")
     If intTmpQSOrderID <> "" Then
          intPurchaseOrderID = intTmpQSOrderID    
     Else
          intPurchaseOrderID = intTmpFormOrderID
     End If
     GetPurchaseOrderID = intPurchaseOrderID
End Function

Sub DisplayResults
     On Error Resume Next
     If GetPurchaseOrderID <> "" Then
          Set RS = Server.CreateObject("ADODB.Recordset")
          SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = " & intPurchaseOrderID
          RS.Open SQL,Connection, 3, 3
%>
<table border="0" width="100%" cellspacing="0" cellpadding="5">
      <tr>
            <td class="PageHeaderRetail">Check Delivery Contents</td>
      </tr>
      <tr>
            <td>
                  <form method="POST" action="/admin/includes/pages/retail/stock/processing/process_expected_delivery.asp">
                  <!--FORM ALIGNMENT TABLE-->
                  <table border="0" width="100%" cellspacing="0" cellpadding="2">
                        <tr>
                              <td class="SubPageHeaderRetail" width="100%" colspan="2" valign="top">Delivery Number: <% '=RS("intOrderNumber") %> - Order Date: <% '=RS("dtmDateStamp") %></td>
                        </tr>
                              <td width="100%" valign="top" colspan="2">Check Products Ordered Match Actual Stock Delivery</td>
                        </tr>
                        </tr>
                              <td width="100%" valign="top" colspan="2">
                              <!--STOCK LIST TABLE-->      
                              <%
                              Set RSOrderDetails = Server.CreateObject("ADODB.Recordset")
                              SQLOrderDetails = "SELECT * FROM tblPurchaseOrdersProducts WHERE intPurchaseOrderNumber = " & intPurchaseOrderID
                              RSOrderDetails.Open SQLOrderDetails,Connection,3,3
                              %>
                              
                              <table class="TableList" align="left" cellspacing="1" cellpadding="3">
                                    <tr class="TableHeadings">
                                          <td width="50" align="center">Order Line ID</td>
                                          <td width="350" align="left">Product Name</td>
                                          <td width="50" align="center">Expected Qty</td>
                                          <td width="50" align="center">Delete</td>
                                          <td width="80" align="center">Edit Expected Qty</td>
                                    </tr>
                                    <%
                                    intCount = 0
                                    Do While Not RSOrderDetails.EOF
                                    isEven = intCount MOD 2
                                          IF (isEven = 0) Then
                                                Response.Write("<tr class=""CellListBlue"">")
                                          ELSE
                                                Response.Write("<tr class=""CellListWhite"">")
                                          END IF
                                    %>
                                          <td width="50" align="center"><% =RSOrderDetails("ID") %>&nbsp;</td>
                                          <td width="350" align="left">
                                          <%
                                          Set RSProductName = Server.CreateObject("ADODB.Recordset")
                                          SQLProductName = "SELECT * FROM tblProducts WHERE intProductID =" & RSOrderDetails("intProductID")
                                          RSProductName.Open SQLProductName,Connection,3,3
                                          
                                                Response.Write RSProductName("txtProductName")

                                          RSProductName.Close
                                          Set RSProductName = Nothing
                                          %>&nbsp;
                                          </td>
                                          <td width="50" align="left"><% =RSOrderDetails("intOrderQty") %>&nbsp;</td>
                                          <td width="50" align="center"><a href="/admin/includes/pages/retail/stock/processing/process_delete_delivery_line.asp?ID=<% =RSOrderDetails("ID") %>" onclick="javascript: return confirm('Are you sure you want to delete this record?\n\n*** THIS WILL DELETE THE RECORD PERMANENTLY! ***');" title="Are You Sure You Wish To Delete?"><img border="0" src="/admin/images/trash_can.gif"></a>
                                          <td width="80" align="center"><a href="#">Change Qty</a></td>
                                          </td>
                                    </tr>
                                    <%
                                    RSOrderDetails.MoveNext
                                    intCount = intCount + 1
                                    Loop
                                    RSOrderDetails.Close
                                    Set RSOrderDetails = Nothing
                                    %>
                              </table>
                              <!--STOCK DELIVERY TABLE-->
                              </td>
                        </tr>
                        </tr>
                              <td width="30%" valign="top">&nbsp;</td>
                              <td width="70%">&nbsp;</td>
                        </tr>
                        <tr>
                              <td class="SubPageHeaderRetail" width="100%" colspan="2" valign="top">Complete Form</td>
                        </tr>
                        <tr>
                              <td width="100%" colspan="2" valign="top">
                                    <input type="reset" value="Reset" name="Reset">
                                    <input type="submit" value="Complete Delivery" name="Submit">
                              </td>
                        </tr>
                  </table>
                  <!--END FORM ALIGNMENT TABLE-->
                  </form>
            </td>
      </tr>
</table>
<%
     Else
          Response.Write "Do Not Continue Processing"
     End If
     If Err.number <> 0 Then
          Response.Write "ERROR: " & Err.Description
     End If
End Sub


Call DisplayResults
%>
0
 
peterxlaneCommented:
The fact that you were getting an error before putting the single quotes in and then they stopped would lead me to believe that for whatever reason, your SQL statements need those single quotes.  They should not cause a problem even if the field is a number, so there is no harm in putting them in.  Give me a minute and I will edit your code and post it.

0
 
peterxlaneCommented:
Give this a try.  I did spot one thing that I had missed in the code before which is now fixed.

<!--#include virtual="/admin/includes/connection_string.asp"-->

<%
Function GetPurchaseOrderID
     intTmpQSOrderID = Request.QueryString("OrderID")
     intTmpFormOrderID = Request.Form("OrderID")
     If intTmpQSOrderID <> "" Then
          intPurchaseOrderID = intTmpQSOrderID    
     Else
          intPurchaseOrderID = intTmpFormOrderID
     End If
     GetPurchaseOrderID = intPurchaseOrderID
End Function

Response.Write "intPurchaseOrderID: " & GetPurchaseOrderID & "<br />"

Sub DisplayResults
     On Error Resume Next
     intPurchaseOrderID = GetPurchaseOrderID
     If intPurchaseOrderID <> "" Then
          Set RS = Server.CreateObject("ADODB.Recordset")
          SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = '" & intPurchaseOrderID & "'"
          Response.Write "First SQL Statement: " & SQL & "<br />"
          RS.Open SQL,Connection, 3, 3
%>
<table border="0" width="100%" cellspacing="0" cellpadding="5">
     <tr>
          <td class="PageHeaderRetail">Check Delivery Contents</td>
     </tr>
     <tr>
          <td>
               <form method="POST" action="/admin/includes/pages/retail/stock/processing/process_expected_delivery.asp">
               <!--FORM ALIGNMENT TABLE-->
               <table border="0" width="100%" cellspacing="0" cellpadding="2">
                    <tr>
                         <td class="SubPageHeaderRetail" width="100%" colspan="2" valign="top">Delivery Number: <% '=RS("intOrderNumber") %> - Order Date: <% '=RS("dtmDateStamp") %></td>
                    </tr>
                         <td width="100%" valign="top" colspan="2">Check Products Ordered Match Actual Stock Delivery</td>
                    </tr>
                    </tr>
                         <td width="100%" valign="top" colspan="2">
                         <!--STOCK LIST TABLE-->    
                         <%
                         Set RSOrderDetails = Server.CreateObject("ADODB.Recordset")
                         SQLOrderDetails = "SELECT * FROM tblPurchaseOrdersProducts WHERE intPurchaseOrderNumber = '" & intPurchaseOrderID & "'"
                         Response.Write "Second SQL Statement: " & SQL & "<br />"
                         RSOrderDetails.Open SQLOrderDetails,Connection,3,3                        
                         %>
                         
                         <table class="TableList" align="left" cellspacing="1" cellpadding="3">
                              <tr class="TableHeadings">
                                   <td width="50" align="center">Order Line ID</td>
                                   <td width="350" align="left">Product Name</td>
                                   <td width="50" align="center">Expected Qty</td>
                                   <td width="50" align="center">Delete</td>
                                   <td width="80" align="center">Edit Expected Qty</td>
                              </tr>
                              <%
                              intCount = 0
                              Do While Not RSOrderDetails.EOF
                              isEven = intCount MOD 2
                                   IF (isEven = 0) Then
                                        Response.Write("<tr class=""CellListBlue"">")
                                   ELSE
                                        Response.Write("<tr class=""CellListWhite"">")
                                   END IF
                              %>
                                   <td width="50" align="center"><% =RSOrderDetails("ID") %>&nbsp;</td>
                                   <td width="350" align="left">
                                   <%
                                   Set RSProductName = Server.CreateObject("ADODB.Recordset")
                                   SQLProductName = "SELECT * FROM tblProducts WHERE intProductID = '" & RSOrderDetails("intProductID") & "'"
                                   Response.Write "Third SQL Statement: " & SQL & "<br />"
                                   RSProductName.Open SQLProductName,Connection,3,3
                                   
                                        Response.Write RSProductName("txtProductName")

                                   RSProductName.Close
                                   Set RSProductName = Nothing
                                   %>&nbsp;
                                   </td>
                                   <td width="50" align="left"><% =RSOrderDetails("intOrderQty") %>&nbsp;</td>
                                   <td width="50" align="center"><a href="/admin/includes/pages/retail/stock/processing/process_delete_delivery_line.asp?ID=<% =RSOrderDetails("ID") %>" onclick="javascript: return confirm('Are you sure you want to delete this record?\n\n*** THIS WILL DELETE THE RECORD PERMANENTLY! ***');" title="Are You Sure You Wish To Delete?"><img border="0" src="/admin/images/trash_can.gif"></a>
                                   <td width="80" align="center"><a href="#">Change Qty</a></td>
                                   </td>
                              </tr>
                              <%
                              RSOrderDetails.MoveNext
                              intCount = intCount + 1
                              Loop
                              RSOrderDetails.Close
                              Set RSOrderDetails = Nothing
                              %>
                         </table>
                         <!--STOCK DELIVERY TABLE-->
                         </td>
                    </tr>
                    </tr>
                         <td width="30%" valign="top">&nbsp;</td>
                         <td width="70%">&nbsp;</td>
                    </tr>
                    <tr>
                         <td class="SubPageHeaderRetail" width="100%" colspan="2" valign="top">Complete Form</td>
                    </tr>
                    <tr>
                         <td width="100%" colspan="2" valign="top">
                              <input type="reset" value="Reset" name="Reset">
                              <input type="submit" value="Complete Delivery" name="Submit">
                         </td>
                    </tr>
               </table>
               <!--END FORM ALIGNMENT TABLE-->
               </form>
          </td>
     </tr>
</table>
<%
     Else
          Response.Write "Do Not Continue Processing because intPurchaseOrderID is blank"
     End If
     If Err.number <> 0 Then
          Response.Write "ERROR: " & Err.Description
     End If
End Sub


Call DisplayResults
%>
0
 
net-workxAuthor Commented:
sorry for delay!!!

right have put the ammended code in.....

the page displays with...

intPurchaseOrderID:
Do Not Continue Processing because intPurchaseOrderID is blank

So variable is not getting populated still....

Any more ideas?

Thanks for all your help!

Carl
0
 
peterxlaneCommented:
Change this line:

Response.Write "intPurchaseOrderID: " & GetPurchaseOrderID & "<br />"

To:

Response.Write "intPurchaseOrderID: " & GetPurchaseOrderID & "<br />"
Response.Write "QueryString: " & Request.QueryString("OrderID") & "<br />"
Response.Write "Form: " & Request.Form("OrderID") & "<br />"

What do you get then?  Are your sure OrderID is the right field you are looking for?

0
 
net-workxAuthor Commented:
Hi if you go here....

http://carlwalker.zapto.org/admin/page.asp?PageID=RecordDelivery

You can see what i have already...

If you select an order number from the list and click on Recieve This Delivery you will see what happens....
0
 
peterxlaneCommented:
Change the function to this:

Function GetPurchaseOrderID
     intTmpQSOrderID = Request.QueryString("OrderID")
     'this next line is different
     intTmpFormOrderID = Request.Form("intPurchaseOrderID")
     If intTmpQSOrderID <> "" Then
          intPurchaseOrderID = intTmpQSOrderID    
     Else
          intPurchaseOrderID = intTmpFormOrderID
     End If
     GetPurchaseOrderID = intPurchaseOrderID
End Function


I didn't realize that the form value was different than if you were going to pass it from querystring...
0
 
peterxlaneCommented:
Well, I have to head home, so hopefully that is enough to resolve the problem.  Will check back in a little while...
0
 
AbulhallajCommented:
I checked your page at address in previous post. Get this advise from me always try to convert recived values from other pages to desired format. the page is correctly send FORM data but problem is in processing page try to use this code may solve problem.

intPurchaseOrderID = CLng(Request.Form(intPurchaseOrderID))

let me know if your problem solved
0
 
darkeryuCommented:
Hi:

try this

SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE [intOrderNumber] = " & intPurchaseOrderID

0
 
net-workxAuthor Commented:
Still says missing operator!

Damn thing!!  

Anyone else have any idea?
0
 
AbulhallajCommented:
I checked your page, this is your error:

===================
# Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'intOrderNumber ='.
/admin/includes/pages/retail/stock/check_expected_stock.asp, line 15

# POST Data:
intPurchaseOrderID=116180&Submit=Recieve+This+Delivery
===================

In this error intOrderNumber is equal null in your SQL code.
But the previuos page send the data correctly so you have

intPurchaseOrderID=116180

write this code at the top of your page and let me know the response

Response.Write(Request.From(intPurchaseOrderID))
Response.End

I am waiting for you.


0
 
net-workxAuthor Commented:
It now says:

Error Type:
Request object, ASP 0102 (0x80004005)
The function expects a string as input.
/admin/includes/pages/retail/stock/check_expected_stock.asp, line 4


Browser Type:
Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1)

Page:
POST 54 bytes to /admin/page.asp

POST Data:
intPurchaseOrderID=116180&Submit=Recieve+This+Delivery

- line 4 is:

Response.Write (Request.Form(intPurchaseOrderID))

Thanks
0
 
AbulhallajCommented:
Can you send this two page to my email address? I will check write the response!!!
0
 
AbulhallajCommented:
I mean check that asp's and I will write the response as soon as possible
0
 
net-workxAuthor Commented:
yes if you send me an email at carl at net-workx.co.uk i will reply with the files in a .zip format.

Thank you!

0
 
peterxlaneCommented:
Sorry, I had Friday off and wasn't able to check on this over the weekend. Line four would need to have quotes:

Response.Write (Request.Form("intPurchaseOrderID"))
0
 
net-workxAuthor Commented:
Ok it was going a bit mad with code and it was all over the place.... the IIWPG was hogging 100% of the CPU so that obviously wasnt going to work!

I have gone back to basics to get it to work so now i have...

<%
intPurchaseOrderID = Request.Form("intPurchaseOrderID")

Set RS = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM tblPurchaseOrdersHeader WHERE intOrderNumber = & intPurchaseOrderID
RS.Open SQL,Connection,3,3
%>

This works perfectly so i know this is not the problem....  The problem is i have the option to delete order lines from this page so i have a processing page which does a response.redirect "/page.asp?PageID=CheckExpectedStock?OrderID=12" etc....

Basically i need to see if i am returning to the page with a querystring as if i am, this value should take precedence over the request.form for which there will be no value anyway (as i am being directed to the page from ASP not posted to the page from a form) - if the querystring is not there then use the form value from the request.form

thanks
Carl

0
 
net-workxAuthor Commented:
I started off by getting it to work with the form value then doing:

intTmpPurchaseOrderID = Request.QueryString("ADODB.Recordset")

IF intTmpPurchaseOrderID = "" THEN
    Response.Write "No Value"
ELSE
    Response.Write intTmpPurchaseOrderID
END IF

intPurchaseOrderID = Request.Form("intPurchaseOrderID")
etc etc etc

This was printing No Value so i knew that the qualifier was = "" not <> "".

Then i changed it to say....

intTmpPurchaseOrderID = Request.QueryString("ADODB.Recordset")

IF intTmpPurchaseOrderID = "" THEN
    intPurchaseOrderID = Request.Form("intPurchaseOrderID")
ELSE
    intPurchaseOrderID = Request.QueryString("OrderID")
END IF

etc etc etc

this now works!!

I am planning on splitting the points between both of you as you have both helped me so much, let me know if this is ok by the both of you (peterxlane  & Abulhallaj)

If any of you have any improvements let me know, as soon as i get an ok from the both of you then i will split the points.

Cheers,
Carl


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.