Unknown column clause in sql

Question

0.00/5 (No votes)

See more:

"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 + "'",

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

Posted 26-Jul-15 0:22am

roldjer

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-26T00:39:00

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

C#

"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 + "'",

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.

OriginalGriff · Answer 2 · 2015-07-26T00:39:00

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."

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

You can repeat the column:

SQL

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

But you can't reference the alias for it.

Unknown column clause in sql

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0