473,218 Members | 1,475 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

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

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
Jul 5 '07 #1
4 4808
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.

"Rich" wrote:
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
Jul 5 '07 #2
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.

"Rich" wrote:
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.

"Rich" wrote:
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
Jul 5 '07 #3
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
"Rich" wrote:
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.

"Rich" wrote:
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.

"Rich" wrote:
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
Jul 6 '07 #4
Thanks.

"Kerry Moorman" wrote:
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
"Rich" wrote:
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.

"Rich" wrote:
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.
>
"Rich" wrote:
>
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
Jul 6 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Dave | last post by:
I have a table with 3 fields of which the primary key is a autonumber. I have created my dataadapter, dataset and datagridview using a wizard. However, in the datagrid, the update and delete...
1
by: Riley | last post by:
Need some help. I am using datagridview in 2005 with C#. I am calling a web service and filling the datagridview with the data returned. This all works fine. Next step is to take a value from one...
0
by: Bob Roggy | last post by:
I would like to write transaction activity to a journal table, specifying Add, Update, or Delete and some information regarding each transaction when a user works in a datagridview. I have...
7
by: John J. Hughes II | last post by:
I have a DataGridView with a TextBoxColumn. I setting the data source to a List<stringvalue in a static class. The list is filled from a background thread. So far all is fine and it works...
4
by: Hexman | last post by:
Hello All, I'd like to find out the best way to add a cb column to a dgv and process efficiently. I see at least two ways of doing it. ------------------------------- 1) Add a cb to the dgv,...
7
by: BillE | last post by:
What is the best way to add data using a DataGridView in a multi-tier application (data entry is handled in a data access layer using stored procedures)? Thanks Bill
0
by: Andrus | last post by:
I tried to edit data in DataGridView. Insert and delete operations in grid are not saved to database. Updating works OK. How to force grid to save Insert and Delete operations also ? Andrus....
4
by: =?Utf-8?B?dGVfYnV0dHM=?= | last post by:
Using VS2005, and SQL 2005 Express. Here is the situation: SQL - 1 Table called Products - First Column called ProductsID (Uniqueidentifier)(newid()) - So it adds new UI when new row is...
2
by: Stewart Berman | last post by:
I have an application that populates a DataGridView control with an XML. file. 1. How do I set up the process so the user initally sees just the headers -- no records. I have the DataGridView...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.