Deleting Values from Multiple Tables in the database

[bhavan kamble]

Guyzz My project is on Restaurant Mgm.
I stuck to a place,where I have to Delete Values from Multiple Tables in the database

This is My code

Private Sub BtnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnCancel.Click

        Dim id1 As Integer

        id1 = Cbid.SelectedItem
        ds.Dispose()
        con.ConnectionString = "Data Source=localhost;Integrated Security=true;Initial Catalog=Restaurant"
        cmd.CommandText = "Select Table_id,Booking_id From Booking_Details Where Reservation_id=" & id1
        cmd.Connection = con
        da.SelectCommand = cmd
        con.Open()
        da.Fill(ds, "Cancel1")
        con.Close()
        da.Dispose()

        Dim w As Integer
        Dim x As Integer
        Dim t As Integer
        Dim b As Integer



        w = ds.Tables("Cancel1").Rows.Count - 1
        For x = 0 To w
            b = ds.Tables("Cancel1").Rows.Item(x).Item("Booking_id")
            t = ds.Tables("Cancel1").Rows.Item(x).Item("Table_id")

            Dim n As String
            n = "NO"
            con.ConnectionString = "Data Source=localhost;Integrated Security=true;Initial Catalog=Restaurant"
            cmd.CommandText = "Update Table_Details set Reserved='" & n & "'where Table_id=" & t
            cmd.Connection = con
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            con.Dispose()
            cmd.Dispose()
        Next
        con.ConnectionString = "Data Source=localhost;Integrated Security=true;Initial Catalog=Restaurant"
        cmd.CommandText = "Delete from Reservation Where Reservation_id=" & id1
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        con.Dispose()
        cmd.Dispose()
        con.Dispose()
        MessageBox.Show("RECORD DELETED SUCCESSFULLY")

        con.ConnectionString = "Data Source=localhost;Integrated Security=true;Initial Catalog=Restaurant"
        cmd.CommandText = "Delete from Booking_Details Where Table_id=" & b
        cmd.Connection = con
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
        con.Dispose()
        cmd.Dispose()
        con.Dispose()

        Call clear()

    End Sub

It is deleting values only from Reservation Table......
But I want To delete Values from BookingDetails Tables and TableDetails Table...

[Reverend Jim]

First thing I would check is whether the query string on line 51 actually contains the query to delete the record you want. For example, if b doesn't have the value you expect then the query won't delete what you want. As for why the record is not being deleted from the TableDetails table, you don't have a query (at least not in the code you posted) to do the delete on that table.

On line 31 you might want to change

"'where Table_id="

to

"' where Table_id="

[Begginnerdev]

Did you also notice you had 2 connection disposes for the same connection?

con.Dispose()
        cmd.Dispose()
        con.Dispose()

[ChrisPadgham]

rows 23 - 37 can be replaced with a single SQL statement

cmd.CommandText = "UPDATE booking_details INNER JOIN Table_Details ON booking_details.TableId = Table_Details.TableId SET Table_Details.Reserved = "No" WHERE (((booking_details.ReservationId)=" & Id1 & "));"

not only less code but it will execute much much faster than manually looping through.

[Reverend Jim]

Cool. And now that I think about it, it is probably a good idea to put that into a transaction because multiple tables are being affected.

[M.Waqas Aslam]

hello !
why not you try a stored procedure to delete records from multiple table , by using stored procedure you have to do very little coding .

Regards

[bhavan kamble]

Did you also notice you had 2 connection disposes for the same connection?

Removed But still not working.....

[bhavan kamble]

rows 23 - 37 can be replaced with a single SQL statement

cmd.CommandText = "UPDATE booking_details INNER JOIN Table_Details ON booking_details.TableId = Table_Details.TableId SET Table_Details.Reserved = "No" WHERE (((booking_details.ReservationId)=" & Id1 & "));"

not only less code but it will execute much much faster than manually looping through.

But i Actually want to delete from the booking_details table......

[bhavan kamble]

hello !
why not you try a stored procedure to delete records from multiple table , by using stored procedure you have to do very little coding .

Regards

I am very new to Vb.net and does'nt know much......I never use Stored Procedure...

can u tell me the simple way in which we can delete Values from Reservation and Booking_Details Table.....

[Reverend Jim]

If you don't want to do it in one step by joining the tables (which can be daunting) you just need to create a delete query for each table and enclose those deletes in a transaction block. The transaction block ensures that if any of the deletes fail then none of the deletes are committed.

Dim trans As SqlClient.SqlTransaction = myConnection.BeginTransaction()

Try	'delete from all tables

    'run delete query on first table
    'run delete query on second table
    'etc
    
    trans.Commit()

Catch	'got an error - roll back the transaction    
    
    trans.Rollback()
    
Finally	'close the connection

    myConnection.Close()
    
End Try

[bhavan kamble]

Guyzz Help Needed........

[Reverend Jim]

Try this. You'll have to have a connection opened first and you will have to finish off defining the third delete query (on TableDetails). I'm more comfortable with access via ADO but this should work.

Dim trans As SqlClient.SqlTransaction = myConnection.BeginTransaction()
     
    Try 'delete from all tables
     
        cmd.CommandText = "Delete from Reservation Where Reservation_id=" & id1
        cmd.ExecuteNonQuery()
        cmd.CommandText = "Delete from Booking_Details Where Table_id=" & b
        cmd.ExecuteNonQuery()
        cmd.CommandText = "Delete from TableDetails Where ......."
        cmd.ExecuteNonQuery()
     
    trans.Commit()
     
    Catch 'got an error - roll back the transaction
     
    trans.Rollback()
     
    Finally 'close the connection
     
    myConnection.Close()
     
    End Try