This is a discussion on "Updating multiple records at the same time prob. with script" within the Classic ASP section. This forum, and the thread "Updating multiple records at the same time prob. with script are both part of the Program Your Website category.
|
|
|
|
|
 |
||
Updating multiple records at the same time prob. with script
|
||
| Notices |
 |
|
|
LinkBack | Thread Tools |
|
#1
Jul 25th, 2005, 18:42
|
|||
|
|||
|
Updating multiple records at the same time prob. with script
Hi
I have created two asp pages called edit.asp and edit_records.asp I have created a stock portfolio which looks up records from my access database. I have the ability to add and delete but I am trying to add the ability to update more than one record at a time. so if I update all my stocks current stock price I would like to press a button and have all the text fields that I had changed be updated at the same time rather than updating it one by one. I create a asp page that was suppose to do that but when I click on the button it just takes me to the second part of the page "edit_records.asp" and nothing that I had changed in the first page went through. Below is the code that I have come up with I am not sure what the problem might be. -- edit.asp code -- <html> <head> <title>My Stocks</title> </head> <body> <table width=100% height=36 border=0 cellpadding=0 cellspacing=0 bgcolor=#E5EEF9> <tr> <th scope=col><table width=100% height=26 border=0 cellpadding=0 cellspacing=0 bgcolor=#B4CCEB> <tr> <th scope=col align = right ><a href = "index.asp">Home</a> | <a href = "add.html">Add</a> | <a href = "delete.asp">Delete</a> | <a href = "edit.asp">Edit</a></th> </tr> </table></th> </tr> </table> <table font width = 100% border = 0 cellspacing = 0 cellpadding = 0> <tr> <th width = 15%></th> <th width = 10%>Name</th> <th width = 10%>Symbol</th> <th width = 10%>Pur Date</th> <th width = 10%>Trade Date</th> <th width = 10%>Shares</th> <th width = 10%>Cost</th> <th width = 10%>Value</th> <th width = 15%></th> </tr> </table> <table align = center bgcolor = #B4CCEB width = 70%><tr><th></th></tr></table> <form action = "edit_records.asp" name = "form" medthod = "post" > <% Dim adoCon Dim rs Dim strSQL Dim iCounter iCounter = 0 set adoCon = Server.CreateObject("ADODB.Connection") adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("stocks.mdb") Set rs = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT * FROM stocks;" rs.Open strSQL, adoCon Do While not rs.EOF 'Write the HTML to display the current record in the recordset Response.Write("<table width = '100%' border = '0' cellspacing = '0' cellpadding = '0'>") Response.Write("<tr>") Response.Write("<th width = '15%'><input type = text name = txtid value ='" & rs("txtid") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txtcompanyname value ='" & rs("txtcompanyname") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txtsymbol value ='" & rs("txtsymbol") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txtpurdate value ='" & rs("txtpurdate") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txttradedate value ='" & rs("txttradedate") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txtshares value ='" & rs("txtshares") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txtcost value ='" & rs("txtcost") &"' size = '10'></th>") Response.Write("<th width = '10%'><input type = text name = txtvalue value ='" & rs("txtvalue") &"' size = '10'></th>") Response.Write("<th width = '15%'></th>") Response.Write("</tr>") Response.Write("</table>") rs.MoveNext iCounter = iCounter + 1 Loop Response.Write("<input type=text name=totalNumberOfRows value ='" & iCounter &"' size = '10'>") rs.Close Set rs = Nothing Set adoCon = Nothing %> <table width = "100%" align = right><tr><th><input type = submit value = "update"></th></tr></table> </form> </body> </html> -- end of code-- -- edit_records.asp code -- <% 'Dimension variables Dim adoCon 'Holds the Database Connection Object Dim rsUpdateEntry 'Holds the recordset for the record to be updated Dim strSQL 'Holds the SQL query to query the database Dim lngRecordNo 'Holds the record number to be updated 'Create an ADO connection object 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("stocks.mdb") Dim iTotalRows Dim i 'iTotalRows = Request.Form("iTotalRows") 'for ( i = 0 ; i <= iTotalRows; i++) for i = 1 to Request.Form("iTotalRows").Count txtid=Request.Form("txtid_" & i) txtcompanyname=Request.Form("txtcompanyname_" & i) txtsymbol=Request.Form("txtsymbol_" & i) txtpurdate=Request.Form("txtpurdate_" & i) txttradedate=Request.Form("txttradedate_" & i) txtshares=Request.Form("txtshares_" & i) txtcost=Request.Form("txtcost_" & i) txtvalue=Request.Form("txtvalue_" & i) 'Create an ADO recordset object Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset") strsql="UPDATE stocks SET " strsql=strsql & "txtcompanyname='" & Request.Form("txtcompanyname") & "'," strsql=strsql & "txtsymbol='" & Request.Form("txtsymbol") & "'," strsql=strsql & "txtpurdate='" & Request.Form("txtpurdate") & "'," strsql=strsql & "txttradedate='" & Request.Form("txttradedate") & "'," strsql=strsql & "txtshares='" & Request.Form("txtshares") & "'," strsql=strsql & "txtcost='" & Request.Form("txtcost") & "'," strsql=strsql & "txtvalue='" & Request.Form("txtvalue") & "'," 'Do for other columns strsql=strsql & " WHERE ID='" & Request.Form("txtid") next 'Write the updated recordset to the database on error resume next adoCon.Execute srrSql if err<>0 then response.write("No update permissions!") else response.write("Record " & Request.Form("txtid") & " was updated!") end if 'Reset server objects adoCon.Close() Set adoCon = Nothing Response.ReDirect "index.asp" %> -- end of code --        
|
|
|
|
#2
Jul 25th, 2005, 19:04
|
|||
|
|||
|
The sql needs to be executed in your loop rather than after it. Also be careful about typos, you've got adoCon.Execute srrSql
|
|
#3
Jul 25th, 2005, 19:04
|
||||
|
||||
|
Use this code for edit_records.asp
http://pastebin.webforumz.com//view.php?id=21 (you can copy and paste the code from the 'make changes' box below the formatted code. I think you'll be able to see what you did wrong... and quiet an easy mistake I might add.... it doesnt immediately hit you when looking at the code what was wrong. Anyway, let me know if it's ok. Rob
__________________
Click the 'Thanks!' button if this post has helped you Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
|
|
#4
Jul 25th, 2005, 19:15
|
|||
|
|||
|
I made the changes but still had the same problem
Hi I made the changes that you suggested
I moved the next to after the sql was executed and I corrrected the type srr but nothing gets updated. Here is the up to date code for edit_records.asp <% 'Dimension variables Dim adoCon 'Holds the Database Connection Object Dim rsUpdateEntry 'Holds the recordset for the record to be updated Dim strSQL 'Holds the SQL query to query the database Dim lngRecordNo 'Holds the record number to be updated 'Create an ADO connection object 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("stocks.mdb") Dim iTotalRows Dim i 'iTotalRows = Request.Form("iTotalRows") 'for ( i = 0 ; i <= iTotalRows; i++) for i = 1 to Request.Form("iTotalRows").Count txtid=Request.Form("txtid_" & i) txtcompanyname=Request.Form("txtcompanyname_" & i) txtsymbol=Request.Form("txtsymbol_" & i) txtpurdate=Request.Form("txtpurdate_" & i) txttradedate=Request.Form("txttradedate_" & i) txtshares=Request.Form("txtshares_" & i) txtcost=Request.Form("txtcost_" & i) txtvalue=Request.Form("txtvalue_" & i) 'Create an ADO recordset object Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset") strsql="UPDATE stocks SET " strsql=strsql & "txtcompanyname='" & Request.Form("txtcompanyname") & "'," strsql=strsql & "txtsymbol='" & Request.Form("txtsymbol") & "'," strsql=strsql & "txtpurdate='" & Request.Form("txtpurdate") & "'," strsql=strsql & "txttradedate='" & Request.Form("txttradedate") & "'," strsql=strsql & "txtshares='" & Request.Form("txtshares") & "'," strsql=strsql & "txtcost='" & Request.Form("txtcost") & "'," strsql=strsql & "txtvalue='" & Request.Form("txtvalue") & "'," 'Do for other columns strsql=strsql & " WHERE ID='" & Request.Form("txtid") 'Write the updated recordset to the database on error resume next adoCon.Execute strSql next if err<>0 then response.write("No update permissions!") else response.write("Record " & Request.Form("txtid") & " was updated!") end if 'Reset server objects adoCon.Close() Set adoCon = Nothing Response.ReDirect "index.asp" %>
|
|
#5
Jul 25th, 2005, 19:30
|
||||
|
||||
|
see my post above.... although catalyst points out one error, there were several.
fixed code here:- http://pastebin.webforumz.com//view.php?id=21
__________________
Click the 'Thanks!' button if this post has helped you Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
|
|
#6
Jul 25th, 2005, 19:38
|
|||
|
|||
|
Hi rob
I also checked out the code that you supplied I wasn't sure which one you made changes to but I tried both the top and the bottom code but when I clicked update on edit.asp nothing got changed for neither of the scripts that I copied and pasted. Quote:
|
|
#7
Jul 25th, 2005, 19:50
|
||||
|
||||
|
I didnt see the first file....
use this for edit.asp in conjuction with the edit_records.asp code I already gave you. http://pastebin.webforumz.com//view.php?id=24 If you have to paste us updated code, please use the pastebin:- http://pastebin.webforumz.com/pastebin.php
__________________
Click the 'Thanks!' button if this post has helped you Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
|
|
#8
Jul 26th, 2005, 13:06
|
||||
|
||||
|
Can you PLEASE use the pastebin!!!
Large amounts of code are very unsightly in the forums.... If you have to paste us updated code, please use the pastebin:- http://pastebin.webforumz.com/pastebin.php
__________________
Click the 'Thanks!' button if this post has helped you Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
|
|
#9
Jul 26th, 2005, 16:01
|
|||
|
|||
|
Sorry about that
Sorry about that I am new to this forum.
I was examining code that you provided and tried to tweak it a little but every time I make a change on the first page, it is not being processed on the second page Quote:
|
|
#10
Jul 28th, 2005, 14:32
|
||||
|
||||
|
Can you paste us the url the pastebin gives you when you've pasted your code there? otherwise we dont know the url and cannot access it.
We know you are new to this forum.... so we forgive you.. lol
__________________
Click the 'Thanks!' button if this post has helped you Rob - Webforumz Founder
Last Blog Entry: Creative Labs threaten developer over home made drivers.... (Apr 1st, 2008)
|
|
#11
Feb 1st, 2006, 16:57
|
|||
|
|||
|
Re: Updating multiple records at the same time prob. with script
I get a page cannot be displayed message when I click on your links. Am I doing something wrong? thanks.
|
|
#12
Feb 1st, 2006, 17:56
|
|||
|
|||
|
Re: Updating multiple records at the same time prob. with script
you need to disable friendly error messages. this link should help you
then tell us what the error message says
|
|
| Tags |
| updating, multiple, records, same, time, prob, script |
| Thread Tools | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Select records based on multiple conditions - need help | Love2Java | Databases | 9 | Oct 29th, 2007 14:37 |
| inserting and updating records in classic asp | codetoad | Classic ASP | 5 | Mar 26th, 2007 12:12 |
| Updating numerous pages at the same time. | mikka23 | Web Page Design | 12 | Jan 1st, 2007 20:22 |
| Array Multiple records | jfergy | Classic ASP | 6 | Jan 17th, 2006 16:44 |
| Appending multiple records | jakyra | Classic ASP | 0 | Sep 8th, 2003 19:33 |
