CODEWITHSUNDEEP
sqlms-accessvisual-studio-2012
user3357808
user3357808

Reputation: 11

Writing a DELETE query in Visual Studio 2012

I am trying to delete a record in my database that will update values in two different tables. I have a Student table and an Enroll table. If the student ID (and other information) is deleted then it should automatically be deleted for the Student table and the Enroll table where the Student ID is the same. The sql tries to work if I just delete the row from the Student table, but it says it can't becaus the value is also contained in the Enroll table. Here is the code I have for the whole delete button. I keep getting an error message saying to specify the table I want to delete from so I am guessing the syntax is wrong for how I added in the second table name. Does anyone have a clue what I need to do? Also I would love to add a message asking the user are they sure they want to delete the record, how would I do that? Thanks!

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click Dim cmd As OleDb.OleDbCommand = Nothing Dim conn As OleDb.OleDbConnection = Nothing

    Try
        conn = New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\csc420\UniversityClassDatabase.accdb")
        cmd = New OleDb.OleDbCommand(
            "Delete  From Student,Enroll  where  [Student.stID] = [Enroll.stID] AND [Student.stID]=@sid", conn)
        cmd.CommandType = CommandType.Text

        conn.Open()
        cmd.Parameters.AddWithValue("@sid", stID.Text)


        cmd.ExecuteNonQuery()
        MsgBox("Record Deleted")


    Catch ex As Exception
        lblresult.Text = ex.Message()
    Finally
        conn.Close()

    End Try
End Sub

Upvotes: 1

Views: 5999

Answers (2)

user2989408
user2989408

Reputation: 3137

If your tables are set up for Cascading deletes then deleting the record from Student table should delete the records form Enroll table too.

Otherwise you will need 2 DELETE statements. The SQL should be something like 

DELETE FROM Enroll WHERE [Enroll.stID] = @sid
Delete FROM Student WHERE [Student.stID] = @sid

Upvotes: 2

John
John

Reputation: 186

delete from student s where s.stID = @sid; 
delete from enroll e where e.stID = @sid;

Try that. You basically just need to delete each table separately, but if you end the delete commands with semicolons, and include it in one command, it will run like a single delete.

Upvotes: 2

Related Questions