0

I have this dropdownlist:

 <asp:DropDownList ID="ddlEvents" runat="server" AppendDataBoundItems="True" AutoPostBack="True" Width="140px"> <asp:ListItem Value="0">Choose Location</asp:ListItem> </asp:DropDownList>

Above dropdownlist options are dynamically populated from the database.

Then I have this on codebehind:

 Public Sub BindGrid() Dim oconn As New SqlConnection(sqlconn) AddHandler ddlLocation.SelectedIndexChanged, New EventHandler(AddressOf ddl_SelectedIndexChanged) oconn.Open() Dim ocmd As New SqlCommand("select e.eventsId,e.Location, dbo.fnFormatDate(t.trainingDates, 'MON/DD/YYYY') t.eventDates, t.eventTime,t.eventDescription from tblEvents e, tblEventgDates t where e.eventid = t.eventid and e.eventid = " & ddlEvents.SelectedValue, oconn) Dim oda As New SqlDataAdapter(ocmd) Dim builder As New SqlCommandBuilder(oda) Dim ds As New DataSet() oda.Fill(ds) gv1.DataSource = ds gv1.DataBind() End Sub

Our users would like to filter results by selecting eventLocation from the dropdownList and have only events associated with that location to be displayed.

Code above is not doing anything.

I suspect that I need selectedIndexChanged?

But how do I incorporate it into the BindData() event?

Thanks much in advance

Imports System.Collections.Generic Imports System.Linq Imports System.Web Imports System.Web.UI Imports System.Web.UI.WebControls Imports System.Data Imports System.Configuration Imports System.Data.SqlClient Partial Class Events Inherits System.Web.UI.Page Private sqlconn As String = ConfigurationManager.ConnectionStrings("DBConnectionString").ToString() Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load If Not IsPostBack Then BindGrid() End If PopulateDates() End Sub Public Sub BindGrid() Dim oconn As New SqlConnection(sqlconn) ' AddHandler ddlEvents.SelectedIndexChanged, New EventHandler(AddressOf ddl_SelectedIndexChanged) oconn.Open() Dim ocmd As New SqlCommand("select e.eventsId,e.Location, dbo.fnFormatDate(t.trainingDates, 'MON/DD/YYYY') t.eventDates, t.eventTime,t.eventDescription from tblEvents e, tblEventgDates t where e.eventid = t.eventid and e.eventid = " & ddlEvents.SelectedValue, oconn) Dim oda As New SqlDataAdapter(ocmd) Dim builder As New SqlCommandBuilder(oda) Dim ds As New DataSet() oda.Fill(ds) gv1.DataSource = ds gv1.DataBind() End Sub Protected Sub gv1_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Dim oconn As New SqlConnection(sqlconn) oconn.Open() Dim ocmd As New SqlCommand("SELECT* FROM Events", oconn) Dim oda As New SqlDataAdapter(ocmd) Dim builder As New SqlCommandBuilder(oda) Dim ds As New DataSet() oda.Fill(ds) Dim ddl As DropDownList = DirectCast(e.Row.FindControl("ddlInstructors"), DropDownList) If ddl IsNot Nothing Then ddl.DataSource = ds ddl.DataValueField = "EventsId" ddl.DataTextField = "EventName" ddl.DataBind() End If If e.Row.RowType = DataControlRowType.Footer Then Dim ddldesig As DropDownList = DirectCast(e.Row.FindControl("ddladddesig"), DropDownList) ddldesig.DataSource = ds ddldesig.DataValueField = "EventsId" ddldesig.DataTextField = "EventName" ddldesig.DataBind() End If End Sub Protected Sub gv1_RowDeleting(ByVal sender As Object, ByVal e As GridViewDeleteEventArgs) Dim EID As Integer = Convert.ToInt32(gv1.DataKeys(e.RowIndex).Value) Dim oconn As New SqlConnection(sqlconn) oconn.Open() Dim ocmd As New SqlCommand() ocmd.CommandText = "DELETE FROM Events WHERE CourseID=@EID" ocmd.Parameters.AddWithValue("@EID", EID) ocmd.Connection = oconn ocmd.ExecuteNonQuery() oconn.Close() BindGrid() End Sub Protected Sub gv1_RowEditing(ByVal sender As Object, ByVal e As GridViewEditEventArgs) gv1.EditIndex = e.NewEditIndex BindGrid() End Sub Protected Sub gv1_RowUpdating(ByVal sender As Object, ByVal e As GridViewUpdateEventArgs) Dim EID As Integer = Convert.ToInt32(gv1.DataKeys(e.RowIndex).Value) 'Dim ENAME As String = DirectCast(gv1.Rows(e.RowIndex).Cells(1).FindControl("txtename"), TextBox).Text Dim DESIGID As Integer = Integer.Parse(DirectCast(gv1.Rows(e.RowIndex).Cells(1).FindControl("ddlInstructors"), DropDownList).SelectedValue) Dim oconn As New SqlConnection(sqlconn) oconn.Open() Dim ocmd As New SqlCommand() ocmd.CommandText = "UPDATE MainEvents SET EventsId=@DESIGID WHERE CourseID=@EID " ocmd.Parameters.AddWithValue("@EID", EID) ocmd.Parameters.AddWithValue("@DESIGID", DESIGID) ocmd.Connection = oconn ocmd.ExecuteNonQuery() gv1.EditIndex = -1 BindGrid() End Sub Protected Sub gv1_RowCancelingEdit(ByVal sender As Object, ByVal e As GridViewCancelEditEventArgs) gv1.EditIndex = -1 BindGrid() End Sub Public Sub PopulateDates() Dim cmd As New SqlCommand("Select EventsId, eventName from tblEvents order by location asc", New SqlConnection(ConfigurationManager.ConnectionStrings("DBConnectionString").ConnectionString)) cmd.Connection.Open() ddlEvents.Items.Clear() Dim ddlValues As SqlDataReader ddlValues = cmd.ExecuteReader() ddlEvents.DataSource = ddlValues ddlEvents.DataValueField = "EventsId" ddlEvents.DataTextField = "EventName" ddlEvents.DataBind() cmd.Connection.Close() cmd.Connection.Dispose() End Sub Protected Sub GridView1_RowUpdated(ByVal sender As Object, ByVal e As GridViewUpdatedEventArgs) ' Indicate whether the update operation succeeded. If e.Exception Is Nothing Then Dim index As Integer = gv1.EditIndex Dim row As GridViewRow = gv1.Rows(index) Message.Text = "Row updated successfully'!" Else e.ExceptionHandled = True Message.Text = e.Exception.Message End If End Sub End Class
Improve this question

