如何防止从vb.net重复进入数据库 [英] How to prevent duplicate entry to database from vb.net

问题描述

我有使用OLeDb数据库的vb.net应用程序，

这是我的代码，以防止重复输入ID到数据库，ID不是主键。

但它显示msg-已经存在到每个条目并且不保存任何记录到数据库。

那么该怎么办？

I have vb.net application with OLeDb database,
Here is my code to prevent duplicate entry of "ID" to database, the ID is not the primary key.
But it shows msg- "Already Exists" to every entry and not saving any record to database.
So what to do?

Dim cmd As New OleDbCommand
       Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Pathology.accdb")

       Dim queryResult As Integer
       Dim sqlQRY As String = "SELECT COUNT(*) AS FROM ESRRegister WHERE ID = '" & IDtxt.Text & "'"
       con.Open()
       cmd.Connection = con
       cmd.CommandType = CommandType.Text
       If queryResult > 0 Then
           cmd.CommandText = "insert into ESRRegister (Dt,ID)VALUES ('" & Dttxt.Text & "' , '" & IDtxt.Text & "')"
           queryResult = cmd.ExecuteScalar()
           MsgBox("Added Successfuly")
       Else

           MessageBox.Show("Already Exists!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)

       End If

推荐答案

queryResult 已经声明但从未启动过，它等于 0 （零）。

您需要执行查询（存储在 sqlQRY 变量中）并检索它返回的值：OleDbCommand.ExecuteScalar Method [ ^ ]。

queryResult has been declared but never initiated, it's equal to 0 (zero).
You need to execute query (stored in sqlQRY variable) and retrieve value returned by it: OleDbCommand.ExecuteScalar Method [^].

Dim cmd As New OleDbCommand(sqlQRY, con)
con.Open()
queryResult=cmd.ExecuteScalar()

你的代码应该是.. :)

your code should be.. :)

Dim commandText = "SELECT COUNT(*) AS FROM ESRRegister WHERE ID = '" & IDtxt.Text & "'"
Using(conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Satyam\Documents\Pathology.accdb")
Using(select = New OleDbCommand(commandText, conn)
    conn.open()
    Dim count = Convert.ToInt32(select.ExecuteScalar())
    if count > 0 Then

        MessageBox.Show("Already Exists!", "ALI ENTERPRISES", MessageBoxButtons.OK, MessageBoxIcon.Information)
else
         'Your insert code
  
    End If

Public Function valueExists(ByVal column As String, ByVal tablename As String, ByVal input As String) As Boolean
        Dim list As New List(Of String)
        Dim col_arr As String()

        sqlstr = "SELECT " + column + " FROM " + tablename
        Dim dt As DataTable = getDataTable(sqlstr)

        For i As Integer = 0 To dt.Rows.Count - 1
            list.Add(dt.Rows(i)(column))
        Next

        col_arr = list.ToArray

        For i As Integer = 0 To col_arr.Length - 1
            If col_arr(i) = input Then
                Return True
            Else
                Return False
            End If
        Next
        Return False
End Function

并实现函数

And to implement the function

If valueExists("<column_name>", "<table_name>", thetextcomponent.Text) = True Then
        MsgBox("Duplicate Product Entry Not Allowed", vbCritical + vbOKOnly)
        Exit Sub
End If

这篇关于如何防止从vb.net重复进入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！