Gridview Record Deletion Problem.

[dilse4sk]

hi i am having problem in deleting a single row from a gridview. i am using binding navigaator to delete the row. i have customer table and booking table. Cust_id is used as foreign key in booking table. when i try to delete a single row from booking table it delete all the bookings related to that customer in the gridview.It also deletes the related customer aswell. i just want to delete one booking of that customer

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

[dilse4sk]

hi i am having problem in deleting a single row from a gridview. i am using binding navigaator to delete the row. i have customer table and booking table. Cust_id is used as foreign key in booking table. when i try to delete a single row from booking table it delete all the bookings related to that customer in the gridview.It also deletes the related customer aswell. i just want to delete one booking of that customer

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

if i use the above coding under a button than i get the following error but if i use the same code under binding navigator save_item it works fine..here is the error which i get when used under button

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Booking_Customer". The conflict occurred in database "photoshoot", table "dbo.Customer", column 'Cust_id'.
The statement has been terminated.

[poojavb]

hi i am having problem in deleting a single row from a gridview. i am using binding navigaator to delete the row. i have customer table and booking table. Cust_id is used as foreign key in booking table. when i try to delete a single row from booking table it delete all the bookings related to that customer in the gridview.It also deletes the related customer aswell. i just want to delete one booking of that customer

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

if i use the above coding under a button than i get the following error but if i use the same code under binding navigator save_item it works fine..here is the error which i get when used under button

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Booking_Customer". The conflict occurred in database "photoshoot", table "dbo.Customer", column 'Cust_id'.
The statement has been terminated.

ur booking table must be having a primary key right?? guess booking_id

one thing u can do is...

take a text box and make it visibility as false

when the user will click on the datagrid the booking id shud be inserted in the textbox and then on the delete button add the code to delete the booking id as it is in the text box....

'code to get the bookingid from datagrid
'code should be wriiten in the cell click event of datagrid

Dim i As Integer
             i = datagridView1.CurrentRow.Index
        textbox1.text= datagridView1.Item(0, i).Value 'ur column 0 in datagrid shud be the bookingid

'code for delete
'connection goes here
    Try
            Dim myCommand As OleDbCommand
            myCommand = New OleDbCommand("DELETE FROM tablenme Where bookingid= '" & textbox1.Text & "'", Connection)
            myCommand.ExecuteNonQuery()
   Catch ex As Exception
            MsgBox("Error in delete query: " + ex.Message)
   End Try

[Pgmer]

@Poojavb why need to add textbox and assign book_id? Cant he bind the book_id to grid and make that coloumn visible false? and read that Id when user click on delete?

[M.Waqas Aslam]

dilse4sk ,please explain your prob , so that i can better understand , please

[dilse4sk]

basically i have a form where i dropped customer dataset as details view on the same form at the bottom i have a gridview which is dropped as datagridview .grid harg bkid cusid location date and payment fields.when i click on delete button on binding navigator it deletes customer details and all related booking even though i highlight the row in the gridview which i want to delete.if i press delete button on the keyboard than it deletes the selected row means 1 record thts wt i want but i dont want to use the delete button on keyboard

[M.Waqas Aslam]

please post your code , and also the snap shot of your form ,
regards

[dilse4sk]

here is the screen shot. If you can see the binding navigator at the top when i select a single row in a gridview and click the cross button on the binding navigator it deletes the customer and all related bookings but if i select the row and press delete on keyborad it works fine. Here is code which is running behind the binding navigator to make the update and delete.
Private Sub CustomerBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles CustomerBindingNavigatorSaveItem.Click

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource1.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

End Sub

please help i am really stuck here.

[M.Waqas Aslam]

i think you have to use proper coding to delete this record. here is an example . assume you have a table name bookings , fields of that tables are , bookingid , cutomerid ,location date , payment . now you want to delete records of a single customer. then do this

dim con as new sqlconnection("your connection string here")
dim cmd as new sqlcommand 
con.open()
cmd.connection= con
cmd.commandtext ="delete from bookings where customerid="& val(gridview.item(4,gridview.currentrow.index).value.tostring)
cmd.executenonquery()
con.close()

use this code at the double click event or single click event , or at a button click event , it will delete record of that customer , and for example if customer have more records and you want to just delete the single booking then change this line

cmd.commandtext ="delete from bookings where customerid="& val(gridview.item(4,gridview.currentrow.index).value.tostring) & " and bookingid="& val(gridview.item(0,gridview.currentrow.index).value.tostring)

and this will delete the single record of customer ,
as i said i dont know the structure of your db so it is an example , so please get an idea from it and complete you task :)

Regards

[dilse4sk]

@ wasqas..waqas bro could you tell me why i get foreign kkey constraint error if i use this code under a button

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

same code works perfectly fine under binding navigator. i cant understand this problem.i use the above code to save the records for booking and customer..

[dilse4sk]

waqas bro i have solved the record deletion problem using this code.

If Not NewBookingDataGridView.CurrentRow.IsNewRow Then
NewBookingDataGridView.Rows.Remove(NewBookingDataGridView.CurrentRow)
End If

[dilse4sk]

how ever i have kind of same problem which is described below any one have any idea why i am having this problem..
could you tell me why i get foreign kkey constraint error if i use this code under a button

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

same code works perfectly fine under binding navigator. i cant understand this problem.i use the above code to save the records for booking and customer..

[M.Waqas Aslam]

@ wasqas..waqas bro could you tell me why i get foreign kkey constraint error if i use this code under a button

Me.Validate()
Me.CustomerBindingSource.EndEdit()
Me.CustomerTableAdapter.Update(NewBookingDataSet.Customer)
Me.BookingBindingSource.EndEdit()
Me.BookingTableAdapter.Update(NewBookingDataSet.Booking)

same code works perfectly fine under binding navigator. i cant understand this problem.i use the above code to save the records for booking and customer..

foreign key constraint error is because your are deleting a record which is linked to you other table as a foreign key.