Saving changes made in DataGridView

[Kevin]

I've been searching forever for examples of saving data changes in a
DataGridView. There's all kinds of examples, but none really show how
to save changes. Someone please help me.

I have a Windows Forms program with a DataGridView and a BindingSource
added to a form. Here is the code I'm using to populate the grid:


Dim CRClassesTableAdapter As SqlDataAdapter
Dim CRClassesTable As New DataTable()

Dim connectionString As New SqlConnection("Initial Catalog=" &
My.Settings.Database & ";Data Source=" & My.Settings.Server &
";Integrated Security=SSPI;")

CRClassesTableAdapter = New SqlDataAdapter("Select * from CRClasses",
connectionString)

CRClassesTableAdapter.Fill(CRClassesTable)

BindingSource1.DataSource = CRClassesTable

BindingSource1.Filter = "fldCLStatus <> 'Closed' AND fldStartDate >=
'" & medStart.Text & "' AND fldStartDate <= '" & medEnd.Text & "'"

BindingSource1.Sort = "fldStartDate, fldClassName"

Grid1.DataSource = BindingSource1


Databse is in SQL Server 2000. I'm using VB2005. I would like each row
to update once that row has lost focus. How would I do it?

 

[Brian Tkatch]

I've been searching forever for examples of saving data changes in a
DataGridView. There's all kinds of examples, but none really show how
to save changes. Someone please help me.

I have a Windows Forms program with a DataGridView and a BindingSource
added to a form. Here is the code I'm using to populate the grid:


Dim CRClassesTableAdapter As SqlDataAdapter
Dim CRClassesTable As New DataTable()

Dim connectionString As New SqlConnection("Initial Catalog=" &
My.Settings.Database & ";Data Source=" & My.Settings.Server &
";Integrated Security=SSPI;")

CRClassesTableAdapter = New SqlDataAdapter("Select * from CRClasses",
connectionString)

CRClassesTableAdapter.Fill(CRClassesTable)

BindingSource1.DataSource = CRClassesTable

BindingSource1.Filter = "fldCLStatus <> 'Closed' AND fldStartDate >=
'" & medStart.Text & "' AND fldStartDate <= '" & medEnd.Text & "'"

BindingSource1.Sort = "fldStartDate, fldClassName"

Grid1.DataSource = BindingSource1


Databse is in SQL Server 2000. I'm using VB2005. I would like each row
to update once that row has lost focus. How would I do it?

Look at the DataAdaptor's UpdateCommand.

B.

 

[Kevin]

Look at the DataAdaptor's UpdateCommand.

B.

That's what I've tried doing, but I just get errors. I'm trying to do
this:

Me.Validate()
Me.BindingSource1.EndEdit()
CRClassesTableAdapter.Update(CRClassesTable)

I get the error: "Update requires a valid UpdateCommand when passed
DataRow collection with modified rows."


What I'm looking for is a CODE EXAMPLE.

 

[Cor Ligthert [MVP]]

Kevin,


As you don't use the designer than you have to build those yourself. By
instance this

http://www.vb-tips.com/dbPages.aspx?ID=3405596d-4556-4aa8-be12-d7c12bbb3726

However if your select is easy you can as well use the commandbuilder.

http://msdn.microsoft.com/library/d...mdatasqlclientsqlcommandbuilderclasstopic.asp

I hope this helps,

Cor

 

[Kevin]

I tried it your way Cor. I went to Data > Add New Data Source... and
added my CRClasses table. I then dragged it onto my form and it
created its own DataGridView and menu bar. I deleted the menu bar and
put my own "Save Changes" button on the form. I cut and pasted the
code from the original save button on the menu bar to my button--the
following three lines:

Me.Validate()
Me.CRClassesBindingSource.EndEdit()
Me.CRClassesTableAdapter1.Update(Me.CFDataSQLDataSet.CRClasses)

When I press the button, I'm getting the same error I got before:

"Update requires a valid UpdateCommand when passed DataRow collection
with modified rows."

Any suggestions?

I'm adding a Filter and a Sort to my CRClassesBindingSource. Could
that have anything to do with it?

 

[Cor Ligthert [MVP]]

Kevin,

You are sure you have done all defaults, and got no errors using that.

This seems something of missing a primary key in your select, but with the
default from that drag method you cannot change that.

Cor

 

[William LaMartin]

Here is some code I did for a friend to demonstrate populating a
datagridview from a database table with the ability to save changes. Change
the connection string to suit your situation. The command builder is what
creates the update command for you.

Create a form and add a datagridview, but do all the data connection work in
code as below. The save command is in a menustrip.


Public Class Form2
Dim MyConnection As New SqlClient.SqlConnection
Dim MyAdapter As SqlClient.SqlDataAdapter
Protected ds As New DataSet
Protected cb As SqlClient.SqlCommandBuilder

Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

MyConnection.ConnectionString = "Data
Source=.\SQLEXPRESS;AttachDBFileName='C:\temp\Test.mdf';Integrated
Security=SSPI"
MyAdapter = New SqlClient.SqlDataAdapter("Select * from
AddressTable", MyConnection)
MyAdapter.Fill(ds)
cb = New SqlClient.SqlCommandBuilder(MyAdapter) 'in case we want to
do any inserts, deletions, etc.
Me.DataGridView1.DataSource = ds.Tables(0)

End Sub

Private Sub SaveDataToolStripMenuItem_Click(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
SaveDataToolStripMenuItem.Click
MyAdapter.Update(ds)
End Sub
End Class

 

[Kevin]

Finally something that works! Thank you William.

 

[Cor Ligthert [MVP]]

Kevin,

I am sorry to have to say this.

William made a fine sample for you, but his code is almost exactly in this
link that I have showed you in this thread.

http://msdn.microsoft.com/library/d...mdatasqlclientsqlcommandbuilderclasstopic.asp

Not that Williame did copied it, Ken and me have for sure as well this in a
way on our website (not in this simple way).

But please will you look at the help we give you. You did confuse me,
because you told you were using what I had said, but that was probably about
a reply from some days ago.

Cor

 

[Kevin]

I believe your first suggestion was to add a new DataSource to my
project and then drag it to my Form, which would create a DataGridView
on my Form. That's the example I tried. When it would do what I wanted
it to do, I came back here to see other suggestions. That's when I saw
William's response, so I tried that before I even got to your other
suggestion.

Maybe you could help me with my next problem?

I'm using William's code to fill my DataGridView when the user presses
a button. I've set up the fields manually in the DataGridView, setting
the DataSource property to each field in my table.

The problem is, when the user presses the button again, the
DataGridView doesn't clear the old rows, it just adds to them. So I
tried setting the Grid's DataSource to DBNull. Not only did I wipe out
all my manual field settings, but the old rows are still there. How
can I clear the DataGridView?

 

[Cor Ligthert [MVP]]

Kevin,

The datagridview shows the dataset, as you fill it more times, it will add
only to that, so if that load code from william is in that button event,
than you have to clear the dataset before.

ds.clear

I hope this helps,

Cor

 

[Kevin]

Thanks Cor. That's why you're the MVP.

Kevin,

The datagridview shows the dataset, as you fill it more times, it will add
only to that, so if that load code from william is in that button event,
than you have to clear the dataset before.

ds.clear

I hope this helps,

Cor