sql only creates one table

Question

I have a section of code dedicated to creating several tables, yet it creates the first one then throws exceptions, but the exception contains nothing, its blank. Here is my code:

string connectionString = "DataSource=\"EventControl.sdf\"";
        SqlCeEngine en = new SqlCeEngine(connectionString);
        en.CreateDatabase();
        string createTable = "CREATE TABLE Login (userName nvarchar(50), UserType nvarchar(10), Salt nvarchar(100), Hash nvarchar(100))";
        string connection = "Data Source =\"EventControl.sdf\"";
        SqlCeConnection connexion = new SqlCeConnection(connection);
        SqlCeCommand table = new SqlCeCommand(createTable, connexion);
        try
        {
            connexion.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        table.ExecuteNonQuery();
        connexion.Close();
        string createTable1 = "CREATE TABLE Logs (LogNo nvarchar(5), LoggedBy nvarchar(20), DateAndTime nvarchar(20), Callsign nvarchar(20), Category nvarchar(20), SubCategory nvarchar(20), Escalated nvarchar(15), Summary nvarchar(MAX), Complete nvarchar(10))";
        string connection1 = "Data Source =\"EventControl.sdf\"";
        SqlCeConnection connexion1 = new SqlCeConnection(connection1);
        SqlCeCommand table1 = new SqlCeCommand(createTable1, connexion1);
        try
        {
            connexion1.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        table1.ExecuteNonQuery();
        connexion1.Close();
        string createTable2 = "CREATE TABLE Sites (SiteName nvarchar(100),SiteAddress nvarchar(100),PersonInCharge nvarchar(100))";
        string connection2 = "Data Source =\"EventControl.sdf\"";
        SqlCeConnection connexion2 = new SqlCeConnection(connection2);
        SqlCeCommand table2 = new SqlCeCommand(createTable2, connexion2);
        try
        {
            connexion2.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        table2.ExecuteNonQuery();
        connexion2.Close();
        string createTable3 = "CREATE TABLE Categories (Category nvarchar(50), SubCategory nvarchar(50))";
        string connection3 = "Data Source =\"EventControl.sdf\"";
        SqlCeConnection connexion3 = new SqlCeConnection(connection3);
        SqlCeCommand table3 = new SqlCeCommand(createTable3, connexion3);
        try
        {
            connexion3.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        table3.ExecuteNonQuery();
        connexion3.Close();
        string createTable4 = "CREATE TABLE Callsigns (StaffName nvarchar (50), Callsign nvarchar(50))";
        string connection4 = "Data Source =\"EventControl.sdf\"";
        SqlCeConnection connexion4 = new SqlCeConnection(connection4);
        SqlCeCommand table4 = new SqlCeCommand(createTable4, connexion4);
        try
        {
            connexion4.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        table4.ExecuteNonQuery();
        connexion4.Close();
        string createTable5 = "CREATE TABLE Events (EventName nvarchar(100), EventLocation nvarchar(100), PersonInCharge nvarchar(100))";
        string connection5 = "Data Source =\"EventControl.sdf\"";
        SqlCeConnection connexion5 = new SqlCeConnection(connection5);
        SqlCeCommand table5 = new SqlCeCommand(createTable5, connexion5);
        try
        {
            connexion5.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
        table5.ExecuteNonQuery();
        connexion5.Close();

It gets to table1.ExecuteNonQuery then stops. Can anyone help??

PS And before anyone says why not just create the databases in VS, its because i have tried that, and they worked fine on my computer but when i created the setup files for deployment the folders got all messed up and the program wouldnt recognise the DB anymore

Any error message? have you run SQL Profiler while stepping through the code? what do mean when you say "stops" - like the code control halts, the app crashes? — Jeremy Thompson, Oct 09 '13 at 04:43
SQL Profiler? Never heard of it, sounds useful though. It doesnt freeze, it just stays on that line and open the form thats supposed to open when all is said and done — tony b, Oct 09 '13 at 04:44
unfortunately [out of luck](http://stackoverflow.com/questions/225121/profiler-for-sql-ce), my advice put the `table1.ExecuteNonQuery();` in the Try-Catch and see if it pop's up a hint. Good luck! — Jeremy Thompson, Oct 09 '13 at 04:49
"There was an error parsing the query. [ Token line number = 1,Token line offset = 201,Token in error = MAX ] ??? — tony b, Oct 09 '13 at 04:53

score 1 · Answer 1 · answered Oct 09 '13 at 05:12

1

changed the MAX value in table1 to 4000

answered Oct 09 '13 at 05:12

tony b

1,341
2
10
12

can you comment on _why_ this worked? Please accept your own answer when the system will allow you to – tacaswell Oct 10 '13 at 04:30

score 1 · Answer 2 · answered Oct 09 '13 at 07:04

1

The data type nvarchar(MAX) is not supported by SQl Server Compact, use ntext instead:

Summary ntext,

answered Oct 09 '13 at 07:04

ErikEJ

40,951
5
75
115

score -1 · Answer 3 · answered Oct 09 '13 at 04:43

SqlCeEngine engine = new SqlCeEngine("Data Source = AdventureWorks.sdf");
engine.Compact(null);
//you need add a user and a password to acess the database 
engine.Compact("Data Source=; Password =a@3!7f$dQ;");

http://msdn.microsoft.com/pt-br/library/system.data.sqlserverce.sqlceengine.compact(v=vs.100).aspx http://msdn.microsoft.com/en-us/library/tk883fc7(v=vs.100).aspx

sql only creates one table

3 Answers3