Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

pass user input as part of oracle query

Posted on 2011-03-16
11
Medium Priority
?
318 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
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.

618 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