[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Output resultset to a seperate page each time

Posted on 2005-04-08
13
Medium Priority
?
281 Views
Last Modified: 2010-08-05
Hi i am trying to output a users details from the db to a page but i want only 1 users details per page

Here is the code i am using which produces all the users details underneath each other:

-----------------------------

<%
   

Statement  = connection.createStatement();
    ResultSet rs50 = Statement.executeQuery("select * from Member where  LetterFeedback = 'Yes' ");

    Statement  = connection.createStatement();

String query="UPDATE Member SET LetterFeedback = ' '  WHERE LetterFeedback ='Yes'";

System.out.println ( "<TT>" + query + "</TT>"  ) ;

Statement.execute( query);


<%
while (rs50.next ())
{
(rs50.getString("FirstName")+" "+ (rs50.getString("LastName") +", " +"<br>" +(rs50.getString("Address1") +", " +"<br>" +(rs50.getString("Address2")+", " +"<br>" +(rs50.getString("Town") +", " +"<br>" +(rs50.getString("County")+"."+"<br>"+"<br>"))))));
}

%>


0
Comment
Question by:acslater
  • 7
  • 6
13 Comments
 
LVL 35

Expert Comment

by:TimYates
ID: 13735684
Change:

while (rs50.next ())

to

if (rs50.next ())

and you will only get one user (the first one) :-)

Tim
0
 

Author Comment

by:acslater
ID: 13735747
Thanks i tryed that and it only gives the first user even when the page is refreshed, but ideally i want a seperate page for each resultset. What i want to do is get the users details if LetterFeedback is 'yes' then get there details and then set LetterFeedback to blank and i want to do this for each user.

Statement  = connection.createStatement();
    ResultSet rs50 = Statement.executeQuery("select * from Member where  LetterFeedback = 'Yes' ");

    Statement  = connection.createStatement();

String query="UPDATE Member SET LetterFeedback = ' '  WHERE LetterFeedback ='Yes'";
System.out.println ( "<TT>" + query + "</TT>"  ) ;

Statement.execute( query);


<%
if (rs50.next ())
{
(rs50.getString("FirstName")+" "+ (rs50.getString("LastName") +", " +"<br>" +(rs50.getString("Address1") +", " +"<br>" +(rs50.getString("Address2")+", " +"<br>" +(rs50.getString("Town") +", " +"<br>" +(rs50.getString("County")+"."+"<br>"+"<br>"))))));
}

%>


0
 
LVL 35

Expert Comment

by:TimYates
ID: 13735772
I'd probably load the users details into an array list, then loop through this list to get to each user...

Or have a page which shows ALL users, then you click on a user to see that user's specific details and change them...

Tim
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:acslater
ID: 13735813
Ok
what we are trying to do is produce letters that have a users personal details taken from the db and scores will be printed out as results calculated on data from another table in db.
Any suggests on the code we could use?
Thanks for sticking with me.
0
 

Author Comment

by:acslater
ID: 13735979
Any ideas Tim
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13736140
So this doesn't really need any interaction at all?

Why can't you do:

while (rs50.next ())
{
    String firstName = rs50.getString("FirstName") ;
    String lastName = rs50.getString("LastName") ;
    ...etc...
    // Then print a letter:
    printLetter( firstName, lastName, ...etc... ) ;
}

or am I missing the point?
0
 

Author Comment

by:acslater
ID: 13736260
We want to produce what could be considered a mail merge letter, where the person has indicated in the registration that they would accept LetterFeedback to be "Yes".

The letter layout is very basic... name and address with a Dear ??? personal greeting.

From the results set we want to produce an individual letter for each user who has "yes"  that can be sent in a batch mailing.  We then want to set the Letterfeedback field to blank when all letters are done... at the moment it produces one letter and set ALL "yes" to blank, using this code:

----------

Statement  = connection.createStatement();
%>

<%
   

Statement  = connection.createStatement();
    ResultSet rs50 = Statement.executeQuery("select * from Member where  LetterFeedback = 'Yes' ");

    Statement  = connection.createStatement();

%>
<p>&nbsp;</p>
<p>   <!-- #BeginDate format:Am1 -->April 8, 2005<!-- #EndDate --> </p>
<p>&nbsp; </p>

