find the way to check in with count

Question

Could you please help me to get a solution to my issue. I am using a SQL Server database, it is a gymnastics program, and I want to get check in the client when he comes to the gym , I have two way of offering the first one is a monthly way , and the second is a daily, the first I don't have a problem with it and I use this code for checkin;

using (SqlCommand com = new SqlCommand("select count(*)from enddate where ID=@ID and startdate <=@C1 and endDate >=@C2", con))
                {

                    com.Parameters.AddWithValue("@ID", ID.Text);
                    com.Parameters.AddWithValue("@C1", DateTime.Now);
                    com.Parameters.AddWithValue("@C2", DateTime.Now);

                    int count = (int)com.ExecuteScalar();
                    if (count > 0)
                    {
                        using (SqlCommand com1 = new SqlCommand("INSERT INTO [checkin] (ID,time,username) VALUES (@ID,@time,@username)", con))
                        {
                            com1.Parameters.AddWithValue("@ID", ID.Text);

                            com1.Parameters.AddWithValue("@time", txttime.Text);

                            com1.Parameters.AddWithValue("@username", txtusername.Text);
                            com1.ExecuteNonQuery();
                        }
                        MetroFramework.MetroMessageBox.Show(this, "Check In Sucssesfuly ................... ", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                    else
                    {
                        MetroFramework.MetroMessageBox.Show(this, "this ID Expired .....................", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);


                    }
                    con.Close();
                }

I want to add to this code the second condition ( the daily offer ) I have the enddate table like ;

| ID | Startdate | month | day | enddate |          offer       |
| 1  | 20-3-2019 |   3   |null |20-6-2019|( summer ) monthly    |
| 2  | 20-3-2019 | null  | 5   |20-3-2019|( student )  daily    |

in this case, the first one can come anytime for 3 months, in the second ID he can come for 5 times only.

my checkin table ;

| ID |   Time   | username |
| 1  | 21-3-2019| test     |
| 1  | 25-3-2019| test     |
| 2  | 27-3-2019| test 2   |

I can count how many time he comes to the gym but I don't know how to add it in my code

Some tips: avoid nesting the SqlCommand using blocks - think of each one as the scope of that command, and try to complete each one as soon as possible. You may want to read [can we stop using AddWithValue](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/). If the `con` is your SqlConnection, and is also in a `using` block, then you don't need to Close it because it will be closed by the implicit Dispose as it exits the block. — Richardissimo, Mar 19 '19 at 06:01
@Richardissimo thx i read your link and i will try to do that :) — ahmed_eltot93, Mar 19 '19 at 06:07
@ahmed can you show us or explain how you count the gym visits? — magicalKhachapuri, Mar 19 '19 at 06:13
@ahmed maybe create a procedure where you will have different behavior for different offer types? Like: first check the offer type of the user and than count visits based on that ? — magicalKhachapuri, Mar 19 '19 at 06:28
@ahmed or did I go completely different direction and you just struggle to output the result in c#? — magicalKhachapuri, Mar 19 '19 at 06:29

Anson Fong · Accepted Answer · 2019-03-19T11:55:20.540

I think you may want to re-think the approach of solving the issue. If I were you I would:

Get the record in enddate table using ID.text, I assume this is your customer offer table. So you have the data of STARTDATE, ENDDATE, Offer and other information for this customer ID.
If ENDDATE is null and Offer = dayly, ENDDATE = DATE(Datetime.Now)
Count the record from checkin table using ID.text. So you have a sum of visits by using the following statement.

SELECT COUNT(*) From checkin WHERE Time >= STARTDATE and (Time <= ENDDATE)

You have the count of visits now, you can put a condition to check if the customer has used up the offer "day" of 5.

After spending a bit of time, I tried to complete your whole logic in C#:

var goodForVisit = false;
int visitedCount;
int offerDayCount;
var endDate = DateTime.MinValue;
DateTime startDate = DateTime.MinValue;

using (SqlCommand com = new SqlCommand("select * from [enddate] where ID=@ID", con))
{
    com.Parameters.AddWithValue("@ID", ID.Text);
    using (SqlDataReader reader = com.ExecuteReader())
    {
        if (reader.Read())
        {
            //get information from enddate table
            var offer = “”;
            if(reader[“offer”] != null)
                  offer = reader["offer"].ToString();
            if (reader[“day”] != null)
                  offerDayCount = (int)reader["day"];
            startDate = (DateTime)reader["Startdate"];
            if (reader["enddate"] != null)
                endDate = (DateTime)reader["enddate"];


            if (reader["enddate"] == null && offer == "dayly")
            {
                endDate = DateTime.Now.Date;
            }

            //count the visit from checkin table
            using (var com2 = new SqlCommand("SELECT COUNT(*) as count From checkin WHERE Time >= @STARTDATE and (Time <= @ENDDATE)"))
            {
                com.Parameters.AddWithValue("@STARTDATE", startDate);
                com.Parameters.AddWithValue("@ENDDATE", endDate);

                using (SqlDataReader reader2 = com2.ExecuteReader())
                {
                    if (reader2.Read())
                    {
                        visitedCount = (int)reader2["count"];
                        if (offer == "dayly" && visitedCount < offerDayCount)
                            goodForVisit = true;

                        if (offer == "monthly" && DateTime.Now >= startDate && DateTime.Now <= endDate)
                            goodForVisit = true;
                    }
                }
            }
        }
    }
}

if (goodForVisit)
{
    using (SqlCommand com1 = new SqlCommand("INSERT INTO [checkin] (ID,time,username) VALUES (@ID,@time,@username)", con))
    {
        com1.Parameters.AddWithValue("@ID", ID.Text);

        com1.Parameters.AddWithValue("@time", txttime.Text);

        com1.Parameters.AddWithValue("@username", txtusername.Text);
        com1.ExecuteNonQuery();
    }
    MetroFramework.MetroMessageBox.Show(this, "Check In Sucssesfuly ................... ", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
    MetroFramework.MetroMessageBox.Show(this, "this ID Expired .....................", "Message", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}

ok i understand that bout that the ( if statement that i will us in my code ) — ahmed_eltot93, Mar 19 '19 at 06:36
I have tried to write the logic in C#, please let me know if there is any error or confusion. — Anson Fong, Mar 19 '19 at 06:43
1- look bro i have many name for offer not just daily and monthly like ( 3month , summer , student ) 2- 'if (reader["enddate"] != null)' can be 'if (reader["enddate"] == "startdate")' ????? — ahmed_eltot93, Mar 19 '19 at 07:11
@ahmed , you can use my code to extend your solution as most of the variables are in place. Read[“enddate”] == startdate statement is really depended on how you insert data into enddate table. In summary, your questions will be more likely a software architecture problem and it’s out of scope of your original question — Anson Fong, Mar 19 '19 at 08:25
offerDayCount = (int)reader["day"]; give me error Specified cast is not valid — ahmed_eltot93, Mar 19 '19 at 08:34
it should work good everything is ok but i don't know why i get this error — ahmed_eltot93, Mar 19 '19 at 08:42
look now when i check for monthly ID it work but when i Us daily ID it stop — ahmed_eltot93, Mar 19 '19 at 09:18
Specific cast error is due to the data is null. Have fixed in the code — Anson Fong, Mar 19 '19 at 10:04
(offer == "dayly" && visitedCount < offerDayCount) the offerDayCount is with red line here — ahmed_eltot93, Mar 19 '19 at 10:29
error red line under offerDayCount here (offer == "dayly" && visitedCount < offerDayCount) — ahmed_eltot93, Mar 19 '19 at 11:42
Data is Null. This method or property cannot be called on Null values — ahmed_eltot93, Mar 19 '19 at 11:52
Maybe the “offer” variable is null. Changed the source with the checking — Anson Fong, Mar 19 '19 at 11:55
look with i get an monthly ID i didn't work when i a daily ID i work good :( — ahmed_eltot93, Mar 19 '19 at 11:58
Sorry that I don’t quite understand your question. Please explain in detail? — Anson Fong, Mar 19 '19 at 12:37
when i inter ID (1) didn't work and gave me error , when i inter ID (2) it working — ahmed_eltot93, Mar 20 '19 at 05:52

find the way to check in with count

1 Answers1