Board index » Visual Studio » Saving changes made in DataGridView

Visual Studio344
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?


-

Kevin wrote:

Look at the DataAdaptor's UpdateCommand.



B.



-

On 25 Aug 2006 08:11:22 -0700, "Brian Tkatch"

<Maxwell_Smart@ThePentagon.com>wrote:





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.

-

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" rel="nofollow" target="_blank">www.vb-tips.com/dbPages.aspx=3405596d-4556-4aa8-be12-d7c12bbb3726



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



http://msdn.microsoft.com/library/default.asp?url" rel="nofollow" target="_blank">msdn.microsoft.com/library/default.asp=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.asp



I hope this helps,



Cor





"Kevin" <kevinp@cfl.rr.com>schreef in bericht



-

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?









On Fri, 25 Aug 2006 18:03:46 +0200, "Cor Ligthert [MVP]"

<notmyfirstname@planet.nl>wrote:

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



"Kevin" <kevinp@cfl.rr.com>schreef in bericht



-

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" <kevinp@cfl.rr.com>wrote in message



-

Finally something that works! Thank you William.





On Sun, 27 Aug 2006 12:46:40 -0400, "William LaMartin"

<lamartin@tampabay.rr.com>wrote:

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/default.asp?url" rel="nofollow" target="_blank">msdn.microsoft.com/library/default.asp=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandbuilderclasstopic.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" <Kevinp@nospam.cfl.rr.com>schreef in bericht



-

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?





On Mon, 28 Aug 2006 06:34:48 +0200, "Cor Ligthert [MVP]"

<notmyfirstname@planet.nl>wrote:

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" <kevinp@cfl.rr.com>schreef in bericht



-

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



On Mon, 28 Aug 2006 18:51:25 +0200, "Cor Ligthert [MVP]"

<notmyfirstname@planet.nl>wrote: