0

This is a bit frustrating! I have a search section in which you can scroll through the rows using buttons and the textboxes updates to the row details but when i add new data into the database an go back to my search menu the newly added row is not showing up? How do i reset the binding source to accomplish this, or at least have the 2 parts work from the same data?

My code: Call Write2tableAddClient() `here i am hoping to add this refresh binding code

My current binding in which is triggered on form load:

Private Sub click()
    sql = "Select * from tbl"
    Using dbcon As New OleDbConnection(ACEConnStr)
        Using cmd As New OleDbCommand(sql, dbcon)

            dbcon.Open()

            dtSample = New DataTable
            dtSample.Load(cmd.ExecuteReader)

        End Using
    End Using

    ' initialize BS from DT
    bsSample = New BindingSource(dtSample, Nothing)
    TxtCI.DataBindings.Add("Text", bsSample, "ID")

End Sub

AddCustomer:

Private Sub AClient()
    If con.State = ConnectionState.Closed Then con.Open()
    cmd = New OleDbCommand
    cmd.Connection = con
    cmd.CommandText = "INSERT INTO tblcustomer(Title)VALUES(?);"

    cmd.Parameters.Add("?", OleDbType.VarChar).Value = TxtName.Text
    cmd.ExecuteNonQuery()
    con.Close()
End Sub

Headings:

Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb")
Dim cmd As OleDbCommand
Dim sql As String
Dim ds As New DataSet
Dim da As New OleDbDataAdapter
Dim RecCnt As Integer
Dim inc As Integer
Private ACEConnStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=.\Database.accdb"
Private bsSample As BindingSource
Private dtSample As DataTable
  • Its the datasource, not the bindings. The DataTable doesnt maintain a live "feed" to the DB. Which I why I urged you to use the datatable everywhere in your app. If SEARCH and ADD used the same DT as a datasource, added data would automagically appear in anything using it. You need to rebuild the datatable to load new data added by other things or users. – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 01:20
  • Oh damn, this is when in life.. You go jump off a building haha spending hours on this program, fix one thing and another error occurs :( –  Mar 22 '16 at 01:25
  • I updated the question there, in which part do i change to make it all in the same datatable? P.s: If i new how to use the same table i really would of haha! –  Mar 22 '16 at 01:31
  • are those the same controls for both the search and the add? they have the same names, but could be on diff forms (it is a semi major refactoring and it still wont show the new ID since the db adds that) – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 01:36
  • All this is on the same form, i just have a menu bar with drop downs called customer (add delete search) The code is all on the same form:L –  Mar 22 '16 at 01:39
  • this shows how to setup the DA to be able to add, insert, delete the DB based on changes to the DT: http://stackoverflow.com/a/33702351 – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 02:12
  • I read that and copied most that code and changed it to suit mine and all i get in errors and its a pain.. Is there no other way of solving this issue? This sucks.. –  Mar 22 '16 at 02:35
  • Where you put the code matters as well as how you changed it. you could rebuild the datatable after you add a row. Split that terrible `nclick()` method into 2 methods - one to build the DT and one to set the control bindings. In form load, create the binding source, then call the first to set up the DS, add `bsSample.DataSource = dtSample`, then also from form load call the second part to set the bindings. REMOVE `bsSample = New BindingSource(...`. Now when you add a new row, call the first method to build the DT and SET the BS again – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 02:47
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/107034/discussion-between-irishwill2008-and-plutonix). –  Mar 22 '16 at 12:44
  • @Plutonix i did split them and when i added that bssample.etc it gives an error on that line? –  Mar 22 '16 at 23:01
  • You should know by now that programming is an exercise in precision. "it gives an error" tells me nothing. As I said, how and where you place the code matters. It matters a great deal. Use pastebin for the form load and the new procedures and paste the link here – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 23:47
  • My error for bssample.data etc is: Object reference not set to an instance of an object. Pastebin: http://pastebin.com/Pfru8nWP Thanks and sorry for being annoying. –  Mar 23 '16 at 00:12
  • @Plutonix i was wondering, is it not possible or better to clear the binding after i insert new row and then rebind with the new information so then the new row is viewable in my search section? I was looking around and people are using bindingsource.clear() in my case, bsSample.clear() but that doesnt work for me. Gives me a error saying: Cannot clear this list. –  Mar 23 '16 at 00:26
  • 1
    the comment above was misleading. You need `bsSample = New BindingSource(...` in form load after you call `nclick()` and before you use it in `nclick2()` (horrible names!). You dont want it in nclick so you can call it without destroying the bindings. Now, to reload data after an add, just call nclick again – Ňɏssa Pøngjǣrdenlarp Mar 23 '16 at 00:39
  • Yeah once i fix this and works i will be renaming them:) Ok, now i dont get an error but when i add the guy and head to search the new guy still doesnt show up. Here is the updated pastebin: http://pastebin.com/9ke9ssQj –  Mar 23 '16 at 00:53
  • 1
    you rebuilt the table so you need to *SET the BS again*: `bsSample.DataSource = dtSample`. add it right after the call just like in form load – Ňɏssa Pøngjǣrdenlarp Mar 23 '16 at 00:58
  • Wow, i totally love you right now!! Whoop!! Back on track:D Thank you so so much i really do appreciate it! Dont know how you didnt just give up on me, i know i would :/ Honestly, you should be a teacher! And totally apply at my college, its a great college and all but dayum we get taught rubbish when comes to VB! –  Mar 23 '16 at 01:04
  • The next step is to get rid of `Write2tableAddClient` and use da.Update for that too. Use the info from jmcilhinney's links. Also an ErrorProvider would eliminate all those horrible MessageBoxes: *Every time you use MessageBox, the Angels weep.* Your code could severely punish the user with oodles of them – Ňɏssa Pøngjǣrdenlarp Mar 23 '16 at 01:07
  • Yeah i will look into changing them tomorrow, pretty wrecked after staying up most nights getting this done! Also on my form with the search section selected it has a delete button so if you are viewing customerID's record number 5 and press the delete button it deletes the record from database but using the same idea with the call nclick and Set bs again after record is deleted it doesnt refresh and display with it gone, any idea? Screenshot: http://imgur.com/vE4cQ5c –  Mar 23 '16 at 01:22
  • Update @Plutonix : Its due to the fact of (Where i put the code) Some reason in my delete section it wouldnt work but when i did a true false statement it worked? Thats that solved! Last night i was tired so brain turned off sorry about that. I am now working on doing what what you said with the Write2tableAddClient getting rid of it. Can i ask, why would i get rid of this when its working now and why would i use the way jmcilhinneys said. I know its less code to an extent but mines working perfect now? Thanks!:) –  Mar 23 '16 at 14:09
  • 1
    `It seems to work` is not a very high bar. As is, you have 2 different db approaches in the app and the right hand knows not what the left doeth. As a result, there are some expensive/wasteful approaches. You should always develop "under load" - with 5-10k rows in the DB; if you did, you be much less pleased with the result. If the point is to learn how to do it right, not just get a grade then the course is clear, starting with a day or two reading MSDN pages first. – Ňɏssa Pøngjǣrdenlarp Mar 23 '16 at 14:37

2 Answers2

1

This is a common mistake. You're doing things the wrong way around. If you have a DataTable containing data, you don't add a record to the database and then refresh the DataTable. You add a record to the DataTable first and then you save that change to the database.

There is no need to refresh the BindingSource. It will expose whatever's in the DataTable. Add a new row to the DataTable and you'll see that row in whatever's bound to the BindingSource. Once you've added the row to the DataTable, use the same data adapter or table adapter that you used to retrieve the data in the first place to save the changes to the database, i.e. call Update on the same adapter that you called Fill on.

jmcilhinney
  • 50,448
  • 5
  • 26
  • 46
  • So i will use: `da.Update(dtSample)`? I aint the best with VB just yet, still getting use to these so sorry in advance if what i just said was a total no no and a facepalm moment. –  Mar 22 '16 at 01:59
  • @irishwill2008 yes, but *your* dataadapter doesnt know how to update or delete – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 02:01
  • So perhaps my ds would as that is a dataset? –  Mar 22 '16 at 02:02
  • In spite of the clever name, a dataset doesnt hold any data directly, it just contains datatables which do. – Ňɏssa Pøngjǣrdenlarp Mar 22 '16 at 02:04
  • Ok, doing some thinking here and this seems good so will: `sql = "Select * from tblcustomer" da = New OleDbDataAdapter(sql, con) da.Update(ds, "tblcustomer")` do the trick? –  Mar 22 '16 at 02:09
  • 1
    Here are some ADO.NET examples of mine. Examples 4 and 5 are relevant to you here. You would simply add a row to the `DataTable` some time before calling `SaveData`. http://www.vbforums.com/showthread.php?469872-Retrieving-and-Saving-Data-in-Databases – jmcilhinney Mar 22 '16 at 02:57
  • Im using the code from 4 and 5 but how do i call the insert? im using: Me.adapter.insert(Me.table) but says its not apart of oledb –  Mar 22 '16 at 13:20
  • Why are you trying to complicate this? Just do what I told you to do, i.e. add the row to the `DataTable` and then call `Update` on the adapter. That saves ALL changes, be they inserts, updates, deletes or any combination thereof. Also, you don't use examples 4 and 5. You either use 4, i.e. you write your own action commands, or you use 5, i.e. you use a command builder. – jmcilhinney Mar 22 '16 at 13:24
  • Ok, in the InitialiseDataAdapter() sub you have i have for update: `Dim update As New OleDbCommand("UPDATE tblcustomer SET Title = ?, Surname = ?, Initials = ?, TownArea = ?, County = ?, Home = ?, Telephone = ?, WorkTelephone = ?, SalesCustomer = ?, FramingCustomer = ?, RestorationCustomer = ?, TapestryCustomer = ?, ExhibitionCustomer = ?, BusinessCustomer = ?, BusinessName = ?", Me.con)` would that be correct? –  Mar 22 '16 at 13:28
  • Its hard for me when i honestly have no clue what i am doing? You guys might be there wow i done this a billion times take me 5 mins to correct my mistake but for me its taking days :/ –  Mar 22 '16 at 13:31
0

If you are like me and have some sort of mess up with data tables and such you can always go the easy way out which is reload the form but show the same things where you last left off!

For example, when i added a customer to the database and searched using bindings the information i added would not display so in my case i added the following code to when i successfully added a client: 

Dim frm = New main ' duplicate your form
frm.Show() ' show your new duplicated form
frm.AddCustomer() 'load back the menu you just left
Me.Close() ' close the old form as your new form will now load!

This for me is a simple but yet effective fix! Might not be a pretty one but sure does the job :) Within my addcustomer method i have an if statement so once customer is added it will present you with a messagebox saying such and such but then says "Reloading data, please wait" and so then people wont go.. Why did this just reload? It works for me anyway, i am not the best coder in the world and still am learning as i go but this honestly is the best i came up with haha. Hope you guys can relate to it! Thanks:)