nv*********@caitele.com wrote:
OK, From what i can tell from your script is that you want a basic form
to enter in new customer equiptment into a database. I can see that you
want to enter in new data everytime you access this page, you are not
looking to update a customer record but enter in a new record.
You must have been looking at something else entirely. The OP's form
clearly displays current data in text boxes for updating. He wants to
be able to change existing values and pass these to the next page which
processes an update. There is no suggestion that he is looking to add
new records with these pages.
If he wants to be able to update one record at a time, his second page
should populate a form with the record associated with the ID number
passed in the querystring, and there is no need for text boxes or a
form on the first page. He can just write the recordset as a list or
in a table on the first page.
First Page:
<%
Do While Not objRecordset.EOF
Response.Write "<tr>"
Response.Write "<td align=center valign=top>" &
objRecordset("ContactName") & "</td>"
Response.Write "<td valign=top align=center>" &
objRecordset("Radio") &
"</td>"
Response.Write "<td valign=top align=center>" &
objRecordset("Mobile") &
"</td>"
Response.Write "<td valign=top align=center><a
href=supers_nxtlUpdate.asp?ID="
Response.Write objRecordset("Id") & ">Update</a></td>"
Response.Write "</tr><tr><td colspan=6><hr></td></tr><tr>"
& vbcrlf
objRecordset.MoveNext
Loop
objRecordset.Close : set ObjRecordset = Nothing
%>
In the second page, it's not a good idea to use a recordset to perform
an update, and dynamic sql should be avoided. He would be better
advised to use parameters and the command object, or better yet, a
saved parameter query.
Open Access, and go top the Query Tab. Click New Query in Design View,
and close the Show Tables dialogue box that appears. Switch to SQL
view and paste this in:
UPDATE tblContactList SET strContactName = [p1], strRadio = [p2],
strMobile = [p3] WHERE ID = [p4];
Save that as qUpdateContact, and then run the query. You are prompted
for values for p1, p2 etc. Enter something sensible to make sure the
query runs. Close Access, and in the second page do this:
<%
If Request.Form("Action") ="Submit" then
p1 = Request.Form("ContactName")
p2 = Request.Form("Radio")
p3 = Request.Form("Mobile")
p4 = Request.Form("ID")
'validate values for p1 - p4
strConnect = "Driver={Microsoft Access Driver (*.mdb)};
DBQ=\\CALSJ1\PMAPPS\nextel.mdb"
'or better still use the OLEDB provider:
'strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\\CALSJ1\PMAPPS\nextel.mdb"
set conn = Server.CreateObject("ADODB.Connection")
conn.Open strConnect
conn.qUpdateContact p1,p2,p3,p4
conn.Close : set conn = Nothing
Response.Write "Record Updated"
Else
....
'Write the form to the page, populating it with the record associated
with the ID passed in the QueryString
End If
%>
Saved parameter queries are so much easier to work with if you are
using Access. The query is constructed, tested and debugged within the
database, they help prevent SQL Injection attacks, and they are easier
to maintain. If a field name needs to be changed, for example, you
only need to do it in the database table and queries, rather than
working through all your scripts to do so. Oh, and you get rid of all
the problems associated with data type mismatches, and incorrectly
delimiting datatypes in concatenated dynamic SQL statements. No
Recordset object needs to be created, and the connection object is
created, opened, used, closed and destroyed in the space of 4 lines.
--
Mike Brind