SQL在日期时间字段周围放置两个单引号并且无法插入记录
我正在尝试插入 SQL 数据库表，但它不起作用。 因此，我使用 SQL Server Profiler 来查看它是如何构建查询的； 它显示的内容如下：
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
如您所见，它在日期时间字段周围放置了双引号两对单引号，从而在 SQL 中产生语法错误。 我想知道是否有什么我必须配置的地方？
任何帮助，将不胜感激。
这是环境详细信息：
- Visual Studio 2008
- .NET 3.5
- MS SQL Server 2005
这是我正在使用的 .NET 代码......
//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" });
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" });
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我怀疑您正在向日期时间字段添加单引号，并将其作为字符串发送？ 不要那样做。 使用日期时间类型作为参数，并且不要添加任何引号。
如果您向我们展示代码的 .Net 部分将会有所帮助。
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.
SP2 或 SP3 应该可以在 Profiler 中解决此问题
SP2 or SP3 should solve this issue in Profiler
试试这个：
注意单引号和“。” 而不是用“:”表示毫秒。 或者试试这个：
确保它不是毫秒。
Try this:
Note the single-quotes and "." instead of ":" for the milliseconds. Or try this:
To make sure it's not the milliseconds.
objPropConsumer.MoveDate 是字符串吗？ 它看起来像是填充了一个在开头和结尾都有撇号的字符串。 尝试将 objPropConsumer.MoveDate 替换为常量 '2009-04-30 00:00:00' 并查看是否有效。 如果确实如此，则问题出在设置或转换 MoveDate 的位置。
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.
我会在您的数据库类中查找问题。 也许
AddInParameter()方法会使用 DateTime 参数执行一些复杂的操作，例如添加格式化字符串或类似的愚蠢操作。为了与 MSSQL 一起使用，
CreateStoredProc()绝对应该返回SqlCommand的实例（还有您不想使用的 DbCommand 的其他子类）。 验证这一点，然后确保AddInParameter()将SqlParameter实例添加到Parameters 集合中，并且其DbType属性为DbType .DateTime及其Value属性的类型为System.DateTime。一旦参数被正确添加到
SqlCommand，它应该可以很好地与MSSQL存储过程以及DateTime数据一起工作（对我来说，它已经有无数次了）。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 ofSqlCommand(there are other subclasses of DbCommand which you don't want to use). Verify that, then ensure that theAddInParameter()adds an instance ofSqlParameterto the Parameters collection, that itsDbTypeproperty isDbType.DateTimeand itsValueproperty is of typeSystem.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).我刚刚遇到了同样的事情 - 事实证明，两个单引号只出现在分析器中。 潜在的（实际的）错误是我向存储过程传递了太多参数。
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.