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

Strange problem

Ok, I have this ASP.NET application I am making.

the main code is the updateTable(), this function populates a Table web control with rows of data, the last cell being a button, this button is attached an attribute to identify the row. A click event is attached to this button. The click event of this button fires the updateTable() again, using the attached attribute as the argument (sort of like a file structure, and the button says, enter this directory so it passes the directory name of that row, and reloads the table with the dir/files of that directory.

Now here is the problem, if I have my initial set root directory and updateTable(root dir) in the page_load, the click event fires correctly, but of course the updateTable gets called twice (which works as the click event is after the first one), but when you try to drill down a second time, it fouls it up because the path gets reset to the begining, and the new path doesn't exist there.

What I am trying to do is this.

Page_Load()

If (!Page.PostBack)
updateTable(Default Path)

Then have my button click event do

updateTable(myButton.Attributes["newDir"].ToString();

But with out the updateTable event firing on Page_Load, the click event does not get handled.

Hopefully this makes sense, if need more info, let me know.I been playing around with this problem for a bit tonight. And I made some changes.

I made the button click event simple set Session["currentDir"] and changed the Page_Load to call updateTable(Session["currentDir"].ToString(); and in the !Page.PostBack clause, I have it setting Session["currentDir"] to the application parameter setting for "rootDir" I have.

Now everything works, but... You got to click twice on all the buttons and I can't seem to figure out how to avoid that. The problem appears to be the click event doesn't fire until after the page is reloaded so the first time it has the last directory set, and changes the session variable, but will only update the table when you click it yet again.
It's a timing issue. You have to consider the fact that events are called after Load and before PreRender. Thus, if you make changes to the data from an event, you need to rebind the controls that depend on this data after you've made the change. Otherwise, the change will only be visible on the next postback (hence the "double click").
By the way, it's not a very good idea to use Session in this context.
Does this help?
How do you rebind the control, you mean attach the button handler to it again?

I tried doing it so that button event called the updateTable() function, but that didn't work unless the event fired in the beginning of page load, so I changed it to a session variable see if that helped. It make the thing work, but didn't solve the problem as now I have to click twice.
No, WhateverControl.DataBind();

The structure of your page should more or less be:

Init: create controls if necessary.
Load: read datasources and bind controls if not ispostback
Events: update data sources according to the event and rebind relevant controls.