如何清除上一会话值以避免向mysql插入重复记录

Question

我正在使用datatable在网格视图中插入新行。

当我点击按钮插入另一个新行时，结果显示在网格视图中可以一行接一行地显示，但在mysql数据库中插入的是现有行和前一行数据，这会造成重复行。

当我插入另一个新行时，如何使用session清除以前的行值？

下面是我的代码，有谁能告诉我哪里出了问题吗？

     Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
    lblVisualWarningCode.Visible = False


    Dim conString As String = ConfigurationManager.ConnectionStrings("prcConnectionString").ConnectionString
    Dim MySql As New MySqlConnection(conString)
    Dim cmd As New MySqlCommand("", MySql)
    MySql.Open()
    cmd.CommandText = "SELECT RepairID FROM tbVisual ORDER BY CAST(RepairID AS UNSIGNED) DESC"

    Dim a As String = cmd.ExecuteScalar
    Dim str As Integer = a
    Dim dt As New DataTable("Visual")

    Try
           If Session("Visual") Is Nothing Then
                dt.Columns.Add("RepairID", GetType(String))
                dt.Columns.Add("VisualInspectionErrorCode", GetType(String))
                dt.Columns.Add("VisualInspectionActionTaken", GetType(String))
                dt.Columns.Add("VisualInspectionComponentLocation", GetType(String))
            Else
                dt = DirectCast(Session("Visual"), DataTable)
            End If

            Session("Visual").Clear()'i am not sure where to put this
             'lbVisual is listbox
            For i As Integer = 0 To lbVisual.Items.Count - 1
                str = str + 1
                Dim dr As DataRow = dt.NewRow()
                dr("RepairID") = str.ToString
                dr("VisualInspectionErrorCode") = ddlVisualInspectionErrorCode.SelectedValue
                dr("VisualInspectionActionTaken") = tbVisualInspectionActionTaken.Text
                dr("VisualInspectionComponentLocation") = lbVisual.Items(i).ToString
                dt.Rows.Add(dr)

            Next


        Catch ex As Exception
            Response.Write(ex.Message)
        End Try


        For Each row As DataRow In dt.Rows
            Dim query1 As String = "INSERT INTO tbvisual (RepairID,VisualInspectionErrorCode,VisualInspectionActionTaken,VisualInspectionComponentLocation) " &
                "VALUES ('" + row("RepairID") + "','" + row("VisualInspectionErrorCode") + "','" + row("VisualInspectionActionTaken") + "','" + row("VisualInspectionComponentLocation") + "')"
            cmd = New MySqlCommand(query1, MySql)
            cmd.ExecuteNonQuery()
        Next



    Session("Visual") = dt

    MySql.Close()
    gvVisual.DataSource = dt
    gvVisual.DataBind()

End Sub

Answer 1

您可以在MYSQL表上创建唯一索引，以防止插入重复的行，并在插入数据时编写"INSERT IGNORE" mysql查询，这将忽略已存在的行。

点击以下链接，了解如何在MYSQL- http://www.w3schools.com/sql/sql_unique.asp中创建唯一索引

这是了解"INSERT IGNORE“MYSQL查询的链接：- http://www.tutorialspoint.com/mysql/mysql-handling-duplicates.htm

Answer 2

使用dt.Clear()而不是Session("Visual").Clear()，这是因为一旦创建了会话，它就包含了既有行又有列的数据表。现在，在每次迭代中，您只需要在每次插入之后保留列和删除行。

dt.Clear()将删除所有行，因此每次插入都只有新行

代码更新：

If Session("Visual") Is Nothing Then
                dt.Columns.Add("RepairID", GetType(String))
                dt.Columns.Add("VisualInspectionErrorCode", GetType(String))
                dt.Columns.Add("VisualInspectionActionTaken", GetType(String))
                dt.Columns.Add("VisualInspectionComponentLocation", GetType(String))
            Else
                dt = DirectCast(Session("Visual"), DataTable)
            End If

            dt.Clear()
             'lbVisual is listbox
            For i As Integer = 0 To lbVisual.Items.Count - 1
                str = str + 1
                Dim dr As DataRow = dt.NewRow()
                dr("RepairID") = str.ToString
                dr("VisualInspectionErrorCode") = ddlVisualInspectionErrorCode.SelectedValue
                dr("VisualInspectionActionTaken") = tbVisualInspectionActionTaken.Text
                dr("VisualInspectionComponentLocation") = lbVisual.Items(i).ToString
                dt.Rows.Add(dr)

            Next