Unable to create SQL Table dynamically in c# -- "invalid column name"

Question

im trying to create a table with the name of a dynamically retrieved value

here is the code

 string nok="";
        while (reader.Read())
            {
                noo = reader.GetInt32(3);
                nok = noo.ToString();
                MessageBox.Show(noo.ToString());
            }
        con.Close();
            var commandStr = "If not exists (select name from sysobjects where name = C"+nok+") CREATE TABLE C"+nok+"(A char(50),B char(50),C char(50),D char(50),E char(50),F char(50),G char(50),H char(50),I char(50),J char(50),K char(50),L char(50),M char(50),N char(50),O char(50))";
            MessageBox.Show(commandStr);   
        con.Open();    
        using (SqlCommand command = new SqlCommand(commandStr, con))
                command.ExecuteNonQuery();

but im getting an error invalid column name with that dynamic value

The SQL is not valid. C3 in name = C3 should at least be enclosed in quotes (ie name = 'C3'). — Simon Belanger
– Simon Belanger, Commented Oct 1, 2014 at 15:40
thanks man got it im getting incorrect syntax near that variable error "If not exists (select name from sysobjects where name = 'C"+nok+"') CREATE TABLE 'C"+nok+"'(A char(50),B char(50),C char(50),D char(50),E char(50),F char(50),G char(50),H char(50),I char(50),J char(50),K char(50),L char(50),M char(50),N char(50),O char(50))"; — BLACKMAMBA
– BLACKMAMBA, Commented Oct 1, 2014 at 15:43

David · Accepted Answer · 2014-10-01 15:43:44Z

3

You didn't wrap the string in quotes.

When referencing the table as an identifier, quotes aren't needed. Because it's an object name:

... CREATE TABLE C"+nok+"(A char(50), ...

becomes:

... CREATE TABLE C1(A char(50), ...

But when referencing the table's name as a value in the WHERE clause, it isn't an object identifier. It's a column value. The name column holds string values, so it needs to be compared with a string:

... where name = 'C"+nok+"') CREATE ...

becomes:

... where name = 'C1') CREATE ...

answered Oct 1, 2014 at 15:43

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David Over a year ago

@JP1016: Glad I could help! I didn't go into detail in my answer regarding the SQL injection vulnerability, mostly because in this specific case the risk seems a bit low. (Since the value originates as an integer.) But the risk still exists and I highly recommend reading up on the subject of SQL injection and how to guard against it.

Stefan Michev · Accepted Answer · 2014-10-01 15:43:37Z

2

The name of the table schould be between 'name'

var commandStr = "If not exists (select name from sysobjects where name = 'C"+nok+"') CREATE TABLE C"+nok+"(A char(50),B char(50),C char(50),D char(50),E char(50),F char(50),G char(50),H char(50),I char(50),J char(50),K char(50),L char(50),M char(50),N char(50),O char(50))";

Easier way to check if a table/object is present

IF OBJECT_ID('C" + nok + "') IS NULL CREATE TABLE ...

answered Oct 1, 2014 at 15:43

Stefan Michev

5,1034 gold badges37 silver badges30 bronze badges

Comments

Vulcronos · Accepted Answer · 2014-10-01 16:03:11Z

2

The problem is here:

select name from sysobjects where name = C"+nok+"

When you run this in oracle the statement executed will be:

select name from sysobjects where name = CWHATEVER

Since CWHATEVER is not in quotes it will be considered a column name instead of a string value. For this to work it needs to be in single quotes:

select name from sysobjects where name = 'C"+nok+"'

However, this opens you up to SQL Injection. I would strongly advise you to use sql parameters instead.

edited Oct 1, 2014 at 16:03

answered Oct 1, 2014 at 15:43

Vulcronos

3,4563 gold badges19 silver badges25 bronze badges

2 Comments

Robert Harvey Over a year ago

So you tell him the wrong way to fix it in detail, but then conclude with "use SQL parameters instead?"

Vulcronos Over a year ago

@RobertHarvey Added a link to an example.

Slippery Pete · Accepted Answer · 2014-10-01 15:43:23Z

1

The value needs single quotes around it:

... (select name from sysobjects where name = 'C"+nok+"') ...

answered Oct 1, 2014 at 15:43

Slippery Pete

3,1101 gold badge16 silver badges17 bronze badges

Comments

AdamMc331 · Accepted Answer · 2014-10-01 15:49:27Z

1

You shouldn't just add 'nok' into your SQL statement. You need to use a parameter. Try something like this. I just took a small snippit:

commandStr = "if not exists (select name from sysobjects where name = 'c@nok')";

And then later, when you have the command text, replace the parameter:

command.Parameters.AddWithValue("@nok", nok);

edited Oct 1, 2014 at 15:49

answered Oct 1, 2014 at 15:43

AdamMc331

16.7k11 gold badges78 silver badges134 bronze badges

7 Comments

Simon Belanger Over a year ago

This is not valid SQL. It would work with ... where name = 'C' + @nok

AdamMc331 Over a year ago

Really? I have a c# application that has something like this: SELECT * FROM projects WHERE name = @name and it works. That's what I used as reference. Perhaps both would work? Or is the difference that I'm not concatenating anything?

Simon Belanger Over a year ago

The difference is c@nok is sent to the SQL server for processing. @name in your example is fine as it is interpreted as a query parameter. c@nok is interpreted as a column.

AdamMc331 Over a year ago

I understand. @Vulcronos explained that pretty well. Thanks for the heads up. I'll edit the answer, even though this one is not best.

TyCobb Over a year ago

Thanks. I think I made a flaw in thinking that c@param was special and not just "give me the value 'C' plus my parameter". D'OH

|

Collectives™ on Stack Overflow

Unable to create SQL Table dynamically in c# -- "invalid column name"

5 Answers 5

1 Comment

Comments

2 Comments

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

2 Comments

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related