Solved

pass user input as part of oracle query

Posted on 2011-03-16
11
299 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 100 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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 400 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
making a message body variable from an oracle select statement 4 23
format dd/mm/yyyy parameter 16 30
Help with Oracle IF statment 5 23
Why use this lambda? 12 30
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

808 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