Solved

Update selected data in a MS Access table

Posted on 2011-02-18
5
929 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Solution works PERFECTLY!!  Thank you.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

762 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

11 Experts available now in Live!

Get 1:1 Help Now