How do I create new app with data access?

[Eric]

I've written a lot of programs and a lot of VB but I'm new to VB.NET so it
may be something obvious I'm missing.
I created a new project in the .NET Express 2008 IDE.
I created a new data with definition (.mdf) with one table.
I created a screen program with a grid and a button.
I put some code into the program which is supposed to load the data table
into the grid on initial startup.
I put some code into the button push event to add a new record to the table
and update using the tableadapter.
When I run the program the grid is blank. When I push the button it adds a
row to the grid. I assume it's updating the table. If I push the button
again it says cannot add duplicate row. If I exit the program and run it
again, the grid comes up blank and pushing the button adds a row. How do I
get it to write to the database, to save the data when I exit so the row
appears the next time I run it?

 

[Cor Ligthert[MVP]]

Eric,

After VB6 with Ado, we have the SqlDataAdapter VB7, the TableAdapter VB8,
Linq and EF VB9.

So you have a lot of choices, however, as I understand you well you have
taken the TableAdapter from FrameWork 2.0 and created that using drag and
drop.

To get the DataGridView filled the best you can do if you have used the fill

Dim bs as new BindingSource
bs.DataSource = TheTable
TheDataGridView.DataSource = bs

(The latter looks a little bit stupid but the bindingsource repairs some
issues from past)

The the Update, Insert and Delete commands should be generated when you
created the adapter as well and the only thing you have to do on the place
you want to update your table, by instance using a button or the menu.

TheTableAdapter.Update(TheTable)

Success

Cor

 

[Eric]

I am using a TableAdapter. It is populating a DataGridView control using a
Fill method which was added from the IDE wizard which should be executing on
startup, except it doesn't populate anything on startup. The problem is
it's not saving data. When I execute code from a push button event to add
one dummy record to the table, it automatically displays in the grid, and
executing the same code again gives me a duplicate error on my unique key
field, but when I close and restart the app the grid is blank again. Is it
not writing to the database or is it somehow clearing the table on restart?
Would missing something on the binding cause it not to save? I can paste in
the entire program (about a dozen lines of code) if it helps. As far as I
can tell, the add data table wizard should have created the
update/insert/delete commands.

 

[Eric]

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TestDataSet.tableone'
table. You can move, or remove it, as needed.
Me.TableoneTableAdapter.Fill(Me.TestDataSet.tableone)
DataGridView1.AutoGenerateColumns = 1
DataGridView1.DataSource = Me.TestDataSet.tableone
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.TestDataSet.tableone.AddtableoneRow("1", "Test")
Me.TestDataSet.tableone.AcceptChanges()
Me.TableoneBindingSource.EndEdit()
Dim newChildRecords As TestDataSet.tableoneDataTable = _
CType(TestDataSet.tableone.GetChanges(Data.DataRowState.Added),
TestDataSet.tableoneDataTable)
Me.TableoneTableAdapter.Update(newChildRecords)
End Sub

 

[Cor Ligthert[MVP]]

Eric,

The answer is the number 1 answer in Net forums and newsgroups.

Why do you use the acceptchanges before the update.

Acceptchanges means accept all the done changes as updated in the dataset.
Therefore any update behind that will do nothing.

Success

Cor

 

[Eric]

I thought I needed to accept the add in order to make the getchanges work
which seemed to be necessary to perform the update.
Is there any missing or unnecessary code in this program, or do I just need
to move the acceptchanges statement after the update statement?

 

[Cor Ligthert[MVP]]

I don't know it your code is complete.

But because of the fact that you updating using a copy of the datarows,
you've to do the acceptchanges of the dataset after the update.

If you use the dataset to update then the acceptchanges is done inside the
data adapter

Be aware that the adapter is only updating changed, added and deleted rows,
but I see that you want to update alone added rows.

Cor

 

[Eric]

I created a database (.mdf file) from within the .NET Express IDE and told
it to link it to my VB.NET program and it added the .Fill statement, so I'm
assuming I have to update using a copy of the datarows, so I'm a little
confused as to whether I need to execute methods on the dataset or the
tableadapter or some combination as I did here.

I told it to only update for added rows because my updates only include one
add statement for this test. I searched msdn for sample code on how to
update and it used that method for updating only added rows, within a try
block which had another update for changed and one for deleted. If update
works generic for all types of updates, I'd probably want to do that, since
I'm writing a local single user app which should be able to write changes
one at a time to the database.

Thanks

 

[Cor Ligthert[MVP]]

Yes and?

this should have been in my idea enough after adding the row

Me.TableoneTableAdapter.Update(TestDataSet.tableone)

Cor

 

[Eric]

So, do I need any references to the dataset or the bindingsource, or just do
this?

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TestDataSet.tableone' table.
You can move, or remove it, as needed.
Me.TableoneTableAdapter.Fill(Me.TestDataSet.tableone)
DataGridView1.AutoGenerateColumns = 1
DataGridView1.DataSource = Me.TestDataSet.tableone
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.TestDataSet.tableone.AddtableoneRow("1", "Test")
Me.TableoneTableAdapter.Update(TestDataSet.tableone)
Me.TestDataSet.tableone.AcceptChanges()
End Sub

 

[Cor Ligthert[MVP]]

Hi Eric,

That acceptchanges is not not needed, the Adapter does that intrinsic
before(or after) closing the connection.

Cor