如何删除具有相同名称但ID不同的记录？ [英] How do I delete records with same name but different ID ?

问题描述

我在这里遇到了一个情况，对此问题有点困惑。我知道如何使用VB从数据库中删除行，但情况是现实世界的场景。



比方说，在我的程序中注册了两个同名的人。但不同的ID。而且他们是完全不同的人。



如果我按名称删除，那么程序将删除它们。如果我想通过ID删除那么我用来选择删除的组合框将输出作为名称而不是ID让我感到困惑。



怎么办？



我已经添加了代码，请帮助摆脱这种情况。



任何想法都将是赞。



考虑我的英语不好。



Ty codeproject。



我尝试过：

 私有  Sub  Button18_Click（发件人 As  对象，e  As  EventArgs）句柄 Button18.Click 
 connection = 新 SqlConnection（  SERVER = 192.168.80.120， 1433; USER ID = sa; PASSWORD = 123456; DATABASE = gogreendb）
  Dim  reader 正如 SqlDataReader 
 尝试 
 connection.Open（）
  Dim  query  As  字符串 
 query =  从教师处删除姓名='& ComboBox1.Text&  ' 
 command = 新 SqlCommand（查询，连接）
 reader = command.ExecuteReader 
 MsgBox（ 教师否更长的可用）
 connection.Close（）
 fac_dgv（）
 fac_list（）
  Catch  ex  As 异常
 MsgBox（ex.Message）
 最后 
 connection.Dispose（）
 结束 尝试 
 结束  Sub  

解决方案

首先，不要那样做。永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。



连接字符串时会导致问题，因为SQL会收到如下命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  Baker' s Wood '   

就SQL而言，用户添加的引号会终止字符串，并且您会遇到问题。但情况可能更糟。如果我来并改为输入：x'; DROP TABLE MyTable; - 然后SQL收到一个非常不同的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x';  DROP  表 MyTable;   -   ' 

哪个SQL看作三个单独的命令：

  SELECT  *  FROM  MyTable  WHERE  StreetAddress = '  x'; 

完全有效的SELECT

  DROP   TABLE  MyTable; 

完全有效的删除表格通讯和

   -   ' 

其他一切都是评论。

所以它确实：选择任何匹配的行，从数据库中删除表，并忽略其他任何内容。



所以总是使用参数化查询！或者准备好经常从备份中恢复数据库。你定期做备份，不是吗？



其次，你需要的是使用ID，而不是名字，或两者兼而有。

例如，要删除除一个用户以外的所有用户：

  DELETE   FROM 教师 WHERE  name = '  John Smith'  AND  ID！=  12345  

首先，SQL Server支持存储过程，可用于插入，更新和删除数据。



所以...

1. 创建存储过程 [ ^ ]获取人名和<啊ref =https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-procedure>返回所有匹配的人 [< a href =https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/return-data-from-a-stored-proceduretarget =_ blanktitle =New Window > ^ ]及其 badgeid 。有关详细信息，请参阅：如何：执行返回行的存储过程 [ ^ ]

2 。创建获取 badgeid 的存储过程并返回已删除记录的数量。请参阅：使用存储过程修改数据Microsoft Docs [ ^ ]

使用带有输出参数的存储过程| Microsoft Docs [ ^ ]



这就是全部！

首先是Griff所说的绝对正确你永远不应该使用这些SQL命令打开的SQL命令。

你有什么绑定到组合框值提交？理想情况下，您应该将组合框值字段绑定为 Id 和文本字段作为名称。删除记录而不是使用文本时，您可以使用如下的值。

 query =DELETE from faculty WHERE name ='& ComboBox1.Value< pre lang =c＃> &安培; 

Well I got a situation here and little bit confused about the problem. I know how to DELETE row from database using VB but the situation is real world scenario.

let say, There are two person registered in my Program with same name. But different ID. And they are completely different person.

If I delete by name then program gonna delete both of them. If I want to delete by ID then the combobox I used to select for deleting which give Output as Name instead of ID is making me confused.

What to do ?

I have added the code please help to get rid with this situation.

Any Ideas will be appreciated.

Consider my Bad English.

Ty codeproject.

What I have tried:

Private Sub Button18_Click(sender As Object, e As EventArgs) Handles Button18.Click
        connection = New SqlConnection("SERVER=192.168.80.120,1433;USER ID=sa;PASSWORD=123456;DATABASE=gogreendb")
        Dim reader As SqlDataReader
        Try
            connection.Open()
            Dim query As String
            query = "DELETE from faculty WHERE name='" & ComboBox1.Text & "'"
            command = New SqlCommand(query, connection)
            reader = command.ExecuteReader
            MsgBox("Faculty no longer available")
            connection.Close()
            fac_dgv()
            fac_list()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            connection.Dispose()
        End Try
    End Sub

解决方案

For starters, don't do it like that. Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:

SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'

The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:

SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'

Which SQL sees as three separate commands:

SELECT * FROM MyTable WHERE StreetAddress = 'x';

A perfectly valid SELECT

DROP TABLE MyTable;

A perfectly valid "delete the table" command

--'

And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

Secondly, what you need is to use the ID, not the name, or both.
For example, to get rid of all but one user:

DELETE FROM Faculty WHERE name = 'John Smith' AND ID != 12345

First of all, SQL Server supports stored procedures, which can be used for insert, update and delete data purposes.

So...
1. Create stored procedure[^] which gets a name of person and returns all matched persons[^] with their badgeid. For further details, please see: How to: Execute a Stored Procedure that Returns Rows[^]
2. Create stored procedure which get badgeid and returns number of deleted records. See: Modifying Data with Stored Procedures | Microsoft Docs[^]
Using a Stored Procedure with Output Parameters | Microsoft Docs[^]

That's all!

First of all what Griff said is absolutely right you should never ever use these type SQL command which are open for SQL injection.
What do you have bound to Combo Box as value filed? Ideally you should bind the combo-box value field as Id and text field as name. While deleting the record instead of using Text you can use value as below.

query = "DELETE from faculty WHERE name='" & ComboBox1.Value <pre lang="c#"> & "'"

这篇关于如何删除具有相同名称但ID不同的记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！