SQL putting two single quotes around datetime fields and fails to insert record

Question

I am trying to INSERT into an SQL database table, but it doesn't work. So I used the SQL server profiler to see how it was building the query; what it shows is the following:

declare @p1 int
set @p1=0
declare @p2 int
set @p2=0
declare @p3 int
set @p3=1
exec InsertProcedureName @ConsumerMovingDetailID=@p1 output, @UniqueID=@p2 output, 
                         @ServiceID=@p3 output, @ProjectID=N'0', @IPAddress=N'66.229.112.168', 
                         @FirstName=N'Mike', @LastName=N'P', @Email=N'[email protected]', 
                         @PhoneNumber=N'(254)637-1256', @MobilePhone=NULL, @CurrentAddress=N'', 
                         @FromZip=N'10005', @MoveInAddress=N'', @ToZip=N'33067', 
                         @MovingSize=N'1', @MovingDate=''2009-04-30 00:00:00:000'', 
                               /*        Problem here  ^^^  */
                         @IsMovingVehicle=0, @IsPackingRequired=0, @IncludeInSaveologyPlanner=1
select @p1, @p2, @p3

As you can see, it puts a double quote two pairs of single quotes around the datetime fields, so that it produces a syntax error in SQL. I wonder if there is anything I must configure somewhere?

Any help would be appreciated.

Here is the environment details:

• Visual Studio 2008
• .NET 3.5
• MS SQL Server 2005

Here is the .NET code I'm using....

//call procedure for results
strStoredProcedureName = "usp_SMMoverSearchResult_SELECT";

Database database = DatabaseFactory.CreateDatabase();
DbCommand dbCommand = database.GetStoredProcCommand(strStoredProcedureName);
dbCommand.CommandTimeout = DataHelper.CONNECTION_TIMEOUT;

database.AddInParameter(dbCommand, "@MovingDetailID", DbType.String, objPropConsumer.ConsumerMovingDetailID);
database.AddInParameter(dbCommand, "@FromZip", DbType.String, objPropConsumer.FromZipCode);
database.AddInParameter(dbCommand, "@ToZip", DbType.String, objPropConsumer.ToZipCode);
database.AddInParameter(dbCommand, "@MovingDate", DbType.DateTime, objPropConsumer.MoveDate);
database.AddInParameter(dbCommand, "@PLServiceID", DbType.Int32, objPropConsumer.ServiceID);
database.AddInParameter(dbCommand, "@FromAreaCode", DbType.String, pFromAreaCode);
database.AddInParameter(dbCommand, "@FromState", DbType.String, pFromState);
database.AddInParameter(dbCommand, "@ToAreaCode", DbType.String, pToAreaCode);
database.AddInParameter(dbCommand, "@ToState", DbType.String, pToState);

DataSet dstSearchResult = new DataSet("MoverSearchResult");
database.LoadDataSet(dbCommand, dstSearchResult, new string[] { "MoverSearchResult" });

Answer 1

Try this:

'2009-04-30 00:00:00.000'

Note the single-quotes and "." instead of ":" for the milliseconds. Or try this:

'2009-04-30 00:00:00'

To make sure it's not the milliseconds.

Answer 2

I suspect you are adding single quotes to your datetime field, and sending it as a string? Don't do that. Use a datetime type for the parameter and don't add any quotes to it.

It would help if you showed us the .Net side of the code.

Answer 3

Is objPropConsumer.MoveDate a string? It looks like it is populated with a string that has apostrophes at the beginning and end. Try replacing the objPropConsumer.MoveDate with a constant '2009-04-30 00:00:00' and see if that works. If it does, the problem lies where the MoveDate is set or converted.

Answer 4

I'm having the same problem using Linq to SQL. I'm setting my objects DateTime property = DateTime.Now and it translates to @p3=''2009-05-12 14:56:49:037'' - SQL doesn't like that.

Answer 5

I've the same problem. But, when I check the database, all data is inserted properly. Only thing is, it not showing the correct SQL Query in the profiler. The dates are outside the single quotes in profiler even if the data getting inserted from the application.

Any idea why?

Answer 6

Same problem here. Someone has found the solution?

Answer 7

SP2 or SP3 should solve this issue in Profiler

Answer 8

I would look for the problem in your Database class. Perhaps the AddInParameter() method performs some jiggery-pokery with DateTime parameters, like adding a formatted string or something silly like that.

For use with MSSQL, the CreateStoredProc() absolutely should return an instance of SqlCommand (there are other subclasses of DbCommand which you don't want to use). Verify that, then ensure that the AddInParameter() adds an instance of SqlParameter to the Parameters collection, that its DbType property is DbType.DateTime and its Value property is of type System.DateTime.

Once parameters are properly added to a SqlCommand, it should work well with MSSQL stored procedures, also with DateTime data (it has for me, zillions of times).

Answer 9

I just encountered the same thing - it turns out that the two single quotes were only showing up in the profiler. The underlying (actual) error was that I was passing too many arguments to the stored procedure.