Nrthugu

I wanna make an worktime-following program on ASP.NET in c# with access database. I know how to prevent SQL injection and so its okay about that my codes will a little be basic.

When user select the work and clicks START button(btnisBaslat button) the work will started , and program will add Start Time , when user click "Beklemeye al" and click STOP button (btnisDurdur button) it means work is not completed and just holding the work like not finished. And user may start it again and after that he/she can finish this work by clicking STOP button and clicking "Bitir" Radio button.

If user clicks "Beklemeye al" (HOLD the work) radio button the program adding End Time to this work , and after that user can see how much minute he working total.after that program adds StartTime again for calculating (HOLDing time) like how much this work was hold by the user.

The problem is: when user select other work from DropdownList and click start the last finished work Just Dont add EndTime for Holding time.And adding StartTime to second work. And ta-daa! the EndTime column for first work will empty.

I want to ask how i can coding the EndTime too. And also
SECOND PROBLEM is When first work on database "is_durum" column like "Beklemede" it means the work is HOLD (waiting to start again when user wants but not finished work). then read my IF block ,

But when I click second work to Start and HOLD it,on database "is_durum" column its updates to "Beklemede" (waiting) it should read my IF block again but it reads only ELSE block all the time. Where is my problem ?. I will share the screenshots and codes now.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data;

