Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

Delete records for Two Tables

marius40marius40 Posts: 42Member
am trying to delete records from two tables at once. They are not using the same Primary key.
Does that make a difference ? STUDENTCODE and STUDENTNUMBER are the primary key
Would it help to set a relationship ?

How can a update my UserInterface after the Delete?

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Me.BindingContext(stu.dt).RemoveAt(Me.BindingContext(stu.dt).Position)

Dim cmd As New OleDbCommand("DELETE FROM [STUDENT] " & _
"WHERE [STUDENTCODE] = '" & MaskedTextBox1.Text & "'", con)
cmd.ExecuteNonQuery()

dt = New DataTable
Dim CMD2 As New OleDbCommand("DELETE FROM [TESTS] " & _
"WHERE [STUDENTNUMBER] = '" & MaskedTextBox2.Text & "'", con)

Me.BindingContext(stu.dt).EndCurrentEdit() ' Student table
Me.BindingContext(bk.dt).EndCurrentEdit() ' Tests table
Button2.PerformClick()
End Sub


Comments

  • seancampbellseancampbell Pennsylvania, USAPosts: 684Member ✭✭✭
    Keep in mind that fields are case sensitive and numbers shouldn't have apostrophe quotes on them orelse SQL will think your passing a string value (unless StudentNumber is a char field).

    You do not need to have a Primary key set up for the tables to pull of these commands, but when you call them it will delete ALL records that match the criteria you pass. So if two Students in STUDENTS has the same StudentCode it would delete both.

    DELETE FROM Students WHERE StudentCode = 'ASDF123'
    DELETE FROM Tests WHERE StudentNumber = 14

    You do not 'need' brackets when there is no spaces in the Table or Field names (which I would suggest against, use underscores if you want spacing in object names). Make sure in the code below that you Execute your CMD2 as well...

    Is this SQL Server or MS Access? I am assuming access since your doing OLEDB...

    I hope I help you shed some light on this issue. If you want further help just post on here, or you can find my contact information if you visit http://www.firesickle.com/contact.asp feel free to email me with questions.

    Sean Campbell - firesickle.com
Sign In or Register to comment.