Why is my TextBox control not binding to the SqlCeResultset?

Question

Everything I have read says I am binding a TextBox to a SqlCeResultset correctly ... but it does not work.

Below is my code that does not work (this is the entire project except for the database which only has 1 table with 1 record with 2 fields...) ... no errors are raised but the ResultSet does not update ... apparently because the TextBox is not bound correctly (even though it does show data from the ResultSet)

 

using System;

using System.Data;

using System.Data.SqlServerCe;

using System.Windows.Forms;

 

namespace test

{

    public partial class Form1 : Form

    {

        static string _fileName = @"Program Files\test\HMCHH.sdf";

        static string _connString = "Data Source=" + _fileName + ";File Mode=Shared Read";

 

        SqlCeConnection conn = new SqlCeConnection(_connString);

        SqlCeResultSet rs;

 

        public Form1()

        {

            InitializeComponent();

        }

 

 

        public void LoadData()

        {

            conn.Open();

 

            string sql =

                "SELECT Patient.PatientKey, LastName " +

                "FROM Patient " +

                "WHERE Patient.PatientKey=1832;";

           

            SqlCeCommand cmd = new SqlCeCommand(sql, conn);

            cmd.CommandType = CommandType.Text;

 

            rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

 

            this.tbPatientKey.DataBindings.Add("Text", rs, "PatientKey");

            this.tbLastName.DataBindings.Add("Text", rs, "LastName");

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            rs.Update();

        }

 

        private void button2_Click(object sender, EventArgs e)

        {

            LoadData();

        }

 

        private void button3_Click(object sender, EventArgs e)

        {

            this.tbPatientKey.DataBindings.Clear();

            this.tbLastName.DataBindings.Clear();

            rs.Close();

            conn.Close();

            tbLastName.Text = "";

            tbPatientKey.Text = "";

        }

 

 

    }
}

Answer 1

You need to bind to SqlCeResultSet.ResultSetView property. A sample of binding it to textbox controls is available in the document: 
http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.resultsetview(VS.80).aspx 

The ResultSetView class "implements data binding interfaces and provides a bridge between user interface controls and the SqlCeResultSet."

---

Please mark the post that helps you, and unmark that does not. This benefits our community.

Answer 2

Thanks for the reply ... I had tried the View too and it did not work either (just did not add it to my post)

---------------------------------------------------------------------------------------------------------------

If you are having this problem ... ITS A BUG!

I have been fighting this for a month!

the answer is here http://geek-goddess-bonnie.blogspot.com/2009/09/fun-with-datasets.html

Bonnie's experience is that it is intermitent ... for whatever reason for me .Update never works unless I run this first!  Must be some dll version I have different than her.

just in case the page goes away here is the code w/o the explanation:

protected virtual void CommitProposedChanges(DataSet ds)
{
    if (ds == null)
        return;

    for (int nTable = 0; nTable < ds.Tables.Count; nTable++)
    {
        for (int nRow = 0; nRow < ds.Tables[nTable].Rows.Count; nRow++)
        {
            if (ds.Tables[nTable].Rows[nRow].HasVersion(DataRowVersion.Proposed))
            {
                ds.Tables[nTable].Rows[nRow].EndEdit();
            }
        }
    }
}

Answer 3

Hi PHT,

I am sure you have found the solution for your question.

I faced the same problem and googled the problem, and the solution I got is use resultset view and don't close the connection and it works like magic.

Hope this helps.