Need Logic to correct my code

hi experts,

Just need a simple logic here. Code is working but I added one more feature and its not working as it should be. Here is it:

JSP:
<input type="checkbox" name="lock" value="<%=rs.getString("UserID")%>"></td>


Java:
String[] Lock = request.getParameterValues("lock");
                  if (Lock != null && Lock.length != 0)
                  {
                        for (int i = 0; i < Lock.length; i++)
                        {
                              //Lock Update Query based on UserID                                                
                              stmt.executeQuery(lo);
                              System.out.println("Lock User: "+ Lock[i]);
                        }          
                  }                    


Now the feature I added in JSP is the check box's will be checked if DBvalue="Y" and unchecked if DBvalue ="N"

JSP
<input type="checkbox" <% if (rs.getString("USER_LOCKED").equals("Y")) {out.print("checked");  }%> name="lock" value="<%=rs.getString("UserID")%>"></td>


Java:
Need code modification here. Help !!
(user can submit all checked, so need to Update DB with "Y", user can uncheck few and check few so need to Update DB according... also NULL pointer exception issue)


Thx,
PH
princehyderabadAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mayank SAssociate Director - Product EngineeringCommented:
>> rs.getString("USER_LOCKED").equals("Y")

You might want to add a null-check there for the value that rs.getString ( "USER_LOCKED" ) returns.

>> also NULL pointer exception issue

If stmt is not null in: >> stmt.executeQuery(lo);

- then it should not happen. BTW, what is lo? Lock[i] is not being used in the query.
rama_krishna580Commented:
Hi,

>>>> <input type="checkbox" <% if (rs.getString("USER_LOCKED").equals("Y"))

<input type="checkbox" <% if (null != rs.getString("USER_LOCKED") && rs.getString("USER_LOCKED").equals("Y"))...

R.K
princehyderabadAuthor Commented:
TO: mayankeagle

>>You might want to add a null-check there for the value that rs.getString ( "USER_LOCKED" ) returns.
This value is comign from DB can't be null either Y or N.  Do I still need to check for null ?

>>what is lo? Lock[i] is not being used in the query.

String lo = "Update table set  Lock="Y"...... where userid="+Lock[i];

TO:rama_krishna580
what abt Java part, bascially I need help in Java code. When user select few more checkbox to lock user those need to update user_lock="Y" and if user deselect checkbox those user need to update user_lock="N".
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

Mayank SAssociate Director - Product EngineeringCommented:
>> This value is comign from DB can't be null either Y or N.  Do I still need to check for null ?

In that case, no.

>> String lo = "Update table set  Lock="Y"...... where userid="+Lock[i];

That should be written inside the for-loop. I don't see it there.
princehyderabadAuthor Commented:
>>That should be written inside the for-loop. I don't see it there.

Yeah that is in for-loop. I just quickly posted Q so didnt mentioned. But again my Java code is doing only setting Lock=Y, its not unlocking that is what I wanted.
By the way here is the actual java code:

String[] Lock = request.getParameterValues("lock");
               if (Lock != null && Lock.length != 0)
               {
                    for (int i = 0; i < Lock.length; i++)
                    {
                         //Lock Update Query based on UserID        
                         String lo = "Update table set  Lock="Y"...... where userid="+Lock[i];                              
                         stmt.executeQuery(lo);
                         System.out.println("Lock User: "+ Lock[i]);
                    }          
               }                  
Mayank SAssociate Director - Product EngineeringCommented:
You also need to do set lock = "N" for those users which are not in the Lock[] array I guess.
princehyderabadAuthor Commented:
That is what I'm trying to Question. How to deal with it ? What if condition and how should I go for it.

Mayank SAssociate Director - Product EngineeringCommented:
Do you want to do it for all users not in the current list?
princehyderabadAuthor Commented:
No that is not what case. Let me explain you in details with pictures, I hope it will be clear then.

DB Table

UserID    Name     User_Lock
001         aaa           N
002         bbb           N
003         ccc            Y
004         ddd            N


GUI Display:

--------------------------------------------
[] un\lock All             name       UserID
---------------------------------------------
[ ]                             aaa           001
[ ]                             bbb           002
[x]                             ccc           003
[ ]                            ddd            004


Now Admin logins and want to unlock userid 003, and may be want to lock userid 003. So he check 1st record, and uncheck 3rd record here in this case.
Then it call my Java code does operations...

New Updated DB Table

UserID    Name     User_Lock
001         aaa           Y
002         bbb           N
003         ccc            N
004         ddd            N


GUI Display:

--------------------------------------------
[] un\lock All             name       UserID
---------------------------------------------
[x]                             aaa           001
[ ]                             bbb           002
[ ]                             ccc           003
[ ]                            ddd            004

Similarly Admin, can lock all or unlock all etc., and perform operation as he like.
I need Java code. Half of coding is done if you can see my posted Question up.
princehyderabadAuthor Commented:
Sorry correction in this line:

