[SOLVED] Unable to insert a record (SqlServer) using Subsonic 2.2

Unable to insert a record (SqlServer) using Subsonic 2.2

I use SqlServer 2008. Here is the DB schema: (Please note that this only to test SubSonic)

Create Table Dept
(
 DeptID int not null Identity(1,1) primary key,
 DeptName varchar(20),
 Location varchar(30) 
) 

GO

 Create Table tUsers
 (
 UserID int not null identity(1,1) primary key,
 UserName varchar(50)
 )
 GO

create table Emp
(
 EmpID int not null identity(1,1) primary key,
 DeptID int foreign key references dept(deptid),
 CreatedBy varchar(50),
 EmpName varchar(50)
 )

Here is the C# code to save an employee:

Emp em = new Emp();            
em.DeptID = 3;
em.CreatedBy = "Temp1";
em.EmpName = "Temp3";            
em.Save();

And here is the Sql profiler trace:

exec sp_executesql N'/* GetInsertSql(Emp) */ INSERT INTO [dbo].[Emp]([DeptID],[CreatedBy],[EmpName]) VALUES(@DeptID,@CreatedBy,@EmpName);SELECT SCOPE_IDENTITY() AS newID;',N'@DeptID int,@CreatedBy varchar(8000),@EmpName varchar(5)',@DeptID=3,@CreatedBy='',@EmpName='Temp3'

As can be seen, CreatedBy is always empty, even though value is passed from application. (Earlier, this column was a foreign key, but SubSonic always threw (Format) error and hence I changed this to varchar, based on a suggestion from SubSonic Forum).

Any idea what went wrong?

Thanks in advance.

Sathya Narayanan

Solution

CreatedBy is part of SubSonic's automatic audit fields. Columns named

CreatedBy
CreatedOn
ModifiedBy
ModifiedOn

are filled in automatically with the value of HttpContext.Current.User.Identity.Name and DateTime.Now.

That's also why you kept getting the format error.

I recommend to simply rename this column to not have kick in the audit mecanism.