Move to next record in a repeat region (displaying 1 record/page) on redirect after insert record

I am using dreamweaver and i have a repeat region displaying 1 record per page.  It is a question and answer set up.   When the user submits the answer, i need to return the user to the same page but to the next record in the dataset.  
wharris26Asked:
Who is Participating?
 
Jason C. LevineConnect With a Mentor No oneCommented:
Hi wharris,

The easiest way to do this is with two recordsets.  The first recordset is one that calls the data to be edited for the page like normal.  It probably looks something like this:

SELECT * FROM YourTable WHERE YourID = %s

and the %s is whatever you are feeding the recordset to filter it.

The second recordset takes that same basic data, but changes the WHERE clause a bit and also throws in an ORDER BY and a LIMIT

SELECT YourID FROM YourTable WHERE YourID > %s ORDER BY YourID ASC LIMIT 1

So what we are doing here is taking the current record's ID (the %s) and finding the next ID in the sequence by doing a greater than comparison and an ascending sort.  The LIMIT 1 ensures the next record is the only one in the recordset.

Finally, you need to alter the redirect in recordset one so that it uses the value of recordset 2 to filter the next page.  The tricky part is that you should define recordset 2 first in the code so you have the value available when you define recordset 1.

0
 
wharris26Author Commented:
So we are actually sql programming the page versus coding into the asp or some dreamweaver trick?
So to further describe my situation, the first question would be fed by a link passing a static value of question 0 which the code to the 2nd recordset would add one and then on each redirect/reload of the page it would add 1 to that question number?  
0
 
Jason C. LevineNo oneCommented:
>> So we are actually sql programming the page versus coding into the asp or some dreamweaver trick?

Sort of.  You are using SQL to get the ID of the next record in the sequence and then putting that stuff into the Dreamweaver code.  But this isn't anything specific to DW...it's just a technique that you can use to get the next ID number.

>> So to further describe my situation, the first question would be fed by a link passing a static value of
>> question 0 which the code to the 2nd recordset would add one and then on each redirect/reload of the
>> page it would add 1 to that question number?  

That's not a description, it's a question.

If your table's primary key sequence is always and forever going to be:

1,2,3,4,5,6,7,8,etc

Then you can just add 1 to current value to get the next one.  But typically, tables don't stay in this sequence always and forever.  Eventually, people need to delete rows so just adding 1 won't work.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
wharris26Author Commented:
OK let me be more descriptive.
 This is a multiple choice page which loads an assignment with questions(1 through whatever) from the assignment table.    The answers are inserted into another table called answers with student_id and date answered.   I have already coded the page using dreamweaver to have a dynamic repeat region which shows 1 record per page.  On insert it returns to the same record in the index(repeat region in dreamweaver)  and the student has to click to next record in the set.  

So the progression will always be question 1, 2, 3, 4....  on question number however I think the way DW codes it, it assigns each in an index for the repeat region which I would love to manipulate on returning to the page by adding 1 to that index.  The sql i have written in the dreamweaver format orders the questions of the assignment sequentially ascending on question number.

This issue I am having is that I am an occupational therapist and a director of a school I started for children with physical disabilities and decreasing the number of clicks and moving the mouse pointer is essential.    Please be patient with me, this is all self learned, not a pro, learned all on my own.  Thanks.
0
 
Jason C. LevineNo oneCommented:
>> have already coded the page using dreamweaver to have a dynamic repeat region which shows 1 record
>> per page.

Eh?  A repeat region is used to show multiple records per page.  If you are using it in conjunction with a single-record recordset, it's overkill.  Are you are using it to show one record with forward/back navigation?  If so, how do you prevent people from clicking through to the next item without submitting the form first?

>> On insert it returns to the same record in the index(repeat region in dreamweaver)  and the student has
>> to click to next record in the set.  

Okay, you want to change that so that after insert the page loads the next record.  That's what the double-recordset solution above does.  I assume you are using URL parameters (something.php?id=X) to filter the recordset?

>> I think the way DW codes it, it assigns each in an index for the repeat region which I would love to
>> manipulate on returning to the page by adding 1 to that index

Depends on the recordset code.  What should be happening is you do not use the Repeat Region as noted above...

Can you show me a link to the page or source code?
0
 
wharris26Author Commented:
Are you are using it to show one record with forward/back navigation?
-----------------Yes that is how I have initially set it up.
 If so, how do you prevent people from clicking through to the next item without submitting the form first?
 ------------ And ,  I don't to this point.

I was trying to do it without recoding the page in place but I guess I will try your method seeing as DW does not seem to account for such a set up
0
 
Jason C. LevineNo oneCommented:
>> I was trying to do it without recoding the page in place. I will try your method seeing as DW does not
>> seem to account for such a set up

It's not a DW thing, it's just not great design combined with limited built-in DW capability.  I suppose you could tie yourIf the goal is to not allow the user to progress until they have completed the form, putting navigation that allows them to progress without completing the form is self-defeating.

There are paid extensions for DW that allow you to specify a more complicated redirect value that uses dynamic data.  But it's not necessary to buy it to get the relatively simple functionality you are after.

The code should flow the following way

1st recordset on the page = takes the next value in the DB from the currently displayed value

2nd recordset on the page = current value displayed by page/form

Insert/Update behavior = DW default, but the redirect loop needs to be authored to echo the value from the first recordset in the URL.

0
 
wharris26Author Commented:
Is your asc limit 1 MySQL?   I am getting a syntax error.  I am working on a sql server 2008.
0
 
Jason C. LevineNo oneCommented:
Yes, I use MySQL.  You'll need to translate syntax to MS SQL as needed.
0
 
wharris26Author Commented:
OK there is no asc limit  in MSSQL and sql coding it becomes complex series of using a row count fucntion or something or other.  SO, I tried something that I think might work.
I made a view that pulls up the ASSIGNMENT ID,  QUESTION ID, AND I MADE AN EXPRESSION SUM THE QUESTION ID AND ADD 1.  so i set up the first recordset on the page to filter by the two parameters and it works when I test the dataset so I think it will work but there is a problem with the redirect.  
I think the parameters previously passed to the page to originally navigate there are hanging because it is not taking passing the newly established QUESTION ID value.  
0
 
Jason C. LevineNo oneCommented:
ASC LIMIT is not what I wrote:

SELECT YourID FROM YourTable WHERE YourID > %s ORDER BY YourID ASC LIMIT 1

The two clauses are ORDER BY YourID ASC (for ascending) and LIMIT 1 (return one row only).  Don't know if that helps you translate at all, perhaps just:

SELECT YourID FROM YourTable WHERE YourID > %s ORDER BY YourID LIMIT 1

>> QUESTION ID AND ADD 1

It will work so long as nothing ever gets deleted.

>> it is not taking passing the newly established QUESTION ID value.  

You have to manually code the new value into DW's redirect statement...
0
 
wharris26Author Commented:
yes, I used the entirety of what you suggested, ORDER BY xxx asc limit 1.  However that particular limit function is not available in MSSQL.  Another gaff by microsoft.

The question ID won't be deleted, it will go with an entire assignment.  Each assignment having questions 1,2,3 throughout whatever.  So it should function as such.

I did recode the redirect in the insert statement to include the recordset with the Q_ID increased by 1 but I think the page is adding those values to the initial querystring values that were sent and cause a datatype mismatch error when returning to the page.   It seem holding on to the previous querystring values.  

0
 
Jason C. LevineNo oneCommented:
>> It seem holding on to the previous querystring values.  

Show the code?
0
 
wharris26Author Commented:
Thank you.  I will repost this problem as a seperate ASP question.
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.