problem with sql + dropdowns

december · June 27th, 2006, 02:03 PM

Hello,
I have a little program that passes values to crystal reports via dropdowns. My problem is I can't get two of them to work. Im not to good with SQL so any help is much 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>
    <td>
    <td valign="top">
      <b>COMMAND</b>
      <table cellpadding="0" cellspacing="0" ID="Table5">
<%
sbPrintChoices , CAT_CMD, 0, aCommandID
%>
      </table>
      <br/>
      <b>TYCOM</b>
      <table cellpadding="0" cellspacing="0" ID="Table6">
<%
sbPrintChoices , CAT_TYCOM, 0, aTycomID
%>





        <tr><td>
          <input type="checkbox" name="chkTycomID" value="0"<% If InStr(Request.Form("chkTycomID"), "0") <> 0 
Then Response.Write " checked"%> ID="Checkbox1"/> TEST<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_CMD
    sCat = "Command"
    sSQL = "SELECT cmd_CommandID, cmd_SName, 0 AS theType FROM tbl_Command ORDER BY cmd_CommandID"
  Case CAT_TYCOM
    sCat = "Tycom"
    sSQL = "SELECT tyc_TycomID, tyc_Tycom, 0 AS theType FROM tbl_Tycom ORDER BY tyc_TycomID"
  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
%>

december · June 27th, 2006, 02:13 PM

Sorry,
The two I can't get to work are the CAT_STATUS and CAT_TYPE

Peso · July 3rd, 2006, 03:05 AM

It seems that you call sbPrintChoices recursively, and thus creating multiple <SELECT> tags, one for each subcategory.

vitthala · July 6th, 2006, 12:22 PM

When you said can't get it to work, What you mean by that. You mean value is not passed to crystal reports. Just check if "iParentID" variable has some thing in it or not. Also execute the Sql and attach it to the recordset to check recordcount > 0. I'm not really sure whats the problem here. More information is necessary in order to fix it.