CODEWITHSUNDEEP
sqlsql-servert-sql
Asif Patankar
Asif Patankar

Reputation: 97

How to create table in sqlcommand string?

Creating table in a sqlcommand string like

"CREATE TABLE '"+txtTableName.Text+"' (ID int IDENTITY(1,1),
ColumnName varchar(255))";

would give you an error saying

"Incorrect syntax near 'whatever table name you specify in the textbox'".

Instead you can use

"CREATE TABLE [dbo].['"+txtTableName.Text+"'] (ID int IDENTITY(1,1),
ColumnName varchar(255))";

Upvotes: 0

Views: 545

Answers (2)

StackUser
StackUser

Reputation: 5398

You can try using T-SQL like this

DECLARE @TableName NVARCHAR(100) = 'MyTable'
DECLARE @SQL NVARCHAR(500) = 'CREATE TABLE [dbo].[' + @TableName + '] (ID int IDENTITY(1,1),
ColumnName varchar(255))'

EXEC sp_executesql @SQL

Upvotes: 1

Patrick
Patrick

Reputation: 5836

First, I would question why you want to create SQL tables via entry into a Textbox. Something seems wrong there.

But if you must ... this should work:

string sql = "If not exists (select name from sysobjects where name = 
'" + txtTableName.Text + "') CREATE TABLE '" + txtTableName.Text + "'(ID int IDENTITY(1,1),
ColumnName varchar(255))";

using (SqlCommand command = new SqlCommand(sql, con)) {
     command.ExecuteNonQuery(); }

Untested, and this doesn't account for errors that will occur based on what the user enters into the textbox, such as the single-quote (') character among others.

I would also suggest to just create a stored procedure that accepts a @tableName parameter and then executes direct SQL on the server side to handle this.

Upvotes: 0

Related Questions