Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

PL/SQL Question

Posted on 2012-03-09
13
Medium Priority
?
430 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 78

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 61

Expert Comment

by:HainKurt
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 61

Expert Comment

by:HainKurt
ID: 37703032
or use this

"(SubStr(UPPER(FIRSTNAME), 1, length(:firstN))=:firstN) AND " +
"(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 78

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 78

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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 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 61

Assisted Solution

by:HainKurt
HainKurt earned 1000 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 61

Expert Comment

by:HainKurt
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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to recover a database from a user managed backup
Suggested Courses

572 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