Solved

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

Posted on 2008-10-30
16
472 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
  • 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
 
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now