strange postback/ SQL problem

I have a datagrid which populates from a SQLDataSource. The page is to default to filter the data as the user that is logged on but there is a drop down menu to select to filter by another user. The drop down menu is autopostback and I have the following code in my on page load event:

 Dim teamleader As String Dim currentyear As String = DateTime.Now.Year 'find the current year

 If Page.IsPostBack Then 'pickup the selected teamleader from drop down menu Dim placeholder As ContentPlaceHolder = CType(Master.FindControl("bodybox"), ContentPlaceHolder) Dim thelist As DropDownList = CType(placeholder.FindControl("dropdownlist1"), DropDownList) teamleader = thelist.Text.ToString 'default to logged in persons name Else teamleader = User.Identity.Name.ToString End If teamdata.SelectCommand = "SELECT username, name, holiday_entitlement_" & currentyear & " as holiday_entitlement, holiday_used_" & currentyear & " as holiday_used, holiday_entitlement_" & currentyear & " - holiday_used_" & currentyear & " AS holiday_left FROM holiday.reps WHERE (teamleader = '" & teamleader & "') AND (hasleft = 'N') ORDER BY name;"

The problem is that if the page isnt postback and the user logged in is "ch12" then I get a populated datagrid. If however the page is a postback the datagrid is showing the empty template. I have looked at the SQL the page is generating and as far as I see they are identical:

Not postback:

SELECT username,name, holiday_entitlement_2006as holiday_entitlement, holiday_used_2006as holiday_used, holiday_entitlement_2006 - holiday_used_2006AS holiday_leftFROM holiday.repsWHERE (teamleader ='ch12')AND (hasleft ='N')ORDER BY name;

Is postback:

SELECT username,name, holiday_entitlement_2006as holiday_entitlement, holiday_used_2006as holiday_used, holiday_entitlement_2006 - holiday_used_2006AS holiday_leftFROM holiday.repsWHERE (teamleader ='ch12')AND (hasleft ='N')ORDER BY name;

Any ideas why one datagrid is appearing empty and the other populated when the select command being generated appears to be identical?

Anyone have any idea on this at all?

Set the datasource to the Grid in the SelectedIndexChanged event of the dropdownlist. Make sure you populate the dropdownlist with PostBack check in the page_load.

page_load() {

if (!Page.IsPostBack) {
// load the dropdownlist
// assign datasource and bind grid -- default
}
}

selectedIndexChanged event of dropdownlist {
// assign datasource and bind grid
}

Thanks

Have worked out the problem but not the fix. I am correctly creating the new sqldatasource1.selectcommand on the postback however the datagrid is always displaying the results from the original page load.

How do I make the page discard the original results and re run the query on each page load (in VB)?

Many thanks

1<%@. Page Language="VB" MasterPageFile="~/loggedin/the.master" %>23<script runat="server">45Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs6Dim teamleader as string7Dim currentyearAs String = DateTime.Now.Year8If Not User.IsInRole("teamleader")Then9 Try10 Dim placeholderAs ContentPlaceHolder =CType(Master.FindControl("bodybox"), ContentPlaceHolder)11Dim thelistAs DropDownList =CType(placeholder.FindControl("dropdownlist1"), DropDownList)12 teamleader = thelist.Text.ToString'default to logged in persons name13If teamleader ="."Then teamleader = User.Identity.Name.ToString14Catch exAs Exception15 teamleader = User.Identity.Name.ToString16End Try17 Else18 teamleader = User.Identity.Name.ToString19End If2021 teamdata.SelectCommand ="SELECT username, name, holiday_entitlement_" & currentyear &" as holiday_entitlement, holiday_used_" & currentyear &" as holiday_used, holiday_entitlement_" & currentyear &" - holiday_used_" & currentyear &" AS holiday_left FROM holiday.reps WHERE (teamleader ='" & teamleader & "') AND (hasleft = 'N') ORDER BY name;"22 End Sub23</script>2425<asp:Content ID="Content1" ContentPlaceHolderID="bodybox" Runat="Server">2627 <asp:DropDownList ID="dropdownlist1" runat="server" DataSourceID="teamleaderlist" DataTextField="Username" DataValueField="Username" AutoPostBack="true" AppendDataBoundItems="True" >28 <asp:ListItem Selected="True" Value="." Text=""></asp:ListItem></asp:DropDownList>29 <asp:SqlDataSource ID="teamleaderlist" runat="server" ConnectionString="<%$ ConnectionStrings:holidaybookingConnectionString %>" ProviderName="<%$ ConnectionStrings:holidaybookingConnectionString.ProviderName %>"></asp:SqlDataSource>3031<asp:SqlDataSource ID="teamdata" runat="server" ConnectionString="<%$ ConnectionStrings:holidaybookingConnectionString %>" ProviderName="<%$ ConnectionStrings:holidaybookingConnectionString.ProviderName %>" ></asp:SqlDataSource>3233<asp:GridView ID="teamview" runat="server" AllowSorting="True"34 DataKeyNames="username" DataSourceID="teamdata" CellPadding="5" GridLines="None" HorizontalAlign="Center">35 <EmptyDataTemplate>36 There are currently no Reps to display37 </EmptyDataTemplate>38 </asp:GridView>39</asp:content>

