将数据类型nvarchar转换为datetime时出错。 [英] Error converting data type nvarchar to datetime.
问题描述
我有每个日期所需的数据类型datetime字段仍然我得到错误将数据类型nvarchar转换为datetime。这个错误。我为此制作了一个存储过程。还有一张桌子。
在我试过的情况下我已经给出了数据库中的表，还有错误来的代码和商店程序代码
我尝试过：
桌面设计：
customeridno varchar（50）未选中
orderno varchar（50）未选中
applicationFormNo varchar（50）
expectedOrderDelivery datetime
testCode varchar（50）
cashDiscount float
cashAmt float未选中
备注varchar（50 ）
approveRemark varchar（50）
orderDate datetime
我收到此错误的代码：< br $>
公共字符串ExecuteNonQuerySP（字符串cmdTExt，ArrayList arrParam，ArrayList arrValue）
{
SqlParameter RetValue = new SqlParameter （）;
OpenConnection（）;
m.CommandType = CommandType.StoredPr ocedure;
m.CommandText = cmdTExt;
m.Connection = k;
if（（arrParam！= null））
{
for（Int32 i = 0;我< = arrParam.Count - 1; i ++）
{
m.Parameters.AddWithValue（arrParam [i] .ToString（），arrValue [i] .ToString（））;
}
}
{
RetValue = m.Parameters.Add（@ strReturn，SqlDbType.Int）;
RetValue.Direction = ParameterDirection.Output;
m.ExecuteNonQuery（）;
返回RetValue.ToString（）;
}
存储过程：
USE [rr]
GO
/ ******对象：StoredProcedure [dbo]。[orderHeader6]脚本日期：03/10/2016 12： 22:51 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER程序[dbo]。[orderHeader6]
（
@type char（1），
@customerName varchar（50），
@customeridno varc har（50），
@orderno varchar（50），
@applicationFormNo varchar（50），
@expectedOrderDelivery datetime，
@testRequriment char（10），
@partShipment char（10），
@status varchar（50），
@testCode varchar（50），
@cashDiscount float，
@cashAmt float，
@remark varchar（50），
@approveRemark varchar（50），
@orderDate datetime，
@totalQuantity float，
@netAmt float，
@excise浮动，
@cst浮动，
@otherCharges浮动，
@totalAmt浮动，
@totalExcies浮动，
@totalCst浮动，
@totalOtherCharges浮动，
@strReturn int输出
）
AS
开始交易
如果@ type ='我'
开始
如果不存在（从orderHeader3中选择customeridno，其中customeridno = @ customeridno）
开始
插入orderHeader3（customername，customeridno，orderno，applicationFormNo，expectedOrderDelivery，testRequirement，
partShipmentt，stauts，testCode，cashDiscount，cashAmt，remark，approveRemark，orderDate，totalQuantity，netAmt，excies，
cst，otherCharges，totalAmt，totalExercise，totalCst，totalOtherCharges）值
（
@ customerName，@ customeridno，@ orderno，@ applicationFormNo，@ expectedOrderDelivery，@ testRequriment， @partShipment，
@ status，@ testCode，@ cashDiscount，@ cashAmt，@ remark，@ approveRemark，@ orderDate，@ totalQuantity，@ netAmt，@ excise，@ cst，
@ otherCharges，@ totalAmt，
@ totalExcies，@ totalCst，@ totalOtherCharges
）
提交交易;
设置@strReturn = 1 - 成功
结束
结束
如果@ type ='你好b $ b开始
如果存在（从orderHeader3中选择customeridno，其中customeridno = @customeridno）
begin
更新orderheader3
set customername = @ customerName，customeridno = @ customeridno，orderno = @ orderno，applicationFormNo = @ applicationFormNo，
expectedOrderDelivery = @ expectedOrderDelivery，testRequirement = @ testRequriment ，partShipmentt = @ partShipment，stauts = @ status，
testCode = @ testCode，cashDiscount = @ cashDiscount，cashAmt = @ cashAmt，remark = @ remark，approveRemark = @ approveRemark，orderDate = @ orderDate，< br $>
totalQuantity = @ totalQuantity，netAmt = @ netAmt，excies = @ excise，cst = @ cst，otherCharges = @ otherCharges，totalAmt = @ totalAmt，totalExercise = @ totalExcies，
totalCst = @ totalCst，totalOtherCharges = @ totalOtherCharges
其中customeridno = @ customeridno;
提交交易
set @strReturn = 1 - 成功
结束
其他
开始
回滚交易;
set @ strReturn = 2 --alreadythr
end
end
如果@ type ='D'
开始
从orderHeader3删除其中customeridno = @customeridno;
提交交易;
设置@strReturn = 1 - 删除
结束
i have datatype datetime for every date required field still i am getting Error converting data type nvarchar to datetime. this error. i have made a stored procedure made for this. and a table also.
in what i have tried i have given the table in the database,also the code where the error is coming and the store procedure code
What I have tried:
table design:
customeridno varchar(50) Unchecked
orderno varchar(50) Unchecked
applicationFormNo varchar(50)
expectedOrderDelivery datetime
testCode varchar(50)
cashDiscount float
cashAmt float Unchecked
remark varchar(50)
approveRemark varchar(50)
orderDate datetime
code in which i am getting this error:
public string ExecuteNonQuerySP(string cmdTExt, ArrayList arrParam, ArrayList arrValue)
{
SqlParameter RetValue = new SqlParameter();
OpenConnection();
m.CommandType = CommandType.StoredProcedure;
m.CommandText = cmdTExt;
m.Connection = k;
if ((arrParam != null))
{
for (Int32 i = 0; i <= arrParam.Count - 1; i++)
{
m.Parameters.AddWithValue(arrParam[i].ToString(), arrValue[i].ToString());
}
}
{
RetValue = m.Parameters.Add("@strReturn", SqlDbType.Int);
RetValue.Direction = ParameterDirection.Output;
m.ExecuteNonQuery();
return RetValue.ToString();
}
stored procedure:
USE [rr]
GO
/****** Object: StoredProcedure [dbo].[orderHeader6] Script Date: 03/10/2016 12:22:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[orderHeader6]
(
@type char(1),
@customerName varchar(50),
@customeridno varchar(50),
@orderno varchar(50),
@applicationFormNo varchar(50),
@expectedOrderDelivery datetime,
@testRequriment char(10),
@partShipment char(10),
@status varchar(50),
@testCode varchar(50),
@cashDiscount float,
@cashAmt float,
@remark varchar(50),
@approveRemark varchar(50),
@orderDate datetime,
@totalQuantity float,
@netAmt float,
@excise float,
@cst float,
@otherCharges float,
@totalAmt float,
@totalExcies float,
@totalCst float,
@totalOtherCharges float,
@strReturn int output
)
AS
begin transaction
if @type='I'
begin
if not exists (select customeridno from orderHeader3 where customeridno=@customeridno)
begin
insert into orderHeader3(customername,customeridno,orderno,applicationFormNo,expectedOrderDelivery,testRequirement,
partShipmentt,stauts,testCode,cashDiscount,cashAmt,remark,approveRemark,orderDate,totalQuantity,netAmt,excies,
cst,otherCharges,totalAmt,totalExercise,totalCst,totalOtherCharges) values
(
@customerName,@customeridno,@orderno,@applicationFormNo,@expectedOrderDelivery,@testRequriment,@partShipment,
@status,@testCode,@cashDiscount,@cashAmt,@remark,@approveRemark,@orderDate,@totalQuantity,@netAmt,@excise,@cst,
@otherCharges,@totalAmt,
@totalExcies,@totalCst,@totalOtherCharges
)
commit transaction;
set @strReturn = 1 --success
end
end
else if @type= 'U'
begin
if exists (select customeridno from orderHeader3 where customeridno= @customeridno)
begin
Update orderheader3
set customername=@customerName,customeridno=@customeridno,orderno=@orderno,applicationFormNo=@applicationFormNo,
expectedOrderDelivery=@expectedOrderDelivery,testRequirement=@testRequriment,partShipmentt=@partShipment,stauts=@status,
testCode=@testCode,cashDiscount=@cashDiscount,cashAmt=@cashAmt,remark=@remark,approveRemark=@approveRemark,orderDate=@orderDate,
totalQuantity=@totalQuantity,netAmt=@netAmt,excies=@excise,cst=@cst,otherCharges=@otherCharges,totalAmt=@totalAmt,totalExercise=@totalExcies,
totalCst=@totalCst,totalOtherCharges=@totalOtherCharges
where customeridno=@customeridno;
commit transaction
set @strReturn = 1 --success
end
else
begin
rollback transaction;
set @strReturn=2 --alreadythr
end
end
else if @type='D'
begin
Delete from orderHeader3 where customeridno= @customeridno;
commit transaction;
set @strReturn = 1 --Deleted
end
推荐答案
我可以看到你要做什么，但是停止将它们转换为strings：将它们作为实际数据类型发送。
这意味着整数以整数形式出现，双精度变为双精度数，日期变为DateTime值。将DateTime转换为字符串时，会生成本地文化中的字符串 - 很可能与SQL Server使用的字符串不同。因此，当它试图将其转换回DATETIME值进行存储时，它必须猜测它所处的格式，如果它是错误的，则会得到您描述的错误。你无法在SQL端解决这个问题 - 因为两个不同的用户可能会使用不同的文化，尽管它们彼此相邻 - 所以你必须通过传递真实值而不是字符串来解决它。
I can see what you are trying to do, but stop converting them to strings: send them through as the actual data type.
That means that integers go as integers, doubles go as doubles, and dates go as DateTime values. When you convert a DateTime to string, you generate a string that is in the local culture - which is quite likely not to be the same as that used by the SQL server. So when it tries to convert it back to a DATETIME value for storage, it has to guess what format it's in, and if it's wrong you get the error you describe. You can't solve that at the SQL end - because two different users may use different cultures despite being on desks next to each other - so you have to solve it by passing "real" values instead of strings.
这篇关于将数据类型nvarchar转换为datetime时出错。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
