Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

PL/SQL Question

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
adskarcox
Asked:
adskarcox
  • 5
  • 4
  • 4
2 Solutions
 
slightwv (䄆 Netminder) Commented:
>>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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
or use this

"(SubStr(UPPER(FIRSTNAME), 1, length(:firstN))=:firstN) AND " +
"(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
0
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.

 
adskarcoxAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
adskarcoxAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
adskarcoxAuthor Commented:
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
 
adskarcoxAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now