<%
if(rs50.next ())
{
out.println(rs50.getString("FirstName")+" "+ (rs50.getString("LastName") +", " +"<br>" +(rs50.getString("Address1") +", " +"<br>" +(rs50.getString("Address2")+", " +"<br>" +(rs50.getString("Town") +", " +"<br>" +(rs50.getString("County")+"."+"<br>"+"<br>"))))));
}

while(rs50.next ())
{
String query="UPDATE Member SET LetterFeedback = ' '  WHERE LetterFeedback ='Yes'";
System.out.println ( "<TT>" + query + "</TT>"  ) ;

Statement.execute( query);
}

rs99= Statement.executeQuery("SELECT Total_Comp, Total_MM, Total_ACC, Total_AIC, Total_Co FROM Test_Results ");


%>
<p>&nbsp;</p>
<p></p>

<p>Dear </p>

<p>Congratulations you recently completing the psychometric assessment. </p>
<p>We are please to confirm your results below which are based on the answers
  given. </p>
<p>Results in here: </p>
<p>&nbsp;</p>
<p>We recommend that you research these course and subject areas further. </p>
<p>Yours sincerely, </p>
<p><strong><em>__________________ </em></strong></p>
<p><em><strong>Results Department </strong></em></p>
<p><strong>BCDTesting.com </strong></p>
</body>
</html>

<%
{
connection.close();
}
%>



0
 
LVL 35

Expert Comment

by:TimYates
ID: 13736319
so you just want a webpage for each letter?  Then you're going to print the webpage?

ok... do the users records have an ID?

what you can do is this (pseudocode)

----------------

<%
    rs1 = fetch list of users with LetterFeedback='Yes' ;
    if( rs1.next() ) ;
    {
        read user variables out (including ID)
        show letter
        set letter feedback = "" for user with THIS ID (just the one)
    }
   close rs1, connection, stmt, etc
%>

Then every time the page is refreshed, a new user will appear, get a letter shown, then set LetterFeedback to ""
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13736325
The usual way of doing this however, is to write a bean which loops through all the users and sends off emails for them all...
0
 

Author Comment

by:acslater
ID: 13736372
>>so you just want a webpage for each letter?  Then you're going to print the webpage?

Yes thats what i want.

Could you give me code using the code i gave you on how to do what your saying because i dont really get what your saying. Yes i do have userid which is username but i am  not printing the user id in the letter
0
 
LVL 35

Accepted Solution

by:
TimYates earned 1400 total points
ID: 13736422
So username is unique?  Ok then...something like this:

ResultSet rs50 = Statement.executeQuery("select * from Member where  LetterFeedback = 'Yes' ");
if(rs50.next ())
{
    String username = rs50.getString( "username" ) ;
    out.println(rs50.getString("FirstName")+" "+ (rs50.getString("LastName") +", " +"<br>" +(rs50.getString("Address1") +", " +"<br>" +(rs50.getString("Address2")+", " +"<br>" +(rs50.getString("Town") +", " +"<br>" +(rs50.getString("County")+"."+"<br>"+"<br>"))))));
    Statement.execute( "UPDATE Member SET LetterFeedback = ' ' WHERE username = '" + username + "'" ) ;
}

Then, pressing refresh will get the next one, and set their "LetterFeedback" to ""...until there are no more left...  when you'll get a blank page :-)
0
 

Author Comment

by:acslater
ID: 13736502
Thanks that works!!!
0
 
LVL 35

Expert Comment

by:TimYates
ID: 13736525
yay!

basically rather than stepping through the list, what it does is take the first item of the list, change it so it won't be in the list any more...

Then the next refresh takes the next item off the list...

Hope you understand what I mean...I'm confusing myself ;-)

Good luck!!

Tim
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

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

Strategic internal linking is often considered an SEO power technique, especially for content marketing. Do you need to hire an SEO agency to optimize you internal linking? No, this article will help you understand the basics of internal linking and…
Exchange administrators are always vigilant about Exchange crashes and disasters that are possible any time. It is quite essential to identify the symptoms of a possible Exchange issue and be prepared with a proper recovery plan. There are multiple…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Screencast - Getting to Know the Pipeline
Suggested Courses

829 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