How to create new database in asp.net?

Question

My goal is to create a new database for new brands that the user will add on the website. I've created a code for doing such task but it is giving me this exception:

An exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll but was not handled in user code Additional information: Incorrect syntax near 'char'.

I already looked at the code several times but I still can't find the root cause of the problem.

Here is the code:

public static void CreateGuitarBrandsDatabase(string brand)
{
    SqlConnection createBrandData = new SqlConnection(@"Data Source=Y560\SQLEXPRESS;Initial Catalog=GuitarItemsDB;Integrated Security=True");
    createBrandData.Open();
    SqlCommand cmdBrandData = new SqlCommand("CREATE TABLE guitarItem" + brand + "(id int,type char(50),model char(50),price float,image1 char(255),image2 char(255),description text,neck type char(100),body char(100), fretboard char(100),fret char(50),bridge char(100),neck pickup char(100),bridge pickup char(100),hardware_color char(50));", createBrandData);
    cmdBrandData.ExecuteNonQuery();//The exception seems to be pointing right here
    createBrandData.Close();
}

You can't use spaces on the names for columns unless you surround the name with square brakets, like "[bridge pickup] char(100)" — Gusman
– Gusman, Commented Feb 17, 2017 at 3:41
if brand is user supplied (sounds like it is) this code is vulnerable to SQL injection attacks. — dmeglio
– dmeglio, Commented Feb 17, 2017 at 3:58
In this case you can't parametrize the query, but you can make sure (and do that server side) that the "brand" contains only letters (and maybe digits). Or use a different configuration, where "brand" is a regular column in your one "guitarItem" table. — Hans Keﬆing
– Hans Keﬆing, Commented Feb 17, 2017 at 9:00

Akash KC · Accepted Answer · 2017-02-17 03:54:58Z

3

You have issue in defining column name. Following column names are seems to have issue in which each column name has been separated by space :

neck type char(100) --> name_type char(100)
neck pickup char(100) --> neck_pickup char(100)
bridge pickup char(100) --> bridge_pickup char(100)

Your complete query should be :

SqlCommand cmdBrandData = new SqlCommand("CREATE TABLE guitarItem" + brand + "(id int,type char(50),model char(50),price float,image1 char(255),image2 char(255),description text,
neck_type char(100),body char(100), fretboard char(100),fret char(50),bridge char(100),neck_pickup char(100),
bridge_pickup char(100),hardware_color char(50));", createBrandData);

If you want to have column name with word having space in between them, then you should enclose them in square bracket([]):

SqlCommand cmdBrandData = new SqlCommand("CREATE TABLE guitarItem" + brand + "(id int,type char(50),model char(50),price float,image1 char(255),image2 char(255),description text,
[neck type] char(100),body char(100), fretboard char(100),fret char(50),bridge char(100),[neck pickup] char(100),
[bridge pickup] char(100),hardware_color char(50));", createBrandData);

answered Feb 17, 2017 at 3:54

Akash KC

16.4k6 gold badges41 silver badges59 bronze badges

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

1 Comment

ASH Over a year ago

Watch out for those spaces when you are working in the database world!!

Collectives™ on Stack Overflow

How to create new database in asp.net?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related