Solved

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

Posted on 2011-03-17
16
575 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now