Multiple records update 1

aarellano · Mar 20, 2009

hello,

I build a form to be submited.

in the form each user can enter their information. But it gets processed at the end of the day after all the info has been verified. Right now I can do a submit for each of the users, is there a way that I could submit all the users at once. right now I but a loopnext loop and get submit in each of the users

Code:

<%


'Dimension variables
Dim adoCon          'Holds the Database Connection Object
Dim rsGuestbook         'Holds the recordset for the record to be updated
Dim strSQL  
dim totalValue      'Holds the SQL query for the database
'Dim lngRecordNo            'Holds the record number to be updated
'Read in the record number to be updated
'lngRecordNo =CLng(Request.QueryString("ID"))

'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("nuse.mdb")

'Set an active connection to the Connection object using DSN connection
'adoCon.Open "DSN=guestbook"

'Create an ADO recordset object
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "SELECT * FROM gm, weeks order by  total DESC"

<body>
<FORM NAME="nuserin" ACTION="updatein.asp" METHOD="POST">

my form

</span> <input name="total" type="hidden" value="<% response.Write(totalValue) & ""%>">

        <input type="hidden" name="ID" value="<% =rsGuestbook("ID") %>">
        <input type="submit" name="Submit"  value="submit">
</form>
      <!-- End form code -->
</body>
</html>   

<%
     
    
      'Increment the count by 1 to get the next number
     
      rsGuestBook.MoveNext
  Loop
End If
%>

wvdba · Mar 23, 2009

you have to form a loop to retrieve each row of the data that you "SELECT"ed and use each one to update whatever you're updating. you have multiple records for the results of the SELECT. is this a DO - LOOP?
it seems that you're missing the DO part of the loop.
do you have to press submit button multiple times?
you can process all with the single press of a button.

DreXor · Mar 23, 2009

google ADO batch update

[thumbsup2]

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

aarellano · Mar 23, 2009

wvdba,

right now, yes, I have to press each button to enter the data but would like not to have to do that. just one submit

DreXor · Mar 23, 2009

you can also "multiform" in the same form, you just use your ID record, and make an additional field to the form that houses all the records.

example :
<input type="hidden" value="1,2,3,4,5,6,7" name="AllIDS">
<input type="hidden" value="FirstName,LastName,Address,City,State,Zip,Phone" name="AllFields">

then ...
all form inputs get a prefix of their ID :
<input type="text" name="1_Firstname">
...........

then on the update handling side :

Code:

fields = split(request("AllFields"),",")
IDS = Split(Request("allIDS"),",")
for each id in IDS
  UpdateSQL = ""
  for each field in Fields
    UpdateSQL  = UpdateSQL & Field & "='" & Replace(request(ID & "_" & Field),"'","''") & "', "
  Next
  UpdateSQL = Left(UpdateSQL, Len(UpdateSQL)-2) ' trims last comma/Space
  UpdateSQL = "UPDATE <TABLE> SET " & UpdateSQL & " Where ID=" & ID
  Set UpdateRS = Connection.Execute(UpdateSQL)
Next

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

DreXor · Mar 23, 2009

and to build the hidden values it's just a matter of loop through recordset once for the ID's and

for each field in recordset.fields
fields = fields & field.name & ","
next
fields = lefT(fields,len(fields)-1) ' trim last comma

[thumbsup2]

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

aarellano · Mar 23, 2009

DreXor,
I will give it a go Thank you!!!. Did do a google search and Tried a couple of examples but did not work on my form or sould I say was not really sure how to adapt them.

I appreciate it

DreXor · Mar 23, 2009

as for the original building of the form for you :

Code:

<form method=post action=whatever>
<%
set RS = Connection.Execute("select new records from whatever, however")
If Not RS.EOF Then
    ALLIDs = ""
    AllFields = ""
    for each field in rs.Fields
        AllFields = AllFields & Field.Name & ","
    Next
    Fields = Left(Fields,Len(Fields)-1) ' trim last comma
    do while not RS.EOF
        ALLIDs = AllIDs & RS(Field.Name) & ","
        for each field in rs.fields
            If Field.name  = "ID" OR Field.Name = "Password" Then 'do nothing, and any othe field that should be left alone or not displayed
            Else
    %>
<%=Field.Name%>: <input type="Text" name="<%=RS("ID") & "_" & Field.Name%>" Value="<%=RS(Field.Name)%>"><br>
    <%        
            End If
        next
        Response.Write "<HR>" ' making a record divider for ease of viewing
        rs.movenext
    loop
    AllIDs = Left(AllIDs,Len(AllIDs)-1) ' trim last comma
%>
    <input type="hidden" name="allIDs" value="<%=AllIDs%>">
    <input type="hidden" name="allFields" value="<%=AllFields%>">
<%    
Else
Response.Write "NO NEW RECORDS"
End If
%>
</form>

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

DreXor · Mar 23, 2009

yeah the batch update when you're leanring how to do multi updating can get rather confusing, it's all arrays and they have to be _PERFECT_ or they ALL fail.

why i posted all the "multiform" methodologies here for you, these are pretty easy to follow when you look at the HTML after they run through, and it's fairly dynamic, even for checkboxes, though you may want to search the faqs/forum for checkbox handling, which was denoted for this type of use.

[thumbsup2]

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

DreXor · Mar 23, 2009

OH forgot one more note, the way it's written now is an all or nothing ordeal, you can easily add a check or checkbox into it to handle if THAT record should be updated, like:

Form Side :

Code:

........
do while not rs.eof 
%>
<input type="checkbox" name="<%=RS("ID") & "_" & "Update"%>">
<%
.......

then on the update side (updated code from above) :

Code:

for each id in IDS
[b]if request(ID & "_Update") = "on" Then [/b][green]' this makes it skip records where you didn't check the update checkky on the form side.[/green]
  UpdateSQL = ""
  for each field in Fields
    UpdateSQL  = UpdateSQL & Field & "='" & Replace(request(ID & "_" & Field),"'","''") & "', "
  Next
  UpdateSQL = Left(UpdateSQL, Len(UpdateSQL)-2) ' trims last comma/Space
  UpdateSQL = "UPDATE <TABLE> SET " & UpdateSQL & " Where ID=" & ID
  Set UpdateRS = Connection.Execute(UpdateSQL)
[b]End If[/b]
Next

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

DreXor · Mar 23, 2009

and last note, once you got a good grip on how all this works, the dymanics, you can easily change it over to create/handle the arrays for a batch update and increase performance.

[thumbsup2]

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

aarellano · Mar 26, 2009

wooow that was really really good!! I appreciate all the help, tips, comments!!!!

DreXor · Mar 26, 2009

right-o

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!
" I always think outside the 'box', because I'm never in the 'loop' " - DreX 2005

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Multiple records update 1

aarellano

MIS

wvdba

IS-IT--Management

DreXor

Programmer

aarellano

MIS

DreXor

Programmer

DreXor

Programmer

aarellano

MIS

DreXor

Programmer

DreXor

Programmer

DreXor

Programmer

DreXor

Programmer

aarellano

MIS

DreXor

Programmer

Similar threads

Part and Inventory Search

Sponsor