Solved

PL/SQL Question

Posted on 2012-03-09
13
425 Views
Last Modified: 2012-03-09
Hello,

I have an ASP.NET (C#.NET 4.0) search form that contains TextBoxes for first name and last name.  I have to query a Oracle database with PL/SQL.  Below is my current query.  If I comment out the ":lastN is null or..." line, then the query will work.  But if this line is left in the query, it does not work.  Any ideas?

string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        ":firstN is null or (UPPER(FIRSTNAME) LIKE :firstN) AND " +
        ":lastN is null or (UPPER(LASTNAME) LIKE :lastN) " +
        "ORDER BY LASTNAME, FIRSTNAME";

oraCmd.Parameters.Add("firstN", this.FirstNameTextBox.Text.ToString().ToUpper().Trim());
oraCmd.Parameters.Add("lastN", this.LastNameTextBox.Text.ToString().ToUpper().Trim());

Open in new window

0
Comment
Question by:adskarcox
[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
  • 5
  • 4
  • 4
13 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37702984
>>Oracle database with PL/SQL

PL/SQL is Oralce's Procedural Language.  What you have is plain old SQL.

>>Any ideas?

Even though you repeat the name for the bind variables, you need the same number or parameters for the number of bind variables.

I read somewhere once about 'named' bind variables with .Net and Oracle but not sure if you could re-use them.  I'll see if I can find that docuemnt.


Also, the is null doesn't make sense:

If the bind variable is null, doesn't matter to the database.

Change it to something like:

"(UPPER(FIRSTNAME) LIKE :firstN)"

Then if the textbox.value value="" pass in "%".

That is something like (my C# is rusty, in VB it would be IIF):


 (this.FirstNameTextBox.Text.ToString().ToUpper().Trim() = "", "%",this.FirstNameTextBox.Text.ToString().ToUpper().Trim() )
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37703010
try

        ":firstN is null or (UPPER(FIRSTNAME) LIKE :firstN) AND " +
        ":lastN is null or (UPPER(LASTNAME) LIKE :lastN) " +

>>>>

"((:firstN='') or (UPPER(FIRSTNAME) LIKE :firstN||'%')) AND " +
"((:lastN='') or (UPPER(LASTNAME) LIKE :lastN||'%')) " +
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37703032
or use this

"(SubStr(UPPER(FIRSTNAME), 1, length(:firstN))=:firstN) AND " +
"(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:adskarcox
ID: 37703035
HainKurt - that resulted in a "Not all variables bound exception."  Here is the code:


string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        "((:firstN='') or (UPPER(FIRSTNAME) LIKE :firstN||'%')) AND " +
        "((:lastN='') or (UPPER(LASTNAME) LIKE :lastN||'%')) " +
        "ORDER BY LASTNAME, FIRSTNAME";

oraCmd.Parameters.Add("firstN", this.FirstNameTextBox.Text.ToString().ToUpper().Trim());
oraCmd.Parameters.Add("lastN", this.LastNameTextBox.Text.ToString().ToUpper().Trim());
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37703037
I cannot find the exact reference buy this one talks about bind by name and bind by position.

http://oradim.blogspot.com/2007/08/odpnet-tip-bind-variables-and.html

Anyway, if you pass in the "%" if the textbox is blank, you only need the two parameters so it is really a non-issue.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37703039
>>HainKurt - that resulted in a "Not all variables bound exception."  Here is the code:

I already posted why this will not work...  You have 4 bind variables in the SQL and only 2 declared.

I also posted that the logic doesn't make sense.
0
 

Author Comment

by:adskarcox
ID: 37703044
HainKurt - both code samples resulted in a "Not all variables bound exception."   In the form, I tried filling in one textbox and both, but both tries resulted in the same exception.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37703052
Why are you not trying my suggestion?  I know it will work...

Change it to something like:

"(UPPER(FIRSTNAME) LIKE :firstN)"

Then if the textbox.value value="" pass in "%".

That is something like (my C# is rusty, in VB it would be IIF):


 (this.FirstNameTextBox.Text.ToString().ToUpper().Trim() = "", "%",this.FirstNameTextBox.Text.ToString().ToUpper().Trim() )
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 37703059
Full code:


string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        "UPPER(FIRSTNAME LIKE :firstN) AND " +
        "UPPER(LASTNAME LIKE :lastN) " +
        "ORDER BY LASTNAME, FIRSTNAME";

oraCmd.Parameters.Add("firstN",(this.FirstNameTextBox.Text.ToString().ToUpper().Trim() = "", "%",this.FirstNameTextBox.Text.ToString().ToUpper().Trim() ));
oraCmd.Parameters.Add("lastN", (this.LastNameTextBox.Text.ToString().ToUpper().Trim() = "", "%",this.LastNameTextBox.Text.ToString().ToUpper().Trim() ));
0
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 250 total points
ID: 37703094
fix for suggestion above by sligtwv

string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        "UPPER(FIRSTNAME) LIKE :firstN AND " +
        "UPPER(LASTNAME) LIKE :lastN " +
        "ORDER BY LASTNAME, FIRSTNAME";
0
 

Author Comment

by:adskarcox
ID: 37703095
slightwv:  I tried your solution and the exception wasn't as bad.  It threw a "missing right parenthesis" exception.  Code is below:


string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        "UPPER(FIRSTNAME LIKE :firstN) AND " +
        "UPPER(LASTNAME LIKE :lastN) " +
        "ORDER BY LASTNAME, FIRSTNAME";

oraCmd.Parameters.Add("firstN", (this.FirstNameTextBox.Text.ToString().ToUpper().Trim() == "" ? "%" : this.FirstNameTextBox.Text.ToString().ToUpper().Trim() ));
               
oraCmd.Parameters.Add("lastN", (this.LastNameTextBox.Text.ToString().ToUpper().Trim() == "" ? "%" : this.LastNameTextBox.Text.ToString().ToUpper().Trim() ));
0
 

Author Comment

by:adskarcox
ID: 37703118
Final working code is below.  Thanks guys!


string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        "UPPER(FIRSTNAME) LIKE :firstN AND " +
        "UPPER(LASTNAME) LIKE :lastN " +
        "ORDER BY LASTNAME, FIRSTNAME";

oraCmd.Parameters.Add("firstN", (this.FirstNameTextBox.Text.ToString().ToUpper().Trim() == "" ? "%" : this.FirstNameTextBox.Text.ToString().ToUpper().Trim() ));
               
oraCmd.Parameters.Add("lastN", (this.LastNameTextBox.Text.ToString().ToUpper().Trim() == "" ? "%" : this.LastNameTextBox.Text.ToString().ToUpper().Trim() ));
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 37703120
anyways, try this code (I used AddWithValue not Add!)

string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
        "FROM MyTable " +
        "WHERE " +
        "(SubStr(UPPER(FIRSTNAME), 1, length(:firstN))=:firstN) AND " +
        "(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
        "ORDER BY LASTNAME, FIRSTNAME";

oraCmd.Parameters.AddWithValue("firstN", this.FirstNameTextBox.Text.ToString().ToUpper().Trim());
oraCmd.Parameters.AddWithValue("lastN", this.LastNameTextBox.Text.ToString().ToUpper().Trim());
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

751 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