Deleting Values from Multiple Tables in the database

Question

bhavan kamble 0 Newbie Poster

14 Years Ago

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...

.net:vb.net database-design

5 Contributors
11 Replies
1K Views
4 Days Discussion Span
Latest Post 14 Years Ago Latest Post by Reverend Jim

Dani AI

Generated 10 Months Ago

Diagnosis and short plan: the posted routine deletes the Reservation row but not the related Booking_Details/Table_Details changes because the code opens/closes/disposes the connection repeatedly, deletes the Reservation before its dependent rows, and uses a loop variable that may not hold the intended value when the final delete runs. already flagged the duplicate Dispose; suggested a transaction; noted a single-set SQL is faster; recommended stored procedures. A simple, reliable pattern is: open one connection, start a transaction, mark affected tables free in one statement, delete Booking_Details by Reservation_id, then delete the Reservation — all with parameterized commands and Using blocks.

Example pattern (VB.NET, SqlClient):

Dim connStr As String = "Data Source=localhost;Integrated Security=true;Initial Catalog=Restaurant"
Dim resId As Integer
If Not Integer.TryParse(Convert.ToString(Cbid.SelectedValue), resId) Then Throw New InvalidOperationException("Invalid reservation selection")

Using conn As New SqlClient.SqlConnection(connStr)
    conn.Open()
    Using tran As SqlClient.SqlTransaction = conn.BeginTransaction()
        Try
            Using cmd As New SqlClient.SqlCommand("UPDATE Table_Details SET Reserved = @no WHERE Table_id IN (SELECT Table_id FROM Booking_Details WHERE Reservation_id = @resId)", conn, tran)
                cmd.Parameters.AddWithValue("@no", "NO")
                cmd.Parameters.AddWithValue("@resId", resId)
                cmd.ExecuteNonQuery()
            End Using

            Using cmd As New SqlClient.SqlCommand("DELETE FROM Booking_Details WHERE Reservation_id = @resId", conn, tran)
                cmd.Parameters.AddWithValue("@resId", resId)
                cmd.ExecuteNonQuery()
            End Using

            Using cmd As New SqlClient.SqlCommand("DELETE FROM Reservation WHERE Reservation_id = @resId", conn, tran)
                cmd.Parameters.AddWithValue("@resId", resId)
                cmd.ExecuteNonQuery()
            End Using

            tran.Commit()
        Catch
            tran.Rollback()
            Throw
        End Try
    End Using
End Using

Notes: run the SELECT subquery first to verify affected Table_id rows before issuing DELETEs; prefer parameterized SQL (avoid string concatenation); keep the connection string in config; if Booking_Details is set to ON DELETE CASCADE the Booking_Details DELETE may be unnecessary; stored procedures are a good next step for encapsulation and security.

All 11 Replies

Reverend Jim 6,665 Hi, I'm Jim, one of DaniWeb's moderators.

14 Years Ago

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="

ChrisPadgham 113 Posting Whiz

14 Years Ago

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 6,665 Hi, I'm Jim, one of DaniWeb's moderators.

14 Years Ago

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

Edited 14 Years Ago by Reverend Jim because: n/a

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Begginnerdev 256 Junior Poster · Answer 1 · 2012-01-27T03:51:00+00:00

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

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

score 0 · Answer 2 · 2012-01-27T06:42:34+00:00

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 67 Posting Pro in Training Featured Poster · Answer 3 · 2012-01-27T10:55:50+00:00

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 0 Newbie Poster · Answer 4 · 2012-01-27T22:16:18+00:00

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

Removed But still not working.....

bhavan kamble 0 Newbie Poster · Answer 5 · 2012-01-27T22:17:36+00:00

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 0 Newbie Poster · Answer 6 · 2012-01-27T22:40:20+00:00

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.....

bhavan kamble 0 Newbie Poster · Answer 7 · 2012-01-30T23:03:47+00:00

bhavan kamble 0 Newbie Poster

14 Years Ago

Guyzz Help Needed........

score 0 · Answer 8 · 2012-01-31T01:14:31+00:00

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

Deleting Values from Multiple Tables in the database

Dani AI

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers