Solved

Binding a Drop down list to a gridview asp/vb.net vs2008 Please help.

Posted on 2011-03-17
16
591 Views
Last Modified: 2012-05-11
hi,

I have drop down list of schools. When the user click on a school in the code needs to pass the school ID ( like 1 or 2 )  from a SQL Query that is excuted and pull the data in a grid view. Any help would be great>  :-) I am useing VS 2008 and asp.net/ vb.net

Thanks
0
Comment
Question by:wmbentoniii
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 5
16 Comments
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35159251
You got any existing code/
0
 

Author Comment

by:wmbentoniii
ID: 35159389
This  code works with a button but I don't can't get it to work with a simple drop down list. The onling this code does is pull back data from the SQl query. I know it's a mess sorry. :-)



Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
       
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand



        myConnection = New SqlConnection("Data Source=DB-DEV;Initial Catalog=STARS-DB;Integrated Security=True")
        'establishing connection. you need to provide password for sql server
        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand("SELECT * FROM pod", myConnection)
            Dim dr As SqlDataReader = myCommand.ExecuteReader()
            While dr.Read()
                'reading from the datareader
                Response.Write(dr(0).ToString())
                Response.Write(dr(1).ToString())
                Response.Write(dr(2).ToString())
                Response.Write(dr(3).ToString())
                Response.Write(dr(4).ToString())
                'displaying data from the table
            End While
            dr.Close()
            myConnection.Close()
        Catch
        End Try
    End Sub





_______________________________________________________________________
 <%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="WebApplication5._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
    <style type="text/css">
        .style1
        {
            width: 92px;
        }
        .style2
        {
            width: 95px;
        }


        #ctl00_siscontentbody_sc1_ddSearchProgramID
        {
            height: 31px;
            width: 289px;
        }


    </style>
<script language="javascript" type="text/javascript">
// <!CDATA[

function ctl00_siscontentbody_sc1_ddSearchProgramID_onclick() {

}

// ]]>
</script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
        <table style="width:100%;">
            <tr>
                <td class="style1">
                    hhhhhh</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style1">
                    hhhhhh</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
            <tr>
                <td class="style1">
                    hhhhh</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
           
            <tr>
                <td class="style1">
                    hhhhhhh</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
             <tr>
                <td class="style1">
                    &nbsp;</td>
                <td class="style2">
                    &nbsp;</td>
                <td>
                    &nbsp;</td>
            </tr>
           
           
           
        </table>
   
    </div>
    <asp:GridView ID="GrdAuthors" runat="server" CellPadding="4"
        ForeColor="#333333" GridLines="None" AutoGenerateColumns="False"
        DataSourceID="SqlDataSource1" Width="715px">
        <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
        <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
        <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
        <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
        <EditRowStyle BackColor="#999999" />
        <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    </asp:GridView>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:STARS-DBConnectionString %>"
        SelectCommand="sp_LC_List" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter Name="School_Id" Type="Int32" />
        </SelectParameters>
    </asp:SqlDataSource>
    <asp:Button ID="Button1" runat="server" Text="Button" />
   
    <input type="hidden" name="ctl00$siscontentbody$sc1$_SearchProgramIncludeRetired" id="ctl00_siscontentbody_sc1__SearchProgramIncludeRetired" value="False" />
</body>
</html><table id="ctl00_siscontentbody_sc1_tblSearch" class="tabledetail"
                align="Center" border="0"
                style="background-color: #F5F5F5; width:100%; color: #003399; background-color: #FFFFFF;"
                bgcolor="Silver">
                  <tr class="tableheader">
                        <td align="left" valign="top" colspan="2" bgcolor="#003399"><span id="Span1" class="headerlines">Search Criteria</span></td>
                  </tr><tr id="ctl00_siscontentbody_sc1_trProgram">
                        <td class="label" align="right" valign="top">
                    <span id="ctl00_siscontentbody_tcMain_tpESL_sc1_lblSearchDescription"
                        class="headerlines">Search Criteria</span></td>
                <td align="left" valign="top" bgcolor="WhiteSmoke"><select name="ctl00$siscontentbody$sc1$ddSearchProgramID" onchange="javascript:setTimeout('__doPostBack(\'ctl00$siscontentbody$sc1$ddSearchProgramID\',\'\')', 0)" id="ctl00_siscontentbody_sc1_ddSearchProgramID" class="dropdownlistlong" onclick="return ctl00_siscontentbody_sc1_ddSearchProgramID_onclick()">
                              <option selected="selected" value="0">All</option>
                              <option value="146">Atlanta Public Schools</option>
                              <option value="174">Broward County Charter Schools - High School</option>
                              <option value="178">Bay District High School</option>
                              <option value="151">Bay District Middle School</option>
                              <option value="188">Duval County Public Schools</option>
                              
                              </select></td>
                  </tr><tr id="ctl00_siscontentbody_sc1_trSearchButtons">
                        <td align="left" valign="top" colspan="2" bgcolor="WhiteSmoke">&nbsp;
                &nbsp;
                &nbsp;
                </td>
                  </tr>
            </table>
    <input type="hidden" name="ctl00$siscontentbody$sc1$_SearchProgramIncludeRetired" id="Hidden1" value="False" />
    <input type="hidden" name="ctl00$siscontentbody$sc1$_SearchCodeDefinition1CodeCategory" id="ctl00_siscontentbody_sc1__SearchCodeDefinition1CodeCategory" />
    <input type="hidden" name="ctl00$siscontentbody$sc1$_SearchCodeDefinition1SortByDisplayValue" id="ctl00_siscontentbody_sc1__SearchCodeDefinition1SortByDisplayValue" />
    <input type="hidden" name="ctl00$siscontentbody$sc1$_SearchSchoolIncludeRetired" id="ctl00_siscontentbody_sc1__SearchSchoolIncludeRetired" value="True" />
    <input type="hidden" name="ctl00$siscontentbody$sc1$_SearchMode" id="ctl00_siscontentbody_sc1__SearchMode" />
   
   
   
