• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

pass user input as part of oracle query

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
-AAA-
Asked:
-AAA-
  • 6
  • 4
3 Solutions
 
ajexpertCommented:
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
 
-AAA-Author Commented:
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
 
ajexpertCommented:
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
The 14th Annual Expert Award Winners

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

 
-AAA-Author Commented:
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
 
ajexpertCommented:
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
 
-AAA-Author Commented:
When I execute your query I get many results returned featuring names with an 'A' in their name
0
 
ajexpertCommented:
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
 
awking00Commented:
See attached.
comment.txt
0
 
-AAA-Author Commented:
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
 
-AAA-Author Commented:
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
 
-AAA-Author Commented:
Came up with the correct query based on responses
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 6
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now