Invalid column name 'userGuid'.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Invalid column name 'userGuid'.

Author

Topic

McDoe
Starting Member

3 Posts

Posted - 2015-03-13 : 17:25:15

In VS I'm getting an error (see title). I think it's because in my table I don't have the userGuid column. My attempts to add one have proved unsuccessful so far.

Here is the code.

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'Users'))
Begin

CREATE TABLE [Users](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [username] [varchar](50) NOT NULL UNIQUE,
    [password] [varchar](50) NOT NULL
)

ALTER TABLE Users  Add userGuid uniqueidentifier NULL



End

Would be awesome if you could tell me what I'm doing wrong.

What's a signature?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-13 : 17:28:49

Your posted code does not throw an error for me, but I also don't think it's doing what you want. Don't you want IF NOT EXISTS? Why not add userGuid to the CREATE TABLE statement?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-13 : 17:29:58

I should add that I am using SSMS and not VS. VS has limitations. If VS can't handle it, you can hide the ALTER TABLE in a variable and use dynamic SQL to execute it. That's what I do to hide things from the compiler.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

McDoe
Starting Member

3 Posts

Posted - 2015-03-13 : 17:40:26

quote:
Originally posted by tkizer

Your posted code does not throw an error for me, but I also don't think it's doing what you want. Don't you want IF NOT EXISTS? Why not add userGuid to the CREATE TABLE statement?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I'm getting an error with VS, not the SQL. I'm trying to add it into the Create Table but it's not working. I get an error regarding incorrect syntax. Might be me however, I'm incredibly new to SQL.

What's a signature?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-13 : 17:47:16

This works fine for me:


IF NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Users')
BEGIN
	CREATE TABLE [Users](
		[UserId] [int] IDENTITY(1,1) NOT NULL,
		[username] [varchar](50) NOT NULL UNIQUE,
		[password] [varchar](50) NOT NULL,
		userGuid uniqueidentifier NULL
	)
END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-13 : 17:50:07

If you are trying to add a column to an existing table, then use this:


IF NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Users'
				 AND  COLUMN_NAME = 'userGuid')
	ALTER TABLE Users  Add userGuid uniqueidentifier NULL

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

McDoe
Starting Member

3 Posts

Posted - 2015-03-13 : 17:58:33

quote:
Originally posted by tkizer

If you are trying to add a column to an existing table, then use this:

IF NOT EXISTS (SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'Users'
AND COLUMN_NAME = 'userGuid')
ALTER TABLE Users Add userGuid uniqueidentifier NULL

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

IF NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Users'
				 AND  COLUMN_NAME = 'userGuid')
	
Begin

CREATE TABLE [Users](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [username] [varchar](50) NOT NULL UNIQUE,
    [password] [varchar](50) NOT NULL,
	/*userGuid uniqueidentifier NULL*/
)

ALTER TABLE Users  Add userGuid uniqueidentifier NULL

Still not working. Does it for you?

What's a signature?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-13 : 18:01:33

The latest one errors because you have an extra comma and are missing END. Use this instead:


IF NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.COLUMNS 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'Users'
				 AND  COLUMN_NAME = 'userGuid')
	
Begin

CREATE TABLE [Users](
    [UserId] [int] IDENTITY(1,1) NOT NULL,
    [username] [varchar](50) NOT NULL UNIQUE,
    [password] [varchar](50) NOT NULL
	/*userGuid uniqueidentifier NULL*/
)

ALTER TABLE Users  Add userGuid uniqueidentifier NULL
END

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources