Invalid Column Name on SQL Server Update After Column Create

Invalid column name on sql server update after column create

In this case you can avoid the problem by adding the column as NOT NULL and setting the values for existing rows in one statement as per my answer here.

More generally the problem is a parse/compile issue. SQL Server tries to compile all statements in the batch before executing any of the statements.

When a statement references a table that doesn't exist at all the statement is subject to deferred compilation. When the table already exists it throws an error if you reference a non existing column. The best way round this is to do the DDL in a different batch from the DML.

If a statement both references a non existing column in an existing table and a non existent table the error may or may not be thrown before compilation is deferred.

You can either submit it in separate batches (e.g. by using the batch separator GO in the client tools) or perform it in a child scope that is compiled separately by using EXEC or EXEC sp_executesql.

The first approach would require you to refactor your code as an IF ... cannot span batches.

IF NOT EXISTS(SELECT *
              FROM   sys.columns
              WHERE  Name = 'OPT_LOCK'
                     AND object_ID = Object_id('REP_DSGN_SEC_GRP_LNK'))
  BEGIN
      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ADD OPT_LOCK NUMERIC(10, 0)

      EXEC('UPDATE REP_DSGN_SEC_GRP_LNK SET OPT_LOCK = 0');

      ALTER TABLE REP_DSGN_SEC_GRP_LNK
        ALTER COLUMN OPT_LOCK NUMERIC(10, 0) NOT NULL
  END; 

SQL Updating column after adding it giving Invalid column name error

Your SQL query to do the UPDATE refers to a column that has not yet been created. At compile time, SQL Server detects that the column does not exist, so it gives you the error "Invalid column name 'ExpIsLocalTime'".

In order to include the UPDATE in this query, you will need to encapsulate it in a dynamic SQL query. In other words, something like this:

IF NOT EXISTS(SELECT * FROM sys.columns WHERE Name = N'ExpIsLocalTime' AND Object_ID = Object_ID(N'[dbo].[tbl_SessionsAvailable]'))
BEGIN
    ALTER TABLE dbo.tbl_SessionsAvailable ADD
    ExpIsLocalTime bit NOT NULL CONSTRAINT DF_tbl_SessionsAvailable_ExpIsLocalTime DEFAULT (0)

    DECLARE @SQL NVARCHAR(1000)
    SELECT @SQL = N'UPDATE dbo.tbl_SessionsAvailable SET ExpIsLocalTime = 1'
    EXEC sp_executesql @SQL
END
GO

We have the same issue in our SQL scripts that maintain tables. After a table is created, if we add a column to it later, we have to use dynamic SQL to avoid these compilation errors.

Insert/ Update Procedure gives Invalid Column Name Error SQL Server

You are getting an error because while inserting data in table 'ProjectBillingSource', you have given column in a single quote, columns name should be specified without single quote.

INSERT INTO ProjectBillingSource(**'projectBillID', 'TransAmount', 'TransDesc', 'TransDate', 'projectID', 'accountMgr'**)
VALUES (@projectBillID, @TransAmount, @TransDesc, @TransDate, @projectID, @accountMgr)

Update the insert statement without quote like this -

INSERT INTO ProjectBillingSource (projectBillID, TransAmount, TransDesc, TransDate, projectID, accountMgr)
VALUES (@projectBillID, @TransAmount, @TransDesc, @TransDate, @projectID, @accountMgr)

Also, you should create a table only when it does not exist, like this -

if OBJECT_ID('ProjectBillingSource') is null
begin
    SELECT * INTO ProjectBillingSource FROM ProjectBilling
end

Invalid column name for SQL Server update-database

You definitely need the single quotes to insert string values. From your error message, the Id column is an auto increment (identity) column. So you can't explicitly define those values without setting identity_insert.

Easiest fix is to remove the Id from your insert and allow the database to maintain those values. For example:

insert into genres (name) values ('Fantasy')

SQL Server show invalid column name when the column name on the table exists

You have a DROP statement before the insert. That's why you get an warning that the column is missing.

You may want to move the DROP statement before the CREATE one, or even use the new syntax DROP TABLE IF EXISTS ... if it is available in your edition.

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.

Invalid column name error in database update statment

String literals in SQL are denoted by single quotes ('). Without them, the arguments you're formatting into the SQL string will be interpreted as column names, and you'd get the error since there are no such columns.

You could quote the values:

sql = string.Format("UPDATE BarcodesData SET Lab = '{0}' WHERE Barcode = '{1}'", args);
// Here -------------------------------------------^---^-----------------^---^

But this is still a bad practice, and will leave your code vulnerable to SQL injection attacks if these arguments are obtained from user input or any for of untrusted data.

A better practice would be to use bind variables:

sql = "UPDATE BarcodesData SET Lab = @lab WHERE Barcode = @barcode";
Adapter.UpdateCommand = new SqlCommand(sql, cnn);
Adapter.UpdateCommand.Parameters.AddWithValue("@lab", LabName);
Adapter.UpdateCommand.Parameters.AddWithValue("@barcode", currentBarCode);
Adapter.UpdateCommand.ExecuteNonQuery();

Getting Invalid Column name Error in T-SQL while performing Update operation

String should be declared with single quotes ' '

SET dbo.Machine.ServerName= 'Server'

Double quotes is for fieldnames

---

Related Topics