Use query string as a variable for a column in a SQL query on asp.net

How can i usa a variable to select a column in my SQL statement from a query string?
Im using asp.net VB
I will declare the state from a query string "Pricing_state"
for example

SELECT   customer_id, @Pricing_state
FROM sometable
Where (customer_id = '111111')
LVL 6
phoenixfire425Asked:
Who is Participating?
 
phoenixfire425Connect With a Mentor Author Commented:
Well i think i got it to work but i am using this case statement.

CASE WHEN @state = 'AK' THEN AK WHEN @state = 'AL' THEN AL WHEN @state = 'AR' THEN AR WHEN @state = 'AZ' THEN AZ WHEN @state = 'CA1' THEN CA1 WHEN @state = 'CA2' THEN CA2 WHEN @state = 'CO' THEN CO WHEN @state = 'CT' THEN CT WHEN @state = 'DC' THEN DC WHEN @state = 'DE' THEN DE WHEN @state = 'FL' THEN FL WHEN @state = 'GA' THEN GA WHEN @state = 'HI' THEN HI WHEN @state = 'IA' THEN IA WHEN @state = 'ID' THEN ID WHEN @state = 'IL' THEN IL WHEN @state = 'IN' THEN [IN] WHEN @state = 'KS' THEN KS WHEN @state = 'KY' THEN KY WHEN @state = 'LA' THEN LA WHEN @state = 'MA' THEN MA WHEN @state = 'MD' THEN MD WHEN @state = 'ME' THEN ME WHEN @state = 'MI' THEN MI WHEN @state = 'MN' THEN MN WHEN @state = 'MO1' THEN MO1 WHEN @state = 'MO2' THEN MO2 WHEN @state = 'MS' THEN MS WHEN @state = 'MT' THEN MT WHEN @state = 'NC' THEN NC WHEN @state = 'ND' THEN ND WHEN @state = 'NE' THEN NE WHEN @state = 'NH' THEN NH WHEN @state = 'NJ' THEN NJ WHEN @state = 'NM' THEN NM WHEN @state = 'NV' THEN NV WHEN @state = 'NY1' THEN NY1 WHEN @state = 'NY2' THEN NY2 WHEN @state = 'NY3' THEN NY3 WHEN @state = 'OH' THEN OH WHEN @state = 'OK' THEN OK WHEN @state = 'OR' THEN [OR] WHEN @state = 'PA' THEN PA WHEN @state = 'PR' THEN PR WHEN @state = 'RI' THEN RI WHEN @state = 'SC' THEN SC WHEN @state = 'SD' THEN SD WHEN @state = 'TN' THEN TN WHEN @state = 'TX' THEN TX WHEN @state = 'UT' THEN UT WHEN @state = 'VA' THEN VA WHEN @state = 'VT' THEN VT WHEN @state = 'WA' THEN WA WHEN @state = 'WI' THEN WI WHEN @state = 'WV' THEN WV WHEN @state = 'WY' THEN WY ELSE '' END

Open in new window

0
 
brad2575Commented:
you would just build a string for the SQL query like this (not as familar with .net so syntax may be incorrect but logic is the same)

SQLVariable = "SELECT  customer_id, " & @Pricing_state & " & _
"FROM sometable Where (customer_id = '111111') "

are you running this from SQL?  or ASP.NET?  IF from SQL you would do it like this:

DECLARE @SQL as varchar(8000)

Set @SQL = 'SELECT  customer_id, ' +  @Pricing_state + '
    FROM sometable Where (customer_id = ''111111'') '

EXEC(@SQL)
0
 
phoenixfire425Author Commented:
I am running this from  ASP.net

Attached is my exact query


SELECT     CLIA_CPT.Procedure_type, CLIA_item_List.part_no, CLIA_item_List.manuf, CLIA_item_List.qty_box, CLIA_item_List.cpt, 
                      @Pricing_state AS Reimbursement, CLIA_item_List.std_cost, CLIA_item_List.item_desc, 
                      CLIA_item_List.std_cost * CASE WHEN active = 'Custom' THEN isnull(change, 0) + @percent ELSE isnull(suggested_margin, 0) 
                      + @percent END + CLIA_item_List.std_cost AS price, (CLIA_item_List.std_cost * (CASE WHEN active = 'Custom' THEN isnull(change, 0) 
                      + @percent ELSE isnull(suggested_margin, 0) + @percent END) + CLIA_item_List.std_cost) / CLIA_item_List.qty_box AS [price per test], 
                      @Pricing_state - (CLIA_item_List.std_cost * (CASE WHEN active = 'Custom' THEN isnull(change, 0) + @percent ELSE isnull(suggested_margin, 0) 
                      + @percent END) + CLIA_item_List.std_cost) / CLIA_item_List.qty_box AS [Rev Per Test], 
                      CASE WHEN @pics = 'off' THEN 'http://clia.diamedusa.com/clia/images/imageoff.gif' ELSE image_loc END AS image_loc
FROM         CLIA_item_List INNER JOIN
                      CLIA_CPT ON CLIA_item_List.cpt = CLIA_CPT.CPT_CODE FULL OUTER JOIN
                          (SELECT     ITEM_UID, [User], change, UID, active
                            FROM          prefs
                            WHERE      ([User] = @user) AND (active = 'custom')) AS prefs_1 ON CLIA_item_List.UID = prefs_1.ITEM_UID
