SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    ASP help. A collection loop is messing up the oder of items!

    I am setting up a loop to update a db table with submitted form values.The form is a presentation of the table that is to be updated using text inputs so that the user can
    change them, and then submit to update the db table.

    Inside the loop, before updating, I will check the name of the input against a list of changed fields and only update the db fields corresponding to form values that were changed.

    The x in the loop returns the name of the input field so it supports this function.



    Here's the loop:

    For Each x In Request.Form
    response.write(x&" "&Request.Form(x)&"<BR>")
    Next


    Obviously, the this loop has to order the items from the form in exaclty the same order as they appear in the form,which is identical to the db table.

    Unfortunately, it doesn't seem to follow this order.

    Here's a segment of the html source that produced the form.

    note that "text_inst_name2" directly follows "text_entered1"


    form html source text:

    <input type=text id=text_entered1 name=text_entered1 style='width:55;' value='' class=manager_input></td><td border=1><input type=text id=text_inst_name_2 name=text_inst_name_2 style='width:220;' value='AACI-***. Americans & Canadians in Israel' class=manager_input>


    Yet in the output of the loop this order is not followed.
    Here is the output of the loop;

    radio1 true
    ****text_entered1
    text_entity_num3 580019545
    text_entered12
    text_entity_num14 580034924
    ****text_inst_name_2 AACI-***. Americans & Canadians in Israel
    text_zip7 92228
    text_inst_name_13 Akim Advancement of Retarded Children
    text_zip18 69410
    sub_update_institutions update
    text_file_num4 550019541
    text_aims12 aims aims aimsaims aimsaims aimsaims aimsaims aimsaims
    text_file_num15 550034920
    text_aims23 aims aimsaims aimsaims aimsaims aimsaims aimsaims aimsaims aimsaims aimsaims aimsaims aims
    text_address5 Pinsker 11
    text_website10
    radio2 true
    text_address16 Pinchas Rosen 69
    text_website21
    text_town6 Jerusalem
    text_town17 Tel Aviv
    text_telephone8 ()
    text_telephone19 ()
    text_fax9 ()
    text_fax20 ()
    text_email11
    text_email22
    hidden_text1


    My questions are;

    1)Why is this loop not following the order of the items in the collection?
    2)Can I get it to follow the order of the items in the collection?
    3)If I don't use this kind of loop, and use something like the ones below, how do I access the name of the imput to check it it is on the list of changed fields?
    request.form(i).name doesn't work. Neither does request.form(i).item


    or will I have to use one like this;

    for item_index=0 to request.form.count-2

    rs1.fields(i)= request.form(i)

    loop


    or one like this;

    dim 1=0
    do until request.form.count-2
    rs.fields("field_name1")=request.form(i)
    i=cint(i)+#
    rs.fields("field_name1")=request.form(i)
    loop


    Thanks in Advance.
    Andy

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The following link may be of help.
    http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=64

    Is there any reason you're updating only fields that have been changed rather than just updating all of them? The only advantage I can see in not updating them all is if they're in multiple tables and you're saving redundant updates.

    Maybe I'm missing something?

  3. #3
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    only changed fields

    The table is 15 collums wide. When I updated everyfield, at about 10 rows the update operation timed out on the server. So I thought that saving trips to the db would enable this multiliple line table update operation to work.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I have never used ASP and my memory of ADODB objects and VB is pretty shakey, so I don't know if I am on the right track here.

    However, that said, one thing that you should keep in mind is that in general relational databases do not maintain order between tuples ("records"/"rows") within a relation (table). This is because database indexes are not structured in a sequential manner, but rather are usually organised as hash tables or balanced trees.

    So the short and curly is that you should always sort the result set yourself. You can do this in the sql query or, if my memory serves me correctly, there is a method of the ResultSet class which will do this as well.

  5. #5
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Do you all think this will work ok?

    Thank you for your thoughtful replies.

    I now understand that the request.form collection does not order the items. But I want the output ordered, without having to worry about an upper limit beyond which the form inputs start messing up the data base.

    So I thought of this solution to get a square peg to fit a round hole. Does it look like it will work?

    Will it take too much time to execute if we assume that the client will not update more than about 10 or 15 fields each submit?

    The input fields are given numbered names like text_1, text_2 ect..

    <%
    dim ,i,item,rs1,changed_fields,searchable_namestr

    sqlstr="SELECT whateve FROM whatever;"
    rs1.Open sqlstr,con,3,3

    'make sure the names of the inputs in form also start with 0
    i=0
    for each item in request.form
    if iitem <> "submitbutton" and item <> "hidden_text"
    namstr=item
    num_pos=inst(namestr,"_")+1
    namenum=mid(namestr,numpos)
    if cint(namenum)=i then
    searchable_namestr=namestr&","
    if instr(namestr,searchable_namestr)>0 then
    rs1.fields(i)=request.form(item) end if
    if cint(i) mod 14=0 and i<>0 then
    rs1.movenext
    end if
    i=cint(i)+1
    end if
    end if
    next
    %>

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Andy,

    I don't think the code will work as you want. As freakysid said you can't rely on the order of the records in the db table. You're using this order to determine which value relates to which record. What you can do is use the record id instead of the 'i' variable.

    I had a quick look around but didn't see any examples of updating multiple records. I've come up with an example using the Northwind db. It writes out 2 form fields for each record field. One to hold the original value that we can then compare against to check to see if we have to update the record. Each form field name is appended with the record id so it can be matched when doing the update.

    The code does update all fields of the records (which I know you was trying to avoid) but does only update the records where a least one of the new values is different from the original value.

    It determines which records to update by building a string of the record ids which is then used in the sql query.

    The code is a bit messy and it could get tricky adding error checking and data validation into it. How it fares in terms of speed I really wouldn't like to guess.

    Personally, I wouldn't use the code in production as it requires locking a certain number of records whilst doing the update. I think I'd just allow the user to update one record at a time.

    Microsoft actually have a client side ActiveX control that enables editing muliple records. You may want to check ASP sites for info on Remote Data Services.

    I've added comments to the code but if you've got any questions just ask.

    Code:
    <%
    Option Explicit
    Response.Buffer = True
    
    '--Global variables
    'ADOVBS constants
    Const adOpenKeyset = 1
    Const adUpdateBatch = &H00010000
    Const adLockBatchOptimistic = 4
    Const adCmdText = &H0001
    
    Dim strDBConnString
    
    strDBConnString = "DRIVER={Microsoft Access Driver (*.mdb)};"
    strDBConnString = strDBConnString & "DBQ=" & Server.Mappath("Northwind.mdb") & ";"
    strDBConnString = strDBConnString & "uid=;pwd=;"
    
    Function Employees()
    
    	Dim objConn, objRS, strSQL
    	Dim strID, strTitle, strFirstName, strLastName, strTitleOfCourtesy
    	Dim strRS_IDs
    	Dim arrIDs, strIDs, intUpdateCount
    	Dim blnUpdateRecord
    	Dim intArraySize, i
    	Dim objDict
    	Dim Item
    	Dim strError, strCount
    	
    	strRS_IDs = ""
    	
    	
    	If Request.Form("btnUpdate") & "" = "" Then
    		'--Display the records
    		
    		'Get some records
    		strSQL = "SELECT TOP 10 EmployeeId, Title, FirstName, LastName, TitleOfCourtesy " & _
    		"FROM Employees"
    		
    		'Create the connection object
    		Set objConn = Server.CreateObject("ADODB.COnnection")
    		objConn.Open strDBConnString
    		
    		Set objRS = objConn.Execute(strSQL)
    		
    		If Not objRS.EOF Then
    			Response.Write "<form action=""" & Request.ServerVariables("SCRIPT_NAME") & """ method=""post"">" & vbCrLf
    			Response.Write "<table border=""0"" width=""500"" cellspacing=""0"" cellpadding=""3"">" & vbCrLf & _
    			"<tr><td class=""heading"">Title</td>" & _
    			"<td class=""heading"">First name</td>" & _
    			"<td class=""heading"">Last Name</td>" & _
    			"<td class=""heading"">Title of courtesy</td></tr>" & vbCrLf
    			
    			Do While Not objRS.EOF
    				'Read the records values into variables
    				strID = Trim(objRS.Fields(0).Value)
    				strTitle = Trim(objRS.Fields(1).Value)
    				strFirstName = Trim(objRS.Fields(2).Value)
    				strLastName = Trim(objRS.Fields(3).Value)
    				strTitleOfCourtesy = Trim(objRS.Fields(4).Value)
    				strRS_IDs = strRS_IDs & strId & ","
    				
    				'Write out 2 input fields for each field
    				'One input text field enables the editing of the value
    				'The second input hidden field holds the original value
    				Response.Write "<tr>" & _
    				"<td>" & _
    				"<input type=""text"" size=""10"" name=""titleofcourtesy_" & strId  & """ value=""" & strTitleOfCourtesy & """>" & _
    				"<input type=""hidden"" name=""titleofcourtesy1_" & strId  & """ value=""" & strTitleOfCourtesy & """>" & _
    				"</td>" & _
    				"<td>" & _
    				"<input type=""text"" size=""20"" name=""firstname_" & strId  & """ value=""" & strFirstName & """>" & _
    				"<input type=""hidden"" name=""firstname1_" & strId  & """ value=""" & strFirstName & """>" & _
    				"</td>" & _
    				"<td>" & _
    				"<input type=""text"" size=""20"" name=""lastname_" & strId  & """ value=""" & strLastName & """>" & _
    				"<input type=""hidden"" name=""lastname1_" & strId  & """ value=""" & strLastName & """>" & _
    				"</td>" & _
    				"<td>" & _
    				"<input type=""text"" size=""40"" name=""title_" & strId  & """ value=""" & strTitle & """>" & _
    				"<input type=""hidden"" name=""title1_" & strId  & """ value=""" & strTitle & """>" & _
    				"</td>" & _
    				"</tr>" & vbCrLf
    				
    				objRS.MoveNext
    			Loop
    			
    			'Trim the comma off the string of ids
    			If Right(strRS_IDs,1) = "," Then
    				strRS_IDs = Left(strRS_IDs, Len(strRS_IDs)-1)
    			End If
    			
    			'Close the table and output an Update button and a hidden input with the value of 
    			'the string of record ids. Close the form.
    			Response.Write "</table>" & vbCrLf & _
    			"<br>" & _
    			"<input type=""submit"" name=""btnUpdate"" value=""Update"">" & vbCrLf & _
    			"<input type=""hidden"" name=""rs_ids"" value=""" & strRS_IDs & """>" & vbCrLf & _
    			"</form>" & vbCrLf
    			
    		Else
    			Response.Write "<p>No records found.</p>"
    		End If
    		
    		
    		'Clean up
    		objRS.Close
    		Set objRS = Nothing
    		objConn.Close
    		Set objConn = Nothing
    		
    		'Display any messages
    		strError = Request.QueryString("error")
    		strCount = Request.QueryString("count")
    		If strError = "0" Then
    			Response.Write "<p>" & strCount & " records updated.</p>"
    		End If
    	
    	Else
    		'--Edit the records
    		
    		'Create instance of dictionary object
    		Set objDict = Server.Createobject("Scripting.Dictionary")
    		
    		'Read all the Form items into the dictionary object
    		For Each Item In Request.Form
    			objDict.Add Item, Request.Form(Item)
    		Next
    		
    		'Get the ids of all records into an array
    		arrIDs = Split(objDict("rs_ids"), ",")
    		'Get the arrat dimension
    		intArraySize = UBound(arrIDs)
    		strIDs = ""
    		intUpdateCount = 0
    		
    		'Response.Write "<p>Array size: " & intArraySize  & "</p>"
    		
    		'Loop around the array of all ids checking to see if the new values match
    		'the old values. If any of the new values are different from the old values
    		'add the record id to a string of record ids to update.
    		For i = 0 To intArraySize
    			blnUpdateRecord = False
    			strID = arrIDs(i)
    			
    			If objDict("title_" & strID) <> objDict("title1_" & strID) Then
    				blnUpdateRecord = True
    			End If
    			
    			If objDict("firstname_" & strID) <> objDict("firstname1_" & strID) Then
    				blnUpdateRecord = True
    			End If
    			
    			If objDict("lastname_" & strID) <> objDict("lastname1_" & strID) Then
    				blnUpdateRecord = True
    			End If
    			
    			If objDict("titleofcourtesy_" & strID) <> objDict("titleofcourtesy1_" & strID) Then
    				blnUpdateRecord = True
    			End If
    			
    			If blnUpdateRecord Then
    				'Add the id to the string of ids to update
    				strIDs = strIDs & strID & ","
    				intUpdateCount = intUpdateCount + 1
    			End If
    		Next
    		
    		'Strip the comma from end of string
    		If Right(strIDs,1) = "," Then
    			strIDs = Left(strIDs, Len(strIDs)-1)
    		End If
    		
    		'Response.Write "<p>updatecount: " & intUpdateCount & "</p>"
    		
    		'Update any records as necessary
    		If intUpdateCount > 0 Then
    				
    			strSQL = "SELECT EmployeeId, Title, FirstName, LastName, TitleOfCourtesy " & _
    					"FROM Employees " & _
    					"WHERE EmployeeId IN (" & strIDs & ")"
    			
    			Set objConn = Server.CreateObject("ADODB.Connection")
    			objConn.Open strDBConnString
    			
    			'Open recordset for batch updating
    			Set objRS = Server.CreateObject("ADODB.Recordset")
    			objRS.Open strSQL, objConn, adOpenKeyset, adLockBatchOptimistic, adCmdText
    					
    			
    			If Not objRS.EOF Then
    				'Loop around the RS setting the field values with the values from 
    				'the dictionary object
    				Do While Not objRS.EOF
    					strID = objRS.Fields(0).Value
    					strTitle = objDict("title_" & strID)
    					strFirstName = objDict("firstname_" & strID)
    					strLastName = objDict("lastname_" & strID)
    					strTitleOfCourtesy = objDict("titleofcourtesy_" & strID)
    					
    					objRS.Fields(1).Value = strTitle
    					objRS.Fields(2).Value = strFirstName
    					objRS.Fields(3).Value = strLastName
    					objRS.Fields(4).Value = strTitleOfCourtesy
    					objRS.MoveNext
    				Loop
    			End If
    			
    			'Update the recordset
    			objRS.UpdateBatch
    			
    			'Clean up
    			objRS.Close
    			Set objRS = Nothing
    			objConn.Close
    			Set objConn = Nothing
    		End If
    		
    		'Clean up
    		Set objDict = Nothing
    			
    		'Redirect user back to edit page
    		Response.Clear
    		Response.Redirect Request.ServerVariables("SCRIPT_NAME") & "?error=0&count=" & intUpdateCount
    
    	End If
    	
    End Function
    
    
    %>
    
    <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
    
    <html>
    <head>
    	<title>Northwind - Multi record edit</title>
    	<style type="text/css">
    	table {
    		border-width: 1px;
    		border-style: solid;
    		border-color: #000000;
    		border-collapse: collapse;
    	}
    	td {
    		font-size: 11px;
    		font-family: Verdana, Arial;
    		color: #333333;
    		background-color: #edede0;
    		border-width: 0x 0px 0px 0px;
    		border-style: solid;
    		border-color: #000000;
    	}
    	td.heading {
    		font-size: 11px;
    		font-family: Verdana, Arial;
    		color: #FFFFFF;
    		background-color: #000066;
    	}
    	input {
    		font-size: 11px;
    		font-family: Verdana, Arial;
    	}
    	p {
    		font-size: 12px;
    		font-family: Verdana, Arial;
    		color: #000000;
    	}
    	</style>
    </head>
    
    <body>
    
    <% Employees %>
    
    </body>
    </html>
    Last edited by shane; Jul 7, 2001 at 07:59.

  7. #7
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    thanks but

    first of all, thank you Shane for all the work you put it to the example. I've printed it and am going to be going over it carefully.

    In the meantime, I don't see why my attempt wont work. I realize the the request.form collection does not order the items for me. So what I did was to pull out the items by looping over the entire collection, in whatever order vb script wants, until the the item that has the next number in its name is found. In other words condition the update action on the for each loop finding an item with a _1 at then end of its name, then a _2, ect.

    What I was wondering was whether this will perfom well. I figured that there was no reason why it shouldn't work at all. And I still don't understand why you don't think it will.

    PS. Please forgive my ignorance if I am missing something obvious especially since I haven't tried the script yet.

    Andy

  8. #8
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    oh!

    I just read your replies again.
    So you are saying that the order of data in db is also lacking in order? Wow! I thought that that was the whole point of databases, to maintain oder and proper relationships between values! Now I am really confused how asp is supposed to work with data bases.

  9. #9
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I didn't look close enough at the piece of code you posted and realise now that you're using the variable i to determine which field is associated with which Request.Form value. This would work fine for the fields as long as you the list of fields in the SELECT statements were exactly the same that you used to get the RS for displaying the records and updating the records.

    However, you'll still have the problem of associating the correct form values with the correct record. The order of the records in the db can't be relied upon. What you need to do is use the primary key value (that is unique for each record in a given table) to match the record that is being updated with the Form values.

    In the example I posted I used the EmployeeId field as it's the primary key of the Employee table. The EmloyeeId was appended to each form item name so that when updating the records we could match the precise record (by EmployeeId) to the Form values.

    Even if we could rely on the db to order the records you'd run into problems when multiple users were updating the same data. Say we were to get all the records in a table and displayed them in a form for a user to edit. And then whilst that user was making their edits a second user deleted some of the records, the ordering of the records in the db wouldn't match the order of the records the first user was editing. When the first user tried to update the records the ordering of the records he was trying to update wouldn't match those in the db table.

    Using the primary key of the record partly solves this problem by matching the record primary key with the Form value. There can still be conflicts if the first user tried to update a record that now longer existed, but it'd be easier to catch this and inform the user.

    I'll have a look around and try and find some other examples of multi record editing. I have found one but it's bloody awful and doesn't do the record matching that we've talked about.

  10. #10
    SitePoint Zealot akohl's Avatar
    Join Date
    Jun 2001
    Location
    Israel
    Posts
    184
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    For this project, there will be only one user updating the existing records in the db. Other users will only be able to add records (not delete or change existing ones) which would seem to get placed at the end of the table. So since my loop is bound by the length of the form data and not the db table, the form data should match the db table data even if another user adds a record to the db.

    But you seem to be saying that even if left untouched, the order of the records in a db can change.

    With my experience from a previous project using the same db program, ms access 97, the order of records in the db could be relied upon, when no deletion of records took place while the table was being updated. The problem I'm having here came from my use of the for each item in request.form loop, which I am using so that I can identify the name of each input and check to see if it was changed in the form before updating db table in order to avoid redundant updates. This request.form collection does not return the values in order, but the db table should.

    So I just want to clarify, are you saying that the problem of order of records in the db table independent of deletion of records by another user. If so, how does this happen?

    By the way, this is just a theoretical question as I have accepted the desireablility of using the pk to identify each record before updating.

    Andy

  11. #11
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If no changes have been made to the db table then the order of the records will most likely stay the same. However, relational db's don't maintain the records in a specific order so it's something that you shouldn't rely on. Any application that is written dependent upon the db's ordering of records would go pear shaped very quickly in a multi user enviroment.

    You may want to have a good search around ASP sites, as multi record editing would seem like something that other developers have done. Personally, I haven't had any need for this, apart from updating a boolean fields, which is quite a bit quicker and simpler, so there's bound to be better examples than mine.



Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •