Solved

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

Posted on 2008-10-30
16
488 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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