Solved

PL/SQL Question

Posted on 2012-03-09
13
422 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
  • 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 51

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 51

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 51

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 51

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

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.

Question has a verified solution.

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

Suggested Solutions

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

756 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