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);
dataGrid1.DataBindings.Clear();
dataGrid1.DataBindings.Add(new Binding("DataSource", ds, TableName));
cn.Close();
}
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());
}
}
}
}