ASP.NET Programmatically create a database table

Question

I'm making a simple web app like Instagram. I want to store images for each user in separate table, for example:

New users login is Bob123,
Create a new record in table Users (Uzytkownicy) for Bob123,
Create a new table, in the same database, called Bob123 and there store all Bobs images

I'm getting an error: Incorrect syntax near '@name'

protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
    try
    {
        if (temp == 0)
        {
            Guid newGuid = Guid.NewGuid();

            SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["UzytkownicyConnectionString"].ConnectionString);
            conn.Open();

            string insertQuery = "insert into Uzytkownicy (Id, Login, Password) values (@id, @Uname, @password)";
            SqlCommand com = new SqlCommand(insertQuery, conn);
            com.Parameters.AddWithValue("@id", newGuid.ToString());
            com.Parameters.AddWithValue("@Uname", TextBoxUN.Text);
            com.Parameters.AddWithValue("@password", TextBoxPass.Text);

            SqlCommand comm = new SqlCommand("CREATE TABLE @name ([Image] VARBINARY (MAX) NOT NULL);", conn);
            comm.Parameters.AddWithValue("@name", TextBoxUN.Text);

            com.ExecuteNonQuery();
            comm.ExecuteNonQuery();

            Response.Redirect("MainWebSite.aspx");
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        Response.Write("Error: " + ex.ToString());
    }
}

Possible duplicate of [How can I Pass a Table Name to SqlCommand?](http://stackoverflow.com/questions/23357481/how-can-i-pass-a-table-name-to-sqlcommand) — Ross Bush, Apr 28 '17 at 13:20
From a database design perspective, this is a very bad idea. — Nick.Mc, Apr 28 '17 at 13:25
Why not store all the images in a single table and just use the user's ID to differentiate which records belong to which users? — mason, Apr 28 '17 at 13:28

Morten Bork · Answer 1 · 2017-04-28T13:42:46.313

0

@name is incorrect syntax.

You are attempting to execute this specific line of code :

"CREATE TABLE @name ([Image] VARBINARY (MAX) NOT NULL);"

against an sql database. SQL does not know what @name is.

for hacky, but correct, syntax, you should do:

...

string tablename = "somename"; ...

"CREATE TABLE " + tablename + " ([Image] VARBINARY (MAX) NOT NULL);"

edited Apr 28 '17 at 13:42

answered Apr 28 '17 at 13:20

Morten Bork

1,413
11
23

score 0 · Answer 2 · answered Apr 28 '17 at 13:21

0

you caanot create table with the name of "@name" use any other name

CREATE TABLE table_name([Image] VARBINARY (MAX) NOT NULL);", conn);

answered Apr 28 '17 at 13:21

user7415073

290
4
22

score 0 · Answer 3 · answered Apr 28 '17 at 13:24

I think you meant to write @Uname instead?

As the others say, @name is not a valid tablename.

Try doing something like this:

string tablename = "Bob123";
SqlCommand comm = new SqlCommand(""CREATE TABLE " + tablename + " ([Image] VARBINARY (MAX) NOT NULL);", conn);

ASP.NET Programmatically create a database table

3 Answers3