Prevent storing Duplicate dates when the user selects dates from Asp Calendar control.

KEY:

1. lstAtHomeDates = listView
2. DatesTable = SQL Server Database Table used to store selected dates

So far this is what I have done to implement this:

 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>");
            }

        }

I get this error from my catch clause when I try to click on the Save button while I was debugging

ex.Message "Unable to cast object of type 'System.Web.UI.WebControls.ListItem' to type 'System.IConvertible'."

Does this mean the date from the listview was not converted and therefore unable to match the dates from the front end and back end? Please help.

CodePudding user response：

I have solved the issue I was facing I the code below worked for me hoping it would help someone who faced the same issue or want to achieve the same thing as myself

 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>");
            }

        }

I also referred to Microsoft Docs for implementation of the SqlDataAdapter. Thank you to those who took their time to explain some things in my question and the issues I was facing.