• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

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.



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:&nbsp;&nbsp;&nbsp;&nbsp;
    <input type="radio" name="LibInternet" value="Yes" <%If LibInternet = "Yes" then response.write "checked" End If%>>Yes&nbsp;&nbsp;&nbsp;&nbsp;
    <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:&nbsp; 
    <input type="radio" name="InterWebmaster" value="Yes" <%If InterWebmaster = "Yes" then response.write "checked" End If%>>Yes&nbsp;&nbsp;&nbsp;&nbsp;
    <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>

Open in new window

0
JLohman
Asked:
JLohman
  • 2
  • 2
1 Solution
 
JoeNuvoCommented:
I think it's problem of your code to update the data back into database.

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.
0
 
JLohmanAuthor Commented:
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>

Open in new window

0
 
JoeNuvoCommented:
I see.
so the problem is, your process is delete and re-insert, not an update.
you should change the way of code to perform insert.
and since it's only 3 fields, you might not have to do the for each loop.

remove your code from line 4 ('If there....) until line 32 ('Finish building....)
and replace with below code

sql = "UPDATE tblWeb SET " &_
	" LibInternet    = '" & Replace(Request("LibInternet"),"'","''") & "'" &_
	",Interlink      = '" & Replace(Request("Interlink"),"'","''") & "'" &_
	",InterWebmaster = '" & Replace(Request("InterWebmaster"),"'","''") & "'" &_
	",WebDate        = '" & Replace(Request("WebDate"),"'","''") & "'" & _
	" WHERE ChartID = " & Request("ChartID")

Open in new window

0
 
peter57rCommented:
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?
0
 
JLohmanAuthor Commented:
Solution works PERFECTLY!!  Thank you.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now