How to check existing data before inserting a new data using vb.net?

Question

I have one table which is external_work_duty_details. So i want to do checking when user cannot inserting the same data as existing data. I have no idea on how to write the code

for example : A want to apply on 24/5/2022 until 25/5/2022 but A already applied it with the same date. so here when A click button submit, display message will appear saying A can't apply. A only can apply it IF the existing data is rejected or cancelled BUT IF it's pending or approved A cannot apply. Column Acknowledgement is for the status. deafult 0 = pending. 1 = approved, 2 = rejected and 3 = cancelled.

external_work_duty_details Table

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   

here is my vb.net code (edit code)

    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

i finally have the solution

I use this sql code and write it in store procedures

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 ;

and it works for me. then i call it in my vb.net code like this

   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

and this

 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

Then i call CheckExistingData() function in ErrorFree() function like this

  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

and lastly, i call the ErrorFree() function in btnSubmitExternalWorkDutyDetails_Click()