spiderbaby
05-01-2003, 09:22 AM
I have a table in an access database that does not contain a primary key field or other unique identifier. The table contains 3 fields (last_name, first_name, ext), any of which could potentially need to be updated.
I'm having trouble opening and, subsequently, updating the fields due to not having a unique identifier to relate the old values to the new values. The code I've got right now is as follows:
<%
Query = "SELECT * FROM emps WHERE last_name='" & Request("last_name") & "' AND first_name='" & Request("first_name") & "' AND ext='" & Request("ext") & "';"
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "phonedir"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,1,2
If Request("submit")="Update" Then
'update record
RSlist("last_name")=Request("lname")
RSlist("first_name")=Request("fname")
RSlist("ext")=Request("extn")
RSlist.Update
%>
<html>
<body bgcolor="lightblue" text="black">
Employee updated.<br>
<a href="listemps.asp">return to list</a>
</body>
</html>
<%Else
'display record in form%>
<html>
<body bgcolor="lightblue" text="black">
<%
x1 = Request.form("last_name")
x2 = Request.form("first_name")
x3 = Request.form("ext")
%>
<form name="myform1" action="upemps.asp?last_name=<%=x1%>&first_name=<%=x2%>&ext=<%=x3%>">
<input type="text" name="last_name" value="<%=RSlist("last_name")%>">
<input type="text" name="first_name" value="<%=RSlist("first_name")%>">
<input type="text" name="ext" value="<%=RSlist("ext")%>">
<input type="submit" name="submit" value="Update">
</form>
</body>
</html>
<%End If
Set RSlist=Nothing
Set DataConn=Nothing
%>
Of course this doesn't work because the SQL query won't select updated record values that don't yet exist in the database. Is there any way I can allow the user to update any/all of the 3 fields without using a unique identifier field in the Access table?
Any help is appreciated.
I'm having trouble opening and, subsequently, updating the fields due to not having a unique identifier to relate the old values to the new values. The code I've got right now is as follows:
<%
Query = "SELECT * FROM emps WHERE last_name='" & Request("last_name") & "' AND first_name='" & Request("first_name") & "' AND ext='" & Request("ext") & "';"
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "phonedir"
Set RSlist = Server.CreateObject("ADODB.recordset")
RSlist.Open Query,DataConn,1,2
If Request("submit")="Update" Then
'update record
RSlist("last_name")=Request("lname")
RSlist("first_name")=Request("fname")
RSlist("ext")=Request("extn")
RSlist.Update
%>
<html>
<body bgcolor="lightblue" text="black">
Employee updated.<br>
<a href="listemps.asp">return to list</a>
</body>
</html>
<%Else
'display record in form%>
<html>
<body bgcolor="lightblue" text="black">
<%
x1 = Request.form("last_name")
x2 = Request.form("first_name")
x3 = Request.form("ext")
%>
<form name="myform1" action="upemps.asp?last_name=<%=x1%>&first_name=<%=x2%>&ext=<%=x3%>">
<input type="text" name="last_name" value="<%=RSlist("last_name")%>">
<input type="text" name="first_name" value="<%=RSlist("first_name")%>">
<input type="text" name="ext" value="<%=RSlist("ext")%>">
<input type="submit" name="submit" value="Update">
</form>
</body>
</html>
<%End If
Set RSlist=Nothing
Set DataConn=Nothing
%>
Of course this doesn't work because the SQL query won't select updated record values that don't yet exist in the database. Is there any way I can allow the user to update any/all of the 3 fields without using a unique identifier field in the Access table?
Any help is appreciated.