保存SQL的问题[已解决] [英] SQL problem in saving [SOLVED]
问题描述
我已经编写了一个小程序来通过SqlSeverCe(紧凑版)管理sdf数据库文件.
我更改并保存的每个数据库都会正确运行一段时间.一段时间后，该程序在保存数据库时遇到问题，但出现以下异常:
System.InvalidOperationException未处理
Message = 对于不返回任何键列信息的SelectCommand，不支持为UpdateCommand生成动态SQL."
我该怎么办?
这是我的源代码:
Hi,
I have written a small program to manage sdf database files by SqlSeverCe(compact edition).
Each database I change and I save works correnctly for a few time. After a while, this program gets problem with saving the database with such an exception:
System.InvalidOperationException was unhandled
Message="Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
What should I do?
This is my source code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlServerCe;
namespace SQL_Editor
{
public partial class MainForm : Form
{
DataSet ds = new DataSet();
SqlCeConnection cn;
SqlCeDataAdapter da;
string DefaultConnectionString;
string DatabaseFile;
string TableName;
public MainForm()
{
InitializeComponent();
DefaultConnectionString = Properties.Settings.Default.DefaultConnectionString;
}
private void Load_Click(object sender, EventArgs e)
{
//////////
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "sdf files (*.sdf)|*.sdf|mdf files (*.mdf)|*.mdf|All files (*.*)|*.*";
ofd.RestoreDirectory = true;
if (ofd.ShowDialog() != DialogResult.OK)
return;
DatabaseFile = ofd.FileName;
//////////
if (CheckSaveChanges() == DialogResult.Cancel)
return;
try
{
string ConnectionStr = DefaultConnectionString.Replace("<DatabaseFileName>", DatabaseFile);
SqlCeConnection cn1 = new SqlCeConnection(ConnectionStr);
cn1.Open();
string strSql = "SELECT * FROM INFORMATION_SCHEMA.TABLES";
SqlCeDataAdapter da1 = new SqlCeDataAdapter(strSql, cn1);
DataSet ds1 = new DataSet();
ds1.Clear();
da1.Fill(ds1);
cn1.Close();
TableSelect tbslc = new TableSelect();
for (int i = 0; i < ds1.Tables[0].Rows.Count; i++)
tbslc.Add(ds1.Tables[0].Rows[i][2].ToString());
tbslc.ShowDialog();
if (tbslc.TableResult == "")
return;
else
TableName = tbslc.TableResult;
cn = new SqlCeConnection(ConnectionStr);
cn.Open();
strSql = "SELECT * FROM " + TableName;
da = new SqlCeDataAdapter(strSql, cn);
ds.Clear();
da.FillSchema(ds, SchemaType.Source);
da.Fill(ds, TableName);
//ds.Tables[0].PrimaryKey = new System.Data.DataColumn[] { ds.Tables[0].Columns[0], ds.Tables[0].Columns[1], ds.Tables[0].Columns[2] };
//////////
dataGrid1.DataBindings.Clear();
dataGrid1.DataBindings.Add(new Binding("DataSource", ds, TableName));
//////////
cn.Close();
//Clipboard.GetDataObject().GetData(DataFormats.Text)
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error");
}
}
private void Option_Click(object sender, EventArgs e)
{
Option opt = new Option();
opt.textBox_ConnectionString.Text = DefaultConnectionString;
opt.ShowDialog();
if (opt.OK_result)
{
DefaultConnectionString = opt.textBox_ConnectionString.Text;
}
}
private void Save_Click(object sender, EventArgs e)
{
Save();
}
private void button_Exit_Click(object sender, EventArgs e)
{
if (CheckSaveChanges() != DialogResult.Cancel)
{
if(cn!=null)
cn.Close();
Close();
}
}
private DialogResult CheckSaveChanges()
{
DialogResult dr;
if (IsChaned())
{
dr = MessageBox.Show("Save changes?", "", MessageBoxButtons.YesNoCancel);
if (dr == DialogResult.Yes)
{
if (!Save())
return DialogResult.Cancel;
}
return dr;
}
else
return DialogResult.No;
}
private bool Save()
{
try
{
SqlCeCommandBuilder cb = new SqlCeCommandBuilder(da);
da.Update(ds, TableName);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Error");
return false;
}
return true;
}
private bool IsChaned()
{
return ds.HasChanges();
}
private void button_Cell_Get_Click(object sender, EventArgs e)
{
try
{
int c = int.Parse(textBox_cell_col.Text);
int r = int.Parse(textBox_cell_row.Text);
textBox_Cell_value.Text = ds.Tables[TableName].Rows[r][c].ToString();
}
catch { }
}
private void button_Cell_Set_Click(object sender, EventArgs e)
{
try
{
int c = int.Parse(textBox_cell_col.Text);
int r = int.Parse(textBox_cell_row.Text);
ds.Tables[0].Rows[r][c] = textBox_Cell_value.Text;
}
catch { }
}
private void RunSQLCommand(string query)
{
try
{
SqlCeCommand cmd = new SqlCeCommand(query, cn);
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Dispose();
cmd.Connection = null;
}
catch (SqlCeException ex)
{
MessageBox.Show(ex.ToString());
}
}
}
}
推荐答案
Message =用于生成动态SQL不返回任何键列信息的SelectCommand不支持UpdateCommand."
Message="Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."
此消息表示，如果您提供的选择语句不包含键列，那么命令构建器将无法为您创建更新语句.因此，将键列添加到SQL语句的SELECT子句中.如果没有键列，则需要重组数据库并将键添加到表中，或者手动创建更新语句.
This message means that the command builder can''t create an update statement for you if the select statement you provided it does not include the key columns. So add the key columns to the SELECT clause of your SQL statement. If you do not have key columns, you either need to restructure your database and add keys to the table or manually create your update statements.
如何在中添加键列> SELECT子句? (请给我一个例子plz)
这是我的选择语句:
strSql = "SELECT * FROM " + TableName;
这是我的连接字符串:
数据源=< DatabaseFileName> ;;"
(< DatabaseFileName>"被替换为数据库名称)
以及如何手动添加键列?
How can I add a key column in my SELECT claus? (give me an example plz)
This is my select statement:
strSql = "SELECT * FROM " + TableName;
and this is my connection string:
"Data Source=<DatabaseFileName>;"
("<DatabaseFileName>" is replaced by the name of the database)
and how can I add a key column manually?
命令构建器正在构建要在数据库上运行的SQL语句.您给它SELECT命令，它将建立一个UPDATE，INSERT和DELETE命令.您收到的错误消息表明它无法为您构建这些命令，因为您尚未选择任何键列.如果您使用的是SELECT *，则您的表没有设置任何键列，或者实际上未为命令构建器设置select语句.您要更新的表设置如何?是否设置了关键列?
The command builder is building SQL statements to run against your database. You give it the SELECT command and it will build an UPDATE, INSERT, and DELETE command. The error message that you are getting indicates that it can''t build those commands for you because you haven''t selected any key columns. If you are using SELECT *, either your table doesn''t have any key columns set or the select statement isn''t actually getting set for the command builder. How is the table setup that you are updating? Does it have key columns set?
这篇关于保存SQL的问题[已解决]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
