Re: Please Urgent - Update Data Source Problem ???

From: William Ryan eMVP (bill_at_NoSp4m.devbuzz.com)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 12:03:23 -0500

1) If you call Update, and the rowstate of all your rows is unchanged (ie
HasChanges = false), then there's nothing to submit to the database. The
way the dataadapter works is it iterations through the rows collection for
the changed rows, calling the DataAdapter's Update command for all rows
marked as Modified, Delete for each of the ones marked Deleted, and Insert
for each one that's inserted.

So, if you don't have any changes, it's the equivalent of putting calling
the Update commandtext in the middle of a loop that never iteraties.
2) You can set up your Update Statement in a way that at the end, it calls
an additional select statement. This is usually done to retrieve Indentity
values that are only assigned by the DB once the update command is executed.
If you go into the dataadapter configuration wizard, and you select Advanced
Options in the part of the wizard where you select the tables, you'll see an
option called Refresh DataSet. This is on by default. If this is checked,
then your Insert Command for instance will have two statements, The insert
command a ";" and a Seelct command. The mappings will take care of updating
the values.

The main problem you are having is where you are calling update. If the
DataSet hasChanges, the changes should be submitted if your command logic is
correct. However this will only happen if you call Update.

Let me look through the new code a little more and see if I can write it for
you to get you started.

Cheers,

