为什么我的SQLServer CE代码失败了？ [英] Why is my SQLServer CE code failing?

问题描述

在我的WindowsCE / Compact Framework（.NET1.1）项目中，我需要在代码中创建一个新表。我以为我可以这样做：

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:

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);
		}
	}
}

...但是我得到了错误的消息。或者，它失败了，无论如何，但没有告诉我太多*。我无法单步执行它，所以我依赖于对MessageBox.Show（）的调用。



*我只看到TableExists，然后是CreateTable42（在任何一种情况下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).

推荐答案

看起来你的SQL语句不正确。



创建表 [ ^ ]文档说明：

It looks like your SQL statement is not correct.

The CREATE TABLE[^] documentation states:

[ NULL | NOT NULL ] | [ PRIMARY KEY | UNIQUE ]

所以它是一个选项而不是两个，这是有道理的，因为主键或唯一键总是 NOT NULL 。



由于ryanb31评论最好，首先尝试使用SMS检查SQL语句。

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.

这篇关于为什么我的SQLServer CE代码失败了？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！