Solved

Update selected data in a MS Access table

Posted on 2011-02-18
5
939 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 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

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 …
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

728 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