Question Two probs with datagridview

[Super_Grover]

Hi all,
I'm struggling with two problems involving datagridview and an access databse. I'm working with VS2008 (VB2008) which seems to differ quite a bit from 2005 on this subject. All I could find on the internet involved mainly VB2005 and didn't work for me.

Problem 1:
Saving new row to db. I have the following code. Row is added in the DGV (and dataset I guess), but is not written for real to the db. Even if I close the appl and reopen the row is still there, but after a couple of minutes it appears not to be. Opening the db directly in Access show that the new data is not saved. Any ideas guys?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Add.Click
        Dim newToetsingRow As DataRow = SampleRegDataSet.Tables("tbl_toetsingen").NewRow()
        Dim li_aantal As Integer = Convert.ToInt32(mtxt_aantal.Text)
        Dim li_akkoord As Integer = Convert.ToInt32(mtxt_akkoord.Text)
        Dim li_betaald As Integer = Convert.ToInt32(mtxt_betaald.Text)
        Dim ls_opm As String = mtxt_opm.Text
        Dim ld_datum As Date = dtp_datum.Value.Date
        Dim li_totaalbedrag As Integer = Convert.ToInt32(mtxt_aantal.Text) * 25
        newToetsingRow("User_ID") = gi_iser
        newToetsingRow("Datum") = ld_datum
        newToetsingRow("Aantal_toetsingen") = li_aantal
        newToetsingRow("Aantal_akkoord") = li_akkoord
        newToetsingRow("Aantal_betaald") = li_betaald
        newToetsingRow("Totaal_bedrag") = li_totaalbedrag
        newToetsingRow("Opmerkingen") = ls_opm

        SampleRegDataSet.Tables("tbl_toetsingen").Rows.Add(newToetsingRow)
        Me.Tbl_toetsingenTableAdapter.Update(Me.SampleRegDataSet)
        MsgBox("New row added!")
End Sub

Problem two:
I would like to use different queries on this DGV from the same db. So, say a couple of buttons which represent another selection of the db. I tried defining another dataAdapter which holds another query, but can't figure out how to display other data in the DGV than the bound data in the first dataset.
Any help is appreciated!

 

[jmcilhinney]

These issues have nothing to do with VB 2005 or 2008. The code would be exactly the same in either.

With regards to the first issue, I'm guessing that the code is working correctly. The first thing to do is to test the value returned by Update. Either it's zero and there were no changes in the DataTable or it's not zero and the changes were saved.

With regards to the second question, to display different data you need to first set the DataSource to Nothing, then clear the Columns collection, then set the DataSource again. New columns will be generated for the new data source.

 

[Super_Grover]

Thank you, jmcilhinney for your reply.

For issue 1 I added:

Me.SampleRegDataSet.AcceptChanges()

This seems to do the trick. Saving to the database seems to be working now.

For issue 2 I tried different things. I added:

Tbl_toetsingenDataGridView.DataSource = Nothing
        Tbl_toetsingenDataGridView.Columns.Clear()
        Tbl_toetsingenDataGridView.DataSource = SampleTableAdapter_2ndQry

The DGV empties but nothing new is loaded.
Can someone point me in the right direction?

Thanks in advance!

 

[jmcilhinney]

Your solution to the first issue is wrong. If you're calling AcceptChanges before calling Update then you are actually not saving anything because there are no changes to save after calling AcceptChanges. If you're calling AcceptChanges after calling Update then you're wasting you're time because, unless you've specifically configured it otherwise, Update will implicitly call AcceptChanges anyway.

For the second problem, you don't a TableAdapter to the grid. Just as you would have done the first time, you use the TableAdapter to populate a Datatable in your DataSet and then you bind the DataTable to the grid.

 

[Super_Grover]

