Saving Datagrid

[microkarl]

Hi all,

I have a datagrid binding to a datatable, and this datagrid has checkbox on each row. upon checking specific checkboxes on each row, those rows will be updated in the DB and for those rows that do not have the check, they will be deleted.

 

Dim ckbDelete as CheckBox 
Dim Conn as new sqlConnection(.....)

           daDelete = New SqlDataAdapter("", Conn)
           For intCount = 0 To dgTest.Items.Count - 1
               ckbDelete = dgTest.Items(intCount).FindControl("chkDelete")
               If ckbDelete.Checked = True Then
                   'If any checkbox is checked, then execute the following command and transaction.
                   With daDelete.SelectCommand
      .CommandText = "sp_Update"
                       .CommandType = CommandType.StoredProcedure
                       .Parameters.Add("@RowID", SqlDbType.NVarChar, 20).Value = dgTest.Items(intCount).Cells(1).Text
                       .ExecuteNonQuery()	
                   End With
               Else
  'If the checkbox is not checked, then delete them...
                   With daDelete.SelectCommand
      .CommandText = "sp_Delete"
                       .CommandType = CommandType.StoredProcedure
                       .Parameters.Add("@RowID", SqlDbType.NVarChar, 20).Value = dgTest.Items(intCount).Cells(1).Text
                       .ExecuteNonQuery()	
                   End With	
               End If
           Next

 

Instead of going through each rows like this tedeously, is there a way to update the datagrid more effeciently.

 

NOTE: the datagrid is binding to a datatable.

[kahlua001]

If the fields you are updating are all the same, then you can do a batch update after you have looped thru your datagrid, building a string of ID's you want to update. update table set field = value where id IN(1,2,3,4)

 

Do the same with the delete,except you dont need the SET part.