datagridview source Tbl - cant Insert/Delete on same process?

[Guest]

On a form - I have a datagridview which is docked to the entire form. The
datagridview allows users to Delete and/or Add Rows. On the Form_Load event
I Fill the datagridview source table with a sql DataAdapter (da)

da.SelectCommand.CommandText = "Select * from Servertbl1"
da.Fill(ds, "tbl1")

so far, so good. If I add a row to the datagridview I use the following
sqlDataAdapter code to update the server table - which works OK when used
alone in the Form Closing Event.

Private Sub Form5_FormClosing(...) Handles Me.FormClosing
If bRowAdded.Equals(True) Then
da.InsertCommand.Parameters.Clear()
da.InsertCommand.CommandText = "Insert Into ServerTbl Select @PromoCodes,
@StartDate, @EndDate, @Description"
da.InsertCommand.Parameters.Add("@PromoCodeID", SqlDbType.VarChar, 50,
"PromoCodeID")
da.InsertCommand.Parameters.Add("@StartDate", SqlDbType.DateTime, 8,
"StartDate")
da.InsertCommand.Parameters.Add("@EndDate", SqlDbType.DateTime, 8,
"EndDate")
da.InsertCommand.Parameters.Add("@Description", SqlDbType.VarChar, 500,
"Description")
da.Update(ds, "tbl1")
End If
End Sub

If I remove a row from the datagridview I use this code on the Form_Closing
Event to delete the row on the server table. This also works OK by itself if
I dont include the Insert code.

Private Sub Form5_FormClosing(...) Handles Me.FormClosing
If bRowDeleted.Equals(True) Then
da.DeleteCommand.Parameters.Clear()
da.DeleteCommand.CommandText = "Delete PromoCodes Where PromoCodeID =
@PromoCodeID"
da.DeleteCommand.Parameters.Add("@PromoCodeID", SqlDbType.VarChar, 50,
"PromoCodeID")
da.Update(ds, "tbl1")
End If
End Sub

But if I combine the Insert and Delete Process in the form closing event,
then nothing happens to the server table and the form wont close - just
hangs. I tried placing the Insert process in the datagridview Leave Event -
which also works OK if I dont have the Delete code in the Closing Event, but
if I add the Delete process in the closing event or to the datagridview Leave
event, then again, nothing happens to the Server Table and the form wont
close - just hangs. It is like I can have one or the other.

My specs are for users to be able to add a row or remove a row (but not
Update a row) directly from the datagridview with no buttons/menu buttons...
(note: this is not a detail data table - just an ancillary table).

I want to stay with the DataAdapter because it is much easier to deal with
contention - deadlocking than if I use a straight forward
Command.ExecuteNonQuery. How can I make this happen with a sqldataAdapter?
Should I create and implement an interface maybe? Any suggestions
appreciated.

Thanks,
Rich

 

[Guest]

I came up with one solution to my problem so far. I created a separate class
which contains the Insert and Delete routines. I invoke this class on the
Form's closing event. The only problem is that I can only invoke one or the
other Proc. Either I do an Insert or I do a delete. But if I try to do both
a Row Insert and a Row Delete in the same process, then the form hangs again,
and nothing happens to the server table. At least, now I can get the Insert
and Delete routines to coexist. I need to make it so a user can add a row
and delete a row before closing the form.

 

[Guest]

OK. I figured this out. I need to have 3 separate procedures. One for
Insert only, one for Delete only, and one for Insert with Delete (or Delete
with Insert). The problem was with the da.Update(ds, "tbl1") statement. If
rows are marked only for delete, then only need a .DeleteCommand. If rows
are marked only as RowAdded, then only need a .InsertCommand. But if rows
are marked Deleted and RowAdded, then need both .InsertCommand and
..DeleteCommand for the da.Update method to function correctly. So the trick
is to loop through the .Net dataset and see if there are any rows that have a
rowstate.Deleted and/or .rowstate = RowAdded. Then add the appropriate
sqlCommand(s).

Now it works correctly.

 

[Guest]

Rich,

The normal approach is to supply insert, update and delete commands to the
data adapter, call the data adapter's update method and let it figure out
which command to use for a particular row.

There is usually no need to loop through the dataset to deternine row
status, etc.

Kerry Moorman

 

[Guest]

Thanks.

Rich,

The normal approach is to supply insert, update and delete commands to the
data adapter, call the data adapter's update method and let it figure out
which command to use for a particular row.

There is usually no need to loop through the dataset to deternine row
status, etc.

Kerry Moorman