Deleting a row in a datagrid

[Jay1b]

Could someone give me a few suggestions please?

 

Below is the code i am using to fill a datagrid.

dim dt as new datatable
'Filled datatable..........

datagrid1.datasource = dt

 

If the user has deleted a row i want to delete it from the underlying database table.

Dim dr as DataRow
For Each dr in dt.rows
 IF dr.rowstate = datarowstate.deleted THEN
   msgbox(dr.item(0))
   'rest of code
 ENDIF
Next dr

 

Unfortunately, instead of displaying the msgbox, i get the following error message returned to me via try,catch

 

'Deleted row information cannot be accessed through the row'

 

So could someone please tell me how i can access this information? As i will need it to build the SQL query that deletes it from the database.

 

Thanks.

[Machaira]

You don't need to know about the row state of the rows in your datatable. Let .NET handle it. Use a DataAdapter:

 

       Dim da As New SqlClient.SqlDataAdapter("SELECT * FROM Users", conn)
       Dim dt As New DataTable

       Dim cmd As New SqlClient.SqlCommandBuilder(da)

       da.InsertCommand = cmd.GetInsertCommand
       da.DeleteCommand = cmd.GetDeleteCommand
       da.UpdateCommand = cmd.GetUpdateCommand

       da.Fill(dt)

       'do stuff to datatable

       da.Update(dt)

[Jay1b]

I have already looked at that, and tried that - it works great if i want to apply the same SQL statement to each datagrid row, for things like wages = wages + £1000 its fantastic. Unfortunately it doesnt allow me to tailor the SQL statement for each individual row. Unless i am doing it wrong?

 

So if i change just two rows, and i want to ensure that the SQL updates those two rows in the table, how could i possible do that with one SQL statement? For example (Update wages = £10000 where UniqueID = 38949) and then again a different wage for someone with a UniqueID of 78992? I cant see how it can be done.

 

Also IF i did manage to use that, how could i build the DeleteCommand without having the rows UniqueID to delete? The DeleteCommand would have to have the UniqueID in it to ensure the right row is deleted, how could you possibly do this with two or more rows? It would have too be in a loop, however .UpDate command would run through the first batch of SQL statements on every row and mark the table as AcceptChanges AND then start over, but the changes would of already been marked as Accepted so it wont work.

 

Or am i just doing this wrong?

 

Thanks.

[techmanbd]

If you have a dataset, and you fill a datagrid with that dataset, then the 2 are linked you can say. So when you delete a row then when you do whatever code like a button to accept the change you can do this

 

dataset1.acceptchanges

[Machaira]

Basically .NET takes care of all the details. It creates template insert, update, delete statements and executes the proper one for each row. It knows what has been changed for each row and inserts the data for the query.

[Jay1b]

Basically .NET takes care of all the details. It creates template insert' date=' update, delete statements and executes the proper one for each row. It knows what has been changed for each row and inserts the data for the query.[/quote']

 

Thanks, but as i have already said i cant tailor the SQL statements to individual rows using this method.

 

For example

 

inventory_t table

Part Numer - Quantity

00012 - 234

01234 - 12

34939 - 34

09032 - 99

23798 - 0

 

"UPDATE inventory_t SET quantity = '" + CurRow.Quantity + WHERE partnumber = ' " + CurRow.Part_Number +" ' "

 

What should go where the CurRow.* bit is then? As the way i am doing this, it can only use a generic SQL statement for every changed row. But obviously it cant use the same SQL statement when the part numbers and quantities are different.