december
06-27-06, 03:06 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!
<%@ 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
%>
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
%>