HarryGreiner
12-14-2005, 11:23 AM
I'm trying to update my database from a web form.
Users are presented with a form that is populated from the database, they can then amend / add/ delete details and when the click "Submit" their data is loaded back to the database.
The Primary Key is "id" - an autonumber field with no duplicates allowed.
However, when they click "Submit" and the data is uploaded it just replaces the data at id=1 i.e. the first record.
The code I'm using is
<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/forchecking.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>
<p>
Thank you. Your course -
<%
DIM strName
strName = Request.Form("title")
Response.Write strName
%>- has been updated to a temporary server for checking before it goes live. Please click the "Submit for checking" button below to notify Marketing for checking and approval.</p>
If I add the "id" field to the update statement thus
<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/forchecking.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3
rst("id") = Request.Form("id")
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>
<p>
Thank you. Your course -
<%
DIM strName
strName = Request.Form("title")
Response.Write strName
%>- has been updated to a temporary server for checking before it goes live. Please click the "Submit for checking" button below to notify Marketing for checking and approval.</p>
I get the error
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/course_updates/updater.asp, line 34
Line 34 being the added "id" line.
Am I making some foolish error? Or am I going about the whole thing wrong? Do I need a "WHERE id=" clause in there somewhere?
Thanks for any help / advice you can give.
Users are presented with a form that is populated from the database, they can then amend / add/ delete details and when the click "Submit" their data is loaded back to the database.
The Primary Key is "id" - an autonumber field with no duplicates allowed.
However, when they click "Submit" and the data is uploaded it just replaces the data at id=1 i.e. the first record.
The code I'm using is
<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/forchecking.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>
<p>
Thank you. Your course -
<%
DIM strName
strName = Request.Form("title")
Response.Write strName
%>- has been updated to a temporary server for checking before it goes live. Please click the "Submit for checking" button below to notify Marketing for checking and approval.</p>
If I add the "id" field to the update statement thus
<%
Response.Buffer = true
dim cnn,rst
set cnn = Server.CreateObject("ADODB.Connection")
set rst = Server.CreateObject("ADODB.RecordSet")
cnn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("../scripts2/courses2005/forchecking.mdb")
sqltext = "SELECT * FROM courselist"
rst.Open sqltext,cnn,3,3
rst("id") = Request.Form("id")
rst("title") = Request.Form("title")
rst("qualification") = Request.Form("qualification")
rst("level") = Request.Form("level")
rst("school") = Request.Form("school")
rst("attendance") = Request.Form("attendance")
rst("lastsfor") = Request.Form("lastsfor")
rst("coursefor") = Request.Form("coursefor")
rst("coursecontent") = Request.Form("coursecontent")
rst("entryrequirements") = Request.Form("entryrequirements")
rst("collegetime") = Request.Form("collegetime")
rst("trips") = Request.Form("trips")
rst("assessments") = Request.Form("assessments")
rst("progression") = Request.Form("progression")
rst("fees") = Request.Form("fees")
rst("costs") = Request.Form("costs")
rst("apply") = Request.Form("apply")
rst("contact") = Request.Form("contact")
rst.update
rst.Close
Set rst= Nothing
cnn.close
Set cnn= Nothing
%>
<p>
Thank you. Your course -
<%
DIM strName
strName = Request.Form("title")
Response.Write strName
%>- has been updated to a temporary server for checking before it goes live. Please click the "Submit for checking" button below to notify Marketing for checking and approval.</p>
I get the error
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
/course_updates/updater.asp, line 34
Line 34 being the added "id" line.
Am I making some foolish error? Or am I going about the whole thing wrong? Do I need a "WHERE id=" clause in there somewhere?
Thanks for any help / advice you can give.