How to Delete a Record by searching that record in two tables

[yousuf13]

hi everybody im a fresher ,can anyone tell me .

i have one textbox ,this textbox contains a number ,i want to delete a record by searching that number in two tables Table1 and Table2,the record may present in Table1 or Table2 ,so it should check both the Tables ,if it is found in table1 it should delete or else from table 2 it should delete.

Thanks in Advance ...

[Steve Mac]

Hi yousurf13;
what type of database are you using?
why dont you try this if you are using mysql data client

'declaration of the variables
dim dr as mysqldatareader
dim command as mysqlcommand
dim newcon as mysqlconnection = new mysqlconnection

'Opening up the databaseconnection
newcon.connectionstring= my.settings.yourdatabaseconnectionstring.tostring
newcon.open

'searching to find out if the record exists in the table
command=new mysqlcommand("Select record from table1 where record='& textbox.text &"'",newcon)
dr= newcommand.executereader

if dr.hasrows then

command=new mysqlcommand("Delete record from table1 where record='"& textbox.text & "'",newcon)
command.executenonQuery
dr.close
else
dr.close

command=new mysqlcommand("Select record from table2 where record='& textbox.text &"'",newcon)

dr= newcommand.executereader

if dr.hasrows then

command=new mysqlcommand("Delete record from table2 where record='"& textbox.text & "'",newcon)
command.executenonQuery
dr.close
newcon.close

' i have not tested the code..it was just to give you an idea on how it could be done. So try and see. Nice coding time :)

[Animal Mother]

Before using the following code insure that all your variables are declared.
I'm only supplying the actual code that deletes the record from your database.

dbAdapter = New OleDb.OleDbDataAdapter(sql, dbConnect.ConnectionString)
dbAdapter.Fill(dbDataset, "TableNameHere")
dbTable = dbDataset.Tables("TableNameHere")
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    For Each dbRow In dbTable.Rows
        intRow = intRow + 1
        If dbRow("ColumnNameHere").ToString = TextBox1.Text Then
            Dim cb As New OleDb.OleDbCommandBuilder(dbAdapter)
            dbDataset.Tables("TableNameHere").Rows(intRow).Delete()
            NavigateRecords()
            dbAdapter.Update(dbDataset, "TableNameHere")
            Exit For
        End If
    Next
End Sub

Then just repeat for the second Table, since the 2 tables are not relative to each other as you explained.

[debasisdas]

i want to delete a record by searching that number in two tables Table1 and Table2,the record may present in Table1 or Table2 ,so it should check both the Tables ,if it is found in table1 it should delete or else from table 2 it should delete.

As you say, you are not sure the record exists in which table. At least you should know the value is expected in which column of the table. And since the tables are independent of each other, you can run DELETE on the individual tables no need to search. When you execute DELETE the record will be deleted it exists else not.