Now Admin logins and want to unlock userid 003, and may be want to lock userid 001. So he check 1st record, and uncheck 3rd record here in this case.
Mayank SAssociate Director - Product EngineeringCommented:
I think that Lock[] will only contain user-names of those check-boxes which were selected. So you could perhaps shoot another query to the DB, like: "UPDATE table set Lock = 'N' WHERE User_ID NOT IN ('a', 'b', 'c') " ) ; Here, 'a', 'b', etc are values in the Lock[] array - you will need to loop through the array to create this query.

princehyderabadAuthor Commented:
>> I think that Lock[] will only contain user-names of those check-boxes which were selected.
Yeah its contains UserID bascially.

>>UPDATE table set Lock = 'N' WHERE User_ID NOT IN ('a', 'b', 'c') " )
Do I need to ?. Because by default all records are set to 'N' when created. Only when if user has changed from status 'Y' to 'N' (checked to unchecked) then may be your above update can be called.

Mayank SAssociate Director - Product EngineeringCommented:
Yes, but after that, if he wants to assign 'N' to something which had 'Y', you will need that.
princehyderabadAuthor Commented:
Okay agreed what you says. Can you help me in code please:-

String[] Lock = request.getParameterValues("lock");
               if (Lock != null && Lock.length != 0)
               {
                    for (int i = 0; i < Lock.length; i++)
                    {
                         //Lock Update Query based on UserID        
                         String lo = "Update table set  Lock="Y"...... where userid="+Lock[i];                              
                         stmt.executeQuery(lo);
                         System.out.println("Lock User: "+ Lock[i]);
                    }          
               }  

I need to modify above and add Lock="N'.
Mayank SAssociate Director - Product EngineeringCommented:
StringBuffer sb = new StringBuffer ( "UPDATE Table SET Lock = ''N' WHERE UserID IN ('" ) ;

for ( int i = 0 ; i < Lock.length ; i ++ )
{
  sb.append ( Lock[i] ) ;
  if ( i < Lock.length - 1 )
    sb.append ( "', " ) ;
}

sb.append ( "'" ) ;
String query = sb.toString () ;

Now execute this query using stmt.executeUpdate () ;

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
princehyderabadAuthor Commented:
Okay I think there were minor correction in code I did it, here is the correct code. BUT STILL IT DIDNT SOLVE MY SOLUTIONS.
==============
StringBuffer sb = new StringBuffer ("UPDATE USERS SET USER_LOCKED='N' WHERE USERID NOT IN ('");
if (Lock != null && Lock.length != 0)
{
for ( int i=0; i<Lock.length; i++ )
{
  sb.append (Lock[i]) ;
  if (i<Lock.length - 1)
    sb.append ("','") ;
}
}
sb.append ( "')" ) ;
String query = sb.toString () ;            
out.print(query);
=========

It didnt solve my soultions because I'm using Pagination to display records as records are in thousands. Let says 1st page has 10 records with 3 usered Locked="Y" and when I try to unlock one user, the query is unlocking this one person (plus) + its also unlocking Locked users which are in page 2nd to page 10th. I hope you got my point. NOW WHAT !!!
Mayank SAssociate Director - Product EngineeringCommented:
Oh well, didn't know about the pagination part. I guess you will have to maintain all the user-IDs in a hidden variable and before calling the servlet, the JSP should populate this hidden variable with the values of the user-IDs that were unchecked. Then you should shoot the above query for those.
princehyderabadAuthor Commented:
I can pass hidden variable but how to check or compare which are checked and which are unchecked.

I mean let say 10 records on page. 10 hidden variables. And say 3 checked box.
If I'm doing do while length of 10 hidden, I dont get proper values from those 3 checked box.
eg:
hidden[1] = checked[1]
hidden[2]=checedk[2]
hidden[3]=checked[3]
hidden[4] = nothing here
hidden[5] = nothing here...
....

But in real sense checked[2] would be 8th record of 10. and checked[3] would be 10th record.  I'm not getting it.  Let me know if my approache is wrong.
Mayank SAssociate Director - Product EngineeringCommented:
Don't make it so complicated. You can maintain one hidden variable which has all user-IDs (say comma-separated).
princehyderabadAuthor Commented:
How about this, let me know if its not correct approach:

Get all ID's in Hidden variables.
for (HiddenVariable.Length)
{
//make query string
}
Query1 = Assign all 10 ID with Lock=N
Get Check Box ID which are checked:
for(checkVariable.length)
{
//make query string
}
Query1=Assign Checked Variable ID with Lock=Y

Bascially you are runing 2 querys. First all records are set to 'N" and then I take checkbox which are not null and set to 'Y'.
Mayank SAssociate Director - Product EngineeringCommented:
You can do it either ways. Set those which are selected in the checkbox (which come from the request.getParameterValues ()) as 'Y' and the others (in the hidden varaible) can be set to 'N'. Yes - you require 2 queries for this or write a stored procedure which can do it in one shot by taking all values as parameters.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.