?
Solved

Update selected data in a MS Access table

Posted on 2011-02-18
5
Medium Priority
?
940 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

771 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