Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Create an If Then statement and insert one of two values into a database field

Posted on 2013-11-06
1
Medium Priority
?
422 Views
Last Modified: 2013-11-06
I have a review page where a user, after making a selection in the previous page, is redirected to the page below to reject a submission. If they select Yes, a drop-down box appears that has a list of standard rejections. However, if none of the standard rejections meet the criteria, the person can select the last option.  The last option displays a text field to allow a person to type a rejection reason. The code is below for your review.

I need to create an If Then statement, beginning in line 35. If someone makes the last selection in the drop-down menu (RejectedExp), then the text field (RejectedText) appears. If text field (RejectedText) is populated, then this information inserts into the RejectedExp field. Otherwise, the drop-down (RejectedExp) inserts a selection into RejectedExp field.

Your assistance and help is greatly appreciated. Thank you.

<%
Dim rs
Dim rs_numRows

pID=Request.Querystring("ID")
pIDX=request.form("IDX")

if request("Submit") = "" Then
Set rs = Server.CreateObject("ADODB.recordset")
rs.ActiveConnection = "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\test\database\test.mdb"
rs.Source = "SELECT * FROM IBX WHERE ID=" & pID
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 1
rs.Open()

rs_numRows = 0

if request("Update") = "Update" Then

Set rs = Server.CreateObject("ADODB.connection")
rs.open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=c:\inetpub\wwwroot\test\database\test.mdb"

sql=""
If Request.Form("Status") & "" <> "" Then
sql=sql & ",Status='" & Request.Form("Status") & "'"
End IF
If Request.Form("Status") & "" <> "" Then
sql=sql & ",Rejected_Date_New='" & Request.Form("Rejected_Date_New") & "'"
End IF
If Request.Form("Status") & "" <> "" Then
sql=sql & ",Rejected='" & Request.Form("Rejected") & "'"
End IF
' Need to create an If RejectedExp ="0", Then RejectedText, Else
If Request.Form("Status") & "" <> "" Then
sql=sql & ",RejectedExp='" & Request.Form("RejectedExp") & "'"
Else
If Request.Form("Status") & "" <> "" Then
sql=sql & ",RejectedExp='" & Request.Form("RejectedText") & "'"
End IF


sql="UPDATE IBX SET " & Mid(sql, 2, 10000)  ' leave out first comma
sql=sql & " WHERE ID = " & pIDX& "" 
'response.write sql 
rs.Execute sql
       
response.redirect "thankyou2.asp"   

end if
end if
%>

<html>
<head>
<title>Test</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="styles/universal.css" rel="stylesheet" type="text/css">
<STYLE TYPE='text/css'>
a:link { color:#0000FF; text-decoration:underline}
a:visited { color:#FF0000; text-decoration:underline}
a:hover { color:#009900; text-decoration:underline; cursor:hand}
a:active { color:#FF0000; text-decoration:underline}
</STYLE>
<script language="javascript">
function process( selectedValue )
{
        if( selectedValue == "Rejected" )
        {
         document.getElementById("RejectedExp").style.display="";
        }
	else
        {
         document.getElementById("RejectedExp").style.display="none";
        }
}

function process2( selectedValue )
{
        if( selectedValue == "0" )
        {
         document.getElementById("RejectedText").style.display="";
        }
	else
        {
         document.getElementById("RejectedText").style.display="none";
        }
}
</script>
</head>

<body leftmargin="0" rightmargin="0" topmargin="0">
<table width="100%" border="0" align="center" cellpadding="0" cellspacing="0" class="text-xlarge">
  <tr> 
    <td height="40" bgcolor="#0000AA"><table width="100%" height="40" border="0" cellpadding="0" cellspacing="0" class="text-xlarge">
        <tr> 
          <td><div align="center"><strong><font color="#00FF00"><strong>REVIEW 
              AREA - REJECTION PAGE</strong></font></strong></div></td>
        </tr>
      </table></td>
  </tr>
  <tr> 
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td> <table border="0" cellspacing="0" cellpadding="0" width="950">
        <tr>
          <td><form name="form1" method="post" action="">
              <table width="950" border="0" cellpadding="0" cellspacing="0" class="text-times">
                <tr> 
                  <td width="10">&nbsp;</td>
                  <td width="225"><strong>Submitter's Name: 
                    <input name="IDX" type="hidden" id="IDX" value="<%=(rs.Fields.Item("ID").Value)%>">
                    </strong></td>
                  <td><%=(rs.Fields.Item("YourName").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Email Address:</strong></td>
                  <td><%=(rs.Fields.Item("YourEmail").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Number:</strong></td>
                  <td><%=(rs.Fields.Item("CaseYear1").Value)%>-<%=(rs.Fields.Item("CaseNumber1").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Tracking Number:</strong></td>
                  <td><%=(rs.Fields.Item("Tracking").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Approved On:</strong></td>
                  <td><%=(rs.Fields.Item("ViewDate").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Reviewed By:</strong></td>
                  <td><%=(rs.Fields.Item("Who").Value)%></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>Proposed Submission:</strong></td>
                  <td><a href="<%=(rs.Fields.Item("Link").Value)%>" target="_blank">Order</a> 
                  </td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong><font color="#CC0000">Reject Submission?</font></strong></td>
                  <td><select name="Status" id="Status" onchange="process(this.options[this.selectedIndex].value)">
                      <option value="">Select</option>
                      <option value="Rejected">Yes</option>
                    </select> &nbsp; </td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><select name="RejectedExp" id="RejectedExp" style="display:none;" onchange="process2(this.options[this.selectedIndex].value)">
                      <option value="Order was rejected">ORDER REJECTED? PLEASE 
                      SELECT AN EXPLANATION BELOW.</option>
                      <option value="Reason One">Reason One</option>
                      <option value="Reason Two">Reason Two</option>
                      <option value="Reason Three">Reason Three</option>
                      <option value="Reason Four">Reason Four</option>
                      <option value="Reason Five">Reason Five</option>
                      <option value="0">Type Rejection Reason</option>
                    </select></td>
                </tr>
                <tr>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><input name="RejectedText" type="text" id="RejectedText" style="display:none;" size="80" maxlength="100"></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><input name="Rejected_Date_New" type="hidden" id="Rejected_Date_New" value="<%=date()%>"> 
                    <input name="Rejected" type="hidden" id="Rejected" value="SUBMITTED ORDER REJECTED"> 
                  </td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td><strong>BEFORE UPDATING, PLEASE</strong></td>
                  <td><strong>ENSURE YOU ARE REJECTING THE CORRECT SUBMISSION.</strong></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td><input name="Update" type="submit" id="Update" value="Update"> 
                    &nbsp; <input type="reset" name="Reset" value="Cancel"></td>
                </tr>
                <tr> 
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                  <td>&nbsp;</td>
                </tr>
              </table>
            </form></td>
        </tr>
      </table>
      <p>&nbsp;</p></td>
  </tr>
</table>
</body>
</html>
<%
rs.Close()
Set rs = Nothing
%>

Open in new window

0
Comment
Question by:arendt73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 33

Accepted Solution

by:
Big Monty earned 2000 total points
ID: 39628570
try this

If Request.Form("Status") & "" <> "" Then
     dim rejected
     if request.Form("RejectedExp") = "0" then
          rejected = Request.Form("RejectedText")
     else
        rejected = request.Form("RejectedExp")
     end if
     sql=sql & ",RejectedExp='" & rejected & "'"
else...
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question