CODEWITHSUNDEEP
c#sql
BLACKMAMBA
BLACKMAMBA

Reputation: 725

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

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

Upvotes: 0

Views: 245

Answers (5)

Vulcronos
Vulcronos

Reputation: 3456

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.

Upvotes: 2

AdamMc331
AdamMc331

Reputation: 16730

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);

Upvotes: 1

David
David

Reputation: 219047

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 ...

Upvotes: 3

Stefan Michev
Stefan Michev

Reputation: 5093

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 ...

Upvotes: 2

Slippery Pete
Slippery Pete

Reputation: 3110

The value needs single quotes around it:

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

Upvotes: 1

Related Questions