window form add and update from MS access DB

[Signo.X]

Hello all ,

im developing a window form that reads the data from MS access DB to a windows form using vb.net 2005..

can any one tell me how to implement the add functionality using a binding source or the data set ? also, how to to update the added record into the DB after adding it using the window form ?

Thanks in advance ,

!signo.X

 

[gpoch]

There's a beginner walk through in the help files. The online version is located here.

http://msdn2.microsoft.com/en-us/library/ms171893(VS.80).aspx

 

[Signo.X]

thanks for that..
ive used this tutorial http://msdn2.microsoft.com/en-us/library/0f92s97z(VS.80).aspx

im really getting annoyed..i followed every step in the tutorial.. but i still cant update my dabase from the windows form..

Me.Validate()
Me.TableoneBindingSource.EndEdit()
Me.TableoneTableAdapter.Update(Me.DB1DataSet.tableone)

can any one please tell me why my database table is not being updated when i use the above?

~signo.X

 

[gpoch]

I just ran though the tutorial, don't know why your having problems but if it helps you can download the code I did here. Place the Nwind database at root of C:\

http://webpages.charter.net/gpoch100/WindowsApplication1.zip

 

[jmcilhinney]

How exactly do you know that it's not working? The Update method of your TableAdapter is a function and it returns an Integer that contains the number of rows affected. If that number is non-zero then it is working.

Note that by default your debug database will be overwritten each time you build your project. If you haven't changed that default behaviour and you're quitting and then running your app again any changes you made in a previous session will be lost.

 

[Signo.X]

Thanks for the replies ,

the update method wasn't working for me, i.e. when i try to modify or add a record and press the save button, i couldnt see the new added or modified records in my database.

I had my database in my project directory but not in the debug directory of the project, but when i added the DB to my debug directory the functionalities works..!
does that mean i need 2 copies of my DB, one in the project directory and one in the debug dir???


~Signo.X

 

[jmcilhinney]

If you have only one database then all the changes you make while debugging are made to that one database. You then have a job to do to clean that database up for deployment. The idea is that you have your pristine, untouched database sitting in your project folder with all the source files. When you build your project it copies the database to the output folder and you can sully it as much as you want. When you're done you've still got a nice clean database sitting in your source folder, ready to overwrite the copy in the output folder for fresh testing or final deployment.

 

[cjard]

When you added the access database to your project, a warning probably appeared saying "The connection you selected uses a local data file that is not in the current project. Would you like to copy the file to your project and modify the connection? If you copy the data file to your project, it will be copied to the project's output directory each time you run the application. Press F1 for information on controlling this behaviour"


Most people click yes without really understanding - I know I did.

What happens is, a original copy of the db goes in with your source code. Each time you start/debug/press play, the app, the original database is copied out when the project is built

So you make an exe, the db is copied out, the exe changes the copy.. the next time you start the app, the original is copied OVER the one you changed. It hence looks like your app is not updating the db - it is!


Click the MDB file in your solution explorer. In the proeprties window change the COPY policy to COPY IF NEWER. Set it back to copy always, when you deploy your app, otherwise the users are going to get a copy of your test database(probably)

 

[Signo.X]

Thanks Cjard,
Perfect!

one more problem about the form., when i try to modify or delete a record that refer to another tables and press the save button, it throws an exception saying i can't change it because it relates to another record, which is fair enough.
But when i undo the change that i had done and bring back he original value and press save, it still throws the same exception!! unless i kill the session then start/debug it again!

any help or idea why is it doing that ?

that's the code im using for the update which auto generated by the BindingNavigator :


Private Sub TableBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TableBindingNavigatorSaveItem.Click
Try
Me.Validate()
Me.PARTBindingSource.EndEdit()
Me.PARTTableAdapter.Update(Me.DB1DataSet.PART)
MsgBox("Update Success!!")
Catch ex As Exception
MsgBox("Update Failed::" & ex.Message)
End Try
End Sub

 

[jmcilhinney]

Interesting that that's what I said, although in brief, in post #5 and you insisted that there was no database in your output folder. Anway.

