Output resultset to a seperate page each time

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>"))))));
}

%>


acslaterAsked:
Who is Participating?
 
TimYatesCommented:
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
 
TimYatesCommented:
Change:

while (rs50.next ())

to

if (rs50.next ())

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

Tim
0
 
acslaterAuthor Commented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
TimYatesCommented:
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
 
acslaterAuthor Commented:
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
 
acslaterAuthor Commented:
Any ideas Tim
0
 
TimYatesCommented:
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
 
acslaterAuthor Commented:
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
 
TimYatesCommented:
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
 
TimYatesCommented:
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
 
acslaterAuthor Commented:
>>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
 
acslaterAuthor Commented:
Thanks that works!!!
0
 
TimYatesCommented:
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
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.

All Courses

From novice to tech pro — start learning today.