CODEWITHSUNDEEP
sql.netsql-serverdatabasevb.net
User1162527
User1162527

Reputation: 197

How to handle spaces in database name for Sql server CREATE TABLE?

I have the following code and I use it in a vb.net project. this works well if there is no space in the database name. for example if the database name is "SampleCompany" it works fine but if the database name changes to "Sample Company" we recive an error in this section of sql command : "BEGIN CREATE TABLE " & cn.Database. We must be able to handle database names with/without spaces .

any suggestion to solve this problem ? Thank you.

cn.Database="Sample Company"

sqlCreate = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE      TABLE_NAME = 'Cloud_UserSetting' AND TABLE_CATALOG ='" & cn.Database & "') " & _
            "BEGIN CREATE TABLE " & cn.Database & ".dbo.Cloud_UserSetting(CloudId int NOT NULL, Name varchar(100) NOT NULL," & _
            "Setting varchar(250) NULL " & _
            "CONSTRAINT PK_Cloud_UserSetting PRIMARY KEY CLUSTERED(CloudId,Name)) END"
command = New SqlCommand(sqlCreate, cn)
command.ExecuteNonQuery()

Upvotes: 6

Views: 17007

Answers (2)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

You can simply use square brackets in your database name like below :

cn.Database="[Sample Company]"

sqlCreate = "IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE      TABLE_NAME = 'Cloud_UserSetting' AND TABLE_CATALOG ='" & cn.Database & "') " & _
            "BEGIN CREATE TABLE " & cn.Database & ".dbo.Cloud_UserSetting(CloudId int NOT NULL, Name varchar(100) NOT NULL," & _
            "Setting varchar(250) NULL " & _
            "CONSTRAINT PK_Cloud_UserSetting PRIMARY KEY CLUSTERED(CloudId,Name)) END"
command = New SqlCommand(sqlCreate, cn)
command.ExecuteNonQuery()

Upvotes: 3

Tharif
Tharif

Reputation: 13971

use square brackets to contain the column names and table names.

E.g.: instead of table name use: [table name]

Upvotes: 9

Related Questions