Updating multiple rows

Results 1 to 2 of 2

Thread: Updating multiple rows

  1. #1
    Join Date
    Dec 1969

    Default Updating multiple rows

    Hi<BR><BR>Wondering if someone can help, and I&#039;m not sure this is easy or not - but I reckon if I can grasp the concept behind this then I will shoot forward in my asp programming(!).<BR><BR>I want to update multiple rows in a table, say a list of students with an exam results for each student.<BR><BR>I can easily display a page with a list of the students and a blank box next to each one, ready to fill in with the exam result, but, when it comes to the database asp page, after clicking on send I get an error:<BR><BR>Provider error &#039;80020005&#039; <BR><BR>Type mismatch. <BR><BR>/courses/occ/admin/editexamdb.asp, line 73<BR><BR>I think this is because it is trying to update only one row, when in fact it should be updating five (because there were five students).<BR><BR>This is the code I used in the editexamdb.asp file. Is the problem in this page, ie. it should be looping or something. Or is the problem much more complex than I have made out??<BR><BR>Many thanks in advance for any help. (I&#039;ve highlighted line 73).<BR><BR>Is there an article on the Web about updating multiple rows.<BR>&#039;Dimension variables<BR>Dim adoCon &#039;Holds the Database Connection Object<BR>Dim rsUpdateEntry &#039;Holds the recordset for the record to be updated<BR>Dim strSQL &#039;Holds the SQL query for the database<BR><BR>&#039;Create an ADO connection odject<BR>Set adoCon = Server.CreateObject("ADODB.Connection")<BR><BR>&#0 39;Set an active connection to the Connection object using a DSN-less connection<BR>adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=F:???????????occexams.mdb" &";uid=;pwd=;"<BR><BR>&#039;Create an ADO recordset object<BR>Set rsUpdateEntry = Server.CreateObject("ADODB.Recordset")<BR><BR>&#03 9;Initialise the strSQL variable with an SQL statement to query the database<BR>strSQL = "queoccexams"<BR><BR>&#039;Set the cursor type we are using so we can navigate through the recordset<BR>rsUpdateEntry.CursorType = 2<BR><BR>&#039;Set the lock type so that the record is locked by ADO when it is updated<BR>rsUpdateEntry.LockType = 3<BR><BR>&#039;Open the tblComments table using the SQL query held in the strSQL varaiable<BR>rsUpdateEntry.Open strSQL, adoCon<BR><BR>&#039;Update the record in the recordset<BR><BR>THIS IS LINE 73<BR><BR>rsUpdateEntry.Fields("examresult") = Request.form("examresult")<BR><BR>&#039;Write the updated recordset to the database<BR>rsUpdateEntry.Update<BR><BR>&#039;Rese t server objects<BR>rsUpdateEntry.Close<BR>Set rsUpdateEntry = Nothing<BR><BR><BR> <BR><BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Updating multiple rows

    Firstly make a control array of the boxes for result i.e all the boxes where u will input the result should be named txt1,txt2,txt3,txt4,txt5 (You can replace txt with any name u like).<BR>Also make another control array of hideen boxes with the same naming convention but different name. The values of these hidden boxes should be some unique field in the table which will be updated.<BR><BR>suppose the hidden textbox array is htxt1,htxt2,htxt3,htxt4,htxt5.<BR><BR>Then in the action page, write <BR><BR>for i= 1 to 5<BR> connectionobject.execute("update tablename set fieldname=" & request("txt" & i) & " where unique field=" & request("htxt" & i))<BR><BR>This i think should work. Try it.<BR><BR>next

Posting Permissions

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