using System.Configuration;





    protected void btnisBaslat_Click(object sender, EventArgs e)

    {



            OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath("Database1.accdb") + "");

            con.Open();

            //OleDbCommand cmd = new OleDbCommand("update Personel set is_Tanimi='" + txtisTanimi.Text + "', baslangic_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' where ID = " + Session["UserID"], con);

            OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='true' where ID=" + Session["UserID"], con);

            cmd.ExecuteNonQuery();

            //OleDbCommand cmd2 = new OleDbCommand("INSERT INTO Gorevler(Kadi,is_tanimi,baslangic_zamani,kull_id) values ('" + Session["Adi"] + "','" + DDLisSec.SelectedValue + "','" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "','" + Session["UserID"] + "')", con);

            //cmd2.ExecuteNonQuery();   

            OleDbCommand cmd2 = new OleDbCommand("Select * from Temsilci where is_durum='Beklemede'", con);

            OleDbDataReader dr = cmd2.ExecuteReader();

            if (dr.Read())

            {

                if (dr["is_durum"].ToString() == "Beklemede" && dr["ID"].ToString() == DDLisSec.SelectedValue.ToString())

                {

                    OleDbCommand cmd4 = new OleDbCommand("UPDATE Temsilci set Tipdurum=1 where Tipdurum=2 AND ID=" + DDLisSec.SelectedValue + " AND personel_id=" + Session["UserID"], con);

                    cmd4.ExecuteNonQuery();

                    OleDbCommand cmd3 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE Tip=2 AND EndTime IS NULL AND GorevId=" + DDLisSec.SelectedValue + " AND SessionId=" + Session["UserID"], con);

                    cmd3.ExecuteNonQuery();

                    OleDbCommand cmd7 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,SessionId,Tip,StartTime) SELECT Temsilci.ID,Temsilci.personel_id,'1' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime From Temsilci where Temsilci.Tipdurum=1 and Temsilci.personel_id=" + Session["UserID"], con);

                    cmd7.ExecuteNonQuery();



                    Response.Write("Beklemede olanlar çalıştı");

                }

                else

                {

                    //Response.Write(DDLisSec.SelectedValue);

                    OleDbCommand cmd4 = new OleDbCommand("UPDATE Temsilci set Tipdurum=1 where ID=" + DDLisSec.SelectedValue + " AND personel_id=" + Session["UserID"], con);

                    cmd4.ExecuteNonQuery();

                    OleDbCommand cmd3 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE Tip=2 AND EndTime IS NULL AND GorevId=" + DDLisSec.SelectedValue + " AND SessionId=" + Session["UserID"], con);

                    cmd3.ExecuteNonQuery();

                    //OleDbCommand cmd4 = new OleDbCommand("INSERT INTO Gorevler(is_tanimi,kull_id,durum) values ('" + DDLisSec.SelectedValue + "','" + Session["UserID"] + "',1)", con);

                    //cmd4.ExecuteNonQuery();

                    //OleDbCommand cmd3 = new OleDbCommand("UPDATE Temsilci set temsilcigorevid=Gorevler.ID WHERE Temsilci.ID='" + DDLisSec.SelectedValue + "'", con);

                    //cmd3.ExecuteNonQuery();

                    //OleDbCommand cmd7 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,SessionId,Tip,StartTime) SELECT Gorevler.ID,Gorevler.kull_id,'1' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime From Gorevler where Gorevler.durum=1 and Gorevler.kull_id=" + Session["UserID"], con);

                    //cmd7.ExecuteNonQuery();

                    OleDbCommand cmd7 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,SessionId,Tip,StartTime) SELECT Temsilci.ID,Temsilci.personel_id,'1' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime From Temsilci where Temsilci.Tipdurum=1 and Temsilci.personel_id=" + Session["UserID"], con);

                    cmd7.ExecuteNonQuery();                                        

                    Response.Write("Null lar çalıştı");

                }



            }







                    //OleDbCommand cmd6 = new OleDbCommand("UPDATE GorevHareket set StartTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' Where [ID] = (SELECT MAX([ID]) FROM GorevHareket)", con);

                    //cmd6.ExecuteNonQuery();





        //cmd.ExecuteNonQuery();



            btnisBaslat.Visible = false;

            btnisDurdur.Visible = true;

            rbtnBekle.Visible = true;

            rbtnBitir.Visible = true;

            con.Close();

            dr.Close();







    }

    protected void btnisDurdur_Click(object sender, EventArgs e)

    {





        OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath("Database1.accdb") + "");

            con.Open();

            //OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

            //cmd.ExecuteNonQuery();

            //OleDbCommand cmd2 = new OleDbCommand("update Gorevler set bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' where kull_id=" + Session["UserID"], con);

            //// OleDbCommand cmd2 = new OleDbCommand("UPDATE Gorevler SET bitis_zamani='"+ DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss")+ "',kull_id='"+Session["UserID"]+"' FROM Gorevler INNER JOIN Personel ON Personel.ID=Gorevler.kull_id WHERE kull_id="+Session["UserID"],con);



            //OleDbCommand cmd2 = new OleDbCommand("UPDATE Gorevler INNER JOIN Personel ON Gorevler.kull_id=Personel.ID SET bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "',kull_id='" + Session["UserID"] + "' WHERE kull_id=" + Session["UserID"], con);

            //cmd2.ExecuteNonQuery();

            //OleDbDataReader dr = cmd.ExecuteReader();

            btnisDurdur.Visible = false;

            btnisBaslat.Visible = true;

            if(rbtnBekle.Checked==true)

            {

                OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

                cmd.ExecuteNonQuery();

                OleDbCommand cmd2 = new OleDbCommand("update Temsilci set Tipdurum=2 WHERE ID=" + DDLisSec.SelectedValue + " and Tipdurum=1 and personel_id=" + Session["UserID"], con);

                cmd2.ExecuteNonQuery();

                OleDbCommand cmd4 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE Tip=1 AND EndTime IS NULL AND GorevId=" + DDLisSec.SelectedValue + " AND SessionId=" + Session["UserID"], con);

                cmd4.ExecuteNonQuery();

                OleDbCommand cmd5 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,Tip,StartTime,SessionId) SELECT Temsilci.ID,'2' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime,Temsilci.personel_id From Temsilci where Tipdurum=2 AND ID=" + DDLisSec.SelectedValue + " AND Temsilci.personel_id=" + Session["UserID"], con);

                cmd5.ExecuteNonQuery();

                OleDbCommand cmd3 = new OleDbCommand("update Temsilci set is_durum='Beklemede' where ID="+DDLisSec.SelectedValue+" and personel_id="+Session["UserID"], con);

                cmd3.ExecuteNonQuery();



                //OleDbCommand cmd2 = new OleDbCommand("Select top 1 * from Gorevler where kull_id="+Session["UserID"]+" Order by ID DESC",con);

                //OleDbDataReader dr = cmd2.ExecuteReader();

                //while (dr.Read())

                //{

                //    Response.Write(dr["is_tanimi"].ToString());

                //}

            }

            else if (rbtnBitir.Checked == true)

            {

                OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

                cmd.ExecuteNonQuery();

                OleDbCommand cmd2 = new OleDbCommand("UPDATE Gorevler SET bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "',is_durum='Bitti' where is_tanimi='" + DDLisSec.SelectedValue + "' and [ID] = (SELECT MAX([ID]) FROM Gorevler)", con);

                cmd2.ExecuteNonQuery();

                OleDbCommand cmd3 = new OleDbCommand("update Temsilci set is_durum='Bitti' where isin_adi='" + DDLisSec.SelectedValue + "'and personel_id=" + Session["UserID"], con);

                cmd3.ExecuteNonQuery();

            }

            else

            {

                OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

                cmd.ExecuteNonQuery();

                OleDbCommand cmd2 = new OleDbCommand("update Gorevler set bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' where kull_id=" + Session["UserID"], con);

                cmd2.ExecuteNonQuery();

            }

            rbtnBekle.Visible = false;

            rbtnBitir.Visible = false;



            con.Close();



    }













    protected void DDLisSec_SelectedIndexChanged(object sender, EventArgs e)

    {







    }











}

