我用的是.net C# winfrom 及Sqlserver
代码如下:
string s = "update Goods set goodsName = N'" + GoodsName + "',goodsCost = '" + GoodsCost + "',goodsSalePrice = '" + GoodsSalePrice + "',";
s = s +"goodsDiscost = '" + GoodsDiscost + "',goodsSupplierId = '" + GoodsSupplier + "',goodsStockQty = " + GoodsQty + ",goodsNote = N'" + GoodsNote + "'";
s = s + " where goodsId = '" + GoodsID + "'";
SqlOperation.ExecuteSQL(s);其中goodsDiscost,goodsCost,goodsSalePrice 在数据库中我定义的都是decimal(6,2)。如果我直接对数据库操作例如:update Goods set goodsCost = '12.50' where gooodsId = 'sok4567' 是没有问题的,但是如果用上述语句操作就不行。就不知道为什么goodsDiscost,goodsCost,goodsSalePrice 怎么赋值都是不对的。
请哪位大侠帮帮忙解解惑。谢谢了!附:SqlOperation中的 ExecuteSQL代码如下
public static int ExecuteSQL(string SQL)
{
SqlConnection sqlConn = new SqlConnection(connstring);
SqlCommand cmd = new SqlCommand(SQL, sqlConn); try
{
sqlConn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex) //每次都是这里的报错:将 varchar转换为数据类型 numeric 时出现算术溢出错误
{ throw ex; }
finally
{
cmd.Dispose();
sqlConn.Close();
}
}
代码如下:
string s = "update Goods set goodsName = N'" + GoodsName + "',goodsCost = '" + GoodsCost + "',goodsSalePrice = '" + GoodsSalePrice + "',";
s = s +"goodsDiscost = '" + GoodsDiscost + "',goodsSupplierId = '" + GoodsSupplier + "',goodsStockQty = " + GoodsQty + ",goodsNote = N'" + GoodsNote + "'";
s = s + " where goodsId = '" + GoodsID + "'";
SqlOperation.ExecuteSQL(s);其中goodsDiscost,goodsCost,goodsSalePrice 在数据库中我定义的都是decimal(6,2)。如果我直接对数据库操作例如:update Goods set goodsCost = '12.50' where gooodsId = 'sok4567' 是没有问题的,但是如果用上述语句操作就不行。就不知道为什么goodsDiscost,goodsCost,goodsSalePrice 怎么赋值都是不对的。
请哪位大侠帮帮忙解解惑。谢谢了!附:SqlOperation中的 ExecuteSQL代码如下
public static int ExecuteSQL(string SQL)
{
SqlConnection sqlConn = new SqlConnection(connstring);
SqlCommand cmd = new SqlCommand(SQL, sqlConn); try
{
sqlConn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex) //每次都是这里的报错:将 varchar转换为数据类型 numeric 时出现算术溢出错误
{ throw ex; }
finally
{
cmd.Dispose();
sqlConn.Close();
}
}
set goodsName = N'GoodsName',
goodsCost = 'GoodsCost',
goodsSalePrice = 'GoodsSalePrice',
goodsDiscost = 'GoodsDiscost',
goodsSupplierId = 'GoodsSupplier',
goodsStockQty = GoodsQty, --估计是因为这个没有加''
goodsNote = N'GoodsNote'
where goodsId = 'GoodsID'--#1.如果是decimal类型或int类型,不要加''.因为你加了,系统也会进行默认的类型转换
--#2.调试C#代码,把拼接好后的SQL,放到查询分析器中,应该会很快找到原因
--#3.可能的原因,看上面的注释
decimal型的,如果我在查询分析器里做,无论加不加''都会成功,但是在这个.net里写,如果加''报的错是“将 varchar转换为数据类型 numeric 时出现算术溢出错误”,如果不加,报的错是“将 int转换为数据类型 numeric 时出现算术溢出错误”。至于你说的把语句放到查询分析器里试一试,我也试过了,不过是把语句改成这样“update Goods set goodsCost = '12.50' where gooodsId = 'sok4567' ”的,而且没有问题。因为我原句里用的是
"goodsCost = '" + GoodsCost + "',goodsSalePrice = '" + GoodsSalePrice + ",goodsDiscost = '" + GoodsDiscost + "'";这样的,这里的GoodsCost,GoodsSalePrice,GoodsDiscost都是字符串,才会出现说varchar转换出错,不加''时它直接把这些内容解析为int型的了,又会说int转换出错。但是我把这些字段改成decimal的然后再写进去也没用,因为我写在string s里的东西肯定是string型的,即使是decimal的我也要tostring了才能写进s里,所以到头来还是变成了字符串,于是就是出错。所以,在一个string里写SQL语句,到底要怎么写decimal型的数据??
goodsCost = " + GoodsCost + ",--这里加单引号搞什么?去掉
goodsSalePrice = '" + GoodsSalePrice + "',";
Dim sValue(2) As Object
Dim oParam(2) As SqlClient.SqlParameterstrSql = "INSERT INTO T01_Book(TITLE,FILENAME,FILESIZE) " & _
"VALUES(@title, @filename, @filesize)"oParam(0) = New SqlClient.SqlParameter("@title", SqlDbType.NVarChar)
oParam(0).Direction = ParameterDirection.Input
oParam(1) = New SqlClient.SqlParameter("@filename", SqlDbType.NVarChar)
oParam(1).Direction = ParameterDirection.Input
oParam(2) = New SqlClient.SqlParameter("@filesize", SqlDbType.BigInt)
oParam(2).Direction = ParameterDirection.InputsValue(0) = "Title"
sValue(1) = "FileName"
sValue(2) = 12300
oSqlServerCommand = New SqlCommand(strSql)
If Not oParameters Is Nothing Then
For i As Integer = 0 To oParameters.Length - 1
If oParameters(i).Direction = ParameterDirection.Output Then
oSqlServerCommand.Parameters.Add(oParameters(i))
Else
oSqlServerCommand.Parameters.Add(oParameters(i)).Value = sValue(i)
End If
Next
End IfoSqlServerCommand.ExecuteNonQuery()