How exactly are you "bringing back the original value"? If you're still geting the same error then you mustn't have cleared the deleted row. There are various ways to handle this with various levels of complexity and user-friendliness. One way is to call RejectChanges on the row in error, or on the entire table if desired. That will reset the data to its original state.

 

[Signo.X]

jmcilhinney , I did read what you wrote in post #5 and i said Thanks to u as well

but maybe i needed more - exact - detail on how to solve my problem ..

The reject changes worked! Thanks!

!Signo.X !!

 

[jmcilhinney]

jmcilhinney , I did read what you wrote in post #5 and i said Thanks to u as well

but maybe i needed more - exact - detail on how to solve my problem ..

any way, when i said brining the values back i meant simply putting the original values in the row the way it was before i saved it and throw the exception..

how do i use the RejectChanges as u mentioned above? what im trying to do is modify a value in a record and press save, if all good then it will save without no problem and update the DB, if any thing wrong, then rolls the WRONG values from the record back and put the values for this record before the changes had done and continue to work normally on the form...???!!

Thanks all...
!signo.X

The individual DataRows each have a RejectChanges method and the DataTable also has a RejectChanges method. Simply call it and the contents of the object will revert to its original state, or to its state when AcceptChanges was last called.

 

[Signo.X]

more questions :

1. another problem in the form, when i try to modify some colum data directely from the Access DB it does accept the changes and updates the DB , but when i try to do the same thing in the form for the same colum, it said i can't modify the record because it relates to another table, i under stand if the colum was the PK of the table and a FK for the other table i wont be able to change it, but why i cant modify the other data ?

it also says in the exception msg that a "record" cant be modofied..in my case, im only trying to change a "colum" data and not the whole record?
any thing to do with the update method ?

------------------------------
2. i have a menu system that when the user clicks a certain menu button , it brings the form up and the binding navigation menu that associated with it..

at the moment, all what i can think of is have 2 methods , one to be called inside the form_load method to hide all the controls that i dont need when the form loads..and another method to be called when the user clicks the form button up option on the menu, to make all the form controls and the navigation menu visible ,

is that a good way of doing it ? any other suggestions ?

-----------------------------------
3. when i click on add record in the form and then leave the whole record empty .i.e. dont enter any value, in the new field, then i try to press the next item button on the binding navigator , i got a nullExceptionAllowed unhandled, i try the following inside the next iteam on click event :

[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Validate()[/SIZE]
[SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].PARTBindingSource.EndEdit()[/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] ConstraintException[/SIZE]
[SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#800000]"ERROR::"[/COLOR][/SIZE][SIZE=2] & ex.Message)[/SIZE]
 
[SIZE=2]DB1DataSet.RejectChanges()[/SIZE]
[SIZE=2]TextBox1_pcode.Text = [/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE]
 
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] hh [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] NoNullAllowedException[/SIZE]
[SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#800000]"ERROR100::"[/COLOR][/SIZE][SIZE=2] & hh.Message)[/SIZE]
[SIZE=2]DB1DataSet.RejectChanges()[/SIZE]
 
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] em [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MsgBox(em.Message)[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]

then why i run it, and try to do the same thing, it will handle a duplicate PK entry and enter an empty record, but it wont handle a null value even though i did catch the exception.. is it about where i cougth the exception ? where should i catch it ?

----------------------------------
4. i want to do the following in the form , if a record has been added successfully, i want to do some thing....

how do i check in my form that when i press the add new record button and add a new record to the form it has been added successuflly ????

too many Question
but Thanks in Advance!!

~signo.X

 

[jmcilhinney]

Whoa! Hold up, hold up. May I suggest one simple rule: one topic per thread and one thread per topic. Things get too convoluted and confusing if you start asking and answering multiple semi-and unrelated questions all together.

Also, you need to be clearer about what you're doing and what is happening. If you can change some data in Access but not via code then you're doing something wrong. OK, no issue there, but if we're to have a hope of fixing the issue then we need specific information. You need to tell us what this data is, exactly what changes you're making to it, exactly how you're trying to update the database (i.e. the actual code) and exactly what the error message is. If you're vague then we have to guess.