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.

populate dropdown from sql

Discussion in 'C#' started by december, Jun 21, 2006.

0
  1. #1
    Hello forum!,
    I'm new to ASP/forum and have to modify the code below. When the app is ran you get a list of checkboxes to choose from which passes values to crystal reports. All I need to do is change those checkboxs to dropdowns. Can someone give me some assistance here??

    
<%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim dFrom, dTo
Dim iIncidentID
Dim aTypeID, aStatusID,aPayGradeID

aTypeID    = Split(Request.Form("chkTypeID"), ",")
aStatusID  = Split(Request.Form("chkStatusID"), ",")
aPayGradeID = Split(Request.Form("chkPayGradeID"), ",")

iIncidentID = 0

%>
<html>
<head>
<title>Test Report</title>
</head>
<body>
<form name="frmSelect" action="rep1.aspx" method="post" onsubmit="return fnCheck();">
[COLOR="Red"]<table border="0">
      <td valign="top">
      <b>STATUS</b>
      <table cellpadding="0" cellspacing="0">
<%
sbPrintChoices ROOT_ID, CAT_STATUS, 0, aStatusID
%>


<b>STATUS2</b> 
<table>
<%
sbPrintChoices ROOT_ID, CAT_STATUS2, 0, aStatus2ID
%>      
</table>
 



<%
Sub sbPrintChoices(ByVal iParentID, ByVal iCat, ByVal iPad, ByRef aID)
  Dim aChild, i, sCat, sID[/COLOR]
  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_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

  If IsArray(aChild) Then
    For i = LBound(aChild, 2) To UBound(aChild, 2) %>
    
       <tr><td style="padding-left:<%=iPad%>px;">
       
 <%     If aChild(CLD_TYPE, i) <> 2 Then    %>

            <input type=checkbox id="chk<%=sCat%>ID" name="chk<%=sCat%>ID"                          value="<%=aChild(CLD_ID, i)%>"

        
        <%
        If IsArray(aID) Then
          For Each sID In aID
            If CInt(sID) = aChild(CLD_ID, i) Then Response.Write " checked"
          Next
        End If
        Response.Write "/> " & aChild(CLD_NAME, i)
        Response.Write "<br/>" & vbCrLf
      Else
        Response.Write "          <u>" & aChild(CLD_NAME, i) & "</u><br/>" & vbCrLf
      End If
        Response.Write "        </td></tr>" & vbCrLf

      If iCat = CAT_STATUS Or iCat = CAT_TYPE Then _
        sbPrintChoices aChild(CLD_ID, i) , iCat, iPad + 10, aID
       
       Next
       
    End If
End Sub
%>
    Code (markup):
    Sorry If this is really long. I believe the problem lies within the sbprintchoices, but not %100 sure.

    Any help is greatly appreicated!
    Thanks,
    d
     
    december, Jun 21, 2006 IP
  2. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #2
    <input type=checkbox id="chk<%=sCat%>ID" name="chk<%=sCat%>ID"/>change this into
    <option>chk<%=sCat%>ID</option>

    then add the following before your loop
    <select id="chk<%=sCat%>ID">and this after your loop is finished
    </select>
    you may also add your checker and select:
    If CInt(sID) = aChild(CLD_ID, i) Then Response.Write " checked"
    here
    <option <%If CInt(sID) = aChild(CLD_ID, i) Then Response.Write " selected"%>>chk<%=sCat%>ID</option>

    hope this will work out, check if I haven't made mistakes when typing <%%>
     
    ludwig, Jun 21, 2006 IP
  3. december

    december Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #3
    Thanks for the response..
    When I inserted the code I got a bunch of dropdowns, but instead of being grouped together each option had its own dropdown, and in the dropdown was the 'chk..' not the actual choice.
     
    december, Jun 21, 2006 IP
  4. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #4
    well, just tell me what you got there, I mean did you get it to work?
     
    ludwig, Jun 22, 2006 IP
  5. december

    december Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #5
    no it didnt work. none of the dropdowns have options in them, just the word 'chkTypeID' . Maybe I did something wrong??

    

If IsArray(aChild) Then
    For i = LBound(aChild, 2) To UBound(aChild, 2) %>
    
       <tr><td style="padding-left:<%=iPad%>px;">
       
 <%     If aChild(CLD_TYPE, i) <> 2 Then	%>

			<option>chk<%=sCat%>ID value="<%=aChild(CLD_ID, i)%>"</option>


           <select id="chk<%=sCat%>ID" NAME="chk<%=sCat%>ID">

