JLohman
asked on
Update selected data in a MS Access table
I am using
- MSAccess
- Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
- FrontPage 2003
I have created a form to update data in a MSAccess table. I only want to allow the user to update 3 of the 7 fields. The three fields that the user is allowed to update are LibInternet, InterLink and InterWebmaster. The data in the other 4 fields should NOT be changed. Currently the update form is allowing the update of the 3 fields, but it is DELETING the content in the other 4 fields.
- MSAccess
- Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
- FrontPage 2003
I have created a form to update data in a MSAccess table. I only want to allow the user to update 3 of the 7 fields. The three fields that the user is allowed to update are LibInternet, InterLink and InterWebmaster. The data in the other 4 fields should NOT be changed. Currently the update form is allowing the update of the 3 fields, but it is DELETING the content in the other 4 fields.
ChartID = Request.querystring("ChartID")
'Get values for form below
dim city, state
sql = "SELECT * FROM tblGeneral WHERE ChartID=" & ChartID & ";"
set objrec = objConn.Execute(sql)
City = objrec("City")
State = objrec("State")
set objRec = nothing
'Now get the data for the form below
sql = "SELECT * FROM tblWeb WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn
'create variables and give them values from recordset fields
'Then use variables in form code below, rather than recordset fields
If not objrec.eof then
LibInternet = objrec("LibInternet")
InterLink = objrec("InterLink")
InterWebmaster = objrec("InterWebmaster")
WebDate = objrec("WebDate")
End If
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title><%=title%></title>
</head>
<body>
<h2><font face="Arial">
<%=title%> - <font color="#FF0000"><%=City%>, <%=State%></font></font>
</h2>
<form method="POST" action="End.asp">
<b><font face="Arial">
<p>
INTERNET website:
<input type="radio" name="LibInternet" value="Yes" <%If LibInternet = "Yes" then response.write "checked" End If%>>Yes
<input type="radio" name="LibInternet" value="No" <%If LibInternet = "No" then response.write "checked" End If%>>No<br>
URL:
<input type="text" name="Interlink" size="75" value="<%=InterLink%>"><br>
Site maintained by staff:
<input type="radio" name="InterWebmaster" value="Yes" <%If InterWebmaster = "Yes" then response.write "checked" End If%>>Yes
<input type="radio" name="InterWebmaster" value="No" <%If InterWebmaster = "No" then response.write "checked" End If%>>No<p>
<p>
<input type="submit" value="Submit"> </p>
<input type="hidden" name="WebDate" value="<%=Date()%>">
<input type="hidden" name="ChartID" value="<%=ChartID%>">
</font>
</form>
<%
objrec.close
set objrec=nothing
%>
</body>
</html>
ASKER
Attached is the code in end.asp
dim sql, objRec, strCol, ChartID, item
ChartID = Request.form("ChartID")
'If there was a record for this ChartID in tblWeb already, delete it.
sql = "DELETE * FROM tblWeb WHERE ChartID = " & ChartID
Set objrec = Server.CreateObject("ADODB.Recordset")
objrec.Open sql, objConn
'Start building your sql statement. We're going to insert a record into tblWeb for each ChartID.
sql = "INSERT INTO tblWeb ("
For each strCol in Request.Form
'Loop through the fields in the form
For each item in Request.Form(strCol)
If Len(Request(strCol)) > 0 then
strColumns = strColumns & strCol & ","
'Find out which columns need to be updated
'Following line does not allow apostrophe
'strValues = strValues & "'" & item & "',"
'The Replace code in the next line corrects apostrophe entries
strValues = strValues & "'" & Replace(item,"'","''") & "',"
'Find out what value is in those form fields
End If
Next
Next
strColumns = left(strColumns, len(strColumns)-1)
strValues = left(strValues, len(strValues)-2)
'Get rid of the commas at the end of the column and value strings
sql = sql & strColumns & ") VALUES (" & strValues & "')"
'Finish building your sql statement then execute the insert
objConn.Execute(sql)
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title><%=title%></title>
</head>
<body>
<h2 align="center"><%=title%></h2>
<p align="center"><i><font size="4">Thank you!</font></i></p>
<font face="Arial" color="#000080" size="2">
<p align="center"><a href="javascript:window.close();">Close window</a> and return to editing your Comparison Chart data.</p>
</body>
</html>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You appear to be deleting the entire record and then adding a new one, so where would you be expecting the other 4 fields to be getting their values from?
ASKER
Solution works PERFECTLY!! Thank you.
Since if you going to update only 3 fields out of 7
when write query to update only those 3, don't try to get and set data for another 4 fields
if you still can't figure it out, post the code of End.asp for analysis.