no data is inserted to sqlce on resultset.insert() does it require primary key record...?

Question

            SqlCeConnection LocalCon = new SqlCeConnection(SqlConnString);
            LocalCon.Open();
            SqlCeTransaction InsertTransaction=LocalCon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
            SqlCeResultSet SqlCeResEt=null;
            SqlCeUpdatableRecord TableRecord;
            SqlCeCommand InsertPromoCmd = LocalCon.CreateCommand();
            try
            {                
                InsertPromoCmd.Transaction = InsertTransaction;
                InsertPromoCmd.CommandText = "PromoTable";
                InsertPromoCmd.CommandType = CommandType.TableDirect;
                SqlCeResEt = InsertPromoCmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable | ResultSetOptions.Sensitive);
                TableRecord = SqlCeResEt.CreateRecord();

                TableRecord.SetValue(SqlCeResEt.GetOrdinal("PoneId"), PoneId);


                TableRecord.SetValue(SqlCeResEt.GetOrdinal("EclipseId"), EclipseId);


                TableRecord.SetValue(SqlCeResEt.GetOrdinal("Country"), Country);


                TableRecord.SetValue(SqlCeResEt.GetOrdinal("BizGroup"), BizGroup);


                TableRecord.SetValue(SqlCeResEt.GetOrdinal("PromoType"), PromoType);


                TableRecord.SetValue(SqlCeResEt.GetOrdinal("ProgrammeType"), ProgramType);


                TableRecord.SetDateTime(SqlCeResEt.GetOrdinal("StartDate"), StartDate);

                TableRecord.SetDateTime(SqlCeResEt.GetOrdinal("EndDate"), EndDate);
                SqlCeResEt.Insert(TableRecord,DbInsertOptions.PositionOnInsertedRow);
                //SqlCeResEt.Update();
                SqlCeResEt.Close();
                InsertTransaction.Commit(CommitMode.Deferred);
            }            

This is the DatBase table .....The masterId is autoincriment ......

The insert() is not throwing any errors or exceptions.... but no data is being saved and how to know how many number of records are affected...

---

$Kracker

Answer 1

Hello,

Follow the link below it may help you

http://stackoverflow.com/questions/5016260/sqlce-upsert-update-or-insert-how-to-prepare-a-row-using-common-method

Answer 2

Thanks but I've already seen that....My question is specefic on why am I unable to insert data ....

---

$Kracker

Answer 3

can anyone please help me.....

---

$Kracker

Answer 4

> The insert() is not throwing any errors or exceptions.... but no data is being saved and how to know how many number of records are affected...


try using the following example.  
   

using System.Data;
using System.Data.SqlServerCe;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            var path = @"c:\temp\test1.sdf";
            Create(path);

            var cc = new SqlCeConnection("Data Source=" + path);
            var cm = cc.CreateCommand();
            cm.CommandType = System.Data.CommandType.TableDirect;
            cm.CommandText = "test";
            cc.Open();
            try
            {
                var rs = cm.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
                try
                {
                    var ti = rs.GetOrdinal("text");
                    var ii = rs.GetOrdinal("id");
                    for (int i = 0; i < 5; i++)
                    {
                        var re = rs.CreateRecord();
                        re.SetString(ti, "hello" + i);
                        rs.Insert(re, DbInsertOptions.PositionOnInsertedRow);
                        var id = rs.GetValue(ii);
                        System.Diagnostics.Trace.WriteLine(id);
                    }
                }
                finally
                {
                    rs.Close();
                }
            }
            finally
            {
                cc.Close();
            }

            var dt = new DataTable();
            var ca = new SqlCeDataAdapter("select * from [test]", cc);
            ca.Fill(dt);
            new DataGridView {Dock = DockStyle.Fill, Parent = this, DataSource = dt };
        }

        private static void Create(string path)
        {
            if (System.IO.File.Exists(path) == false)
            {
                var ce = new SqlCeEngine("Data Source=" + path);
                ce.CreateDatabase();
                var cc = new SqlCeConnection("Data Source=" + path);
                var cm = cc.CreateCommand();
                cm.CommandText = "create table [test] (id int identity, text nvarchar(100))";
                try
                {
                    cc.Open();
                    cm.ExecuteNonQuery();
                }
                finally
                {
                    cc.Close();
                }
            }
        }
    }
}

 
 

Answer 5

got it...

actually

SqlConnString 

was poiting some other SQL...

Thanks guys...

---

$Kracker