Creating An Audit Table

Please, post whole code of this function

Changes Made On The Edit Page:
Added: If Not CStaff_Events.BeforeEdit(Page, cmdInsert.Parameters, sKeyFields) Then Return

Added: <!--#INCLUDE FILE = "App_Code/Staff_Events.vb" -->
Source File For Staff_Events.VB
<%@ Import Namespace="System"%>

<%@ Import Namespace="System.Data"%>

<%@ Import Namespace="System.Web.UI.WebControls"%>

<%@ Import Namespace="System.Configuration"%>

<%@ Import Namespace="System.Data.OleDb"%>
<script runat="server">
Public Class CStaff_Events
Public Shared Function BeforeEdit(Page As System.Web.UI.Page, parNewValues As OleDbParameterCollection, where As String) As Boolean
'Create An Audit History
Dim sSQLInsert As String = "INSERT INTO [Audit] ([Name], [Department], [Group], [Position], [Date], [Username], [IP]) VALUES (@ID, @Name, @Location, @Branch, @Department, @Group, @Position, @Date, @Username, @IP)"
Dim cn As New OleDbConnection(func.GetConnectionStr())

Dim cmdInsert As OleDbCommand = new OleDbCommand(sSQLInsert, cn)

cmdInsert.CommandType = System.Data.CommandType.Text
cmdInsert.Parameters.Add("@Name", OleDbType.VarChar).Value = func.TypeCast("Name", fldName.Text, "OleDbType.VarChar")
cmdInsert.Parameters.Add("@Department", OleDbType.VarChar).Value = func.TypeCast("Department", fldDepartment.SelectedValue, "OleDbType.VarChar")
cmdInsert.Parameters.Add("@Group", OleDbType.VarChar).Value = func.TypeCast("Group", fldGroup.SelectedValue, "OleDbType.VarChar")
cmdInsert.Parameters.Add("@Position", OleDbType.VarChar).Value = func.TypeCast("Position", fldPosition.SelectedValue, "OleDbType.VarChar")
cmdInsert.Parameters.Add("@Date", OleDbType.DBTimeStamp).Value = func.TypeCast("Date", func.DropDownListToDate(fldDayDate, fldMonthDate, fldYearDate), "OleDbType.DBTimeStamp")
cmdInsert.Parameters.Add("@Username", OleDbType.DBTimeStamp).Value = System.Web.HttpContext.Current.Session("User").Username
cmdInsert.Parameters.Add("@IP", OleDbType.DBTimeStamp).Value = System.Web.HttpContext.Current.Request.UserHostAddress
cn.Open()

cmdInsert.ExecuteNonQuery()

cn.Close()
Return True ' return true if you like to proceed with editing this record ' return false in other case
End Function
End Class
</script>

Try to use next code:
...

Dim txt As String = ""

Dim fldName As TextBox = Page.FindControl("fldName")

If Not IsNothing(fldName) Then txt = fldName.Text
cmdInsert.Parameters.Add("@Name", OleDbType.VarChar).Value = func.TypeCast("Name", txt, "OleDbType.VarChar")

...

I have modified my code to match the code posted, but now I am getting a new error.
BC30451: Name 'cmdInsert' is not declared.
Below is the line it is pointing to:

When I declare cmdInsert on the edit page, the following error is returned.
Object Reference Not Set
Code For Staff_Events:

String
[codebox]

If Not CStaff_Events.BeforeEdit(Page, cmdInsert.Parameters, sKeyFields) Then Return

[/codebox]

should be like:

[codebox]

If Not CStaff_Events.BeforeEdit(Page, cmd.Parameters, sKeyFields) Then Return

[/codebox]
Also, you aren't able to save Username and UserHostAddress in fields like DBTimeStamp - instead of this you should use VarChar field.

I've implemented the changes you mentioned and the edit page is now loading as it should. When committing any changes to the database, the following error message is returned.

Please, ensure that all saved data are correct and correspond to fields types in database.

You can also send me your project (including project file, output directory and database backup) on support@xlinesoft.com

I will investigate it on my box.

Hi Eugene,
I have sent the requested files to the support email address.

Thanks Eugene,
All is working now but I have now run into some other minor problems.

1. The User name Field in the database is always being left blank.
   
2. When I chose a department and the auto post back occurs to display the available groups in that department, only the first item in the drop down list is inserted. For example, if I chose Estate Management 4, Estate Management 1 is inserted into the database. It even occurs when I chose Trust Management 4, Trust Management 1 is inserted.

You have error in database structure.

In "Groups" talbe there should be not only linked field 'ID' (that is for Departments table), but also "GroupID" field (that will be included in Staff table)

I'm a bit lost to what you mean. Would you be able to elaborate a bit more?

Tables structure should be like this:
Departments

ID

Departments
Groups

IDDepartment------------------------------>Departments.ID

Groups

GroupID (autoinc)
Staff

ID

Location

Branch

Department------------------------------>Departments.ID

Group ------------------------------>Groups.GroupID

Name

Position

Date
Lookup Wizard properties:
Link Field - GroupID (autoinc)

Display Field - Groups

If I understand correctly, I will have to add an IDDepartment and GroupID Column to the Groups table and then link IDDepartment to the ID Column in the Departments Table? I will then link the the Department Column in the Staff Table to the ID Column in the Departments Table and also link Group in the Staff Table to GroupID in the Groups Column?

Hi Eugene,
I modified my table as recommended. After modifcation, the on select change function broke which I managed to fix. I can now add a record without any problems, but when editing / copying a record, the links no longer work (it points back to staff_list.aspx). Will I have to re-generate my asp.net pages using the new database. If so, would I be able to email a copy of the database to ensure everything is correct before re-generating the pages?

I've sent corrected projected with database to your e-mail.

Thank you for your information,i just get it!

Hi
Thank you very much,The information that you have posted really helped me.
Thank you
Langa Shabangu