1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SQL + dropdowns

Discussion in 'Databases' started by december, Jun 27, 2006.

  1. #1
    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!


    
    <%@ 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
    %>
    
    
    Code (markup):

     
    december, Jun 27, 2006 IP
  2. december

    december Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Sorry,
    The two I can't get to work are the CAT_STATUS and CAT_TYPE
     
    december, Jun 27, 2006 IP