Your solution to the first issue is wrong. If you're calling AcceptChanges before calling Update then you are actually not saving anything because there are no changes to save after calling AcceptChanges. If you're calling AcceptChanges after calling Update then you're wasting you're time because, unless you've specifically configured it otherwise, Update will implicitly call AcceptChanges anyway.

For the second problem, you don't a TableAdapter to the grid. Just as you would have done the first time, you use the TableAdapter to populate a Datatable in your DataSet and then you bind the DataTable to the grid.

Yes, you're right about the first issue. It's not working.
I tested the update return value and it returns 'true'. So I guess that's just fine.
The DGV gets updated and the newly added row is showed. Even after closing VS and angain debugging the project the new rows are displayed. But strangely, after a while the new rows are dropped. It look like they're stored somewhere temporarely, but not in de access db.

I'll work further on the second issue. The first time I bound the data to the grid in the designer. That's what confuses me. I'll take the sequence you mentioned and try to figure it out.
Many thanks so far.

 

[jmcilhinney]

Yes, you're right about the first issue. It's not working.
I tested the update return value and it returns 'true'. So I guess that's just fine.
The DGV gets updated and the newly added row is showed. Even after closing VS and angain debugging the project the new rows are displayed. But strangely, after a while the new rows are dropped. It look like they're stored somewhere temporarely, but not in de access db.

I'm in the process of writing a blog post on this "issue" but it's not done yet, so I'll just give the condensed version. You have two copies of your database: one in the source folder and one in the output folder. The one in the source folder stays clean, so you always have that to fall back on. When you run the project in the debugger, it's the copy in the output folder you work against. When you add data it gets inserted into that copy. If you exit and run the project again, the same copy is used so you see the data again.

Now, if you exit and then edit any source file at all (VB code file, config file, source database, etc), the next time you run the project it gets rebuilt. By default, every time you build your project, the source database is copied to the output folder, overwriting the existing database and your test data. If that's not what you want then you need to select the database in the Solution Explorer and then change its Copy To Output Directory property to Copy If Newer. That way, a new copy of the database will only be created if you actually change the source database, either by changing the schema or the data.

 

[Super_Grover]

I'm in the process of writing a blog post on this "issue" but it's not done yet, so I'll just give the condensed version. You have two copies of your database: one in the source folder and one in the output folder. The one in the source folder stays clean, so you always have that to fall back on. When you run the project in the debugger, it's the copy in the output folder you work against. When you add data it gets inserted into that copy. If you exit and run the project again, the same copy is used so you see the data again.

Now, if you exit and then edit any source file at all (VB code file, config file, source database, etc), the next time you run the project it gets rebuilt. By default, every time you build your project, the source database is copied to the output folder, overwriting the existing database and your test data. If that's not what you want then you need to select the database in the Solution Explorer and then change its Copy To Output Directory property to Copy If Newer. That way, a new copy of the database will only be created if you actually change the source database, either by changing the schema or the data.

Thanks so much, jmcilhinney! Issue 1 solved! I wasn't aware of that. So all this time I got the procedure right...
Tonight I'll continue with issue 2.

 

[Super_Grover]

Tonight I searched and tried and found a good step-by-step databinding explanation on the web. So I got it working.
I'm sure it's pretty simple, but I got so confused that I couldn't see the forest for the trees. I work with non-dotnet language so I really need to get used to this. But I'll get there ;-)
Anyway I used this code:

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\SampleReg.mdb;"
        Dim oAccessConn As New OleDbConnection(sConnectionString)
        oAccessConn.Open()
        Dim daCZ As New OleDbDataAdapter("SELECT EPaysID,Clntnr,Clntnaam,Wrksrt,Datum_ingave,Verw_bedrag,BB,Datum_bet,Ontv_bedrag FROM(EPays) WHERE (BB = 'N')", oAccessConn)
        Dim dsBB As New DataSet("EPays")
        daBB.FillSchema(dsBB, SchemaType.Source, "Epays")
        daBB.Fill(dsBB, "EPays")
        Dim tblEPays As DataTable
        tblEPays = dsBB.Tables("EPays")
        DataGridViewBB.DataSource = tblEPays

