?
Solved

pass user input as part of oracle query

Posted on 2011-03-16
11
Medium Priority
?
328 Views
Last Modified: 2012-05-11
basically i have a web app that takes text from the user for a last name.  this data is in textbox1.  I then want to take this data and search for anything that matches the string within my oracle database.  below is what I have for my data connection and query and it is not working.
any ideas?

        <asp:SqlDataSource ID="oracleHRP" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
            SelectCommand="SELECT PERSON_ID, LAST_NAME, FIRST_NAME, BU_NAME, JOB_TITLE, PAY_LINKED, PRIMARY_APPT FROM HRREPORT.V_APPT_BASIC WHERE (PAY_LINKED = - 1) AND (PRIMARY_APPT = - 1) AND (LAST_NAME = '%&Name%') ORDER BY LAST_NAME, FIRST_NAME">
            
                        <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="Name" 
                    PropertyName="Text" Type="String" />

Open in new window

0
Comment
Question by:-AAA-
  • 6
  • 4
11 Comments
 
LVL 14

Expert Comment

by:ajexpert
ID: 35150523
Try This
<asp:SqlDataSource ID="oracleHRP" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
            ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" 
            SelectCommand="SELECT PERSON_ID, LAST_NAME, FIRST_NAME, BU_NAME, JOB_TITLE, PAY_LINKED, PRIMARY_APPT FROM HRREPORT.V_APPT_BASIC WHERE (PAY_LINKED = - 1) AND (PRIMARY_APPT = - 1) AND (LAST_NAME LIKE '%&Name%') ORDER BY LAST_NAME, FIRST_NAME">
            
                        <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="Name" 
                    PropertyName="Text" Type="String" />

Open in new window

0
 

Author Comment

by:-AAA-
ID: 35150557
Ahhh I'm very sorry ajexpert, I was fooling around with things and pasted a version where I was just trying to set the name even equal to a valid entry but originally I had the problem with the query as you constructed where:
(LAST_NAME LIKE '%&Name%')
Sorry for not having that in the original question, I will edit it to avoid any confusion.
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 400 total points
ID: 35150598
Ok, is your search case sensitive?

With LIKE operator your search shouldn't be case sensitive...

You can also try UPPER(LAST_NAME) and make sure you pass all &Name as upper case
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 

Author Comment

by:-AAA-
ID: 35150674
my search is not case sensitive and i wasnt even considering that at the momeny.  but for right now if i enter 'a' into my text box with:
(LAST_NAME LIKE '%&Name%')
I have 0 results.  I should be grabbing any name that has an 'a' in it.  Like wise if I search for a name with 'A' i should see any name with a A in it.  For now I get no results.
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35150930
Can you execute this query directly in DB to check if its return results
SELECT PERSON_ID, LAST_NAME, FIRST_NAME, BU_NAME, JOB_TITLE, PAY_LINKED, PRIMARY_APPT FROM HRREPORT.V_APPT_BASIC WHERE (PAY_LINKED = - 1) AND (PRIMARY_APPT = - 1) AND (LAST_NAME LIKE '%A%') ORDER BY LAST_NAME, FIRST_NAME

Open in new window

0
 

Author Comment

by:-AAA-
ID: 35150989
When I execute your query I get many results returned featuring names with an 'A' in their name
0
 
LVL 14

Expert Comment

by:ajexpert
ID: 35152912
I am not sure on ASP syntax but what I see is that you are using NAME in building query first      but declaring name parameter later.  Is this allowed?
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1600 total points
ID: 35156938
See attached.
comment.txt
0
 

Author Comment

by:-AAA-
ID: 35157184
ajexpert,  textbox1 is declared and has a value in it...that bit of code is just there so that we query can match up the field to the input data.  

awking00, i tried your ideas and looked a bit more online with no avail.  I'm thinking maybe the problem lies more within asp.net and not the query itself.
0
 

Assisted Solution

by:-AAA-
-AAA- earned 0 total points
ID: 35157556
ok i have the solution:
SelectCommand="SELECT PERSON_ID, LAST_NAME, FIRST_NAME, BU_NAME, JOB_TITLE, PAY_LINKED, PRIMARY_APPT FROM HRREPORT.V_APPT_BASIC WHERE (PAY_LINKED = - 1) AND (PRIMARY_APPT = - 1) AND (LOWER(LAST_NAME) LIKE LOWER('%' || :Name || '%')) ORDER BY LAST_NAME, FIRST_NAME">

The key part was: (LOWER(LAST_NAME) LIKE LOWER('%' || :Name || '%'))
Specifically: LIKE ('%' || :Name || '%')

Thanks for all your help!
0
 

Author Closing Comment

by:-AAA-
ID: 35187335
Came up with the correct query based on responses
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses
Course of the Month17 days, 12 hours left to enroll

831 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