Member Avatar for 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 ...

  • 4 Contributors
  • 3 Replies
  • 115 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

A straightforward, safe pattern is: run a parameterized DELETE against Table1 and check the rows-affected. If it was 0, run the same DELETE against Table2. This avoids a prior SELECT/reader and keeps the operation simple and fast. It also avoids the SQL-injection risk in concatenated SQL strings that used in his example.

Example (VB.NET pattern — swap provider for MySQL/OleDb if needed):

Using conn As New System.Data.SqlClient.SqlConnection(connectionString)
    conn.Open()
    Using cmd As New System.Data.SqlClient.SqlCommand("DELETE FROM Table1 WHERE KeyCol = @id", conn)
        cmd.Parameters.AddWithValue("@id", Integer.Parse(TextBox1.Text))
        Dim affected = cmd.ExecuteNonQuery()
        If affected = 0 Then
            cmd.CommandText = "DELETE FROM Table2 WHERE KeyCol = @id"
            affected = cmd.ExecuteNonQuery()
        End If
        ' affected > 0 => delete succeeded in one of the tables
    End Using
End Using

Notes and gotchas:

  • Validate/parse the textbox value (Integer.Parse/TryParse) before using it. Never embed raw textbox text into SQL.
  • Use transactions only if you need atomic behavior across both tables (for example, if duplicates in both should not happen). Otherwise the two-step approach is fine.
  • If you truly want to remove the row from both tables (as suggested), a single multi-statement command or a single DELETE per table is fine; ExecuteNonQuery returns total rows affected for the batch.
  • ’s DataAdapter/DataSet approach works but is heavier when you only need a single-row delete.
  • For reference on ExecuteNonQuery and parameterized commands see Microsoft docs for SqlCommand.ExecuteNonQuery and general advice on avoiding SQL injection with parameters at SQL injection prevention.

Follow these patterns for clarity, safety, and maintainability.

Recommended Answers

All 3 Replies

Member Avatar for 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 :)

Member Avatar for 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.

Edited by Animal Mother because: Left out something
Member Avatar for 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.

Edited by debasisdas because: n/a
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.