C# 获取错误：SQL 中的“将数据类型 nvarchar 转换为数字时出错”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11014322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting Error: "Error converting data type nvarchar to numeric" in SQL
提问by Paks
I am passing 4 Parameters to an asp.net Webservice. This is my Code so far:
我将 4 个参数传递给 asp.net Web 服务。到目前为止，这是我的代码：
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;
}
}
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:
如果我使用 4 个参数调用该 Webmethod，则该方法工作正常，我将获得 RaumName 和 RaumID 的列表。但是如果我只输入一个参数，我就会收到一个错误：
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()
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.
数据库中的 ID 存储为数字和 i 传递字符串。我认为这就是问题所在。但我不知道如何解决这个问题。
I also want that my Query works also with only two or three entered Parameters.
我还希望我的 Query 也只使用两个或三个输入的参数。
Thank in advance!
预先感谢！
采纳答案by Thorsten Dittmar
Usually it's not a problem to pass a stringto 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.
通常，将 a 传递string给数字参数不是问题，只要 SQL Server 能够将字符串的内容本身转换为数字值即可。如果这不起作用，您会收到此错误。
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!
例如：传递"Hello"给数字参数，您会收到错误消息。通过"1234"你没有。请注意，空字符串或包含空格的字符串不能转换为数值！
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 stringtypes in your application could help.
但是，应该说这样做并不是很好的作风。您应该确保您在应用程序中使用的类型与数据库中的类型相匹配以避免出现问题。也许有关为什么需要string在应用程序中使用类型的更多详细信息可能会有所帮助。
EDIT 1
To make a parameter optional for the query, the way to go would be the following:
编辑 1
要使查询的参数可选，方法如下：
- 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
- 更改您的 SQL 语句以允许诸如
WHERE RAUMKLASSE_ID = ISNULL(@Raumklasse_ID, RAUMKLASSE_ID). @Raumklasse_ID如果它应该是可选的，请不要添加参数或添加值DBNull.Value
You should really consider changing your stringproperties to nullable types like int?.
您真的应该考虑将您的string属性更改为可空类型，例如int?.
EDIT 2
This is how your code could look implementing the changes I suggested in Edit 1:
编辑 2
这就是您的代码如何实现我在编辑 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);
...
}
回答by Jon Egerton
The problem might be that you're not giving anything to infer the data type from as you're not setting the value of the parameter.
问题可能是您没有提供任何信息来推断数据类型，因为您没有设置参数的值。
Try setting the parameters using the Add method as follows:
尝试使用 Add 方法设置参数，如下所示：
cmd.Parameters.Add("@Raumklasse_ID", SqlDbType.Int).Value = RAUMKLASSE_ID;
That way if you don't want to set the value, you can still define the parameter.
这样，如果您不想设置该值，您仍然可以定义该参数。
See here for more: http://msdn.microsoft.com/en-us/library/wbys3e9s
有关更多信息，请参见此处：http: //msdn.microsoft.com/en-us/library/wbys3e9s
回答by Shyju
Convert your string to intger type
将您的字符串转换为整数类型
cmd.Parameters.AddWithValue("@Raumklasse_ID", Convert.ToInt32(RAUMKLASSE_ID));
cmd.Parameters.AddWithValue("@Stadt_ID", Convert.ToInt32(STADT_ID));
cmd.Parameters.AddWithValue("@Gebaeude_ID", Convert.ToInt32(GEBAEUDE_ID));
cmd.Parameters.AddWithValue("@Region_ID", Convert.ToInt32(REGION_ID));
回答by Talha
Do like this
这样做
cmd.Parameters.Add("@Raumklasse_ID", SqlDbType.Int);// use here your SQL DataType, if it is numberic than use Numeric.
cmd.Parameters[0].value = Convert.ToInt32(RAUMKLASSE_ID);
