Avatar of C Williams
C Williams
Flag for United States of America 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.
<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') ">                       
                            <asp:ControlParameter ControlID='<%# gvIngredients.FindControl("lblIngred") %>' PropertyName="Text" Name="lblIngred" Type="String" />
                            <asp:SessionParameter Name="lblIngred" SessionField="lblIngred"  />

Open in new window

Visual Basic.NETASP.NET

Avatar of undefined
Last Comment
C Williams

8/22/2022 - Mon

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.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
C Williams

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.
Bob Learned

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

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 

Open in new window

C Williams

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?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Bob Learned

1) You would be using a Parameter, and not a ControlParameter.

2) Did you change the text from that example, for the SqlDataSource?
C Williams

So I should be using just an '?'.

No I didn't change the text from your example.
C Williams

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(ByVal sender As Object, ByVal e As GridViewRowEventArgs) Handles gvIngredients.RowDataBound
If e.Row.RowType = DataControlRowType.DataRow Then
            Dim itemNumberSrc As TextBox = e.Row.FindControl("txtIngred")
            Dim itemNumber As String = itemNumberSrc.Text

            neccoLocation.SelectCommand() = "SELECT DISTINCT in_locn_key FROM in_stock_tbl
            WHERE (in_locn_key NOT LIKE 'area%') AND (in_locn_qoh > '0')
            AND (in_item_key LIKE '" & itemNumber & "')"
End If
End Sub
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
C Williams

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.
Bob Learned

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.
C Williams

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck