CODEWITHSUNDEEP
sqlsql-serverasp.net-mvc-3dapper
Alan Ford
Alan Ford

Reputation: 375

Dapper SqlDateTime overflow using GETDATE() in SQL Server

I have the following code which gets the input from a form and calls a stored procedure to store it in a database.

 [HttpPost]
 public ActionResult Index(GuestMessage model)
 {
        if (ModelState.IsValid)
        { 
            using(IDbConnection conn = DatabaseAcess.OpenConnection())
            {
                const string storedProcedure = "dbo.InsertMessage";
                conn.Execute(storedProcedure, new GuestMessage { Name = model.Name, Email = model.Email, Message = model.Message }, null, null, CommandType.StoredProcedure);

                return View("ThankYou");
            }
         }
        return View();
}

There are three fields, Name, E-mail and Message that have to be filled in by the user. In the database I store the data that the user enters, from these three fields, and the time of the entry using a stored function called dbo.Insert Message. 

BEGIN
  BEGIN TRAN

  SET NOCOUNT ON;

  DECLARE @GuestID int;
  INSERT INTO Guest(Name, Email) VALUES (@Name, @Email);
  SELECT @GuestID = scope_identity();

  INSERT INTO Message (EntryDate, GuestID, Message, Status) 
  VALUES (GETDATE(), @GuestID, @Message, 2);

  COMMIT TRAN
END

Notice that I do not pass the entry date and time as an input parameter to the stored procedure since there is no need for that. I use the built-in function GETDATE() in SQL Server to determine the time a user has entered a message.

However, whenever I try to enter a message I get the following error:

SqlDateTime overflow. Must be between 1/1/1753 12:00:00AM and 12/31/9999 11:59:59PM

Upvotes: 2

Views: 1326

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1063714

I would suggest the following change:

conn.Execute(storedProcedure,
    new { Name = model.Name, Email = model.Email, Message = model.Message },
    commandType: CommandType.StoredProcedure);

The only actual change there is that I have swapped new GuestMessage { ... } for new { ... }. What I suspect is happening is that your GuestMessage type has additional properties that aren't needed, but which it is sending as parameters. A DateTime left at its default value is "00:00:00.0000000, January 1, 0001". By swapping to just new { ... }, we have explicitly limited the members we are sending to Name, Email and Message, because those are the only things defined.

Note: when using plain-text, dapper does use some voodoo to try to see which parameter names are actually used in the SQL, and it doesn't send anything that it knows isn't referenced in the SQL - however, it cannot do this with a stored procedure.

Upvotes: 1

Related Questions