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.

  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.
    SEMrush
    Any help is greatly appreicated!
    Thanks,
    d
     
    december, Jun 21, 2006 IP
    SEMrush
  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