The Database Forum

**december** · 05:49 PM

Hello,
I'm having problems with a SQL statement in my program. I have a few dropdowns that send a value to crystal reports. I can get a couple to work, but Im having troubles with CAT_STATUS and CAT_TYPE. What happens is every option in the sql table has its own dropdown instead of all being in one. Any help at all would be greatly appreciated!

Code:

 
 
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim dFrom, dTo
Dim iIncidentID
Dim aTypeID, aStatusID, aCommandID, aTycomID, aPayGradeID
 
If iUserRoleID <> ROL_ADMIN And iUserRoleID <> ROL_GLOBALREADER Then Response.Redirect "main.asp"
dTo         = Request.Form("sltTo")
dFrom       = Request.Form("sltFrom")
aTypeID     = Split(Request.Form("chkTypeID"), ",")
aTycomID    = Split(Request.Form("chkTycomID"), ",")
aStatusID   = Split(Request.Form("chkStatusID"), ",")
aCommandID  = Split(Request.Form("chkCommandID"), ",")
aPayGradeID = Split(Request.Form("chkPayGradeID"), ",")
iIncidentID = 0
 
%>
<html>
<head>
<title>TEST APP</title>
<body >
<form name="frmSelect" action="reports.aspx" method="post" onsubmit="return fnCheck();" ID="Form1">
<table border="0">
  <tr>
    <td colspan="2">
      From:
      <input type="text" name="sltFrom" value="<%= dFrom %>" size="10" maxlength="10" ID="Text1"/>
      To:
      <input type="text" name="sltTo" value="<%= dTo %>" size="10" maxlength="10" ID="Text2"/>
    </td>
    <td colspan="2" align="right"><a href="javascript:fnReport();">Snap Shot</a></td>
  </tr>
  <tr>
    <td colspan="2" bgcolor="blue"><b>Inner Column</b></td>
    <td bgcolor="blue"><b>Outer Column</b></td>
    <td valign="top" rowspan="2">
      <input type="submit" value="Submit" ID="Submit1" NAME="Submit1"/><br/>
      <br/>
      <input type="button" value="Cancel" onclick="fnCancel();" ID="Button1" NAME="Button1"/>
    </td>
</tr>
  <tr>
    <td valign="top">
      <b>INCIDENT TYPE</b>
      <table cellpadding="0" cellspacing="0" border=1 ID="Table2">
<%
sbPrintChoices ROOT_ID, CAT_TYPE, 0, aTypeID
%>
 
      </table>
<br/>
   <td valign="top">
   <b>PAYGRADE</b>
   <table cellpadding=0 cellspacing=0 ID="Table3">
 
 
 
  <% 
  sbPrintChoices  ROOT_ID, CAT_PAYGRADE, 0, aPayGradeID
  %>
     </table>
  <br/>
 
 
    <td valign="top">
      <b>STATUS</b>
      <table cellpadding="0" cellspacing="0" ID="Table4">
<%
sbPrintChoices Root_ID, CAT_STATUS, 0, aStatusID
%>
      </table>
 
 
        <tr><td>
          <input type="checkbox" name="chkTycomID" value="0"
<% If InStr(Request.Form("chkTycomID"), "0") <> 0 Then Response.Write " checked"%> ID="Checkbox1"/> COMNAVAIRFOR<br/>
        </td></tr>
      </table>
    </td>
  </tr>
</table>
<input type="hidden" name="hdnSort" value="<%= Request.Form("hdnSort") %>" ID="Hidden1"/>
<input type="hidden" name="PerPage" value="<%= Request.Form("PerPage") %>" ID="Hidden2"/>
<input type="hidden" name="PageNum" value="<%= Request.Form("PageNum") %>" ID="Hidden3"/>
<input type="hidden" name="chkShowClosed"  value="<%= Request.Form("chkShowClosed")  %>" ID="Hidden4"/>
<input type="hidden" name="chkShowDeleted" value="<%= Request.Form("chkShowDeleted") %>" ID="Hidden5"/>
</form>
</body>
</html>
<%
Sub sbPrintChoices(ByVal iParentID, ByVal iCat, ByVal iPad, ByRef aID)
  Dim aChild, i, sCat, sID
  Select Case iCat
  Case CAT_STATUS
    sCat = "Status"
    sSQL = "SELECT tbl_Status.sta_StatusID, tbl_Status.sta_Status, tbl_SubStatus.sst_Type, tbl_SubStatus.sst_Multiple, tbl_SubStatus.sst_Type " & _
           "FROM tbl_Status INNER JOIN tbl_SubStatus ON tbl_Status.sta_StatusID = tbl_SubStatus.sst_ChildID " & _
           "WHERE tbl_SubStatus.sst_ParentID = " & iParentID & " ORDER BY tbl_Status.sta_StatusID"
    Case CAT_TYPE
    sCat = "Type"
    sSQL = "SELECT tbl_Type.typ_TypeID, tbl_Type.typ_Type, tbl_SubType.sty_Type, tbl_SubType.sty_Multiple, tbl_SubType.sty_Type " & _
           "FROM tbl_SubType INNER JOIN tbl_Type ON              tbl_SubType.sty_ChildID=tbl_Type.typ_TypeID " & _
           "WHERE tbl_SubType.sty_ParentID = " & iParentID & " ORDER BY tbl_Type.typ_TypeID"    Case CAT_PAYGRADE
   sCat = "PayGrade"
   sSQL = "SELECT pay_PayGradeID, pay_PayGrade, 0 AS theType FROM tbl_Paygrade ORDER BY pay_PayGradeID"
  End Select
  openRst sSQL, "", "", "", "", ""
  If Not oRst.EOF Then aChild = oRst.GetRows
 closeRst
 
   %>
 
<select id="chk<%=sCat%>ID" name="chk<%=sCat%>ID">
<%  If IsArray(aChild) Then
    For i = LBound(aChild, 2) To UBound(aChild, 2) %>
            <option value="<%=aChild(CLD_ID, i)%>"<%If CInt(sID) = 
aChild(CLD_ID, i) Then%>" selected"<%end if%>>
<%=aChild(CLD_NAME, i)%></option>
      <%
   If iCat = CAT_STATUS Or iCat = CAT_TYPE Then sbPrintChoices aChild(CLD_ID, i) , iCat, iPad + 10, aID
       Next%>
</select>
     </td></tr>
    <%End If
End Sub
%>