</td>
      </tr><tr>
            <td><table id="ctl00_siscontentbody_ce1_Table7" class="tabledetail" border="0"
                style="width:100%;" bgcolor="#CCCCCC">
                  <tr>
                        <td class="tabledetail" colspan="3">
            <div id="div1" style="height: 200px; overflow: auto; width: 99%">
                <div>
                              <table class="gridview" cellspacing="0" border="0"
                        id="ctl00_siscontentbody_ce1_gvCourses"
                        style="width:96%;border-collapse:collapse; color: #003399;"
                        bgcolor="WhiteSmoke">
                                    <tr class="tableheader">
                                          <th scope="col">&nbsp;</th><th align="left" scope="col" class="style2">Learning
                            Community</th><th align="left" scope="col" class="style4">Classroom</th><th align="left" scope="col"
                                                        class="style5">&nbsp;</th><th align="left" scope="col">&nbsp;</th><th align="left" scope="col">
                            &nbsp;</th><th align="left" scope="col">&nbsp;</th>
                                    </tr><tr class="gridviewrowstyle" style="height:25px;">
                                          <td><a class="label" href="javascript:__doPostBack('ctl00$siscontentbody$ce1$gvCourses','Select$0')">
                                Select</a></td><td align="left" class="style2">&nbsp;</td><td align="left"
                                class="style4">&nbsp;</td><td align="left" class="style5">
                                &nbsp;</td><td align="left">&nbsp;</td><td align="left">&nbsp;</td><td align="left">&nbsp;</td>
                                    </tr><tr class="gridviewaltrowstyle" style="background: #F5F5F5; height:25px;">
                                          <td><a class="label" href="javascript:__doPostBack('ctl00$siscontentbody$ce1$gvCourses','Select$1')">
                                Select</a></td><td align="left" class="style2">&nbsp;</td><td align="left"
                                class="style4">&nbsp;</td><td align="left" class="style5">
                                &nbsp;</td><td align="left">&nbsp;</td><td align="left">&nbsp;</td><td align="left">&nbsp;</td>
                                    </tr><tr class="selectedrow" style="height:25px;">
                                          <td><a class="label" href="javascript:__doPostBack('ctl00$siscontentbody$ce1$gvCourses','Select$2')">
                              
                              
                              
                              
                              
                              
                              
                              
                              
                              
                              
                              
    </table>
    <asp:DropDownList ID="DropDownList1" runat="server" Height="38px" Width="524px"
                        DataSourceID="SqlDataSource1" DataTextField="podid" DataValueField="podname">
    </asp:DropDownList>
    </form>
    </table>
</form>
<p>
    &nbsp;</p>
<p>
    &nbsp;</p>
<p>
    &nbsp;</p>
<p>
    &nbsp;</p>




0
 

Author Comment

by:wmbentoniii
ID: 35159421
so here is what I got I am useing VS2008  ASP.net and the code behind isVB.net. I drag asimple drop down list and a gridview. I have a couple of schools coded in the drop down list and when I slect them I would like the SQl querey which is (SELECT podid,podname FROM pod WHERE cepschoolid = @school_id ORDER BY podname) to go out and grab the data and display it in a gridview. But I am not sure how to bind the drop down list and the gridview together so they work as one.
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

Author Comment

by:wmbentoniii
ID: 35159556
Updated asp.net code that populates the drop downlist with schools but when I click on one nothen happens.





