更新数据库vb.net时检查重复条目 [英] Checking of duplicate entry when updating the database vb.net

问题描述

我在添加时已经可以检查了，它就像我想要的那样工作。

我使用oledbDataReader检查我的数据库

这是我的检查数据库时使用的示例代码（此代码用于添加新条目时）

I am already okay with the checking when adding and it works just as I wanted it to be.
I used the oledbDataReader to check my database
This is my sample code used when checking the database (This code is for when I am adding new entry)

  Dim CompQuery As String = "SELECT * FROM Company WHERE CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & " "
 Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con)
        compCommand.Parameters.AddWithValue("CompanyName", txtCompName.Text)
        compCommand.Parameters.AddWithValue("AssociationID", cmbUnderAssoc.SelectedValue)
        con.Close()
con.Open()
                Using compReader As OleDbDataReader = compCommand.ExecuteReader
                    If compReader.HasRows Then
                        MsgBox("Entry already exist! Please input new entry")
                        Exit Sub
                    Else

我在更新时尝试了相同的逻辑，但它甚至阻止了正在输入的条目编辑。所以我需要更改公司名称或其协会才能更新。



我想要发生的是，正在编辑的条目可以随时更新即使没有进行任何更改，datareader也会将条目与数据库中的其他条目进行比较，以检查更新后是否有任何重复条目。



提前感谢：）

I tried the same logic when updating but it blocks even the entry that is being edited. So I need to change Name of the Company or its Association in order for it to update.

What I want to happen is that the entry being edited can be updated anytime even without changes being made while the datareader will compare the entry to the other entries in the database to check whether it will have any duplicate entry upon updating.

Thanks in advance :)

推荐答案

嗯......当你在阅读参数化查询时，你是否注意到你应该如何使用它们？

此查询：

Um...when you were reading up on parameterised queries, did you notice how you were supposed to use them?
This query:

Dim CompQuery As String = "SELECT * FROM Company WHERE CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & " "

不使用参数。相反，它直接使用文本，并且对SQL注入很开放。

试试这个：

does not use parameters. Instead, it uses the text directly and is wide open to SQL injection.
Try this:

Dim CompQuery As String = "SELECT COUNT(*) FROM Company WHERE CompanyName = ? AND AssociationID = ?"
Dim compCommand As OleDbCommand = New OleDbCommand(CompQuery, con)
        compCommand.Parameters.AddWithValue("?", txtCompName.Text)
        compCommand.Parameters.AddWithValue("?", cmbUnderAssoc.SelectedValue)
        con.Close()
con.Open()
                    If Convert.ToInt32(compCommand.ExecuteScalar()) > 0 Then
                        MsgBox("Entry already exist! Please input new entry")
                        Exit Sub
                    Else

更新 [ ^ ]语句用于根据条件更新记录。所以，它不能创建重复！

Update[^] statement is used to update record(s) based on criteria. So, it can't create duplicates!

UPDATE TableName
SET FieldName = "Whatever"
WHERE <Condition>

最后，您的查询应如下所示：

Finally, your query should look like:

Dim CompQuery As String = "UPDATE * Company" & vbCr & _
"SET CompanyName = '" & txtCompName.Text & "' AND AssociationID = " & cmbUnderAssoc.SelectedValue & vbCr & _
"WHERE CompanyId = " & txtComapnyId.Text & " "

注意：我使用 CompanyId 作为主键 [ ^ ] 公司表。

这篇关于更新数据库vb.net时检查重复条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！