Duplicate PK update problem ....

[David]

Hi,

I have a page which lets me select a user from a list by checking a
check box next to the specific user and pressing a submit button. The
Check box holds the value = 'UserID'.

A new form opens with the selected users details in text boxes for
editing if required. There are the following fields:
UserID (PK in table)
Password
Confirm Password
Email

When this form is submit it runs the following code:

______________________________________________________

<%
vUser = request.form("user")
vPass = request.form("pass")
cPass = request.form("Cpass")
vEmail = request.form("email")

If vPass <> cPass then
response.redirect "Customer_Edit_AccessData_Error.asp"

else

uSQL = "UPDATE OrderStatusAccess SET "
uSQL = uSQL & "UserID= '" & vUser & "'"
uSQL = uSQL & ", Password = '" & vPass & "' "
uSQL = uSQL & ", Email ='" & vEmail & "'"
uSQL = uSQL & ", CustomerID = '"& session("customer") & "';"

Set RS = adoDataConn.Execute(uSQL)

Response.Redirect "Customer_AccessData_Conf.asp"
'RS.close

end if
%>

____________________________


The problem is that I get the following error:

ADODB.Connection.1 error '80004005'

SQLState: S1000
Native Error Code: 1062
[TCX][MyODBC]Duplicate entry 'DavidH' for key 1


/SceneDouble/Orders/Tracking/Customer_AccessData_Update.asp, line 29


WHAT AM I DOING WRONG ?



THANKS

David

 

[Ray at]

If you're using the UserID as your PK, and that value is known when you're
doing your update, you should be using it in a WHERE clause. As it stands
now, you'd update ALL of your records to have the same username, password,
etc., so be glad you got an error. That is actually what is causing it is
that you're trying to give ALL your records the same PK. Try:

uSQL = "UPDATE OrderStatusAccess SET "
uSQL = uSQL & ", Password = '" & vPass & "' "
uSQL = uSQL & ", Email ='" & vEmail & "'"
uSQL = uSQL & ", CustomerID = '"& session("customer") & "'"
uSQL = uSQL & " WHERE UserID= '" & vUser & "'"

Ray at work

 

[TomB]

Your Update statement doesn't have a WHERE clause, so it's trying to Update
every record.
You want a statement like.....
UPDATE OrderStatusAccess Set Password='abc', Email='(e-mail address removed)',
CustomerID=123 WHERE UserID=456

You are also leaving yourself open to SQL Injection attacks. Review the
article at....

http://www.sqlsecurity.com (I think that's where it is.)

Tom B

 

[David Gordon]

Thanks Ray,

Why did'nt I think of that !

Works great now....Thanks.


David