sql中的未知列子句 [英] Unknown column clause in sql

问题描述

"Select tblvisitors.Resident_ID,tblvisitors.Visitors_First,tblvisitors.Relationship_First,tblvisitors.Visitors_Second,tblvisitors.Relationship_Second,tblvisitors.Lastname,CONCAT(tblvisitors.Lastname,tblresinformation.Firstname)as Fullname from dbladiesdormitory.tblvisitors INNER JOIN dbladiesdormitory.tblresinformation ON (tblvisitors.Resident_ID=tblresinformation.Resident_ID) where Fullname='" + textBox14.Text + "'",

错误表示'where子句中的'未知列''Fullname' 。这里有人可以知道如何解决这个问题吗？

the error said that Unknown column 'Fullname' in 'where clause'. can some here know how to fix this?

推荐答案

首先，不要将文本框中的值直接连接到SQL语句。这使您对SQL注入持开放态度，并可能导致语句出现各种问题。例如，如果文本框包含'character ...



如果您使用的是Ole Db，请使用OleDbParameter [ ^ ]



错误是什么，全名是您用于列的别名。你不能在WHERE子句中使用别名，除非它在内部内联视图中。



因此查询可能应该像

First of all do not concatenate values from text boxes directly to SQL statements. This leaves you open to SQL injections and may cause different kinds of problems for the statements. For example consider what happens if the text box contains ' character...

If you're using Ole Db, then use OleDbParameter[^]

What comes to the error, full name is an alias you have used for a column. You cannot use an alias in a WHERE clause unless it's inside an inner inline view.

So probably the query should be like

"Select tblvisitors.Resident_ID,tblvisitors.Visitors_First,tblvisitors.Relationship_First,tblvisitors.Visitors_Second,tblvisitors.Relationship_Second,tblvisitors.Lastname,CONCAT(tblvisitors.Lastname,tblresinformation.Firstname) as Fullname from dbladiesdormitory.tblvisitors INNER JOIN dbladiesdormitory.tblresinformation ON (tblvisitors.Resident_ID=tblresinformation.Resident_ID) where CONCAT(tblvisitors.Lastname,tblresinformation.Firstname)='" + textBox14.Text + "'",

但我猜想即使这不是你想要的。最好的选择可能是你将文本框中的名称分隔为 firstname 和 lastname 变量并直接将它们与相应的栏目。这样，如果只给出姓氏，你可以轻松省略条件中的第一个名字等等。

But I would guess that even that is not what you're after. The best option probably is that you separate the names from the text box to firstname and lastname variables and compare those directly to corresponding columns. This way if only last name is given you can easily omit the first name from the condition and so on.

有几件事：

1）不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

2）帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得今天的TextBox8是手机号码，但是当你必须修改它时在三个星期的时间里，你会吗？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大概和8次击键...

3）你不能在WHERE子句中使用列别名：https://msdn.microsoft.com/en-us/library/ms173451.aspx [ ^ ]

A couple of things:
1) Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
2) Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...
3) You can't use a column alias in a WHERE clause: https://msdn.microsoft.com/en-us/library/ms173451.aspx[^]

"column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause."

这是因为在列数据出于显而易见的原因之前评估了WHERE！



您可以重复以下列：

This is because the WHERE is evaluated before the column data is for obvious reasons!

You can repeat the column:

WHERE CONCAT(tblvisitors.Lastname,tblresinformation.Firstname) = ...

但是你不能为它引用别名。

But you can't reference the alias for it.

这篇关于sql中的未知列子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！