[MarmiteX1]

Hi,

I have a database with a few records. I have a datagridview which is bound to a dataset. I can click on a record in the datagrid view and display information from the fields into corresponding textboxes. I am using SQL CE software.

What I would like to do is say I edit some text in a textbox, I would like to update the corresponding field that the textbox relates to in the database table permanantly.

E.g Say I have a textbox called Forename and I edit the forename of a selected patient record. It should update that particular patients forename.

I used these two methods:

tableAdapter1.Update(MyPatientsDataSet.Patients);
tableAdapter1.Fill(MyPatientsDataSet.Patients);

But the problem that occurs is if i invoke the Update method on the adapter the record is only updated temporarily which is not what I want. So then i used the fill method but that doesnt update the row with the updated values permanantly.

This is the code I have attempted:

try {
this.PatientForename = Forename.Text;
this.PatientSurname = Surname.Text

//Datagrid view mouse click event sets the position 'pos' globally.
PatientsGridView.Rows[pos].Cells[1].Value = this.PatientForename;
PatientsGridView.Rows[pos].Cells[2].Value = this.PatientSurname;

//it is here i call the update and fill methods.

}
catch(Exception ex)
{
  Console.Writeline(ex.StackTrace.ToString());
}

I wondering if someone could assist me please? I would really appreciate your help.

Thanks in advance.

*01/17/10 - Just tried again today to see if i could get it to work. If i take the "Fill" statment out the changes are only made temporarily and not permanantly. I would appreciate if somebody could advise on this.

This post has been edited by MarmiteX1: 17 January 2010 - 07:03 AM

[Choscura]

I won't claim to be an expert, but it looks like you need a way to save the updated values so that they load when the program does- maybe an app.config file will do the trick. Here's a tutorial that covers the kind of stuff you're asking about: http://www.dreaminco...wtopic31314.htm

[MarmiteX1]

Hi,

Yes thats would I like to do. So I would like the update to be made permanantly and the changes are shown automatically.

Thanks for sharing, i'll have a look into it.

[modi123_1]

What's your stored procedure looking like that is doing the update?

[MarmiteX1]

modi123_1, on 18 Jan, 2010 - 01:05 PM, said:

What's your stored procedure looking like that is doing the update?

Here is what I found in regards to stored procedure:

//I am using this one which is automatically generated as far as i know
//9 parameters
PatientsTableAdapter.Update(string, string, string, string, string, string, string, string, string)

//this one is when I created an adapter in my code
//10 parameters
PatientsTableAdapter.Update(string, string, string, string, string, string, string, string, string, string)

When i looked further into the above after setting a break point i found the following

//Below is the one which has 10 parameters
UPDATE [Patients] SET [PatientsID] = @p1, [Title] = @p2, [Forename] = @p3, [Surname] = @p4, [Address] = @p5, [Postcode] = @p6, [DOB] = @p7, [ContactTel] = @p8, [CellNum] = @p9 WHERE (([PatientsID] = @p10))"

Suprisingly i found the following method which originally had 9 parameters is identical to the above:

UPDATE [Patients] SET [PatientsID] = @p1, [Title] = @p2, [Forename] = @p3, [Surname] = @p4, [Address] = @p5, [Postcode] = @p6, [DOB] = @p7, [ContactTel] = @p8, [CellNum] = @p9 WHERE (([PatientsID] = @p10))"

I shouldn't really have 2 Update methods should i? I have noticed that I did create second Adapter programatically and i then commented it out because i was already using the generated adapter.

I think because the signature still exists in the project, i think there could possibly be some conflict happening.

Should i just delete the Adapter that I am not using? Or will that cause problems?

Thanks

This post has been edited by MarmiteX1: 19 January 2010 - 06:15 AM

[FlashM]

I think that you problem is:

When adding you new data source you were informed that the connection you selected uses a local data file that is not in the current project. Then you were asked if you would like to copy the file to you project and modify the connection. There you should choose not to do that and your updating should be working.


I hope this resolves your issue. Drop a comment if that helped.

[FlashM]

Otherwise if you select choose to copy the file to your project, then every time you rebuild your application, your database gets truncated (emptied) because if you click on your database.sdf file in solution explorer, then you can see in properties window it is chosen by default to copy the file to output directory IF NEWER. And this is the reason your database gets empty every time.

[MarmiteX1]

FlashM, on 19 Jan, 2010 - 09:04 AM, said:

Otherwise if you select choose to copy the file to your project, then every time you rebuild your application, your database gets truncated (emptied) because if you click on your database.sdf file in solution explorer, then you can see in properties window it is chosen by default to copy the file to output directory IF NEWER. And this is the reason your database gets empty every time.

I usually set my database to "Copy if Newer". I then have to take a copy of my database from the \bin directory and paste it into my |DataDirectory| such \MyApp\MyApp\<database goes here>.sdf

Which setting do you recommend i set to? "Do Not Copy"?

Thanks

[FlashM]

No, just choose not to copy the database file to your project when Visual studio asks you - this is when you're adding new data source.

[MarmiteX1]

Ok done.

After further investigation the problem with the database update was due to the values i was passing into the method. Since there were all strings I didnt include one of the values which was required so the issue now appears to be resolved.