How do I correctly modify entries in the AdventureWorks database, such as Employee JobTitle values, plus have the ModifiedDate update correctly?

Question

This is probably a super noob question, but i'm not sure what the correct or a correct way is to modify records in the database.
After modifying the Employee JobTitle or Title values, the database no longer allows access to those employee rows.
Is there a sample application that I should be using, or should I use the stored procedure uspUpdateEmployeePersonalInfo ?

I just need to accomplish a simple value change and have the modified date updated to either a more current time/date or the current time/date of the update.

Thanks :)

Answer 1

You can use a stored procedure, but the stored procedure just contains an UPDATE statement.  UPDATE is the statement you want to use to modify existing rows in the database.  The UPDATE statement is explained here: http://msdn.microsoft.com/en-us/library/ms177523.aspx  Here's an example

UPDATE HumanResources.Employee
SET JobTitle = 'New title you want'
WHERE JobTitle = 'Existing title'

and you're done.  No need to tweak the ModifiedDate.  That will happen automatically because the ModifiedDate field as a DEFAULT constraint that set the value to the current date by using the GETDATE() function.  However, you could override that default value by doing something like this:

UPDATE HumanResources.Employee
SET JobTitle = 'New title you want' ,
       ModifiedDate = '20091003'  -- using the format 'yyyymmdd'
WHERE JobTitle = 'Existing title'


Kind regards,
Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Thanks Gail :)

I figured out a couple of things.  The first, my import errors following these modifications were because i had mapped the Title property as a 25 character string (default) in SharePoint (BDC profile import).  The fields were too long.  Corrected that.

The other thing i found is that the ModifiedDate does not get updated automatically when you update an entry in the [AdventureWorks].[HumanResources].[Employee] table.  had to run this query to update that at the same time:

UPDATE [AdventureWorks].[HumanResources].[Employee]
SET ModifiedDate = getDate()
WHERE EmployeeID = '1'

Thanks for getting me thinking in the right direction!
I was thinking my edits were breaking the table, when they were actually breaking the profile import mapped property :|

Many Thanks,
Mike

Answer 3

Glad you figured things out.  You're right that the default constraint on the ModifiedDate doesn't get updated via an UPDATE statement.  I'm going to plead "lack of caffine" on that one.

Regards,
Gail

---

Gail Erickson [MS] This posting is provided "AS IS" with no warranties, and confers no rights