Updating Access database

[Radi Radichev]

Hi!
I have a Typed Dataset dsPersonal1 and i try to update the database after i
edit a row in a datagrid bound to the dataset. Afret I execute
personalDataAdapter1.Update(dsPersonal1) the dataset is updated but not the
actual database.Can anyone tell me what am I doing wrong? I'm usinf Visual
C# Express and .net 2.0. Please explain me how do I update the actual
database...
Thanks!

 

[W.G. Ryan - MVP]

Radi:

The first thing to look at is to ensure that you Adapter is configured
correctly for Updates. Are you using the configuration wizard, the
commandbuilder, the enterprise library or did you write your own. If you're
using the Visual tools , make sure that all of the commands were generated
correctly at the end.

If they are, then you need to check the Rowstate of each row. When you call
Update, the adapter loops through the rows and checks the rowstate. If the
rowstate is deleted, then it uses the Adapter's Delete command and after
submitting the update, calls AcceptChanges on the row which resets the
rowstate on the row. It does the same for Inserts and Updates. However, if
you don't have Changes in your rows, then nothing gets submitted to the
server. One way to verify that is to trap the StateChanged event of the
connection and pop up a messagebox when the state changes. if you don't see
a messagebox then nothing go sent back tot he server (in Sql Server as
opposed to access, you can use Profiler but since you're not using Sql
Server, I just mention it in passing). The better way to check for changes
is to use Debug.Assert(dsPersonal1.HasChanges, "No Changes Are Present");
right before you call Update. If the assertion passes, then you have changes
and if the DB isn't getting updated, then the problem is almost certianly
with your commands themselves. If the assertion fails, then the problem is
that the UI isn't updating as you expected and more than likely, you need to
End the current edit of the row. You do this differently depending on how
you're binding things.

So first, verify that you have correct commands and more importantly, verify
that you have changes to be sumbitted. 99% of the time it's one or both of
these issues. if it isn't, let me know and we'll take it from there.

Cheers,

Bill

 

[Radi Radichev]

Hi!
So first some info... So I'm adding a dataset to the solution explorer as a
new item. From there i choose my *.mdb file and then the table that i wish
to use. I then create a typed dataset using the wizard. Now i have two new
components in the toolbox. i add a personalTableAdapter and it gives me that
it has generated all needed commands including Update. Next i added a
binding source and set its datasource and data member properties
respectively to dsPersonal1 and personal(thats the tablename). i then add a
datagridview to the form. i use the dataadapters fill method to fill the
dataset on FormLoad event. It works fine. I then change a value and in the
FormClosing event i call the personalDataAdapter1.update(dsPersonal) method.
when i checked in debug mode for changes it returns a dataset with changes,
but when i close and the start the programm the changes are not there.... I
hope that helps you to understand what am I doing. Hope that you can help
me!

 

[W.G. Ryan - MVP]

If changes are present and you're calling Update and the changes aren't
taking, the problem is likely with the update logic. Verify that you have a
key on your table first and that if you're using hte configuration wizard,
that all of the commands are correctly generated. You may need to call
EndCurrentEdit on the binding manager or the datasource so that it's not in
edit mode. Just to test this, go ahead and make your changes, then scroll
down creating a new row, don't put anything in it, then scroll back up to
the row you edited. Close the form and see if that doesn't fix it.

 

[Radi Radichev]

Ahm I can't seem to fix it
I saw the update Command and ist seems fine.. I must add though that I call
the Personal form from another from. and when i close the personal form and
start it again without closing the main form the changes are there. but when
i clos the main form and run it again and call personal form there are no
changes... can this be the problem??

greetz

 

[Radi Radichev]

If it helps i can post the Update SQL string so you can see if it is
correct?

 

[W.G. Ryan - MVP]

When you say the changes are there, then aren't, is the same dataset being
used (the same instance) or do you fill it each time when the form opens?

 

[W.G. Ryan - MVP]

that might help but it's also the column mappings with the parameters. How
did you get the Update command, did you code it yourself or did you was it
generated for you? If the latter, with what?

 

[Radi Radichev]

I use this to open the personal form:
frmPersonal PersonalForm = new frmPersonal();

