Prevent over booking ticket by using busno and date

Question

Am working on small project and am new to coding, i want the system to be a booking system whenever specific bus is booked for specific date then that bus will be specific to that date. for example Bus12 is booked to be used on 21 of Aug then if by mistake the admin tries to book the same bus for something else prevent that booking based on checking the date and busno. am simply using web forms, below is my database table and behind code.please help me am not getting any error nothing is stored in my database table when i refresh it.i want if the entered date and Busno is same as the one in table then prevent booking.

protected void Button1_Click(object sender, EventArgs e)
{
    string cs = ConfigurationManager.ConnectionStrings["MyDatabase1ConnectionString"].ConnectionString;    
    tbDate.Text = Calendar1.SelectedDate.ToShortDateString();

    using (SqlConnection con = new SqlConnection(cs))
    {    
        string check = "SELECT  BusNo, Date FROM Ticket WHERE (BusNo = @busno) AND(Date = @NewDate))";

        SqlCommand cmd = new SqlCommand(check, con);
        cmd.Parameters.AddWithValue("@busno", tbBusno.Text);
        cmd.Parameters.AddWithValue("@NewDate", DateTime.Parse(tbDate.Text));

        con.Open();
        using (SqlDataReader rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                if (rdr.HasRows)
                {
                    Response.Write("double booking");
                }
                else
                {
                    string insertQuery = "INSERT INTO Ticket (BusNo, Date, Time,Bickup,DropOff,Fare) VALUES (@busno ,@date , @time , @bickup , @dropoff ,@fare )";

                    SqlCommand cmd2 = new SqlCommand(insertQuery, con);
                    cmd2.Parameters.AddWithValue("@busno", tbBusno.Text);
                    cmd2.Parameters.AddWithValue("@date", DateTime.Parse(tbDate.Text));
                    cmd2.Parameters.AddWithValue("@time", tbTime.Text);
                    cmd2.Parameters.AddWithValue("@dropoff", tbDrop.Text);
                    cmd2.Parameters.AddWithValue("@bickup", tbBickup.Text);
                    cmd2.Parameters.AddWithValue("@fare", int.Parse(tbfare.Text));

                    con.Open();
                    cmd2.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }
}

Your code is correct, you are also checking duplicate entry condition, You said that it is not inserting data in sql server, just debug `button1_click` event code by each line you will get the problem. — saAction, Aug 21 '18 at 06:32
Are you sure that you don't have any kind of exception? This code should fail with a Syntax Error. Date and Time are reserved keywords and cannot be used in a query without enclosing them in square brackets — Steve, Aug 21 '18 at 06:33
_SELECT BusNo, [Date] FROM_ the same in the insert around the Date and Time field names — Steve, Aug 21 '18 at 07:47

score 1 · Answer 1 · answered Aug 21 '18 at 06:33

1

Can try removing the while (rdr.Read()) portion ?

So your code will be

using (SqlDataReader rdr = cmd.ExecuteReader())
{
    if (rdr.HasRows)
    {
        Response.Write("double booking");
    }
    else
    {
        string insertQuery = "INSERT INTO Ticket (BusNo, Date, Time,Bickup,DropOff,Fare) VALUES (@busno ,@date , @time , @bickup , @dropoff ,@fare )";

        SqlCommand cmd2 = new SqlCommand(insertQuery, con);
        cmd2.Parameters.AddWithValue("@busno", tbBusno.Text);
        cmd2.Parameters.AddWithValue("@date", DateTime.Parse(tbDate.Text));
        cmd2.Parameters.AddWithValue("@time", tbTime.Text);
        cmd2.Parameters.AddWithValue("@dropoff", tbDrop.Text);
        cmd2.Parameters.AddWithValue("@bickup", tbBickup.Text);
        cmd2.Parameters.AddWithValue("@fare", int.Parse(tbfare.Text));

        con.Open();
        cmd2.ExecuteNonQuery();
        con.Close();
    }
}

answered Aug 21 '18 at 06:33

rcs

6,713
12
53
75

yeah i will try to remove the while loop. – yasmin ahmed Aug 21 '18 at 07:09
Nothing is working i have removed and still i don't see any data . – yasmin ahmed Aug 21 '18 at 07:23
Is there any exception occurred? Any output like "double booking" or something? – rcs Aug 21 '18 at 07:26
Nothing happen. i don't see anything – yasmin ahmed Aug 21 '18 at 07:27
so meaning if you do a debug, running step-by-step, there is no exception? Can you see whether the program goes into the `else` portion ? – rcs Aug 21 '18 at 07:32
let me check for that. – yasmin ahmed Aug 21 '18 at 07:33
you need to perform step by step debugging. if you are using visual studio, you can put the breakpoint by pressing F9 on the line you want to inspect. – rcs Aug 22 '18 at 07:06
am still not able to figured out this question can anybody help me.when i debug i see nothing happens. – yasmin ahmed Aug 23 '18 at 10:26
if you put `Response.Write("insert successful");` below `con.close();`, does it get printed? If not, then something is wrong with your query. – rcs Aug 24 '18 at 00:34
i can insert but its about my date comparing. i can insert as much as i can but it can be duplicated record i want to prevent it. – yasmin ahmed Aug 24 '18 at 02:29
it's quite weird... I see you are parsing the data from your textbox. What do you enter there? And what is the data being inserted to database? Alternatively, you can set unique constraint to your table, with fields of `BusNo` and `Date` as the field. Then you can use `try` and `catch` clause instead. – rcs Aug 24 '18 at 05:22
I really don't know am trying my best to learn. please excuse my ignorance. i will try whatever you guys advice me thank you so much. – yasmin ahmed Aug 24 '18 at 12:41
am able to enter data in the insertion part separately. that's it am new student to all these so may be am messing it so badly. sorry – yasmin ahmed Aug 24 '18 at 12:53

Prevent over booking ticket by using busno and date

1 Answers1