Cathal O 'Donnell Cathal O 'Donnell - 3 months ago 13
ASP.NET (C#) Question

MVC -String or binary data would be truncated.\r\nThe statement has been terminated

I understand that this error means that I am trying to insert a value that is larger than what I allow in my database but I cannot see which value is causing the problem

This is where I am inserting the data:

UserActivity log = new UserActivity();

ApplicationDbContext db = new ApplicationDbContext();

log.UserEmail = User.Identity.Name;
log.SQL_Query = queryString;
log.Query_Start = String.Format("{0:hh:mm:ss}", start);
log.Query_End = String.Format("{0:hh:mm:ss}", end);
log.Query_Time = String.Format("{0:mm:ss.ff}", Duration.ToString());

db.UserActivities.Add(log);

db.SaveChanges();


This is the database table definition:

CREATE TABLE [dbo].[UserActivities] (
[Id] INT NOT NULL,
[UserEmail] VARCHAR (255) NOT NULL,
[SQL_Query ] VARCHAR (1000) NOT NULL,
[Query_Start] VARCHAR (11) NULL,
[Query_End] VARCHAR (11) NULL,
[Query_Time] VARCHAR (11) NULL,
[Error_Message] VARCHAR (255) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);


In the debugger I using the length command to look at the length of the values I am passing in, this was my results:

log.UserEmail.Length;
21
log.Query_End.Length;
8
log.Query_Start.Length;
8
log.SQL_Query.Length;
32


This is a sample of the data that would be entered
enter image description here

This is the UserActivity class

public class UserActivity
{
[Key]
public int Id { get; set; }
public string UserEmail { get; set; }
public string SQL_Query { get; set; }
public string Query_Start { get; set; }
public string Query_End { get; set; }
public string Query_Time { get; set; }
public string Error_Message { get; set; }
}

Answer

With the logic shown below, I'm getting a value of "00:02:00.0030060" in log.Query_Time, which is why I suspect that is where you are having an issue. That string is 16 characters, where your Query_Time column in the database is only 11 characters.

public class UserActivity
{
  public string UserEmail { get; set; }
  public string SQL_Query { get; set; }
  public string Query_Start { get; set; }
  public string Query_End { get; set; }
  public string Query_Time { get; set; }
}

static void Main(string[] args)
{
  UserActivity log = new UserActivity();
  DateTime start = DateTime.Now - TimeSpan.FromMinutes(2);
  DateTime end = DateTime.Now;
  TimeSpan Duration = end - start;

  log.UserEmail = "email@here.gov";
  log.SQL_Query = "exec FTSP_FTProblemsByCategory 5";
  log.Query_Start = String.Format("{0:hh:mm:ss}", start);
  log.Query_End = String.Format("{0:hh:mm:ss}", end);
  log.Query_Time = String.Format("{0:mm:ss.ff}", Duration.ToString());
}
Comments