我有一張表是 external_work_duty_details。所以我想檢查用戶何時無法插入與現有資料相同的資料。我不知道如何撰寫代碼
例如：A 想在 24/5/2022 至 25/5/2022 之前申請，但 A 已在同一日期申請。所以這里當A點擊按鈕提交時，會出現顯示資訊A不能申請。如果現有資料被拒絕或取消但如果它正在等待或批準 A 不能應用，則 A 只能應用它。列確認用于狀態。默認 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
uj5u.com熱心網友回復：
使用 VB.NET 6.0.5、Visual Studio 2022 社區和 Windows 10 x64。
我使用這個 SQL 在 XAMPP 本地主機上創建 MySQL 資料庫：
-- 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。
AltTNN
uj5u.com熱心網友回復：
我終于有了解決方案
我使用這個 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
然后我像這樣在 ErrorFree() 函式中呼叫 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
最后，我在 btnSubmitExternalWorkDutyDetails_Click() 中呼叫 ErrorFree() 函式
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/484754.html