1

I have a dropdown (selProject) populated by a SQL table. The value is the id field, the name is the name field.

I have a gridview control fed by a sqldatasource.

What I want to do is to conditional where clause based on the selProject dropdown. I have spent 3 hours searching and trying to figure this out. I fear I am going to snap and kill a co-woker.

When I choose a specific value in the dropdown, the selectedValue of the control is "40". I verified this through debugging. This is correct and expected.

I have a controlParameter that looks like:

<asp:ControlParameter ControlID="selProject" PropertyName="SelectedValue" Name="ProjectIDPass" Type="Int32" />

The SelectCommand includes: (Batches.ProjectID = @ProjectIDPass) as part of the Where clause.

I get no rows, no errors, no problems. The issue I suspect is that the SelectedValue of the dropdown is a string and the Batches.ProjectID is an integer.

When I hard code 40 into the where clause in place of @ProjectIDPass, it works great. When I go back to the ControlParameter is bombs out. I have tried all manner of conversion techniques.

Is there any way to see the SqlDataSouce SelectCommand that was executed with actual values in place of the parameters.

C Palmer
  • 17
  • 6

2 Answers2

0

The issue I suspect is that the SelectedValue of the dropdown is a string and the Batches.ProjectID is an integer.

Doubtful - the Type property handles the conversion, and your RDBMS would as well (though it'd probably convert the int column to string, screwing your indexes).

Is there any way to see the SqlDataSouce SelectCommand that was executed with actual values in place of the parameters.

A RDBMS profiler like Sql Profiler would be the best way. But you can also hook the SqlDataSource.Selecting and SqlDataSource.Selected events and examine e.Command and e.Arguments.

Mark Brackett
  • 84,552
  • 17
  • 108
  • 152
  • That was tremendously helpful. The problem I have is that during the SqlDataSource.Selecting I am checking the values of the dropdowns selected.index and selected.value and the values are not set yet. So it is feeding values to the SQLDataSource that are invalid, hence pulling no rows. I am sure that this is a problem with the order things are loading on the page. In the contentPlaceHolder section, I am setting the value of the dropdown.selectedvalue from code from a master page variable. From the selecting event, the master page variable is not available yet. Any thoughts? – C Palmer Feb 25 '16 at 13:42
  • @CPalmer - you can rebind after setting the selectedvalue – Mark Brackett Feb 25 '16 at 13:55
  • Ok, I see that the initial databind is happening on page_init before any of the code that sets values on the dropdowns that control the sqldatasource parameters. GAH!! Any thoughts on a workaround? – C Palmer Feb 25 '16 at 14:15
  • Backing off a step and looking at this from a higher perspective. Ok so I am trying to set a cookie based on what the user last selected on 2 dropdowns. Read that cookie from the master page. Set the values of the 2 dropdowns, and then populate the gridview based on the values of the 2 dropdowns. But the gridview is populating before the dropdowns are set. So VB.Net is going to force the sqldatasource to populate, and then I need to force a repopulate, causing 2 queries run against the SQL Server per page load? – C Palmer Feb 25 '16 at 14:33
  • @CPalmer - ask a new Q w/your Init and SelectedValues code so we can see what's going on; odds are something like not having your initial DataBind in a !IsPostBack, or you've turned off ViewState, etc. Quick fix could be to cancel the first Selecting, or set it manually, or set your master page variable earlier...tough to say w/o code. – Mark Brackett Feb 25 '16 at 14:55
  • That is exactly the problem. There is no post back. The values from these two dropdowns follow the user from page to page. If they load this page, it reads the values from the cookie and fills the gridview. No PostBack. After pulling out my hair for a few, and doing some reading, I am starting to think that datasets are the way to go, as I can then force when the data is fed to the Gridview. – C Palmer Feb 25 '16 at 15:25
  • @CPalmer - lots of ways to skin the cat...CookieParameter sounds like it would work as well to set the query. But, yeah - in general, SqlDataSource and the like start to be more trouble than the worth as your requirements get more complex. – Mark Brackett Feb 25 '16 at 15:45
0

So I solved my issue, and it was a complete lack of understanding how GridViews are handled.

I wanted to load data based on values from 2 Drop Downs without doing a post back. This way, a user can go from page to page and not have to keep setting dropdowns. That isn't possible till the page is loaded, as the dropdowns are not set until the page load is nearly complete. Gridviews are populated on Page Init. I could use the cookies I was setting to set the data in SQL query, but if the user changes a drop down, the cookies are not reset with the new data until the page loaded, so I would be loading the wrong data.

I found a solution, which is to allow the page to load, then use the values of the boxes which are set properly at that point, and use them to query a dataset and asign it to the GridView. The trick to get it to actually display the data since the page is fully loaded at this point, is an update panel.

See this article for full description: http://www.aspsnippets.com/Articles/Bind-Load-GridView-after-Page-load-is-completed-using-AJAX-UpdatePanel-in-ASPNet.aspx

C Palmer
  • 17
  • 6