SQL CE & Dataset - Row does not exist

Question

I'm (new to and) working with a SQL CE database that will later be connected to a project where we send and receive information to a device over a SerialPort. This database will store information about every part of the communication. I'm a little stuck when it comes to updating a Dataset and committing this updated data to the database.

data = new DataSet();
adapter = new SqlCeDataAdapter("SELECT * FROM " + [table_name])
builder = new SqlCeCommandBuilder(adapter);
adapter.Fill(data, [table_name]);

data.Tables[table_name].Rows[identity_value][a_column] = a_column_value;
adapter.Update(data, [table_name]);

Before I run this method I'm ensuring that I have a record in the table at identity value 1. However I'm getting a There is no row at position 1 IndexOutOfRangeException before I call the adapter.Update(). I'm assuming that I've misunderstood how to use and update a Dataset. Any advice?

I've tried looking into the Dataset prior to trying to update the row however the debugger doesn't seem to let me peer into the Dataset, is there a way to do this?

Answer 1

try this:

var rows = data.Tables[table_name].Select(String.Format("[Id] = {0}",identity_value));

if (rows.Length == 1)
{
    rows[0][a_column] = a_column_value;
}

you are interpreting wrong the property Rows

data.Tables[table_name].Rows[ROWINDEX][COLUMNINDEX]

ROWINDEX is the array index not the identity

A suggestion:

if you have to use DataSet then load table schema info too with SqlCeDataAdapter.FillSchema:

adapter.FillSchema(data.Tables[table_name], SchemaType.Mapped);

adapter.Fill(data, [table_name]); this loads only the data:

Then if you have at least one column designated as a primary key column in the DataTable you can get the row with DataRowCollection.Find Method:

DataRow foundRow = data.Tables[table_name].Rows.Find(identity_value);