Create table command doesn't actually create the table c#

Question

I'm trying to create a command which should create a table based on the name is typed in a textBox. No errors, works, query looks good, but doesn't actually create the table. Why is that?

private void button1_Click(object sender, EventArgs e)
{
    int count = 0;

    var connString = (@"Data Source=" + System.IO.Path.Combine(System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location)) + @"\Grupe.sdf");

    using (var conn = new SqlCeConnection(connString))
    {
        try
        {
            conn.Open();
            var query = "CREATE TABLE "  + textBox1.Text.Trim() + "(" + "Id int NOT NULL IDENTITY (1,1) PRIMARY KEY" ;
            MessageBox.Show(query);

            foreach (Control c in this.Controls)
            {
                if (c.Name.Contains("temp") && c is TextBox)
                {
                    if (!String.IsNullOrEmpty(c.Text))
                    {
                        query += "," + c.Text.Trim() + " nvarchar(MAX) NULL";
                        count++;
                    }
                }
            }

            query += ")";

            var command = new SqlCeCommand(query, conn);
            command.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }
}

is there a button actually connected to the handler? Is the handler actually called? Good spot for a _breakpoint_. — , Sep 04 '15 at 08:59
If you click two times on the button without interrupting the debug session did you get an error message stating the table exists? — Steve, Sep 04 '15 at 09:00
If I press twice it says that the Table already exists. Strange.. But it is still not showed in Server Explorer — Ezekiel, Sep 04 '15 at 09:03
Then the database where the table is created is not the one you think — Steve, Sep 04 '15 at 09:03
Have you refreshed the server explorer? It doesn't refresh automatically... — Luaan, Sep 04 '15 at 09:04
It is correct the database. I messageboxed the path to it, and it's sure that. I press right click -> refresh. — Ezekiel, Sep 04 '15 at 09:05
You could be in this situation: http://stackoverflow.com/questions/17147249/why-saving-changes-to-a-database-fails — Steve, Sep 04 '15 at 09:06
if you sure in that (c.Name.Contains("temp") && c is TextBox) then your sql is correct — , Sep 04 '15 at 09:18

score 0 · Answer 1 · 2015-09-04T12:46:43.797

0

Try a

    MessageBox.Show(query);

before

    var command = new SqlCeCommand(query, conn);

You would surely find a syntax error. Code appears to be fine but i guess you must see the complete generated query for create table.

I believe nvarchar is something that has/had a limit of 4000 characters and i could not find nvarchar is list of supported types. Using ntext worked just fine in your code.

private void button1_Click(object sender, EventArgs e)
    {
        int count = 0;
        var connString = (@"Data Source=Grupe.sdf");

        if (!File.Exists("Grupe.sdf"))
        {
            SqlCeEngine engine = new SqlCeEngine(connString);
            engine.CreateDatabase();
        }

        using (var conn = new SqlCeConnection(connString))
        {
            try
            {
                conn.Open();
                var query = "CREATE TABLE " + textBox1.Text.Trim() + "(" + "Id int NOT NULL IDENTITY (1,1) PRIMARY KEY";
                MessageBox.Show(query);
                foreach (Control c in this.Controls)
                {
                    if (c.Name.Contains("temp") && c is TextBox)
                    {
                        if (!String.IsNullOrEmpty(c.Text))
                        {
                            query += "," + c.Text.Trim() + " ntext NULL";


                            count++;
                        }
                    }


                }
                query += ")";

                MessageBox.Show(query);

                var command = new SqlCeCommand(query, conn);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

        }
    }

edited Sep 04 '15 at 12:46

answered Sep 04 '15 at 09:03

@Ezekiel - Are you sure you are looking at the right DB when you say the table is not created? – Sep 04 '15 at 09:04
Yes. It is sure. I already did the messagebox thing and it's ok. The only question I have. The querry is like this: `CREATE TABLE dsa(id int NOT NULL(1,1) PRIMARY KEY ` and then no paranthes. Should it be a paranthes in the final? – Ezekiel Sep 04 '15 at 09:09
CREATE TABLE TEMP (Id int NOT NULL IDENTITY (1,1) PRIMARY KEY, X nvarchar(MAX) NULL); – Sep 04 '15 at 09:20
or CREATE TABLE TEMP (Id int NOT NULL IDENTITY (1,1) PRIMARY KEY); – Sep 04 '15 at 09:20
Not working if I add ")", because it shows token in error, even if I add `query+=")";` – Ezekiel Sep 04 '15 at 09:22
try toclose connection at the end – Sep 04 '15 at 09:25
1

try to "insert into temp values ('1')" from code andsee if you can add row. If yes, then see comment from Steve – Sep 04 '15 at 09:27
Did you check the value of "Copy to output directory" property assigned to your SDF file listed between your project files? – Steve Sep 04 '15 at 09:56
It was set as Copy if newer, changed to Copy always, but the same, nothing happend. One thing I saw. If I press the button twice, it says that the table already exists. If I close the app, and start it again, it doesn't say it anymore even if I enter the exact same name.. If is not saving after closing it? – Ezekiel Sep 04 '15 at 10:02
Tried to change to a sample table create command like ` var query = "CREATE TABLE myTable" + "(id INTEGER CONSTRAINT PKeyMyId PRIMARY KEY)"; ` , but still not creating anything. Damn. – Ezekiel Sep 04 '15 at 12:04
I believe its specific issue to CE where nvarchar is not a supported type anymore! use ntext instead query += "," + c.Text.Trim() + " ntext NULL"; See my edit above. – Sep 04 '15 at 12:43

score 0 · Answer 2 · answered Sep 05 '15 at 06:46

0

Look for a copy of your database file in the bin/debug folder!

answered Sep 05 '15 at 06:46

ErikEJ

40,951
5
75
115

The database file is there, but a copy of it isn't. – Ezekiel Sep 07 '15 at 07:30

Create table command doesn't actually create the table c#

2 Answers2