How to delete a record from a DB?

[Ishaniwp]

Hi!
I'm a beginner to VB.NET. I'm working on an application which is created using VB.NET 2005 and the DB is created in SQL server 2005. I'm able to delete a record from a table when it is not used in another table as a Foreign Key.
For eg. If I create a new Customer and saves it in Customer Table, i can delete it with out any problems using the following code..

If .DeleteCancelbutton.Text = "&Delete" Then
                    deleteDialogResult = MessageBox.Show("Delete this record?", "Confirm Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                    If deleteDialogResult = Windows.Forms.DialogResult.Yes Then
                        .CustomerBindingSource.RemoveCurrent()
                        .CustomerTableAdapter.Update(.ProjectPrototypeV2DataSet.Customer)
                        .ToolStripStatusLabel1.Text = " Record Deleted"
                    End If

But if i add a SITE to that customer, then try to delete the Customer, It deletes the customer, but does not delete the related SITE.
I tried setting the foreign key constriants to DELETE CASCADE, but then I can't load the tables using the Fill method to the Tableadapters... gives an error saying something about too much forign key constraints. currently i'm only having relational constraints.
is there a method which I use to delete a record from a table and delete all the information in other tables that are related to the deleted record?
I was thinking of using a Stored Procedure, but I don't know how to call a Stored procedure in the forms. I'm using TableAdapters and Bindingsource to get data to the froms...
Can any one please help me....

Ishani

[kplcjl]

It is preferable to execute a procedure over letting your code have ANY access to tables directly.
I pulled the following script from help files looking up ExecuteNonQuery. Note the same type of query with SQL objects is available as well.

Public Sub CreateMyOdbcCommand(myExecuteQuery As String, _
myConnectionString As String)
    Dim myConnection As New OdbcConnection(myConnectionString)
    Dim myCommand As New OdbcCommand(myExecuteQuery, myConnection)
    myCommand.Connection.Open()
    myCommand.ExecuteNonQuery()
    MyConnection.Close()
End Sub

This is a simple query without parameters, but you can build the command with parameters as well. There are tools that will set it up for you as well. Read up on how ADO.NET works. Or you can build your command:
str = "Exec dbo.mysproc @custid=" & id.Tostring()
Again, using the built in commands is much better because it builds a type safe interface between the parameters you want to use and the values passed. For instance it would take "Exec dbo.mysproc @custname=xxyy", but it would fail to execute because xxyy wasn't quoted.

[TomW]

I agree calling a stored procedure to first delete your child records & then the parent is the way to go. However I would suggest not calling "exec" thru a regular query statement; this causes unneccesary processing by the database, it forces the database to first parse the info and then build the SP anyway; additionally concatenating filter values in the query string rather then using a parameter is not the best choice as kplcjl has already pointed out.

To properly call a stored procedure is just as easy although many dont seem familar with it. Create your command object and set a few extra properties. Set the CommandType to StoredProcedure (default is Text) and add your parameter.

Using con As New SqlConnection(strMyConnectionString)
            Dim cmdDelete As New SqlCommand

            With cmdDelete
                .Connection = con
                .CommandType = CommandType.StoredProcedure
                .CommandText = "myStoredProcedureName"
                .Parameters.AddWithValue("@CustId", CInt(id))
                .ExecuteNonQuery()
                .Dispose()
            End With 'cmdDelete
        End Using 'con

The above was based on SQL Server but will also work for other databases by changing to the proper namespaces.

[TomW]

Also I wanted to add my above example would remove the record from the database but not from your already filled dataset/datatable. In that case you have atleast 3 choices, 1) refill your table afterwards, 2) manually remove the record from the datatable too and call acceptchanges, or 03) (best choice) assign the above delete command object to your DataAdapter.DeleteCommand and then when you remove the record from your in memory datatable and call your dataadapter.update method all will be synchronized.

[Ishaniwp]

Hi! Thank you very much for replying for my probelm...
But I can't understand... how to use what both of you have told.
I have not used the sql command, connections...etc before. I added the DB created in SQL in the the project using the wizard... and I am using the dataset, tableadapters and Bindingsources....
And I have used the tableadapters Update method to add and edit data in to the tables. They seem to be working.
When I'm deleteing, I'm using

BindingSource.RemoveCurrent()

and then I use the update method to update changes. It deletes the record, but it does not delete the records from the children tables. I want to know how I can delete the records related to the parent table, when the parent is beign deleted. My DB has lot of tables...
Customer Table
CusID --(PK)
....
.....
Site Table
Site ID -- (PK)
CusID
...
...
Business Unit Table
BUnit ID - -(PK)
CusID
SiteID
....
Circuit Table
CircuitID --(PK)
SiteID
...
...
Rack Table
Rack ID --(PK)
SiteID
...
...
SiteDevice Table
SDeviceID ---(PK)
SiteID
RackID
CircuitID
BUnitID
...
...
and so on...
So if I delete a site, then all the other table records have to be deleted... ..
What shuld I do....

Ishani

[kplcjl]

Again, build a stored procedure in your DB. This procedure would have one parameter defined. (@SiteID int - Since you didn't specify type, I'm using int)
Delete your children records (Delete from Circuit where siteid=@SiteID etc.)
Delete your parent record (Delete from site where [site id]=@SiteID
As TomW stated, this won't affect the data entered in your dataset. You can either have your code remove the records from the table, sync up as he suggests, or have your interface incorrectly display data that should be deleted.
Neither of us are getting into validation techniques and processes you might use in the stored procedure.