?
Solved

pass user input as part of oracle query

Posted on 2011-03-16
11
Medium Priority
?
307 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-
[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
  • 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

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

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

743 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