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

From: Lianna (anonymous_at_discussions.microsoft.com)
Date: 03/21/04 

Date: Sat, 20 Mar 2004 20:56:06 -0800

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?)"
>