Click here to Skip to main content
15,892,298 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
In my WindowsCE / Compact Framework (.NET1.1) project, I need to create a new table in code. I thought I could do it this way:

C#
if (! TableExists("table42"))
{
	DBUtils.CreateTable42();
}

public static bool TableExists(string tableName)
{
	try
	{
		using (SqlCeConnection sqlConn = new SqlCeConnection(@"Data Source=\my documents\Platypus.SDF"))
		{
			sqlConn.Open();
			string qryStr = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ?";
			SqlCeCommand cmd = new SqlCeCommand(qryStr, sqlConn);
			cmd.Parameters[0].Value = tableName;
			cmd.CommandType = CommandType.Text;
			int retCount = (int)cmd.ExecuteScalar();
			return retCount > 0;
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show("TableExists " + ex.Message);
		return false;
	}
}

public static void CreateTable42()
{
	try
	{
		using (SqlCeConnection con = new SqlCeConnection(@"Data Source=\my documents\Platypus.SDF"))
		{
			con.Open();
			using (SqlCeCommand com =  new SqlCeCommand(
						

	   "create table table42 (setting_id INT IDENTITY NOT NULL PRIMARY KEY,  setting_name varchar(40) not null, setting_value(63) 

varchar not null)", con))
			{
				com.ExecuteNonQuery();
				WriteSettingsVal("table42settingname", "table42settingval");
			}
		}
	}
	catch (Exception ex)
	{
		MessageBox.Show("CreateTable42 " + ex.Message);
	}
}

public static void WriteSettingsVal(string settingName, string settingVal)
{
	using (SqlCeConnection sqlConn = new SqlCeConnection(@"Data Source=\my documents\Platypus.SDF"))
	{
		sqlConn.Open();
		string dmlStr = "insert into tabld42 (setting_name, setting_value) values(?, ?)";
		SqlCeCommand cmd = new SqlCeCommand(dmlStr, sqlConn);
		cmd.CommandType = CommandType.Text; 
		cmd.Parameters[0].Value = settingName;
		cmd.Parameters[1].Value = settingVal;
		try
		{
			cmd.ExecuteNonQuery();
		}
		catch (Exception ex)
		{
			MessageBox.Show("WriteSettingsVal " + ex.Message);
		}
	}
}


...but I'm getting err msgs with that. Or, it's failing, anyway, but not telling me much*. I can't step through it, so I rely on those calls to MessageBox.Show().

* I only see "TableExists ", then "CreateTable42 " (there's nothing in ex.Message in either case, although it's obviously throwing an exception).
Posted
Comments
[no name] 31-Jul-13 15:46pm    
Care to share what these "err msgs" are or is it a secret?
B. Clay Shannon 31-Jul-13 16:42pm    
Read the end of the post.
[no name] 31-Jul-13 17:11pm    
Those are not error messages.
CHill60 31-Jul-13 16:16pm    
Why can't you step through it?
B. Clay Shannon 31-Jul-13 16:43pm    
Because it's a Windows CE project using archaic technology. I must build, copy, install on the handheld, and run it to test.

1 solution

It looks like your SQL statement is not correct.

The CREATE TABLE[^] documentation states:
SQL
[ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ]
So it is either one off the options but not both, which makes sense as a primary or unique key is always NOT NULL.

As ryanb31 commented best first try using SMS to check the SQL statement.
 
Share this answer
 
Comments
B. Clay Shannon 31-Jul-13 16:49pm    
I assume SMS is "SQL Management Studio"* or something like that, but I don't think I have that in my setup (XP Mode, .NET1.1, Visual Studio 2003). * Binging it gave me "Short Message Service"
B. Clay Shannon 31-Jul-13 16:50pm    
It fails prior to the Create code, though; it fails with TableExists() first.
B. Clay Shannon 31-Jul-13 17:18pm    
I changed the exception code in the TableExists() method to:

catch (Exception ex)
{
MessageBox.Show("TableExists ex.Message == " + ex.Message);
MessageBox.Show("TableExists ex.ToString() == " + ex.ToString());
MessageBox.Show("TableExists ex.GetBaseException() == " + ex.GetBaseException());
return false;
}

...and now see:

TableExists ex.Message ==

TableExists ex.ToString() == System.Data.SqlServerCe.SqlCeException at System.Data.SqlServerCe.SqlConnection.ProcessResults(Int32 hr) at ...at Open(boolean silent) ...

TableExists ex.GetBaseException() == [same as ex.ToString() above]

Int32 hr ... ???
André Kraak 1-Aug-13 1:18am    
Try catching the SqlCeException instead and see if that one contains any more useful information. Maybe the Message property will contain information.
B. Clay Shannon 1-Aug-13 12:11pm    
Good idea; I'll try that.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900