Solved

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

Posted on 2010-11-18
14
443 Views
Last Modified: 2012-05-10
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.  
0
Comment
Question by:wharris26
  • 7
  • 7
14 Comments
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 500 total points
ID: 34170233
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
 

Author Comment

by:wharris26
ID: 34172117
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34181925
>> 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
 

Author Comment

by:wharris26
ID: 34183407
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34184134
>> 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
 

Author Comment

by:wharris26
ID: 34184256
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34184402
>> 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:wharris26
ID: 34205945
Is your asc limit 1 MySQL?   I am getting a syntax error.  I am working on a sql server 2008.
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34210495
Yes, I use MySQL.  You'll need to translate syntax to MS SQL as needed.
0
 

Author Comment

by:wharris26
ID: 34215371
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34215726
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
 

Author Comment

by:wharris26
ID: 34218380
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
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 34223682
>> It seem holding on to the previous querystring values.  

Show the code?
0
 

Author Comment

by:wharris26
ID: 34227429
Thank you.  I will repost this problem as a seperate ASP question.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
This article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now