DGV Updates every row with the same data????

[blakemckenna]

I have a DataGridView that updates each row with the data of the last row. What cause this?

Thanks,

[jmcilhinney]

I have a DataGridView that updates each row with the data of the last row. What cause this?

Thanks,

Your code causes it. As you haven't provided the code there's no way to be more specific.

[blakemckenna]

Sorry JMC, here it is as an attachment!

[jmcilhinney]

This is how you save the changes from a DataTable to a database. Note the use of parameters and note that there are no literal values specified. The parameters specify which column of the DataTable the data is to be drawn from. Then when you call Update it can save every row by using the values contained in that row:

Dim con As New SqlConnection("connection string here")
Dim adp As New SqlDataAdapter("SELECT ID, FirstName, LastName FROM Customer", con)
Dim del As New SqlCommand("DELETE FROM Customer WHERE ID = @ID", con)
Dim ins As New SqlCommand("INSERT INTO Customer (FirstName, LastName) VALUES (@FirstName, @LastName)", con)
Dim upd As New SqlCommand("UPDATE Customer SET FirstName = @FirstName AND LastName = @LastName WHERE ID = @ID", con)
 
del.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
ins.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName")
ins.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName")
 
upd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50, "FirstName")
upd.Parameters.Add("@LastName", SqlDbType.VarChar, 50, "LastName")
upd.Parameters.Add("@ID", SqlDbType.Int, 4, "ID")
 
adp.DeleteCommand = del
adp.InsertCommand = ins
adp.UpdateCommand = upd
 
Dim tbl As New DataTable
 
adp.Fill(tbl)
 
'Make changes to data here.
 
adp.Update(tbl)

Note that everything before the declaration of the DataTable can, and in my opinion should, be done in the designer.

Note also that the grid is nowhere to be seen. Binding to the grid and making changes through the UI is all incorporated into that one comment and is irrelevant to the process of retrieving and saving the data.

[blakemckenna]

By using Parameters, I'm assuming your using stored procedures...correct? And by the way JMC...that's a great example!

[blakemckenna]

I tried using your code and it didn't update a row...so I'm thinking the parameters are for SP's only.

[jmcilhinney]

There is no reference to stored procedures in my code. It's got four literal SQL statements in it. Anyway, it doesn't matter whether you use stored procedures or not, parameters are parameters. If I created stored procedures containing those SQL statements my parameters would still be EXACTLY the same. My code is an example only. If you have a table named "Customer" that has ID, FirstName and LastName columns of type int (identity), varchar(50) and varchar(50) respectively then it will work exactly as it's supposed to. You can delete, add and edit rows where I've put the comment and all the changes will be saved afterwards.

If you want to test that code specifically then you're going to have to create a table with that schema. If you want to test it in your actual situation then you need to adapt the principles to that situation. The principles are:

1. Create a DataAdapter with a SelectCommand.
2. Create a DeleteCommand, InsertCommand and UpdateCommand as needed. These can use literal SQL statements or stored procedures. IT DOES NOT MATTER WHICH.
3. Add parameters to your commands. Do not set the Value of ANY parameter. Add them with a name, type, size and source column.
4. Add the commands to the adapter.
5. Populate a DataTable by calling the adapter's Fill method.
6. Make changes to the data in the DataTable as required. It doesn't make ANY difference whatsoever how this achieved.
7. Save the changes in the DataTable by calling the adapter's Update method.