收到错误："错误转换数据类型为nvarchar到数字"在SQL [英] Getting Error: "Error converting data type nvarchar to numeric" in SQL
问题描述
我传递4参数到一个asp.net web服务。这是我到目前为止的代码：
I am passing 4 Parameters to an asp.net Webservice. This is my Code so far:
WEBMETHOD：
Webmethod:
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
[WebMethod]
public List<RaumHelper.RAUM> Raum(string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
{
return RaumHelper.Raum(RAUMKLASSE_ID, STADT_ID, GEBAEUDE_ID, REGION_ID);
}
助手类：
Helperclass:
public class RaumHelper
{
public class RAUM
{
public string RaumName { get; set; }
public string RaumID { get; set; }
}
internal static List<RAUM> Raum( string RAUMKLASSE_ID, string STADT_ID, string GEBAEUDE_ID, string REGION_ID)
{
List<RAUM> strasseObject = new List<RAUM>();
using (SqlConnection con = new SqlConnection(@"Data Source=Localhost\SQLEXPRESS;Initial Catalog=BOOK-IT-V2;Integrated Security=true;"))
using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = @Raumklasse_ID OR STADT_ID = @Stadt_ID OR GEBAEUDE_ID = @Gebaeude_ID OR REGION_ID = @Region_ID", con))
{
con.Open();
cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
cmd.Parameters.AddWithValue("@Gebaeude_ID", GEBAEUDE_ID);
cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (rdr["BEZEICHNUNG"] != DBNull.Value && rdr["ID"] != DBNull.Value)
{
strasseObject.Add(new RAUM()
{
RaumName = rdr["BEZEICHNUNG"].ToString(),
RaumID = rdr["ID"].ToString()
});
}
}
}
}
return strasseObject;
}
}
如果我调用该WEBMETHOD与4参数，方法是做工精细的我得到的RaumName的和RaumID的一个列表。但是，如果我把只有一个参数IAM得到一个错误：
If i invoke that Webmethod with the 4 Parameters, the Method is working fine an i get a LIST of the RaumName's and RaumID's. But if i put only one Parameter iam getting an Error:
System.Data.SqlClient.SqlException: Error converting data type nvarchar to numeric.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.HasMoreRows()
at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read()
该ID在数据库存储为数字和我传递字符串。我认为这就是问题所在。但我不知道如何解决这个问题。
The ID's in the Database are stored as Numeric and i passing string. I think that is the problem. But i dont know how to fix this.
我也希望，我的查询工作也只有两个或三个输入的参数。
I also want that my Query works also with only two or three entered Parameters.
感谢在前进！
推荐答案
通常它不是通过一个字符串问题来一个参数的数字，只要在SQL Server能够将字符串的内容转换为数值本身。如果不工作，你得到这个错误
Usually it's not a problem to pass a string to a parameter that's numeric, as long as the SQL Server is able to convert the content of the string to a numeric value itself. If that doesn't work, you get this error.
例如：传递你好来的参数这就是数字，你会得到一个错误。通过1234你不知道。请注意，一个空字符串或包含空白的字符串不能转换为数值！
For example: Passing "Hello" to a parameter that's numeric, you get an error. Passing "1234" you don't. Please note that an empty string or a string containing whitespace can not be converted to a numeric value!
但是，应该说，这是不好的风格做。你应该确保你在你的应用程序中使用的类型匹配数据库类型以避免出现问题。也许你为什么需要有在您的应用程序可以帮助串类型。
However, it should be said that it is not good style to do that. You should make sure that the types you use in your application match the types in the database to avoid problems. Maybe some further detail on why you need to have string types in your application could help.
修改一些进一步的细节1 结果
为了使可选的查询参数，要走的路会是以下内容：
EDIT 1
To make a parameter optional for the query, the way to go would be the following:
- 更改您的SQL语句，允许可选参数，如
WHERE RAUMKLASSE_ID = ISNULL（@Raumklasse_ID，RAUMKLASSE_ID）。 - 请不要添加
@Raumklasse_ID参数是否应该是可选的或添加值DBNull.Value
- Change your SQL statement to allow optional parameters like
WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID). - Do not add the
@Raumklasse_IDparameter if it should be optional or add the valueDBNull.Value
您真的应该考虑改变你的字符串属性可空类型，如 INT？。
You should really consider changing your string properties to nullable types like int?.
编辑2 结果
这是你的代码如何看我落实在编辑建议的修改1：
EDIT 2
This is how your code could look implementing the changes I suggested in Edit 1:
using (SqlCommand cmd = new SqlCommand(@"SELECT r.BEZEICHNUNG AS BEZEICHNUNG, r.ID AS ID FROM RAUM r WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID) OR STADT_ID = ISNULL(@Stadt_ID, STADT_ID) OR GEBAEUDE_ID = ISNULL(@Gebaeude_ID, GEBAEUDE_ID) OR REGION_ID = ISNULL(@Region_ID, REGION_ID)", con))
{
con.Open();
if (!String.IsNullOrWhitespace(RAUMKLASSE_ID))
cmd.Parameters.AddWithValue("@Raumklasse_ID", RAUMKLASSE_ID);
else
cmd.Parameters.AddWithValue("@Raumklasse_ID", DBNull.Value);
if (!String.IsNullOrWhitespace(STADT_ID))
cmd.Parameters.AddWithValue("@Stadt_ID", STADT_ID);
else
cmd.Parameters.AddWithValue("@Stadt_ID", DBNull.Value);
if (!String.IsNullOrWhitespace(GEBAEUDE_ID))
cmd.Parameters.AddWithValue("@Gebaeude_ID", GEBAEUDE_ID);
else
cmd.Parameters.AddWithValue("@Gebaeude_ID", DBNull.Value);
if (!String.IsNullOrWhitespace(REGION_ID))
cmd.Parameters.AddWithValue("@Region_ID", REGION_ID);
else
cmd.Parameters.AddWithValue("@Region_ID", DBNull.Value);
...
}
这篇关于收到错误："错误转换数据类型为nvarchar到数字"在SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
