Looping through a form and updating SQL 1

[fdarkness]

I'm just right baffled with this and I'm at my wits end.

I have a form that queries a database and displays the results on a page in a table. The results are displayed in the following format:

<% while NOT AccountsRS.EOF %>
	<tr><td>First Name:</td>
	<td><input type="text" value="<%=AccountsRS("FirstName")%>" name="<%=AccountsRS("AccountID")%>"></td>
	<td>Last Name:</td>
	<td><input type="text" value="<%=AccountsRS("LastName")%>" name="<%=AccountsRS("AccountID")%>"></td>
	<td>Account #:</td>
	<td><input type="text" value="<%=AccountsRS("AccountNumber")%>" name="<%=AccountsRS("AccountID")%>"></td></tr>
<% AccountsRS.MoveNext
wend %>

As can be seen by the while loop, this will produce several lines with three values (FirstName, LastName, AccountNumber) each associated with an ID (AccountID).

The input fields allow the user to make changes in the contents and upon pressing "submit", the changes are to be sent to the database. There can be multiple lines so it would require as many updates to the database as there are lines.

I've tried two things, and I can't progress beyond processing the data into the update.

First method:
set the name of the input variables to AccountID (taken from the RecordSet). Using this and the following kludge of code:

for each key in Request.Form
	if NOT key = "submit" then
	Response.Write ("<p>" & key & " = " & Request.Form(key) & "</br>")
	end if
next

I managed to get the following:

1407 = Jim, James, 123456
1510 = John, George, 98765

This corresponded to AccountID, FirstName, LastName, AccountNumber respectively.

I tried to use this in the SQL statement, but the three variables (FirstName, LastName, AccountNumber) are one string and I can't figure out how to parse them out of the single string (which is what Request.Form(key) winds up being due to the naming all the input tags the same).

Second method:
I named the various form elements to something different. I associated them with the AccountID though. Using a for loop to go through each individual element, I wound up with the following results:

FirstName1407 = Jim
LastName1407 = James
FirstName1510 = John
AccountNumber1407 = 123456
LastName1510 = George
AccountNumber1510 = 98765

Looking at that, I couldn't figure out what to do with it. The variable names themselves are skewy. Because they didn't seem to process in order, I couldn't put them into one SQL update call. At best, it would take six updates to the database, one for each value, which doesn't seem the smart way to do it.

So what am I missing here? This can't be that complicated. Please help!

 

[Dashley]

fdarkness

Hope this helps

request.form.item will return the actual value of the form item. The .key just returns the name. You want to assign a var the value etc..

Dim I

For I = 1 to request.form.count

request.form.item(i)

MyVal = request.form.item(i)

insert into mydb tabel myval


next

 

[fdarkness]

Dashley... your code is giving similar results to what my second method did. It would require 3x updates to the database, updating only once for each field.

For example, two lines in my table, when submitted with your code, went through the loop seven times (included the submit button).

This would work fine if I was doing an insert, but I'm doing an update and need either a string (value1, value2, value3) or separate variables.

I might not be making sense here. This has been frustrating me all day and nothing I search out online has been able to solve it for me.

 

[barny2006]

you needt to grab the 3 values together and your sql command should look like this:
___________________
UPDATE my_table set
field1 = form_field1,
field2 = form_field2,
field3 = form_field3;
______________________
this doesn't need to be inside a loop. do your loop. get the form_field1, form_field2, and form_field3. after the loop, execute the sql command once as above.

 

[Dashley]

Your wanting to update each input from the form using
the the AccountsRS("AccountID") as rhe reference right?

 

[fdarkness]

Barny: there needs to be a loop because there's multiple instances of the same fields. It's multiple lines in a table. Each line would need to be updated in the database.

Dashley: Yes, the SQL would look something like this:
UPDATE table
SET LastName = formfield(LastName),
FirstName = formfield(FirstName),
AccountNumber = formfield(AccountNumber)
WHERE AccountID = AccountRS("AccountID")

I just came up with an idea for something... I'll try it combined with some of the ideas here so far.

 

[Dashley]

Normally what i do is as the recordset loops out the data I assigen an additional value to the intput statement

<input type="text" value="<%=AccountsRS("FirstName")%>" name="<%=AccountsRS("AccountID")%>">

DIM J

While not rs.eof
Response.write "<input type='text' value='" & <%=AccountsRS("FirstName")%> & "'& J name='<%=AccountsRS("AccountID")%>'&J>