<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="WebApplication9._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
   
      <asp:dropdownlist id="ddlCategories" runat="server" appenddatabounditems="True" autopostback="True"
            datasourceid="SqlDataSource1" datatextfield="SchoolNameDescr"
            datavaluefield="CepSchoolID">
            <asp:listitem text="All Categories" value="-1">
            </asp:listitem>
      </asp:dropdownlist>
          <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:STARS-DBConnectionString %>" SelectCommand="SELECT
       cep.CepSchoolID
      ,cep.SchoolName SchoolNameDescr
      ,2 Ord

FROM
       CEPSchool cep INNER JOIN
       SI_SchoolInfo si ON si.CEPSchoolID = cep.CEPSchoolID
WHERE
       si.StudentPortalActiveTF = 'True'

UNION ALL
SELECT
       0
      ,'Messages for All Schools'
      ,1

ORDER BY
       Ord
      ,SchoolNameDescr

"></asp:SqlDataSource>
      <asp:gridview id="gvProducts" runat="server" autogeneratecolumns="False"
            datasourceid="sdsProducts" style="margin-top: 12px;">
      </asp:gridview>
      <asp:sqldatasource id="sdsProducts" runat="server" connectionstring="<%$ ConnectionStrings:STARS-DBConnectionString %>"
            
            selectcommand="SELECT podid,podname FROM pod WHERE cepschoolid = @school_id ORDER BY podname">
        <SelectParameters>
            <asp:Parameter Name="school_id" />
        </SelectParameters>
      </asp:sqldatasource>

    </form>
</body>
</html>
0
 

Author Comment

by:wmbentoniii
ID: 35159655
ok this vb.net code linked with I just posted  gives me a single data,
Imports System.Data
Imports System.Data.SqlClient

Partial Public Class _Default
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    End Sub

    Protected Sub ddlCategories_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlCategories.SelectedIndexChanged
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand



        myConnection = New SqlConnection("Data Source=DB-DEV;Initial Catalog=STARS-DB;Integrated Security=True")
        'establishing connection. you need to provide password for sql server
        Try
            myConnection.Open()
            'opening the connection
            myCommand = New SqlCommand("SELECT * FROM pod", myConnection)
            Dim dr As SqlDataReader = myCommand.ExecuteReader()
            While dr.Read()
                'reading from the datareader
                Response.Write(dr(0).ToString())
                Response.Write(dr(1).ToString())
                Response.Write(dr(2).ToString())
                Response.Write(dr(3).ToString())
                Response.Write(dr(4).ToString())
                'displaying data from the table
            End While
            dr.Close()
            myConnection.Close()
        Catch
        End Try
    End Sub




End Class
0
 

Author Comment

by:wmbentoniii
ID: 35159688
ok the sp is this @School_Id int
AS
      SELECT podid,podname FROM pod WHERE cepschoolid = @school_id ORDER BY podname


it's useing @School_Id int which is a numeric value. So when the user selects the school it needs to pass the value to @School_Id int to display the data in the grid view. But I am still fuzzy on the grid view. any help would be great.
 
0
 

Author Comment

by:wmbentoniii
ID: 35159694
I think I may be talking too my self here? is there anyone there that can help?
0
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 500 total points
ID: 35161497
Well you need to update the sqldatasource for the gridview to use the storedprocedure, which from the sounds of it you have already done.
Then change your select parameters to :
        <SelectParameters>
            <asp:ControlParameter Name="school_id"  ControlID="ddlCategories" PropertyName="SelectedValue"/>
        </SelectParameters>

So when the gridview binds it will use the selectedvalue of the dropdown list.
So you won't need the code in the selectedIndex changed event handler.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35161545
Forgot to say that in the selectedIndexchanged event you will need to get the gridview to rebind by calling gvProducts.Bind()
Also if you change your select parameter as I have specified above it will work with your select statement as is.
0
 

Author Comment

by:wmbentoniii
ID: 35166415
Forgot to say that in the selectedIndexchanged event you will need to get the gridview to rebind by calling gvProducts.Bind()
Also if you change your select parameter as I have specified above it will work with your select statement as is.


how would i do that?
0
 

Author Comment

by:wmbentoniii
ID: 35166442
never mind I think I got it :-)
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35166445
In your code behind, replace the code in your ddlCategories_SelectedIndexChanged event handler to :

Protected Sub ddlCategories_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles ddlCategories.SelectedIndexChanged
    gvProducts.Bind()
End Sub

0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35166447
ok cool
0
 

Author Comment

by:wmbentoniii
ID: 35167485
Thank you
0
 

Author Closing Comment

by:wmbentoniii
ID: 35167494
Awsome
0

Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In an ASP.NET application, I faced some technical problems. In this article, I list them out and show the solutions that I found.  I hope it will be useful. Problem: After closing a pop-up window, the parent page should be refreshed automaticall…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question