PersonalForm.Show();

PersonalForm.Activate();

And then in the Onload Method of Personal form i call
the Fill Method. So an instance is created everythime i open the Personal
form..

The update command is generated when i create my typed dataset. When i open
the dsPersonal1.xsd in visual studio and click on configure it generates all
commands automaticly. I can't show you the complete code. it is very much.
but the update command is here:

UPDATE `personal` SET `prs_name` = ?, `prs_egn` = ?, `prs_address` = ?,
`prs_city` = ?, `prs_postalcode` = ?, `prs_pidnum` = ?, `prs_pidvalid` = ?,
`prs_passport` = ?, `prs_passvalid` = ?, `prs_birthdate` = ? WHERE
((`prs_id` = ?) AND ((? = 1 AND `prs_name` IS NULL) OR (`prs_name` = ?)) AND
((? = 1 AND `prs_egn` IS NULL) OR (`prs_egn` = ?)) AND ((? = 1 AND
`prs_address` IS NULL) OR (`prs_address` = ?)) AND ((? = 1 AND `prs_city` IS
NULL) OR (`prs_city` = ?)) AND ((? = 1 AND `prs_postalcode` IS NULL) OR
(`prs_postalcode` = ?)) AND ((? = 1 AND `prs_pidnum` IS NULL) OR
(`prs_pidnum` = ?)) AND ((? = 1 AND `prs_pidvalid` IS NULL) OR
(`prs_pidvalid` = ?)) AND ((? = 1 AND `prs_passport` IS NULL) OR
(`prs_passport` = ?)) AND ((? = 1 AND `prs_passvalid` IS NULL) OR
(`prs_passvalid` = ?)) AND ((? = 1 AND `prs_birthdate` IS NULL) OR
(`prs_birthdate` = ?)))



"W.G. Ryan - MVP"

 

[Radi Radichev]

I've changed my Update Query and when i execute it it runs perfectly but i
still can't update the database when i close the program.... Any Ideas??

 

[Cor Ligthert [MVP]]

Radi,

Can you take a different approach, I have seen that you are able to see the
code the designer have made for you.

Let us take completely the designer to create the program that I saw that
you roughly want.

Open a new windows form project

Click on the Tab Data in top of your program.
Choose for Add new Datasource
Follow that to make your tables as you probably already did
Than go to the same Tab Data in Top and do Show datasources
Drag the datasource from (normaly) the rigth side of the screen on your form

Every thing is done,

Click on Debug

Be aware that there is not any kind of error trapped.

I hope this helps,

Cor

 

[Radi Radichev]

Now It works fine... that's funny where could the mistake be... Anyway thank
you for your help!!

 

[W.G. Ryan - MVP]

If you're calling Fill each time, that's the actual data from the db, so if
you make changes, close the form and reopen and they're there, then they do
appear to be in place. Is this the case or am i misunderstanding you

 

[Radi Radichev]

How can i delete or insert a record without using the bindingNavigator
control? I can move through the records with the binding source but i can't
find out how to delete and insert records.... Any tips?

Greetz!

 

[Radi Radichev]

Hi!
I'm trying to check in the OnFormClosing event if the dataset has changes
that are still not commited and if yes to display a dialog box showing a
question if the user wants to save tha changes or not. I test it with
dataset.HasChanges() but it returns true even only when i change the record
with binding navigator... Does anybody know how to do this right?? Also how
can i load a picture in the database and display it in a imagecontrol?
Thanks!!!

 

[Cor Ligthert [MVP]]

Radi,

Can you ask all your questions one by one as new questions, I would not know
how to answer them no in an organized way.

Your first question was answered you told, now it looks for those who are
searching in this newsgroup that it is not.

Cor

 

[W.G. Ryan eMVP]

Radi:

The same principles apply and there's nothing at close that should be
stopping this. The only thing I can think of is the event sequence on
closing. Verify that you have changes, the update is correct (sounds like
you already have this nailed down) and that update is getting called. If all
three are in place it should work.

 

[Radi Radichev]

I've managed to get it running! thanks for your help!
if you can help me with my other posts that would be cool!!

Cheers!