WHERE     (CLIA_CPT.Procedure_type LIKE @Procedure_type)
ORDER BY CLIA_CPT.Procedure_type, CLIA_item_List.part_no

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
phoenixfire425Author Commented:
This is what my current asp.net connection string looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" 
                ConnectionString="<%$ ConnectionStrings:Custom_WebsConnectionString %>" 
                
                        
                        SelectCommand="SELECT CLIA_CPT.Procedure_type, CLIA_item_List.part_no, CLIA_item_List.manuf, CLIA_item_List.qty_box, CLIA_item_List.cpt, @Pricing_state AS Reimbursement, CLIA_item_List.std_cost, CLIA_item_List.item_desc, CLIA_item_List.std_cost * CASE WHEN active = 'Custom' THEN isnull(change , 0) + @percent ELSE isnull(suggested_margin , 0) + @percent END + CLIA_item_List.std_cost AS price, (CLIA_item_List.std_cost * (CASE WHEN active = 'Custom' THEN isnull(change , 0) + @percent ELSE isnull(suggested_margin , 0) + @percent END) + CLIA_item_List.std_cost) / CLIA_item_List.qty_box AS [price per test], @Pricing_state - (CLIA_item_List.std_cost * (CASE WHEN active = 'Custom' THEN isnull(change , 0) + @percent ELSE isnull(suggested_margin , 0) + @percent END) + CLIA_item_List.std_cost) / CLIA_item_List.qty_box AS [Rev Per Test], CASE WHEN @pics = 'off' THEN 'http://clia.diamedusa.com/clia/images/imageoff.gif' ELSE image_loc END AS image_loc FROM CLIA_item_List INNER JOIN CLIA_CPT ON CLIA_item_List.cpt = CLIA_CPT.CPT_CODE FULL OUTER JOIN (SELECT ITEM_UID, [User], change, UID, active FROM prefs WHERE ([User] = @user ) AND (active = 'custom')) AS prefs_1 ON CLIA_item_List.UID = prefs_1.ITEM_UID WHERE (CLIA_CPT.Procedure_type LIKE @Procedure_type) ORDER BY CLIA_CPT.Procedure_type, CLIA_item_List.part_no">
                <SelectParameters>
                    <asp:QueryStringParameter Name="Pricing_state" QueryStringField="state" />
                    <asp:QueryStringParameter Name="percent" QueryStringField="code" />
                    <asp:QueryStringParameter Name="pics" QueryStringField="pics" />
                    <asp:QueryStringParameter Name="user" QueryStringField="user" />
                    <asp:ControlParameter ControlID="DropDownList1" Name="Procedure_type" 
                        PropertyName="SelectedValue" />
                </SelectParameters>
            </asp:SqlDataSource>

Open in new window

0
 
brad2575Commented:
not that familar with .NET, sorry, but you should be able to update the SelectCommand from what you have above to (only the part that applies is here):
From:
.....CLIA_item_List.cpt, @Pricing_state AS Reimbursement, ...

to this:

....CLIA_item_List.cpt, " & request.QueryString("state") & " AS Reimbursement, ....

so you are dynamically building the SelectCommand instead of using the @variable.  You will have to update the code/syntax to properly retrieve the querystring variable using .NET code in the above example though as this is classic ASP code to get query string variables.
0
 
phoenixfire425Author Commented:
I am getting the error.

"The server tag is not well formed."
0
 
phoenixfire425Author Commented:
Basically what i am trying to do is this.

The table CLIA_CPT has 50 columns.
These columns are the 50 states or the USA. (ie  OH, MI, FL, ...)
each of these a number in them for each row.

What i am trying to do is select the State Column from a query string.
so if the query string = OH
then in the select statement the column OH is used.

Hope that helps.
0
 
brad2575Commented:
did you change this line to use .net code/syntax?

....CLIA_item_List.cpt, " & request.QueryString("state") & " AS Reimbursement, ....

I apologize but I do not know it.
0
 
phoenixfire425Author Commented:
Well i am not exactly sure how to change that over..
0
 
brad2575Commented:
googled it, this should work



....CLIA_item_List.cpt, " & Request.QueryString.GetValues("state")(1 - 1) & " AS Reimbursement, ....
0
 
phoenixfire425Author Commented:
Nope.
Still get the same error.
0
 
brad2575Commented:
sorry had syntax wrong try

....CLIA_item_List.cpt, " + Request.QueryString.GetValues("state")(1 - 1) + " AS Reimbursement, ....

or

....CLIA_item_List.cpt, " + Request"state") + " AS Reimbursement, ....
0
 
brad2575Commented:
The above code you posted will work but it will take a LOT more processing power/time to run.
0
 
phoenixfire425Author Commented:
i tried and i get the same thing.
"The server tag is not well formed."
0
 
brad2575Commented:
sorry this was formatted incorrect:

...CLIA_item_List.cpt, " + Request"state") + " AS Reimbursement, ....

change that to

...CLIA_item_List.cpt, " + Request("state") + " AS Reimbursement, ....
0
 
phoenixfire425Author Commented:
Im sorry that still did not work i think i am sticking with the long case statement.

Thanks for trying though
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.