SQL Server Reports 'Invalid Column Name', But the Column Is Present and the Query Works Through Management Studio

SQL Server reports 'Invalid column name', but the column is present and the query works through management studio

I suspect that you have two tables with the same name. One is owned by the schema 'dbo' (dbo.PerfDiag), and the other is owned by the default schema of the account used to connect to SQL Server (something like userid.PerfDiag).

When you have an unqualified reference to a schema object (such as a table) — one not qualified by schema name — the object reference must be resolved. Name resolution occurs by searching in the following sequence for an object of the appropriate type (table) with the specified name. The name resolves to the first match:

• Under the default schema of the user.
• Under the schema 'dbo'.

The unqualified reference is bound to the first match in the above sequence.

As a general recommended practice, one should always qualify references to schema objects, for performance reasons:

• An unqualified reference may invalidate a cached execution plan for the stored procedure or query, since the schema to which the reference was bound may change depending on the credentials executing the stored procedure or query. This results in recompilation of the query/stored procedure, a performance hit. Recompilations cause compile locks to be taken out, blocking others from accessing the needed resource(s).

• Name resolution slows down query execution as two probes must be made to resolve to the likely version of the object (that owned by 'dbo'). This is the usual case. The only time a single probe will resolve the name is if the current user owns an object of the specified name and type.

[Edited to further note]

The other possibilities are (in no particular order):

• You aren't connected to the database you think you are.
• You aren't connected to the SQL Server instance you think you are.

Double check your connect strings and ensure that they explicitly specify the SQL Server instance name and the database name.

SQL Server: Invalid Column Name

Whenever this happens to me, I press Ctrl+Shift+R which refreshes intellisense, close the query window (save if necessary), then start a new session which usually works quite well.

How to fix invalid 'Invalid column name' after adding a column in the table

The issue has been fixed.

The problem was I disregarded the error of my 'Address' table.

It has a problem with its foreign key.

Every time there's an error in the compilation, I just republish.

And the application gets publish successfully.

But, this isn't true. I believe the cycle of the entire compilation is interrupted with this error.

So, now every time I will see an error in the compilation -- I should fix (lesson learned)..

I updated my 'Address' table which has the error. Now, I can successfully add attributes to my 'Contact' table.

I hope this will shed some light to someone who's having the same problem as I had.

Happy coding everyone!

:)

Invalid Column Name in java but works on database

There is also another method with the same name based on index.

int getInt(int columnIndex) throws SQLException

Try to see if work.As i see from image is at 4 index (start numbering columns from 1)

player.xp = result.getInt(4);

Return specific data from SQL query and error invalid column name

Your issue is you are referencing your column alias in your WHERE clause, not the column itself. Try this:

SELECT        Account.mm_registrationnumber AS CL_Reg_Number, Account.Name, Account.mm_supplierstatus AS Supplier_Status, 
                     Account.PrimaryContactIdName AS Primary_Contact, Account.EMailAddress1 AS Email, Account.Telephone1 AS Telephone, mm_address.mm_line1 AS Line1, 
                     mm_address.mm_line2 AS Line2, mm_address.mm_line3 AS Line3, mm_address.mm_line4 AS Line4, mm_address.mm_city AS City, 
                     mm_address.mm_county AS County, mm_address.mm_postcode AS Postcode, mm_turnover.mm_name AS Signup_Turnover, Account.mm_signupdate AS SignUp_Date, 
                     mm_payment.mm_paymenttype AS Payment_Type, mm_payment.mm_paymentstatus AS Payment_Status, mm_payment.mm_vatamount AS Payment_Amount_VAT, 
                     mm_payment.mm_paymentamount AS Payment_Amount_Net, Invoice.InvoiceNumber AS Invoice_ID, Invoice.DueDate AS Due_Date, 
                     Invoice.CreatedOn AS Created_Date

FROM            Account INNER JOIN
                     mm_payment ON mm_payment.mm_organisation = Account.AccountId INNER JOIN
                     mm_address ON Account.mm_address = mm_address.mm_addressId INNER JOIN
                     mm_turnover ON Account.mm_turnover = mm_turnover.mm_turnoverId INNER JOIN
                     Invoice ON Invoice.AccountId = Account.AccountId

WHERE mm_payment.mm_paymentstatus = 'Paid'; //  error 'invalid column name Payment_Status'

I also added your table name to the column for clarification. This will also fix a potential problem of having the same column name in any of your joined tables.

edit: If mm_paymentstatus is an integer or bit, try this, assuming 1 is 'Paid':

WHERE mm_payment.mm_paymentstatus = 1

---

Related Topics