SQL Server 2008日期时间不能通过c#插入，但可以在SQL Server Management Studio

本文关键字：SQL Server 但可以 Management Studio 插入 日期 2008 时间 不能 | 更新日期: 2023-09-27 18:07:45

我正在使用SQL Server存储过程，通过c#将数据放入数据库。当我从SQL Server管理工作室内运行存储过程时，它可以工作，但当我尝试用c#插入行时，它失败了，我找不到原因。我最初在存储过程中使用DATETIME类型时遇到了麻烦，但现在当我使用CAST时就可以工作了。我也试过传递DateTime值作为c#的字符串，但这也不起作用!?

我的SP如下:

ALTER PROCEDURE [dbo].[SaveFlightInfo]
(
        @FlightInfoID int,
        @AirportFrom varchar(5),
        @AirportTo varchar(5),
        @TimeDeparture datetime,
        @TimeArrival datetime,
        @Price float,
        @DateAdded datetime,
        @Carrier varchar(30),
        @Url varchar(300)
)
AS
DECLARE @sql as varchar(1000)

--If the primary key is zero then insert new record
IF ( @FlightInfoID = 0) BEGIN
DECLARE @sql3 as varchar(1000)
SET @sql ='
INSERT INTO '+@AirportFrom+'
(AirportFrom,
AirportTo,
TimeDeparture,
TimeArrival,
Price,
DateAdded,
Carrier,
Url)
 VALUES ('''+@AirportFrom+''',
'''+@AirportTo+''',
CAST('''+CAST(@TimeDeparture as varchar(50))+''' as DATETIME),
CAST('''+CAST(@TimeArrival as varchar(50))+''' as DATETIME),
CAST('''+CAST(@Price as varchar(10))+''' as FLOAT),
CAST('''+CAST(@DateAdded as varchar(50))+''' as DATETIME),
'''+@Carrier+''',
'''+@Url+''')
'
    END
    EXEC(@sql)

我的c#代码如下:

   Database db = DatabaseFactory.CreateDatabase("DBConnectionString");
        DbCommand dbCommand = db.GetStoredProcCommand("SaveFlightInfo");
        db.AddInParameter(dbCommand, "FlightInfoID", DbType.Int32, flightInfoID);
        db.AddInParameter(dbCommand, "AirportFrom", DbType.String, airportFrom);
        db.AddInParameter(dbCommand, "AirportTo", DbType.String, airportTo);
        db.AddInParameter(dbCommand, "TimeDeparture", DbType.DateTime, timeDeparture);
        db.AddInParameter(dbCommand, "TimeArrival", DbType.DateTime, timeArrival);
        db.AddInParameter(dbCommand, "Price", DbType.Int16, price);
        db.AddInParameter(dbCommand, "DateAdded", DbType.DateTime, dateAdded);
        db.AddInParameter(dbCommand, "Carrier", DbType.String, carrier);
        db.AddInParameter(dbCommand, "Url", DbType.String, url);
        IDataReader dr = db.ExecuteReader(dbCommand);

SQL Server 2008日期时间不能通过c#插入，但可以在SQL Server Management Studio

也可以使用带有参数的sp_executesql系统存储过程

DECLARE @sql as varchar(1000)
--If the primary key is zero then insert new record
IF (@FlightInfoID = 0)
BEGIN
  DECLARE @sql3 as varchar(1000)
  SET @sql = 'INSERT INTO ' + @AirportFrom + 
    '(AirportFrom, AirportTo, TimeDeparture, TimeArrival, Price, DateAdded, Carrier, Url)
     VALUES @AirportFrom, @AirportTo, @TimeDeparture, @TimeArrival, @Price, @DateAdded, @Carrier, @Url)'
END
EXEC sp_executesql @sql, N'@AirportFrom varchar(5), 
                           @AirportTo varchar(5), 
                           @TimeDeparture datetime,
                           @TimeArrival datetime, 
                           @Price float, 
                           @DateAdded datetime, 
                           @Carrier varchar(30), 
                           @Url varchar(300)', @AirportFrom,
                                               @AirportTo,
                                               @TimeDeparture,
                                               @TimeArrival,
                                               @Price,
                                               @DateAdded,
                                               @Carrier,
                                               @Url
相关文章：