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

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

CodePudding user response：

Using VB.NET 6.0.5, Visual Studio 2022 Community, and Windows 10 x64.

I used this SQL to create the MySQL datbase on XAMPP Localhost:

-- 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;

Then I used this code and I tested and it's working. Feel free to adjust to fit your needs.

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

I don't see all your code; so feel free to modify my code to fit your needs. And let me know if it works and if you need any further assistance.
I can't guarantee that it's the optimal solution, but at least it answers your question as far as I understood it.

You can download the project and test it before you implement it.

^✽ To open the NuGet Package Manager, hit the following four keys in succession without holding the Alt key.

AltTNN

CodePudding user response：

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()