2 Answers 2

Reset to default
0

To answer your question directly, take out the addHandler code in BindGrid(). Add parameters to BindGrid() required for selected index changed event, and wire up the event in markup:

 <asp:DropDownList ID="ddlEvents" runat="server" AppendDataBoundItems="true" AutoPostBack="true" OnSelectedIndexChanged="BindGrid"> <asp:ListItem Value="0">Choose Location</asp:ListItem> </asp:DropDownList> Public Sub BindGrid(ByVal Sender As Object, ByVal e As EventArgs) Dim oconn As New SqlConnection(sqlconn) oconn.Open() Dim ocmd As New SqlCommand("select e.eventsId,e.Location, dbo.fnFormatDate(t.trainingDates, 'MON/DD/YYYY') t.eventDates, t.eventTime,t.eventDescription from tblEvents e, tblEventgDates t where e.eventid = t.eventid and e.eventid = " & ddlEvents.SelectedValue, oconn) Dim oda As New SqlDataAdapter(ocmd) Dim builder As New SqlCommandBuilder(oda) Dim ds As New DataSet() oda.Fill(ds) gv1.DataSource = ds gv1.DataBind() End Sub

However, I would like to propose a more simple solution. You do not need to handle SelectedIndexChanged at all in order to use it as a query parameter. Mark it up like this:

<asp:DropDownList ID="ddlEvents" runat="server" AppendDataBoundItems="true" AutoPostBack="true" > <asp:ListItem Value="0">Choose Location</asp:ListItem> </asp:DropDownList>

Markup your grid like this with a DataSourceID referring to a SqlDataSource (shown below):

<asp:GridView ID="gridView1" runat="server" DataSourceID="GridSqlSrc"> </asp:GridView>

And finally create a SqlDataSource for your grid and configure it with a parameter (prevents SQL Injection attacks) that automatically pulls from your DropDownList:

<asp:SqlDataSource runat="server" SelectCommand="SELECT e.eventsId, e.Location, fnFormatDate(t.trainingDates, 'MON/DD/YYYY') t.eventDates, t.eventTime,t.eventDescription from tblEvents e, tblEventgDates t where e.eventid = t.eventid and e.eventid = @eventId)"> <SelectParameters> <asp:ControlParameter ControlID="ddlEvents" PropertyName="SelectedValue" Name="@eventId" /> </SelectParameters>

Now when a user selects from the DropDownList, the grid automatically re-queries and binds data.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

thanks for the proposed solutions. I like the first solution but it doesn't work. First, I select an option from the dropdownlist, and it does not display records associated with it. Second, when I select an option, it immediately goes back to the first option on the list. I would have preferred this option because using the second solution would create the duplicate binding datasource error since I am already binding from codebehind.
You must have other code interfering. Can you post your entire code-behind?
sure. I have attached it above but long though. Thanks a lot
I can see right off that you didn't add the required event handler parameters to BindGrid. In my example I show the use of 'sender' and 'e'.
I knew it had to be me! But then, how do I pass Bindgrid to pageLoad() event and other subs that need it?
|
0

I have solved this problem and would like to share it in case it helps someone else in the future.

Added onselectedindexchanged to the dropdownlist

 <asp:DropDownList ID="ddlEvents" runat="server" AutoPostBack="True" Width="140px" onselectedindexchanged="ddlEvents_SelectedIndexChanged" > <asp:ListItem Value="0">Choose Location</asp:ListItem> </asp:DropDownList>

Created the SelectedIndexChange sub and called BindGrid sub from there:

 Protected Sub ddlEvents_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) BindGrid() End Sub

And it solved my problem.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.