Solved

Update selected data in a MS Access table

Posted on 2011-02-18
5
930 Views
Last Modified: 2012-05-11
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
Comment
Question by:JLohman
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 34931715
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
 

Author Comment

by:JLohman
ID: 34931783
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
 
LVL 11

Accepted Solution

by:
JoeNuvo earned 500 total points
ID: 34931823
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34931828
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
 

Author Closing Comment

by:JLohman
ID: 34933538
Solution works PERFECTLY!!  Thank you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now