防止從ASP.NETCoreWeb應用程式在SQLServer中存盤重復日期

當用戶從 Asp Calendar 控制元件中選擇日期時，防止存盤重復的日期。

鑰匙：

1. lstAtHomeDates = listView
2. DatesTable = 用于存盤選定日期的 SQL Server 資料庫表

到目前為止，這是我為實作這一點所做的：

 void InsertDate()
        {


            try
            {
                SqlConnection cons = new SqlConnection(conectString);
                if (cons.State == ConnectionState.Closed)
                {
                    cons.Open();
                }

                for (int i = 0; i < lstAtHomeDates.Items.Count; i  )
                {
                    string str = "SELECT COUNT(*) FROM DatesTable WHERE convert(varchar, myDate, 23) = @theDate and empID = @employID";
                    SqlCommand cmd = new SqlCommand(str, cons);

                    cmd.Parameters.AddWithValue("@theDate", Convert.ToDateTime(lstAtHomeDates.Items[i]).ToString("yyyy-mm-dd"));
                    cmd.Parameters.AddWithValue("@employID", Session["employId"]);
                    int count = Convert.ToInt32(cmd.ExecuteScalar());

                    cons.Close();

                    if(count > 0)
                    {
                        Response.Write("<Script>alert('Dates already Submitted try another date...')</Script>");
                    }
                    else
                    {
                        cons.Open();

                        string stri = "INSERT INTO DatesTable(myDate, empID, labelID) VALUES(@thedates, @employID, 500 )";
                        cmd = new SqlCommand(stri, cons);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@thedates", lstAtHomeDates.Items[i]);
                        cmd.Parameters.AddWithValue("@employID", Session["employId"]);

                        cmd.ExecuteNonQuery();

                        Response.Write("<Script>alert('working from home dates added!!...');</Script>");
                    }

                    ClearList();
                }
            }
            catch (Exception ex)
            {
                Response.Write("<Script>alert('Error Message: "   ex.Message   "');</Script>");
            }

        }

當我在除錯時嘗試單擊“保存”按鈕時，我從 catch 子句中收到此錯誤

ex.Message“無法將'System.Web.UI.WebControls.ListItem'型別的物件轉換為'System.IConvertible'型別。”

這是否意味著串列視圖中的日期未轉換，因此無法匹配前端和后端的日期？請幫忙。

uj5u.com熱心網友回復：

我已經解決了我面臨的問題我下面的代碼對我有用，希望它能幫助遇到同樣問題或想要實作與我自己相同的事情的人

 void InsertDates()
        {
            try
            {
                SqlConnection cons = new SqlConnection(conectString);
                if (cons.State == ConnectionState.Closed)
                {
                    cons.Open();
                }

                for (int i = 0; i < lstAtHomeDates.Items.Count; i  )
                {
                    string str = "SELECT dateID FROM DatesTable WHERE convert(varchar, myDate, 111) = @theDate and empID = @employId";

                    String date = lstAtHomeDates.Items[i].Text;

                    SqlDataAdapter adapter = new SqlDataAdapter("", cons);

                    adapter.SelectCommand.Parameters.AddWithValue("@theDate", date);
                    adapter.SelectCommand.Parameters.AddWithValue("@employID", Session["employId"]);
                    adapter.SelectCommand.CommandType = CommandType.Text;
                    adapter.SelectCommand.CommandText = str;
                    
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);

                    int count = dataset.Tables[0].Rows.Count;
                    cons.Close();

                    if (count > 0)
                    {
                        Response.Write("<Script>alert('Dates already Submitted try another date...')</Script>");
                    }
                    else
                    {
                        cons.Open();

                        string stri = "INSERT INTO DatesTable(myDate, empID, labelID) VALUES(@thedates, @employID, 500 )";

                        SqlCommand cmd = new SqlCommand(stri, cons);

                        cmd = new SqlCommand(stri, cons);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@thedates", lstAtHomeDates.Items[i].Text);
                        cmd.Parameters.AddWithValue("@employID", Session["employId"]);

                        cmd.ExecuteNonQuery();

                        Response.Write("<Script>alert('working from home dates added!!...');</Script>");
                    }

                    ClearList();
                }
            }
            catch (Exception ex)
            {
                Response.Write("<Script>alert('Error Message: "   ex.Message   "');</Script>");
            }

        }

我還參考了 Microsoft Docs 來實作SqlDataAdapter。感謝那些花時間解釋我的問題和我面臨的問題的人。

標籤：sql服务器 日期 asp.net 核心 重复

上一篇：用于回傳年終日期的VBA函式

下一篇：從不同的資料框中查找最近的日期