<%        If IsArray(aID) Then
          For Each sID In aID
         <option <%If CInt(sID) = aChild(CLD_ID, i) 
         Then Response.Write " selected"%>>chk<%=sCat%>ID</option> </select>
        <%  Next
        End If
        Response.Write "/> " & aChild(CLD_NAME, i)
        Response.Write "<br/>" & vbCrLf
      Else
        Response.Write "          <u>" & aChild(CLD_NAME, i) & "</u><br/>" & vbCrLf
      End If
      Response.Write "        </td></tr>" & vbCrLf

      If iCat = CAT_STATUS Or iCat = CAT_TYPE Then _
        sbPrintChoices aChild(CLD_ID, i), iCat, iPad + 10, aID
    
  End If
End Sub
%>
    Code (markup):
     
    december, Jun 22, 2006 IP
  6. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #6
    yes you did wring I'll correct it for you and get back in a few minutes
     
    ludwig, Jun 22, 2006 IP
  7. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #7
    now try this and let me know

    <%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim dFrom, dTo
Dim iIncidentID
Dim aTypeID, aStatusID,aPayGradeID

aTypeID    = Split(Request.Form("chkTypeID"), ",")
aStatusID  = Split(Request.Form("chkStatusID"), ",")
aPayGradeID = Split(Request.Form("chkPayGradeID"), ",")

iIncidentID = 0

%>
<html>
<head>
<title>Test Report</title>
</head>
<body>
<form name="frmSelect" action="rep1.aspx" method="post" onsubmit="return fnCheck();">
<table border="0">
      <td valign="top">
      <b>STATUS</b>
      <table cellpadding="0" cellspacing="0">
<%
sbPrintChoices ROOT_ID, CAT_STATUS, 0, aStatusID
%>


<b>STATUS2</b> 
<table>
<%
sbPrintChoices ROOT_ID, CAT_STATUS2, 0, aStatus2ID
%>      
</table>
 



<%
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_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) %>
    
       <tr><td style="padding-left:<%=iPad%>px;">
       
 <%     If aChild(CLD_TYPE, i) <> 2 Then    %>
            <option value="<%=aChild(CLD_ID, i)%>"
        <%
        If IsArray(aID) Then
          For Each sID In aID
            If CInt(sID) = aChild(CLD_ID, i) Then Response.Write " selected"
          Next
        End If
        Response.Write ">" & aChild(CLD_NAME, i) & "</option>" & aChild(CLD_NAME, i)
      Else
        Response.Write "          <u>" & aChild(CLD_NAME, i) & "</u><br/>" & vbCrLf
      End If%>
        </td></tr>
      <%
	  If iCat = CAT_STATUS Or iCat = CAT_TYPE Then _
        sbPrintChoices aChild(CLD_ID, i) , iCat, iPad + 10, aID
       Next
    End If
End Sub
%>
    Code (markup):
     
    ludwig, Jun 22, 2006 IP
  8. december

    december Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Heres a screen shot of whats going on..
    
<ahref="http://img66.imageshack.us/img66/2816/result4cp.jpg"> </a>
    HTML:
    The values are still outside of the boxes, and each have there own. I would like to just have 2 with all the options inside. Thanks again for the help.
     
    december, Jun 22, 2006 IP
  9. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #9
    you can't have an option inside the option

    sorry its hard to do it like this, for me

    but try this one too, I made it a bit simple for ya

    <%@ Language=VBScript %>
<% Option Explicit %>
<%
Dim dFrom, dTo
Dim iIncidentID
Dim aTypeID, aStatusID,aPayGradeID

aTypeID    = Split(Request.Form("chkTypeID"), ",")
aStatusID  = Split(Request.Form("chkStatusID"), ",")
aPayGradeID = Split(Request.Form("chkPayGradeID"), ",")
iIncidentID = 0
%>
<html>
<head>
<title>Test Report</title>
</head>
<body>
<form name="frmSelect" action="rep1.aspx" method="post" onsubmit="return fnCheck();">
<table border="0">
      <td valign="top">
      <b>STATUS</b>
      <table cellpadding="0" cellspacing="0">
<%
sbPrintChoices ROOT_ID, CAT_STATUS, 0, aStatusID
%>
<b>STATUS2</b> 
<table>
<%
sbPrintChoices ROOT_ID, CAT_STATUS2, 0, aStatus2ID
%>      
</table>
<%
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_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):
    try to modify some of your code also. maybe there is something else you're doing wrong
     
    ludwig, Jun 22, 2006 IP
  10. december

    december Peon

    Messages:
    7
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    okay.. back to the drawing boards for me! Thanks for your help ludwig.. when i ran that last code some of the dropdowns worked, so atleaste not all has failed.
     
    december, Jun 22, 2006 IP
  11. ludwig

    ludwig Notable Member

    Messages:
    2,253
    Likes Received:
    66
    Best Answers:
    0
    Trophy Points:
    225
    #11
    Hope you can figure out the rest,

    GoodLuck
     
    ludwig, Jun 22, 2006 IP