adskarcox
asked on
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?
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());
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||'%')) " +
":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||'%')) " +
or use this
"(SubStr(UPPER(FIRSTNAME), 1, length(:firstN))=:firstN) AND " +
"(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
"(SubStr(UPPER(FIRSTNAME),
"(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
ASKER
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("fir stN", this.FirstNameTextBox.Text .ToString( ).ToUpper( ).Trim());
oraCmd.Parameters.Add("las tN", this.LastNameTextBox.Text. ToString() .ToUpper() .Trim());
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("fir
oraCmd.Parameters.Add("las
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.
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.
>>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.
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.
ASKER
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.
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.Tex t.ToString ().ToUpper ().Trim() = "", "%",this.FirstNameTextBox. Text.ToStr ing().ToUp per().Trim () )
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.Tex
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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("fir stN", (this.FirstNameTextBox.Tex t.ToString ().ToUpper ().Trim() == "" ? "%" : this.FirstNameTextBox.Text .ToString( ).ToUpper( ).Trim() ));
oraCmd.Parameters.Add("las tN", (this.LastNameTextBox.Text .ToString( ).ToUpper( ).Trim() == "" ? "%" : this.LastNameTextBox.Text. ToString() .ToUpper() .Trim() ));
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("fir
oraCmd.Parameters.Add("las
ASKER
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("fir stN", (this.FirstNameTextBox.Tex t.ToString ().ToUpper ().Trim() == "" ? "%" : this.FirstNameTextBox.Text .ToString( ).ToUpper( ).Trim() ));
oraCmd.Parameters.Add("las tN", (this.LastNameTextBox.Text .ToString( ).ToUpper( ).Trim() == "" ? "%" : this.LastNameTextBox.Text. ToString() .ToUpper() .Trim() ));
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("fir
oraCmd.Parameters.Add("las
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.AddWithV alue("firs tN", this.FirstNameTextBox.Text .ToString( ).ToUpper( ).Trim());
oraCmd.Parameters.AddWithV alue("last N", this.LastNameTextBox.Text. ToString() .ToUpper() .Trim());
string sqlstmt1 = "SELECT FIRSTNAME, MIDDLENAME, LASTNAME "+
"FROM MyTable " +
"WHERE " +
"(SubStr(UPPER(FIRSTNAME),
"(SubStr(UPPER(LASTNAME), 1, length(:lastN))=:lastN) " +
"ORDER BY LASTNAME, FIRSTNAME";
oraCmd.Parameters.AddWithV
oraCmd.Parameters.AddWithV
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.Tex