Bill
"Lianna" <anonymous@discussions.microsoft.com> wrote in message
news:54737A4E-B2E2-489B-82BF-6F3ECC013EF3@microsoft.com...
> Yes William,
>
> In answer 4, - what means "call update all year and nothing will happen"
???
>
> In answer 5, - I'm not really understand what you means "unless your code
is such that it fires additional select statements." In addition, if I have
update the existing appointment and I close the application and open again
will the updated data still exists. How to get the updated data ?? Cause in
my program, I've four tab
>
> - 1st tab display the today appointment
>
> - 2nd tab allows user to view their appointment by click on the
dataTimePicker. Each time the date change, the program
> will retreive the appointment for that particular date from the local
database, and display it in a data grid.
> User can update the appointment by click on the datagrid, and the update
form will display
>
> - 3rd tab allows user to add new appointment. This part there is no
problem as I use sql to insert the value and call
> "ExecuteNonQuery( )" to commit the action. Then I go to 2nd tab and
select the date that the appointment I just insert,
> it display the appointment made.
>
> - 4th tab is used to delete the existing appointment. This part face the
same problem. But I think if manage to solve the
> update problem, this will solve also.
>
>
> Thanks i will try to solved the problem by myself first, if stud then
will let you know thankssss
>
> ----- William Ryan eMVP wrote: -----
>
> 1) When the app starts, I'd check to see if the table exists.
Creating a
> database/table every time the app loads is most likely unnecessary.
If you
> have data in the database from the last session, you'll delete it
under your
> scenario, and if you don't need to persist the data, then there's
better
> ways than handle data than SqlCe. You could create a
datatable/dataset
> locally and just never serialize it so it goes away when the app
does.
> That'd probably be a preferable method to creating the database each
time
> and deleting it beforehand if it does.
>
> As far as the connection..think of it this way. If you put
connection close
> anywhere in your try block and you throw an exception, you'll never
close
> the connection. If you put it in the catch block, you'll only close
it if
> you throw an exception. Neither of which is desireable. So, here's
what I
> recommend. In the finally block, use a test if(cn.State <>
> ConnectionState.Closed){cn.Close();} Now, you can still close it
> immediately when you are finished with the connection but this will
make
> sure it's closed. If you are using a DataAdapter, I'd highly
recommend
> letting it open and close itself so you don't accidentally forget to
close
> it. However, I'd still use the finally block in case something blows
up.
> with the command object methods, you have no choice but to open the
> connection manually, and consequently, you're responsible for closing
it.
>
> 2) Yes
> 3) You can do it in form_load, that's fine. Remember that this isn't
doing
> anything with the data adapter other than setting it's properties.
It only
> needs to happen once, so Form_Load is fine for this
> 4) No, all that HasChanges tells you is if the dataset has changes.
If it
> doesn't , then you can call update all year and nothign will happen.
> However, if it does, you can call update and hopefully you're changes
will
> be commmited. They may not though because of many things, like no
available
> connection. Usually though, everything will be fine.
>
> DataAdapter.Update returns an Integer value indicating how many rows
were
> changed in the DB. So, something like int i =
DataAdapter.Update(dataSet,
> TableName);
> if i > 0 it worked.
> 5) Unnecessary. SqlCE isn't going to be updated by someone else
other than
> the user. So, all of the changes will be reflected locally. If your
update
> fails, and you repopulate it, you'll overwrite your changes. Update
only
> sends the changes back to the database, it doesn't pull any new
values back
> unless your code is such that it fires additional select statements.
> 6) Right
> 7) add using System.Diagnostics; at the top of your code.
>
> Let me know how it goes and if you have any problems.
>
> Cheers,
>
> Bill
>
> "Lianna" <anonymous@discussions.microsoft.com> wrote in message
> news:2E2E7414-D9AD-4669-B206-F760218C3BD2@microsoft.com...
> > Thanks a million. I really appreciate that you willing to help me
all this
> while. However, since the scenario seems complicated, I've decide to
code
> the program again. But First I would describe my scenario again and
then
> would like to clarify few questions.
> >> Scenario:
> > I want to write a simple program that allows users to view their
daily
> schedule, and they can update the existing schedule, add new schedule
and
> delete schedule made.
> >> 1. If I put the create table code in "form_load" does it correct
?? I've
> followed your
> > suggestion and catch the exception with below code:
> >> catch (SqlCeException ex)
> > {
> > for(int cu = 0; cu < ex.Errors.Count; ++cu)
> > {
> > MessageBox.Show("Error:" + ex.Errors[cu].ToString()+ "\n");
> > }
> > }
> >> This code does show error message that the table I create
already
> > exists. So I've modified the code:
> >> if (! File.Exists (@"\My Documents\MSFA.sdf"))
> > {
> > SqlCeEngine engine = new SqlCeEngine (
> > @"Data Source = \My Documents\MSFA.sdf");
> > engine.CreateDatabase();
> > }
> > else
> > {
> > File.Delete(@"\My Documents\MSFA.sdf");
> > SqlCeEngine engine = new SqlCeEngine (
> > @"Data Source = \My Documents\MSFA.sdf");
> > engine.CreateDatabase();
> > }
> >>> And I do close the connection which is shown in the "finally"
> > statement. Or it's advisable to close the connection once create
the
> > table. If I put before "catch", does it work ???
> >>>> 2. Does the way I bind the control is correct ?? I bind the
control
> > with following code and the binding code is put in the
"form_load".
> >>> SqlCeDataAdapter da = new SqlCeDataAdapter();
> > DataSet ds = new DataSet();
> > DataTable dt = new DataTable();
> > da.Fill(ds, "Schedule");
> > dt = ds.Tables["Schedule"];
> > dataGrid.DataSource = dt;
> > txtDate.DataBindings.Clear();
> > txtDate.DataBindings.Add("Text", dt, "Date");
> > txtTime.DataBindings.Clear();
> > txtTime.DataBindings.Add("Text", dt, "Time");
> > txtTask.DataBindings.Clear();
> > txtTask.DataBindings.Add("Text", dt, "Task");
> >>>> 3. Where should I place the following DataAdapter command?? Is
it also
> > put in "form_load" ?? Is it necessary to have all this command
in
> > order to update the data source ??
> >> SelectCommand, UpdateCommand, InsertCommand and DeleteCommand
> >> Example:
> > cmdUpdate = cnn.CreateCommand();
> > cmdUpdate.CommandText = "UPDATE Schedule SET DATE = @Date,Time =
> > @Time, Task = @Task WHERE Date = @Date AND Time = @Time AND Task
=
> > @Task ";
> >> prm = cmdUpdate.Parameters.Add("@Date", SqlDbType.NVarChar, 0,
> > "Date");
> > prm = cmdUpdate.Parameters.Add("@Time", SqlDbType.NVarChar, 0,
> > "Time");
> > prm = cmdUpdate.Parameters.Add("@Task", SqlDbType.NVarChar, 0,
> > "Task");
> > prm.SourceVersion = DataRowVersion.Original;
> > da.UpdateCommand = cmdUpdate;
> >>> 4. To check whether the dataset is updated or not is it I can use
> following code: If not then how can I know the row is change ??. In
my
> existing program, I try to put it after call the -- da.update(ds,
> "Schedule") and the result show the dataset does not change.
> >> if(ds.HasChange())
> > {
> > // Do something
> > }
> >> 5. I put the btnRetreive because I think after I call the
da.Update(ds,"
> Schedule") then I check whether the
> > data source is updated by click the retrieve button and select
all the
> row from the table again. Does it
> > logic ??
> >> 6. Actually what I need in the "UpdateButton_Click" ??? Is it
just need
> the following code ??
> >> try{
> > da.Update(ds, "Schedule");
> > ds.AcceptChanges();
> > }
> > catch (Exception ex)
> > {
> > // Do something
> > }
> >> 7. The "Assertion (Debug.Assert(ds.HasChanges())); " code that you
ask me
> try does not work, the system
> > display - "The type or namespace name 'Debug' could not be
found (are
> you missing a using directive or
> > an assembly reference?)"
> >