在使用vb.net插入新数据之前，如何检查现有数据？

Question

我有一个表是外部_work_duty_details。因此，我想检查用户何时无法插入与现有数据相同的数据。我不知道如何编写代码

：一个要在24/5/2022上申请，直到25/5/2022，但已经以相同的日期应用了它。因此，当单击按钮提交时，显示消息将显示A无法应用。如果现有数据被拒绝或取消，则只能应用它，但是如果它正在待处理或批准，则不能申请。 列确认为状态。 deAfult 0 =待处理。 1 =批准，2 =拒绝，3 =取消。

external_work_duty_details表

id  duty_id employee_no UserId  employee_dept   Reason      Start_date  End_date    Remarks Acknowledgement     
48  D2205-00029 1315    800          3         OUTSTATION   2022-05-17  2022-05-19  out test    3   
49  D2205-00030 1511    1342        32        COMPANY TRIP  2022-05-23  2022-05-27  trip test   1   
50  D2205-00031 1115    127         3          MEETING      2022-05-25  2022-05-25  meet        1   

这是我的vb.net代码（编辑代码）

    Private Function checkExistingData(ByVal startdate As String, ByVal enddate As String, ByVal intUserid As Integer, ByVal reason As String) As DataTable
        Dim dt As New DataTable
        Dim strCheck As String = "SELECT * FROM FROM exteral_work_duty_details WHERE Start_Date=@Start_Date, End_Date=@End_Date, UserId=@UserId, Reason=@Reason"
        myconn.AddParameter("@Start_Date", MySqlDbType.VarChar)
        myconn.SetParameter("@Start_Date", startdate)
        myconn.AddParameter("@End_Date", MySqlDbType.VarChar)
        myconn.SetParameter("@End_Date", enddate)
        myconn.AddParameter("@UserId", MySqlDbType.Int32)
        myconn.SetParameter("@UserId", intUserid)
        myconn.AddParameter("@Reason", MySqlDbType.String)
        myconn.SetParameter("@Reason", reason)
        Try
            myconn.OpenConnection()
            myconn.FillDataTable(dt, strCheck)
            myconn.CloseConnection()
            myconn.ClearAllParameter()

        Catch ex As Exception
            myconn.CloseConnection()
            myconn.ClearAllParameter()
        End Try
        Return dt
    End Function

Answer 1

使用VB.NET 6.0.5，Visual Studio 2022社区和Windows 10X64。

我使用此SQL在XAMPP Localhost上创建MySQL Datbase：

-- phpMyAdmin SQL Dump
-- version 5.1.3
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 25, 2022 at 01:06 PM
-- Server version: 8.0.29
-- PHP Version: 8.0.18

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

--
-- Database: `company`
--

-- --------------------------------------------------------

--
-- Table structure for table `external_work_duty_details`
--