rs.movenext
wend


That way when the come through the form you can grab them with the I loop like above. That way all 3 values can go in one line for an update.

 

[tsuji]

Your description on the table and what posted back do not agree with each other? Why 1407 etc ever appears as suffix to firstname, lastname etc? Is it appeared as such in the database itself? Where is accountid ever displayed in the page?...

 

[fdarkness]

tsuji: the number appears as a suffix in one type of loop I used to pull the data out and corresponds to the AccountID. I tried naming the form elements as "AccountID" & LastName in order to make them different and that's what I got.

Dashley: See my answer to tsuji.

I'm going to keep trying today and see if I can come up with something. If I can't, the only way I can see working it is if I allow only one line to be displayed and editted at a time, which isn't what I wanted to do.

 

[tsuji]

I only stick to your first post.

[1] If accoundid is a pure digit, you should avoid using it as such in the name. Not that it immediately breaks the functionality, it is just no good if there is one-day client side function using the name. Append some alphabet or underscore in front of it would make it good.

[2] You use accountid as name for all the input elements of the same row : firstname, lastname and accountnumber. In that case, the return to server would pick them up as a comma delimited string. To get each individual data, it is a normal routine of splitting it up.
[tt]
for each key in Request.Form
if NOT key = "submit" then
'[blue]you have to make it robust such as ubound(a)=2 for instance
'and you have to decide what to do if it is not
'also investigate what if "," appears in the data[/blue]
a=split(request.form(key),",")
firstname=a(0)
lastname=a(1)
accountname=a(2)
Response.Write ("<p>" & "Account Id = " & key & "; First Name = " & firstname & "; Last Name = " & lastname & "; Account Name = " accountname & "</br>")
end if
next
[/tt]
In any case, that is how you get individual piece of data out of it.

 

[fdarkness]

tsuji: yay! It worked! Thank you!
It took some fussing with, but it's now up and going perfectly!

 

[fdarkness]

Dangit!

tsuji, any idea on how I can fix the comma (,) issue? Everything I'm thinking of, the replacement will affect the separator used in the split command. There's only one section where there's a possibility that a comma will be used, but it's definitely going to screw up my update command.

 

[tsuji]

You should then really try not to use the same name for input elements even with a good sense of grouping. The other way to fix the comma issue is to escape the text input client-side in the onsubmit handler and unescape server-side after split. There are works to put into it, though a bit boring but straightforward.

 

[fdarkness]

Ok, so if I don't use the same name for the elements, how can I read the contents of them properly and place them in the UPDATE command?

 

[tsuji]

Like for instance keeping the accountid as suffix, and prefixing it with "fn_", "ln_" and "acn_". In this case, you fix at the same time the problem with pure digit as element name.

On the server-side, you then inspect (by script) the suffix after "_" to identify the same account. The prefix before "_" to identify whether it is first name,... You can set up for instance three dictionary object one for first name, one for ... with accountid as the key and the data (first name, etc. individually specific to the particular dictionary object) as item. Then you are set.

Maybe there are other ways too. Don't take my words as final...

 

[fdarkness]

I tried pulling it out with prefix and suffix information and found that I'd lose the information through the loop and therefore couldn't build the UPDATE command.

I'll try it again and see what comes up. This has been stressing me out for two days now.

 

[fdarkness]

I'm back to the problem that this would require me to do an update for *each* element, as opposed to doing one update per set of three elements. My keys (names of elements) come out looking like this:

key: LN_3751
key: LN_3750
key: AID_3750
key: FN_3751
key: FN_3750
key: AID_3751

Each line is an interation of the loop. As you can see, the order appears to be pretty random, so I can't even take the first three iterations of the loop and use the data that way.

The only way I can see doing that is taking the first iteration, determining the table column from the prefix, the WHERE clause from the suffix, and updating for each iteration. It's very possible and *probably* wouldn't take up that much time to update, but it's far from the eloquent way of doing things.

 

[tsuji]

I have not read in detail your post. As to the randomness, the order is not following a simplistic order and hence appearing so. That's why I suggested three (3) dictionary object one for each data with AccountID as key. In that case, you throw data into the dictionary without any regard to the order, and you don't have to worry about the order. After all the data being processed, you retrieve data orderly using the key (ie the accountid) from the dictionaries to build the sql. (Validation server-side is still very much desirable before any operation on transacting with db.)