Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-10-30
16
Medium Priority
?
490 Views
Last Modified: 2013-11-07
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')
0
Comment
Question by:phoenixfire425
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
16 Comments
 
LVL 16

Expert Comment

by:brad2575
ID: 22843832
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
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22843905
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
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22843956
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
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 16

Expert Comment

by:brad2575
ID: 22844028
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
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22844081
I am getting the error.

"The server tag is not well formed."
0
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22844184
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22844416
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
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22844439
Well i am not exactly sure how to change that over..
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22844476
googled it, this should work



....CLIA_item_List.cpt, " & Request.QueryString.GetValues("state")(1 - 1) & " AS Reimbursement, ....
0
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22844509
Nope.
Still get the same error.
0
 
LVL 6

Accepted Solution

by:
phoenixfire425 earned 0 total points
ID: 22844725
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22851330
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
 
LVL 16

Expert Comment

by:brad2575
ID: 22851353
The above code you posted will work but it will take a LOT more processing power/time to run.
0
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22904742
i tried and i get the same thing.
"The server tag is not well formed."
0
 
LVL 16

Expert Comment

by:brad2575
ID: 22907234
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
 
LVL 6

Author Comment

by:phoenixfire425
ID: 22949927
Im sorry that still did not work i think i am sticking with the long case statement.

Thanks for trying though
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question