Record Saving and Updating

[dctechuser]

I am having a problem getting my computer to know what is an update or save. My application is saving a record to the datebase and letting me update a record, but I want the computer to indicate the DateTimeModified as the current date of the change. I am using a Sequel Server 2005 database which has five tables. The Call/Issue table is the main table holding all of the records that are added using combo box fields for Issue, resolution, resolution time, and time fields. When I go back to make a change to one of the records previously added, the computer changes my DateTimeAdded field. I just want the DateTimeModified field to change not the DateTimeAdded Field. I want this field on the record to remain the original date that the record was added.

How can I manange the Combo Boxes to do what I want so the computer knows there is an update and not a saved record taking place?

My coding:

Public Class TechCallIssue
Dim NewPosition As Integer
Dim txtPosition1 As Integer
Dim Position As Integer


Dim SelectedPosition As Integer
Dim IsDirty As Boolean
Private Sub MainCallIssueBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MainCallIssueBindingNavigatorSaveItem.Click

'If Me.IDTextBox.Text <= Position Then

MessageBox.Show("Record saved.")
Me.DateTimeAddedTextBox.Text = Date.Now()
Me.DateTimeModifiedTextBox.Text = Date.Now()
Me.TechnicianTextBox.Text = "G.Casto"
Me.UserAddedTextBox.Text = Me.TechnicianTextBox.Text
Me.UserModifiedTextBox.Text = Me.TechnicianTextBox.Text
Me.Validate()
Me.MainCallIssueBindingSource.EndEdit()
Me.MainCallIssueTableAdapter.Update(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue)
Me.MainCallIssueTableAdapter.FillBy(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text)
Exit Sub



MessageBox.Show("Record updated.")
Me.DateTimeModifiedTextBox.Text = Date.Now()
Me.UserModifiedTextBox.Text = Me.TechnicianTextBox.Text


Me.Validate()
Me.MainCallIssueBindingSource.EndEdit()
Me.MainCallIssueTableAdapter.Update(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue)
Me.MainCallIssueTableAdapter.FillBy(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text)


Exit Sub




End Sub

Private Sub TechCallIssue_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Time' table. You can move, or remove it, as needed.
Me.TimeTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Time)
'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.ResolutionTime' table. You can move, or remove it, as needed.
Me.ResolutionTimeTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.ResolutionTime)
'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Resolution' table. You can move, or remove it, as needed.
Me.ResolutionTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Resolution)
'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.Issue' table. You can move, or remove it, as needed.
Me.IssueTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.Issue)
'TODO: This line of code loads data into the 'ABC_Call_Issue_TrackingDataSet12.MainCallIssue' table. You can move, or remove it, as needed.

Me.MainCallIssueTableAdapter.Fill(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue)


Me.MainCallIssueTableAdapter.FillBy1(Me.ABC_Call_Issue_TrackingDataSet12.MainCallIssue, TechnicianTextBox.Text)
'Me.MainCallIssueBindingSource.AddNew()

Me.MainCallIssueBindingSource.MoveLast()
NewPosition = Me.MainCallIssueBindingSource.Position + 1
Position = Me.MainCallIssueBindingSource.Position
SelectedPosition = Me.MainCallIssueBindingSource.Position + 1
IsDirty = False


End Sub

I have a Details View and Data Grid View. The Details View has query that pulls open records based on the DateTimeAdded field. I want only those records less than 6 days old to show in the Details View and Data Grid View.


My Query:

Select ID, Issue, Resolution, ResolutionTime, DateTimeAdded, Time, Technician
Where Technician=@Technician
AND DateTimeAdded >= DATEDIFF(d, 0, GETDATE()-5)
AND DateTimeAdded < DATEDIFF(d, 0, GETDATE()+1)

This query is added to the Technician TextBox

DCTechUser

 

[RiverGuy]

Write Update triggers against the SQL Server tables.

 

[dctechuser]

Where do I write the Update Triggers? I have the SQL management Studio. Do I write the Triggers like stored procedures under programmability? Also, How does the trigger run whben updates are performed?

 

[RiverGuy]

You write the triggers in an application which can send T-SQL queries and commands to a database, such as Management Studio. The benefit of using triggers is that the tables are not reliant upon every application which can modify the data to ensure the column is updated--it happens "automatically" on the back-end.

Here is some basic trigger code:

CREATE TRIGGER t_TableName_LastModified
ON dbo.TableName
FOR UPDATE 
AS
BEGIN
     UPDATE a
     SET a.LastModified = GETDATE()
     FROM TableName a
     INNER JOIN INSERTED b
          ON a.PrimaryKeyColumn = b.PrimaryKeyColumn
END

If you notice, there is a table in the code called "INSERTED." This table is available in your trigger and contains your "new data." Either inserts or updated data, depending on the type of trigger.

A full explanation of triggers is beyond the scope of this post, so I suggest you read up on them. They will definitely come in handy for any database development you do.

http://msdn.microsoft.com/en-us/library/ms189799.aspx