asked on How to use a gridview cell's value as a control parameter
I have an asp.net (VB) web app that has 7 columns. The 5th column has a seperate DataSource than the rest of the columns. I want to use the value from the first column as a ControlParamter (or SessionParamter if that works) in the SelectParameter of that column.
Is that Possible? Attached is partial code of what I've tried for my SelectParameters.
Is that Possible? Attached is partial code of what I've tried for my SelectParameters.
<asp:SqlDataSource ID="neccoLocation" runat="server" ConnectionString="<%$ ConnectionStrings:necco40 %>" SelectCommand="SELECT DISTINCT in_locn_key FROM in_stock_tbl WHERE gl_cmp_key = 'NE' AND in_whs_key = '12' AND (in_locn_key NOT IN ('warehouse', 'staging', 'shortage')) AND (in_locn_key NOT LIKE 'r%') AND (in_locn_key NOT LIKE 's%') AND (in_locn_key NOT LIKE 'q%') AND (in_locn_key NOT LIKE 'w%') AND (in_locn_key NOT LIKE '3%') AND (in_locn_key NOT LIKE '5%') AND (in_locn_key <> '67ship') AND (in_locn_key NOT LIKE 'area%') AND (in_locn_qoh > '0') AND (in_item_key = '@lblIngred') ">
<SelectParameters>
<asp:ControlParameter ControlID='<%# gvIngredients.FindControl("lblIngred") %>' PropertyName="Text" Name="lblIngred" Type="String" />
<asp:SessionParameter Name="lblIngred" SessionField="lblIngred" />
</SelectParameters>
</asp:SqlDataSource>
Visual Basic.NETASP.NET
Last Comment
C Williams
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Another possibility would be to use a regular parameter, and set the value in the SqlDataSource.Selecting event handler.
Accessing and Updating Data in ASP.NET 2.0: Examining the Data Source Control's Events
http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/112206-1.aspx
Accessing and Updating Data in ASP.NET 2.0: Examining the Data Source Control's Events
http://aspnet.4guysfromrolla.com/demos/printPage.aspx?path=/articles/112206-1.aspx
Protected Sub CategoriesDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles CategoriesDataSource.Selecting
'Set parameter value through e.Command.Parameters...
e.Command.Parameters("@Salary").Value = 50000
End Sub
ASKER
The important thing here is that each line will have a different value. Here is what I'm trying to do (just to make things a little clearer. In the example below, my last column is a dropdownlist, which uses a variable in the select statement...that variable is the value in col1.
Also, (TheLearnedOne) examples, I'm receiving an error about 'Handle clauses require withEvent variables'.
Col1 Col2 Col3
12 lineComment DropDownListValues using DataSource w/ variable 12 (from col1)
14 linecomment DropDownListValues using DataSource w/ variable 14 (from col1)
Secondly, in both of these examples, should I still be using the typical '@variableName' syntax in the actual select statement?
Also, (TheLearnedOne) examples, I'm receiving an error about 'Handle clauses require withEvent variables'.
Col1 Col2 Col3
12 lineComment DropDownListValues using DataSource w/ variable 12 (from col1)
14 linecomment DropDownListValues using DataSource w/ variable 14 (from col1)
Secondly, in both of these examples, should I still be using the typical '@variableName' syntax in the actual select statement?
1) You would be using a Parameter, and not a ControlParameter.
2) Did you change the text from that example, for the SqlDataSource?
2) Did you change the text from that example, for the SqlDataSource?
ASKER
So I should be using just an '?'.
No I didn't change the text from your example.
No I didn't change the text from your example.
ASKER
Alright, I've changed Carl's code a bit...instead of changing the parameter, I'm now just changing the whole select statement...but when I run the code, it runs the original select statement...how can I force the app to use the newly created select command?
Protected Sub gvIngredients_RowDataBound
If e.Row.RowType = DataControlRowType.DataRow
Dim itemNumberSrc As TextBox = e.Row.FindControl("txtIngr
Dim itemNumber As String = itemNumberSrc.Text
neccoLocation.SelectComman
WHERE (in_locn_key NOT LIKE 'area%') AND (in_locn_qoh > '0')
AND (in_item_key LIKE '" & itemNumber & "')"
End If
End Sub
Protected Sub gvIngredients_RowDataBound
If e.Row.RowType = DataControlRowType.DataRow
Dim itemNumberSrc As TextBox = e.Row.FindControl("txtIngr
Dim itemNumber As String = itemNumberSrc.Text
neccoLocation.SelectComman
WHERE (in_locn_key NOT LIKE 'area%') AND (in_locn_qoh > '0')
AND (in_item_key LIKE '" & itemNumber & "')"
End If
End Sub
ASKER
Disregard my last comment of forcing the new Select Command...I am using the code in my last post, but the issue that I have is that the code runs using the default select command first, then the variable from line one is used on line two, variable from line 2 is used on line 3, and so on.
I need to, obviously, stop that from happening.
I need to, obviously, stop that from happening.
You can have a named parameter in your SelectCommand, and then define an <asp:Parameter> with the same name, and then set the value in the Selecting event handler, as shown above.
ASKER
Carlnorrbom, you lead me in the right direction with your answer in using the RowDataBound functionality. I used this option, but changed the selectCommand on each iteration. The variable I wanted inserted into each newly created selectCommand was written to a hidden label, which I then call and insert that into the selectCommand. Not writting the variable to a hidden label, caused my selectCommand to be behind a step...meaning line one's value was being used in line 2.
Great suggestion, but I'm getting the following error message:
"The variable name '@lblIngred' has already been declared. Variable names must be unique within a query batch or stored procedure".
I'm trying to throw your code into a For Each Row loop to hopefully increment the variable name on each row, but that's been a little tricky. I'm going to keep trying...any other suggestions on why I'm getting this error and how to fix would be much appreciated.