Thanks for al the help!

 

[Super_Grover]

Tonight I searched and tried and found a good step-by-step databinding explanation on the web. So I got it working.
I'm sure it's pretty simple, but I got so confused that I couldn't see the forest for the trees. I work with non-dotnet language so I really need to get used to this. But I'll get there ;-)
Anyway I used this code:

Dim sConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\temp\SampleReg.mdb;"
        Dim oAccessConn As New OleDbConnection(sConnectionString)
        oAccessConn.Open()
        Dim daCZ As New OleDbDataAdapter("SELECT EPaysID,Clntnr,Clntnaam,Wrksrt,Datum_ingave,Verw_bedrag,BB,Datum_bet,Ontv_bedrag FROM(EPays) WHERE (BB = 'N')", oAccessConn)
        Dim dsBB As New DataSet("EPays")
        daBB.FillSchema(dsBB, SchemaType.Source, "Epays")
        daBB.Fill(dsBB, "EPays")
        Dim tblEPays As DataTable
        tblEPays = dsBB.Tables("EPays")
        DataGridViewBB.DataSource = tblEPays

Thanks for al the help!

So, I got one more question, guys.
Got all the above working, but now I wanna add (update) one or two value to an existing database 'row'. So the database(access) holds 9 fields (represented as 9 colomns in de datagridview), but initially only 7 are filled. Later on I wanna add the other two values --> so update database. This will be done with two textboxes and a 'update'-button.

So, teh sql-command (well, command string) would be like (only one value):

Dim ls_updStr As String
ls_updStr = "UPDATE EPays SET Ontv_bedrag='" & li_ontBed & "' WHERE EPaysID='" & li_EPID & "'"

I can point to the selected row and colomn of the datagrid (=li_EPID)
What's the best way for me to accomplish this?
Earlier in this thread there's my 'AddRow' code:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Add.Click
        Dim newToetsingRow As DataRow = SampleRegDataSet.Tables("tbl_toetsingen").NewRow()
        Dim li_aantal As Integer = Convert.ToInt32(mtxt_aantal.Text)
        Dim li_akkoord As Integer = Convert.ToInt32(mtxt_akkoord.Text)
        Dim li_betaald As Integer = Convert.ToInt32(mtxt_betaald.Text)
        Dim ls_opm As String = mtxt_opm.Text
        Dim ld_datum As Date = dtp_datum.Value.Date
        Dim li_totaalbedrag As Integer = Convert.ToInt32(mtxt_aantal.Text) * 25
        newToetsingRow("User_ID") = gi_iser
        newToetsingRow("Datum") = ld_datum
        newToetsingRow("Aantal_toetsingen") = li_aantal
        newToetsingRow("Aantal_akkoord") = li_akkoord
        newToetsingRow("Aantal_betaald") = li_betaald
        newToetsingRow("Totaal_bedrag") = li_totaalbedrag
        newToetsingRow("Opmerkingen") = ls_opm

        SampleRegDataSet.Tables("tbl_toetsingen").Rows.Add(newToetsingRow)
        Me.Tbl_toetsingenTableAdapter.Update(Me.SampleRegDataSet)
        MsgBox("New row added!")
End Sub

Is there something similar like the AddRow command, but then like UpdateRow?

 

[Super_Grover]

I thougth about this and I guess I was thinking in the wrong direction.
I figured I just put the values in the datagrid positions and then safe (update) the grid to the database (just like earlier by inserting a new row)
Am I thinking right?

So I wanna add twoo values to a existing row in the datagridview. I used this code and it adds the values but does not save it to the db. Next time I check the values are gone.

