Solved

Loop through a list in .ASP and post to MS Access

Posted on 2013-11-04
5
348 Views
Last Modified: 2013-11-19
I have a ul of text boxes. I want to loop through the list elements and post their values to an Access database. How can I do this in asp?  Here is my current attempt. rsAwards is my recordset, my connection object is named dataCon. I'm looking for a loop that will post all the values of the text boxes within the unordered list. As you can see the list is populated dynamical from the database.


Set dataCon = Server.CreateObject("ADODB.Connection")
Set rsAwards = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Awards"
rsAwards.Open strSQL, dataCon

<form name="sort_award" action="action_awards.asp?Action=sort&Award_ID=<%=Award_ID%>" method="post">

response.write "<ul id='sortable'>"
  dim i
  i=0

    While not rsAwards.EOF

      response.write "<li onclick='sort()'><input type='text' id='AwardNum' size='1' value="&i&" ></li>"

      rsAwards.MoveNext
      i=i+1

    Wend 
response.write "</ul></form>"

Open in new window

0
Comment
Question by:CDCAuman
  • 3
5 Comments
 
LVL 6

Expert Comment

by:Manuel Marienne-Duchêne
ID: 39621849
response.write "<li onclick='sort()'><input type='text' id='AwardNum' size='1' value=""" & i &""" ></li>"
0
 

Author Comment

by:CDCAuman
ID: 39621903
Thank you, manumd, for your response.

Unfortunately, that doesn't seem to result in a change.

FYI:
Here is my loop to post the data

    While not rsEditCourse2.EOF
        for i = 1 to 10
            rsEditCourse2.Fields("Award_Number")(i) = Request.form("AwardNumber")(i)
        i = i + 1
        next
    Wend
0
 

Author Comment

by:CDCAuman
ID: 39622313
Attached is a screenshot of the unordered list written in classic ASP.

The value of the textbox (left) reflects the order of the items within the list. The number in the list (right) is populated from a field inside the database.

Some code has been implemented to allow the user to reorder the list by dragging and dropping the list items. Currently, when this is done, the value inside the textbox (left) changes according to it's new position within the list.

What I would like is by clicking the form's submit button, the number (right) is updated in the database to reflect the new value of it's corresponding textbox (left).







Below is the ASP that creates the list dynamically


<form name="sort_award" action="action_awards.asp?Action=sort&Award_ID=<%=Award_ID%>" method="post">
     <%
 end if
 response.write "<ul id='sortable'>"
 dim i
 i=1

 While not rsAwards.EOF

 response.write "<li onclick='sort()'> &nbsp <input type='text' name='AwardNumber' id='" & rsAwards("Award_Number") & "' size='1' value="&i&" > &nbsp" & rsAwards("Award_Name")
 %>
 <a href='edit_awards.asp?Action=edit&Award_ID=<%=rsAwards("Award_ID")%>'>Edit</a>&nbsp;<a class="lb" href='action_awards.asp?Action=delete&Award_ID=<%=rsAwards("Award_ID")%>'>Delete</a></li>
 <%
 rsAwards.MoveNext
 i=i+1
 Wend

 %></ul>
 <input type="submit" value="Sort">
 </form>

Open in new window



 



And below is what he has done so far in regards to posting the values of the textbox (left) to the database.


if Request.QueryString("Action") = "sort" then
     
     'Tell the recordset we are adding a new record to it
     editSQL2 = strSQL & " where Award_ID = " & Request.QueryString("Award_ID")
     
     rsEditCourse2.Open editSQL2, dataCon
     'Add a new record to the recordset

         dim j
         j = 1
             while j < 10
                 While not rsEditCourse2.EOF
                     rsEditCourse2.Fields("Award_Number") = j
                     rsEditCourse2.MoveNext
                 wend
             j = j + 1
             wend

     'Write the updated recordset to the database
     rsEditCourse2.Update

     'Reset server objects
     rsEditCourse2.Close
     Set rsEditCourse2 = Nothing
     Set dataCon = Nothing

 end if 



 

Open in new window

screen-for-si.png
0
 
LVL 33

Accepted Solution

by:
Big Monty earned 500 total points
ID: 39622538
this should be in the classic asp zone, and not the asp .net zone...

to answer your question, it should be relatively simple, as it looks like you're naming your text boxes AwardNumber. Since you're doing that, you will get the data passed over a comma delimited string. So all you would have to do in your save routine would be something like this:

dim AwardNumberList
AwardNumberList = Request.Form("AwardNumber")

'--now split the data into an array
arrAwardNumberList = Split( AwardNumberList, "," )

'-- now loop through the array and save
for each AwardNumber in arrAwardNumberList
    saveToAccessDB( AwardNumber )      '-- your function that does the actual saving
next

Open in new window

0
 

Author Comment

by:CDCAuman
ID: 39660731
Thank you for your response, The_Big_Daddy.  This was quite helpful. By the way, I would have posted in classic asp zone if I could find it... This area was the only .asp area I could find.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

821 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