Above is the code for the problem area - I havent posted the code generating the SQL for the dropdownmenu as it is rather long and convoluted and is working fine.

Post your code!

Thanks

DropDownList onselectedIndexchanged will cause postback and your page_load code executes everytime it is posted. So you are getting the same values. Add a postback check in the page_load. Where in the page_load you are binding the grid?? In the SelectIndexChanged event again you need to write the code for the selected userRole

5Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs
6Dim teamleader as string
7Dim currentyearAs String = DateTime.Now.Year
If Not Page.IsPostBack Then
8If Not User.IsInRole("teamleader")Then
9 Try
10 Dim placeholderAs ContentPlaceHolder =CType(Master.FindControl("bodybox"), ContentPlaceHolder)
11Dim thelistAs DropDownList =CType(placeholder.FindControl("dropdownlist1"), DropDownList)
12 teamleader = thelist.Text.ToString'default to logged in persons name
13If teamleader ="."Then teamleader = User.Identity.Name.ToString
14Catch exAs Exception
15 teamleader = User.Identity.Name.ToString
16End Try
17 Else
18 teamleader = User.Identity.Name.ToString
19End If
20
21 teamdata.SelectCommand ="SELECT username, name, holiday_entitlement_" & currentyear &" as holiday_entitlement, holiday_used_" & currentyear &" as holiday_used, holiday_entitlement_" & currentyear &" - holiday_used_" & currentyear &" AS holiday_left FROM holiday.reps WHERE (teamleader ='" & teamleader & "') AND (hasleft = 'N') ORDER BY name;"
End If

protected void DropdownList1_SelectedIndexChanged(object sender, EventArgs e)
{
6Dim teamleader as string
7Dim currentyearAs String = DateTime.Now.Year
8If Not User.IsInRole("teamleader")Then
9 Try
10 Dim placeholderAs ContentPlaceHolder =CType(Master.FindControl("bodybox"), ContentPlaceHolder)
11Dim thelistAs DropDownList =CType(placeholder.FindControl("dropdownlist1"), DropDownList)
12 teamleader = thelist.Text.ToString'default to logged in persons name
13If teamleader ="."Then teamleader = User.Identity.Name.ToString
14Catch exAs Exception
15 teamleader = User.Identity.Name.ToString
16End Try
17 Else
18 teamleader = User.Identity.Name.ToString
19End If
20
21 teamdata.SelectCommand ="SELECT username, name, holiday_entitlement_" & currentyear &" as holiday_entitlement, holiday_used_" & currentyear &" as holiday_used, holiday_entitlement_" & currentyear &" - holiday_used_" & currentyear &" AS holiday_left FROM holiday.reps WHERE (teamleader ='" & teamleader & "') AND (hasleft = 'N') ORDER BY name;"

}

Also try using the dropDownList.SelectedItem.Value rather the .Text when selecting values fromthe dropdownlist.

Thanks
Many thanks, all works great now