Click here to Skip to main content
15,895,606 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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());
            }
        }
    }
}
Posted
Updated 29-Jan-10 2:10am
v2

aasser wrote:
Message="Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."


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.
 
Share this answer
 
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?
 
Share this answer
 
v4
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?
 
Share this answer
 

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