**I made it like this and it worked. Sadly nobody answers this :/ Belki birinin işine yarar **

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.OleDb;

using System.Data;

using System.Configuration;





    protected void btnisBaslat_Click(object sender, EventArgs e)

    {



            OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath("Database1.accdb") + "");

            con.Open();

            //OleDbCommand cmd = new OleDbCommand("update Personel set is_Tanimi='" + txtisTanimi.Text + "', baslangic_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' where ID = " + Session["UserID"], con);

            OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='true' where ID=" + Session["UserID"], con);

            cmd.ExecuteNonQuery();

            //OleDbCommand cmd2 = new OleDbCommand("INSERT INTO Gorevler(Kadi,is_tanimi,baslangic_zamani,kull_id) values ('" + Session["Adi"] + "','" + DDLisSec.SelectedValue + "','" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "','" + Session["UserID"] + "')", con);

            //cmd2.ExecuteNonQuery();

            OleDbCommand cmd6 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE StartTime IS NOT NULL AND EndTime IS NULL AND SessionId=" + Session["UserID"], con);

            cmd6.ExecuteNonQuery();



            OleDbCommand cmd2 = new OleDbCommand("Select * from Temsilci where ID=" + DDLisSec.SelectedValue, con);

            OleDbDataReader dr = cmd2.ExecuteReader();

            while (dr.Read())

            {

                if (dr["is_durum"].ToString() == "Beklemede")

                {

                    OleDbCommand cmd4 = new OleDbCommand("UPDATE Temsilci set Tipdurum=1 where Tipdurum=2 AND ID=" + DDLisSec.SelectedValue + " AND personel_id=" + Session["UserID"], con);

                    cmd4.ExecuteNonQuery();

                    OleDbCommand cmd3 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE Tip=2 AND EndTime IS NULL AND GorevId=" + DDLisSec.SelectedValue + " AND SessionId=" + Session["UserID"], con);

                    cmd3.ExecuteNonQuery();

                    OleDbCommand cmd7 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,SessionId,Tip,StartTime) SELECT Temsilci.ID,Temsilci.personel_id,'1' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime From Temsilci where Temsilci.Tipdurum=1 and Temsilci.personel_id=" + Session["UserID"], con);

                    cmd7.ExecuteNonQuery();



                    Response.Write("Beklemede olanlar çalıştı");

                }

                else

                {

                    //Response.Write(DDLisSec.SelectedValue);

                    OleDbCommand cmd4 = new OleDbCommand("UPDATE Temsilci set Tipdurum=1 where ID=" + DDLisSec.SelectedValue + " AND personel_id=" + Session["UserID"], con);

                    cmd4.ExecuteNonQuery();

                    OleDbCommand cmd3 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE Tip=2 AND EndTime IS NULL AND GorevId=" + DDLisSec.SelectedValue + " AND SessionId=" + Session["UserID"], con);

                    cmd3.ExecuteNonQuery();

                    //OleDbCommand cmd4 = new OleDbCommand("INSERT INTO Gorevler(is_tanimi,kull_id,durum) values ('" + DDLisSec.SelectedValue + "','" + Session["UserID"] + "',1)", con);

                    //cmd4.ExecuteNonQuery();

                    //OleDbCommand cmd3 = new OleDbCommand("UPDATE Temsilci set temsilcigorevid=Gorevler.ID WHERE Temsilci.ID='" + DDLisSec.SelectedValue + "'", con);

                    //cmd3.ExecuteNonQuery();

                    //OleDbCommand cmd7 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,SessionId,Tip,StartTime) SELECT Gorevler.ID,Gorevler.kull_id,'1' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime From Gorevler where Gorevler.durum=1 and Gorevler.kull_id=" + Session["UserID"], con);

                    //cmd7.ExecuteNonQuery();

                    OleDbCommand cmd7 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,SessionId,Tip,StartTime) SELECT Temsilci.ID,Temsilci.personel_id,'1' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime From Temsilci where Temsilci.Tipdurum=1 and Temsilci.personel_id=" + Session["UserID"], con);

                    cmd7.ExecuteNonQuery();                                        

                    Response.Write("Null lar çalıştı");

                }



            }







                    //OleDbCommand cmd6 = new OleDbCommand("UPDATE GorevHareket set StartTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' Where [ID] = (SELECT MAX([ID]) FROM GorevHareket)", con);

                    //cmd6.ExecuteNonQuery();





        //cmd.ExecuteNonQuery();



            btnisBaslat.Visible = false;

            btnisDurdur.Visible = true;

            rbtnBekle.Visible = true;

            rbtnBitir.Visible = true;

            con.Close();

            dr.Close();







    }

    protected void btnisDurdur_Click(object sender, EventArgs e)

    {





        OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + Server.MapPath("Database1.accdb") + "");

            con.Open();

            //OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

            //cmd.ExecuteNonQuery();

            //OleDbCommand cmd2 = new OleDbCommand("update Gorevler set bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' where kull_id=" + Session["UserID"], con);

            //// OleDbCommand cmd2 = new OleDbCommand("UPDATE Gorevler SET bitis_zamani='"+ DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss")+ "',kull_id='"+Session["UserID"]+"' FROM Gorevler INNER JOIN Personel ON Personel.ID=Gorevler.kull_id WHERE kull_id="+Session["UserID"],con);



            //OleDbCommand cmd2 = new OleDbCommand("UPDATE Gorevler INNER JOIN Personel ON Gorevler.kull_id=Personel.ID SET bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "',kull_id='" + Session["UserID"] + "' WHERE kull_id=" + Session["UserID"], con);

            //cmd2.ExecuteNonQuery();

            //OleDbDataReader dr = cmd.ExecuteReader();

            btnisDurdur.Visible = false;

            btnisBaslat.Visible = true;

            if(rbtnBekle.Checked==true)

            {

                OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

                cmd.ExecuteNonQuery();

                OleDbCommand cmd2 = new OleDbCommand("update Temsilci set Tipdurum=2 WHERE ID=" + DDLisSec.SelectedValue + " and Tipdurum=1 and personel_id=" + Session["UserID"], con);

                cmd2.ExecuteNonQuery();

                OleDbCommand cmd4 = new OleDbCommand("update GorevHareket set EndTime='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' WHERE Tip=1 AND EndTime IS NULL AND GorevId=" + DDLisSec.SelectedValue + " AND SessionId=" + Session["UserID"], con);

                cmd4.ExecuteNonQuery();

                OleDbCommand cmd5 = new OleDbCommand("INSERT INTO GorevHareket(GorevId,Tip,StartTime,SessionId) SELECT Temsilci.ID,'2' AS Tip,'" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' AS StartTime,Temsilci.personel_id From Temsilci where Tipdurum=2 AND ID=" + DDLisSec.SelectedValue + " AND Temsilci.personel_id=" + Session["UserID"], con);

                cmd5.ExecuteNonQuery();

                OleDbCommand cmd3 = new OleDbCommand("update Temsilci set is_durum='Beklemede' where ID="+DDLisSec.SelectedValue+" and personel_id="+Session["UserID"], con);

                cmd3.ExecuteNonQuery();



                //OleDbCommand cmd2 = new OleDbCommand("Select top 1 * from Gorevler where kull_id="+Session["UserID"]+" Order by ID DESC",con);

                //OleDbDataReader dr = cmd2.ExecuteReader();

                //while (dr.Read())

                //{

                //    Response.Write(dr["is_tanimi"].ToString());

                //}

            }

            else if (rbtnBitir.Checked == true)

            {

                OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

                cmd.ExecuteNonQuery();

                OleDbCommand cmd2 = new OleDbCommand("UPDATE Gorevler SET bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "',is_durum='Bitti' where is_tanimi='" + DDLisSec.SelectedValue + "' and [ID] = (SELECT MAX([ID]) FROM Gorevler)", con);

                cmd2.ExecuteNonQuery();

                OleDbCommand cmd3 = new OleDbCommand("update Temsilci set is_durum='Bitti' where isin_adi='" + DDLisSec.SelectedValue + "'and personel_id=" + Session["UserID"], con);

                cmd3.ExecuteNonQuery();

            }

            else

            {

                OleDbCommand cmd = new OleDbCommand("update Personel set tiklama='false' where ID=" + Session["UserID"], con);

                cmd.ExecuteNonQuery();

                OleDbCommand cmd2 = new OleDbCommand("update Gorevler set bitis_zamani='" + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss") + "' where kull_id=" + Session["UserID"], con);

                cmd2.ExecuteNonQuery();

            }

            rbtnBekle.Visible = false;

            rbtnBitir.Visible = false;



            con.Close();



    }   

















}