Cannot create SQLCE database. What am I doing wrong?

Question

0.00/5 (No votes)

See more:

I want to look at using SQL Compact Edition as the source for a text based game (yeah, yeah, very nostalgic) but I cannot get a database created. In Visual Studio 2008, I have tried this:

1. Right click Data Connections in the Server Explorer and select "Add Connection" from the menu.

2. Select "Microsoft SQL Server Compact 3.5" in the Choose Data Source and click Continue.

3. Under Connection Properties, click Create....

At that point, the Add Connection form closes and there is no interface for creating the database. Whether I provide a name for the database file or leave it blank, the result is the same. I have also tried going in through the menu item Data | Add New Data Source; the connection wizard takes me back to the same Choose Data Source interface with the same problem.

First off, is this sequence correct or am I missing something? Would it make a difference to attempt a reinstall of SQLCE? Or should I give up this approach entirely and go with a compacted XML file for my data?

Posted 21-Jul-11 4:51am

Gregory Gadow

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2011-07-21T08:27:00

The sequence is correct - when I try I get the "Create New SQL Server Compact Database" dialog after pressing the "Create" button.

However, I also have SQL Server 2008 R2 installed on my machine, so my configuration may not be the same as yours. I do vaugely remember having problems creating SqlCE fdatabases, but that was about two years ago, and I can't remember all the details of how I fixed it.

Things to check:

Can you create a connection to any other database via the Server Explorer?
Can you connect to an existing SQLCE database via Server Explorer?

If it all falls apart, you could do the following, and see if you can connect to that:

private void RebuildDB()
    {
    if (!File.Exists(strDB))
        {
        try
            {
            SqlCeEngine engine = new SqlCeEngine(strCon);
            engine.CreateDatabase();
            Log.AddLog("DataBase Created");
            }
        catch (System.Exception ex)
            {
            Log.AddLog("Error creating database: " + ex.ToString());
            }
        }
    using (SqlCeConnection con = new SqlCeConnection(strCon))
        {
        con.Open();
        SqlCeCommand cmd = new SqlCeCommand("SELECT table_name FROM INFORMATION_SCHEMA.TABLES", con);
        SqlCeDataReader r = cmd.ExecuteReader();
        Log.AddLog("Reading Tables");
        bool tablePresent = false;
        while (r.Read())
            {
            string tableName = (string) r[0];
            if (tableName == strTable)
                {
                tablePresent = true;
                }
            }
        if (tablePresent)
            {
            Log.AddLog("Removing table");
            cmd = new SqlCeCommand(string.Format("DROP TABLE {0}", strTable), con);
            cmd.ExecuteNonQuery();
            }
        Log.AddLog("Creating table");
        cmd.Dispose();
        cmd = new SqlCeCommand(string.Format("CREATE TABLE {0} (id int NOT NULL, name nvarchar(100), level nvarchar(10))", strTable), con);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cmd = new SqlCeCommand(string.Format("CREATE UNIQUE INDEX idx1 ON {0} (id)", strTable), con);
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        cmd = new SqlCeCommand(string.Format("INSERT INTO {0} (id, name, level) VALUES (@ID, @NM, @LV)", strTable), con);
        cmd.Parameters.AddWithValue("@ID", 1);
        cmd.Parameters.AddWithValue("@NM", "Joe Green");
        cmd.Parameters.AddWithValue("@LV", "User");
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 2;
        cmd.Parameters["@NM"].Value = "Pete Smith";
        cmd.Parameters["@LV"].Value = "User";
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 3;
        cmd.Parameters["@NM"].Value = "Miss Scarlet";
        cmd.Parameters["@LV"].Value = "User";
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 4;
        cmd.Parameters["@NM"].Value = "Mike Black";
        cmd.Parameters["@LV"].Value = "User";
        cmd.ExecuteNonQuery();
        cmd.Parameters["@ID"].Value = 5;
        cmd.Parameters["@NM"].Value = "The Boss";
        cmd.Parameters["@LV"].Value = "Admin";
        cmd.ExecuteNonQuery();
        cmd.Dispose();
        con.Close();
        }
    }

That will create a small, single table DB, and populate it with a few values.