CREATE TABLE `external_work_duty_details` (
  `id` int NOT NULL,
  `duty_id` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `employee_no` int NOT NULL,
  `UserId` int NOT NULL,
  `employee_dept` int NOT NULL,
  `Reason` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `Start_date` date DEFAULT NULL,
  `End_date` date DEFAULT NULL,
  `Remarks` varchar(255) COLLATE utf8mb4_general_ci NOT NULL,
  `Acknowledgement` int NOT NULL DEFAULT '1'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `external_work_duty_details`
--
ALTER TABLE `external_work_duty_details`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `external_work_duty_details`
--
ALTER TABLE `external_work_duty_details`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;
COMMIT;

然后我使用了此代码并进行了测试并且正在工作。随时适应您的需求。

Imports MySqlConnector 
' Don't forget to install MySqlConnector from NuGet Package Manger ✽
Public Class Form1
    Private Sub Button1_Click_1(sender As Object, e As EventArgs) Handles Button1.Click
        Dim conn As New MySqlConnection
        Dim cmd As New MySqlCommand
        Dim UserID As String = TextBoxUserID.Text
        Dim Start_Date = DateTimePicker_Start.Value.ToString("yyyy-M-d")
        Dim End_Date = DateTimePicker_End.Value.ToString("yyyy-M-d")
        Dim Reason As String = TextBoxReason.Text
        Dim Remarks As String = TextBoxRemarks.Text
        conn.ConnectionString = "Server=localhost;Database=company;Uid=root;Pwd=;"
        cmd.Connection = conn
        Dim strCheck As String = "SELECT * FROM external_work_duty_details WHERE Start_Date=@Start_Date AND End_Date=@End_Date AND UserId=@UserId AND Reason=@Reason AND (Acknowledgement = 1 OR Acknowledgement = 2)"
        ' assuming that 1 is for pending and 2 for approved
        ' remember to set in your MySQL database default value for Acknowledgement = 1 
        If (conn.State = ConnectionState.Closed) Then
            conn.Open()
        End If
        cmd.CommandText = strCheck
        cmd.Parameters.AddWithValue("@UserID", UserID)
        cmd.Parameters.AddWithValue("@Start_Date", Start_Date)
        cmd.Parameters.AddWithValue("@End_Date", End_Date)
        cmd.Parameters.AddWithValue("@Reason", Reason)
        Dim dr = cmd.ExecuteReader()
        If dr.HasRows = False Then
            ' here you can add code to save new request
            ' Users can add new request because their previous request was either rejected or canceled
            ' here routine to add new request
            cmd.Parameters.Clear()
            dr.Close()
            Dim strInsert As String = "INSERT INTO external_work_duty_details (UserID, Start_Date, End_Date, Reason, Remarks) VALUES (@UserID, @Start_Date, @End_Date, @Reason, @Remarks)"
            If (conn.State = ConnectionState.Closed) Then
                conn.Open()
            End If
            cmd.CommandText = strInsert
            cmd.Parameters.AddWithValue("@UserID", UserID)
            cmd.Parameters.AddWithValue("@Start_Date", Start_Date)
            cmd.Parameters.AddWithValue("@End_Date", End_Date)
            cmd.Parameters.AddWithValue("@Reason", Reason)
            cmd.Parameters.AddWithValue("@Remarks", Remarks)
            Dim CommandStatus As Integer = cmd.ExecuteNonQuery()
            If CommandStatus > 0 Then
                MessageBox.Show("Request saved!")
            Else
                MessageBox.Show("Something went wrong!")
            End If
        Else
            Dim dt = New DataTable()
            Dim Result As String = ""
            dt.Clear()
            DataGridView1.DataSource = dt
            DataGridView1.DataSource = Nothing
            DataGridView1.Refresh()
            dt.Load(dr)
            DataGridView1.AutoGenerateColumns = True
            DataGridView1.DataSource = dt
            DataGridView1.Refresh()
            dr = cmd.ExecuteReader()
            While dr.Read()
                Select Case dr("Acknowledgement").ToString
                    Case 1
                        Result = "Pending"
                    Case 2
                        Result = "Approved"
                End Select
                MessageBox.Show("Your request: (" & dr("Reason").ToString & ") already saved and its (" & Result & ")!")
            End While
        End If
    End Sub
End Class

• 我看不到您的所有代码；因此，请随时修改我的代码以满足您的需求。让我知道它是否有效，是否需要进一步
  帮助。
• 我不能保证这是最佳解决方案，但至少它据我了解。

您可以

^✽要打开Nuget软件包管理器，连续点击以下四个键，而无需持有 alt alt 键。

alt t n n

Answer 2

解决方案

我终于有了我使用此SQL代码的

DELIMITER $

USE `lrtapp`$

DROP PROCEDURE IF EXISTS `SP_EXTERNAL_WORK_DUTY_DETAILS_SELECT_EXISTING_DATA`$

CREATE DEFINER=`leaderr1`@`%` PROCEDURE `SP_EXTERNAL_WORK_DUTY_DETAILS_SELECT_EXISTING_DATA`(
    IN _user_id INT,
    IN _start_date VARCHAR (10),
    IN _end_date VARCHAR (10)
        
)
BEGIN
     
    SELECT UserId , duty_id FROM external_work_duty_details WHERE UserId=_user_id AND (Start_Date BETWEEN _start_date AND _end_date OR End_Date BETWEEN _start_date AND _end_date) AND (acknowledgement = 0 OR acknowledgement = 1)
              
    ;
 
END$

DELIMITER ;

，并将其写入商店过程中，对我有用。然后，我将其称为vb.net代码

   Public Function GetExistingdata(ByVal intuserId As Integer, ByVal startDate As String, ByVal enddate As String) As DataTable
        Dim result As DataTable = Nothing
        Try
            result = New DataTable
            'Initialize the connection and transation
            If Not Me._IsInit Then
                Me.Init()
                Me._IsCommitHere = True
            End If

            Using command As New MySqlCommand("SP_EXTERNAL_WORK_DUTY_DETAILS_SELECT_EXISTING_DATA", Me._SQLConn, Me._SQLTran)
                Dim Param As New MySqlParameter
                command.CommandType = CommandType.StoredProcedure
                Param = New MySqlParameter
                Param.ParameterName = "@_user_id"
                Param.MySqlDbType = MySqlDbType.Int32
                Param.Direction = ParameterDirection.Input
                Param.Value = clsCommon.ToInt(intuserId)
                command.Parameters.Add(Param)

                Param = New MySqlParameter
                Param.ParameterName = "@_start_date"
                Param.MySqlDbType = MySqlDbType.VarChar
                Param.Direction = ParameterDirection.Input
                Param.Value = clsCommon.ToStr(startDate)
                command.Parameters.Add(Param)

                Param = New MySqlParameter
                Param.ParameterName = "@_end_date"
                Param.MySqlDbType = MySqlDbType.VarChar
                Param.Direction = ParameterDirection.Input
                Param.Value = clsCommon.ToStr(enddate)
                command.Parameters.Add(Param)

               

                Dim SQLReader As MySqlDataReader = command.ExecuteReader
                result.Load(SQLReader)
                SQLReader.Close()
                SQLReader = Nothing
            End Using
        Catch ex As MySqlException
            'Dim str_funcs As String = Me.ToString
            'clsErrorLog.ErrorLog(Me.fstrPageID, ex)
        End Try
        Return result
    End Function

，

 Private Function CheckExistingData(ByVal intUserId As Integer, ByVal strtDate As String, ByVal endDate As String) As DataTable
        Using checking As New clsExternalWorkDuty_func
            Dim dt As New DataTable
            dt = checking.GetExistingdata(intUserId, strtDate, endDate)

            Return dt
        End Using
    End Function

然后在errirfree（）函数中称为checkexistingdata（）函数

  Private Function ErrorFree() As Boolean

        Dim check As New DataTable
        check = CheckExistingData(lblhdddnuserid.Text, datepickerFrom.Text, datepickerto.Text)
        If check.Rows.Count > 0 Then
            DisplayMessage("Error", "Error", "Something went wrong, the data already exist. Please check and change the data.")
            Return False
            Exit Function
        End If
        Return True
    End Function

，最后，我在btnsubmitexternalworkderworkdutydetails_click（）中调用errirfree（）函数。