Dim li_ontBed As Integer
        Dim li_tblIndex As Integer
        Dim ld_Bet_dat As Date = Datum_betDateTimePicker.Value
        li_ontBed = Ontv_bedragTextBox.Text
        li_tblIndex = EPaysDataGridView.CurrentRow.Index
        EPaysDataGridView.Item(8, li_tblIndex).Value = li_ontBed
        EPaysDataGridView.Item(7, li_tblIndex).Value = ld_Bet_dat
        Me.EPaysDataSet.AcceptChanges()
        Me.EPaysTableAdapter.Update(Me.EPaysDataSet)

What should I do to really save the changes to the access database?

 

[Super_Grover]

I tried some other things, but no success.
One thing I tried is this:

li_ontBed = Ontv_bedragTextBox.Text
li_tblIndex = DataGridView1.CurrentRow.Index
DataGridView1.Item(7, li_tblIndex).Value = li_ontBed
DataGridView1.Item(6, li_tblIndex).Value = ld_Bet_dat
DataGridView1.EndEdit()
PsychRegDataSet.AcceptChanges()
Me.EPaysTableAdapter.Update(Me.PsychRegDataSet.EPays)

De value appear in the DGV but when I close and reopen the app the changes are gone.
What am I doing wrong here?
Thanks in advance!

 

[jmcilhinney]

First up, don;t work with the grid in code. If you want to edit data then work with the data source, i.e. the DataTable or, better yet, the BindingSource.

As for your immediate issue, get rid of the call to AcceptChanges. If you have already accepted the changes then there's no changes to save when you call Update. It's a rare thing that you should ever need to call AcceptChanges explicitly because Update does it for you implicitly.

 

[Super_Grover]

First up, don;t work with the grid in code. If you want to edit data then work with the data source, i.e. the DataTable or, better yet, the BindingSource.

As for your immediate issue, get rid of the call to AcceptChanges. If you have already accepted the changes then there's no changes to save when you call Update. It's a rare thing that you should ever need to call AcceptChanges explicitly because Update does it for you implicitly.

Thank you for your reply,
But I don't really get it. What do you mean not to work with the grid in code? You mean these lines?:

li_ontBed = Ontv_bedragTextBox.Text
li_tblIndex = DataGridView1.CurrentRow.Index
DataGridView1.Item(7, li_tblIndex).Value = li_ontBed
DataGridView1.Item(6, li_tblIndex).Value = ld_Bet_dat

I point to two specific cells and add a value there. How should I do it better? Got any code for me?

I removed the AcceptChanges() line, but no luck. Data is not saved into the database. I guess the dataset isn't really changed at all. And therefor the db isn't either?
Can you help me out with some code please?
So first, new rows are added to the db except for two values (which works well). Then at a later stage user add the other two values to the existing db record. Those changes are not saved for some reason. That's all really. I guess I take it all the wrong way?

Can you help me onto the right course?
Thanks!

 

[jmcilhinney]

What value does Update return?

If you haven't already, bind your DataTable to a BindingSource and bind that to the grid. You then access the data via the BindingSource. The Current property returns the current row as a DataRowView, which you can delete or edit. The AddNew method adds a new row. The Position property gives you the index of the current row. The Item property gives you any row by index. You can loop through all the rows using a For each loop. Etc, etc.

 

[Super_Grover]

What value does Update return?

If you haven't already, bind your DataTable to a BindingSource and bind that to the grid. You then access the data via the BindingSource. The Current property returns the current row as a DataRowView, which you can delete or edit. The AddNew method adds a new row. The Position property gives you the index of the current row. The Item property gives you any row by index. You can loop through all the rows using a For each loop. Etc, etc.

Thanks again, jmcilhinney.

As expected, the update return a false... So no changes are really made.

Right now I'm trying to figure out the second part of your post. Searching the web and many books for the right syntax to:
-bind dataTable to BindingSource
-bind BindingSource to grid
-access data via BindingSource

There's really so much variations and term that it's very confusing. But I'm trying really hard.