C# 在以下代码中获取“无效的列名”Sql 异常

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/17670744/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-08-10 10:09:20  来源:igfitidea点击:

Getting "Invalid Column Name " Sql Exception in the following code

c#asp.netsql

提问by sagarwadhwa1

I am trying to pass both Column name and the Value to be checked in the code at runtime. However I am getting an "Invalid Column Name " exception. The code is as follows :

我试图在运行时传递要在代码中检查的列名和值。但是,我收到“无效的列名称”异常。代码如下:

string temp = TextBox1.Text.ToString();
SqlConnection con = new SqlConnection("data source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Sagar\Documents\Test.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
SqlCommand com = new SqlCommand("Select * from Employee Where @field = Sagar", con);
com.Parameters.AddWithValue("@field", DropDownList1.SelectedValue.ToString());
//com.Parameters.AddWithValue("@value", temp);
SqlDataAdapter da = new SqlDataAdapter(com);
con.Open();
SqlDataReader reader = com.ExecuteReader();
GridView1.DataSource = reader;
GridView1.DataBind();

采纳答案by Vidhya Sagar Reddy

Instead you can make your code this way, which works perfectly:

相反,您可以通过这种方式编写代码,这非常有效:

"Select * from Employee Where "+DropDownList1.SelectedValue.ToString()+" =
'Sagar'" – Vidhya Sagar Reddy

回答by Thorsten Dittmar

The message says that there is no column named 'Sagar' in the table. Is there such a column? Things would be easier if you showed us the table schema instead of having us guess from the error message.

该消息表示表中没有名为“Sagar”的列。有这样的专栏吗?如果您向我们展示表模式而不是让我们从错误消息中猜测,事情会更容易。



It is not possible to parameterize column names using SqlParameterin C#. This has been discussed here multiple times.

无法SqlParameter在 C# 中使用参数化列名。这已经在这里讨论过多次。

What's happening with the query the way Vidhya Sagar Reddyis doing it, is the following. He assumes that the following query

查询发生的情况Vidhya Sagar Reddy如下。他假设以下查询

Select * from Employee Where @field = 'Sagar'

is replaced by this query when setting "Name"as the value for the @fieldparameter:

设置"Name"@field参数的值时,由此查询替换:

Select * from Employee Where Name = 'Sagar'

This, however, is wrong!What's happening is that the @fieldparameter is replaced as follows:

然而,这是错误的!发生的事情是@field参数被替换如下:

Select * from Employee Where 'Name' = 'Sagar'

This returns no results, as the WHEREclause is always false. Of course, if you use the field name Sagar, this akways returns true, as the statement then reads:

这不会返回任何结果,因为WHERE子句总是false。当然,如果您使用字段 name Sagar,则通常会返回true,因为语句如下所示:

Select * from Employee Where 'Sagar' = 'Sagar'


Here's an easy test to prove what I've said above. Use the following statement to set the @fieldparameter (supposed, there's no column named eirghoerihgohin the table):

这是一个简单的测试来证明我上面所说的。使用以下语句设置@field参数(假设eirghoerihgoh表中没有命名列):

com.Parameters.AddWithValue("@field", "eirghoerihgoh");

If the query executes correctly (maybe not returning any results), the above is correct. If it was not correct, an exception should be thrown about the eirghoerihgohcolumn not being present.

如果查询正确执行(可能不返回任何结果),则上述内容是正确的。如果不正确,则应抛出有关该eirghoerihgoh列不存在的异常。



Thank you Vidhya Sagar Reddyfor proving my point. By using this line

谢谢你Vidhya Sagar Reddy证明了我的观点。通过使用这条线

com.Parameters.AddWithValue("@field", "eirghoerihgoh");

you say you didn't get any results, but you also didn't get an exception. However, if the statement really had been changed to

你说你没有得到任何结果,但你也没有得到例外。但是,如果语句真的被更改为

Select * from Employee Where eirghoerihgoh = 'Sagar'

there had to be an exception saying that there was no column named eirghoerihgoh. As you didn't get that exception, there's only one possible explanation: The statement was changed to

必须有一个例外,说明没有名为 的列eirghoerihgoh。由于您没有收到该异常,因此只有一种可能的解释:语句已更改为

Select * from Employee Where 'eirghoerihgoh' = 'Sagar'

and this executes, but doesn't return results, as the condition is always false.

这会执行,但不会返回结果,因为条件始终为false

回答by Vidhya Sagar Reddy

The reason for that is quite simple, the value that are to be specified in SQL should be in single quotes and this is a simple mistake by the way..!!!!

原因很简单,要在 SQL 中指定的值应该用单引号引起来,顺便说一下,这是一个简单的错误..!!!

SqlCommand com = new SqlCommand("Select * from Employee Where @field = 'Sagar'", con);

And even change the parameter to "field" in the following line and not "@field"..!!

甚至在下一行中将参数更改为“field”而不是“@field”..!!

com.Parameters.AddWithValue("field", DropDownList1.SelectedValue.ToString());

This is working..>!!!!

这是工作..>!!!!