I am not really a professional programmer but an enthusiast who needs help. Here's my challenge:
I've got a table filled with data for the employees.
Presently, any one of our employees can turn to the "Update" web site on our site and kind in the new surname within the previous one that's displayed inside a textbox and click on submit, his surname is going to be up-to-date within the database. However, my supervisor really wants to keep your employees' previous surname within the same table because the new surname. Her idea happens when an worker types in the new surname within the textbox, it'll trigger the database to keep his previous surname inside a column known as "Alias" after which update his new surname within the "LastName" column. How do you proceed with this?
This is actually the structure on the table:
PeopleID (int) JobIDNum (int) EmployeeIDNum (varchar(25)) Email (varchar(100)) Password (varchar(50)) LastName (varchar(50)) FirstName (varchar(25)) Deleted (char(1)) Alias (varchar(50))
I appreciate any assistance and/or advice I'm able to get.
UPDATE TABLENAME SET Alias = LastName, LastName = @LastName WHERE PeopleID = @PeopleID
In which the
@LastName is really a parameter with new value,
@PeopleID is really a id of corresponding people.
Advise a saved procedure that you could call from ASP.
CREATE PROC UpdateEmp @EmpID int, @LastName varchar(50), --all the others properties you need to update this person AS BEGIN UPDATE Employee SET Alias = LastName, LastName = @LastName, --all the other properties you want updated. WHERE PeopleID = @EmpID; END
Refer to this as saved proc inside your ASP code such as this:
Dim cmd Dim ln Dim retCount Dim conn Set conn= Server.CreateObject("ADODB.Connection") Set cmd = Server.CreateObject("ADODB.Command") conn.Open "some connection string" With cmd .ActiveConnection = conn .Commandtext = "UpdateEmp" .CommandType = adCmdStoredProc .Parameters.Append .CreateParameter("@EmpID", adInteger, adParamInput, 10) .Parameters("@EmpID") = 22 'some Employee you get from your code .Parameters.Append .CreateParameter("@LastName", adVarChar, adParamInput, 50) .Parameters("@LastName") = "MyLastName" 'some Employee you get from your code .Execute ln, , adExecuteNoRecords End With Set cmd = Nothing
Do this update statement:
UPDATE Employee Set LastName = @NewLastName, Alias = LastName WHERE PeopleId = @Whatever