Case when if is time sequence 5 minutes from sql c#

Goran Bibic

8y1kLearn iOS Programming

If worker come to company 5 minutes and

if (Int32.Parse(queryString) != 0)
{
//provjeravamo da li se radnik mozda prijavio prije 5 minuta, samim tim nema pravo na odjavu a ni na novu prijavu
string commanda = "SELECT CASE WHEN (SELECT FLOOR(TIME_TO_SEC(TIMEDIFF(now(),max(VrijemeDolaska)))/60) from prijava_radnika where IdPosjetioc=" + osobaId + ") < 5 THEN 1 ELSE 0 END;";
//MessageBox.Show(commanda);
int broj = Int32.Parse.Execute(commanda));
commanda = "SELECT CASE WHEN (SELECT FLOOR(TIME_TO_SEC(TIMEDIFF(now(),max(VrijemeOdlaska)))/60) from prijava_radnika where IdPosjetioc=" + osobaId + ") < 5 THEN 1 ELSE 0 END;";
int broj2 = Int32.Parse.Execute(commanda));
//MessageBox.Show(broj.ToString());
if (broj == 1 || broj2 == 1)
{
notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
messageLabel.Text = "Prijava i odjava su blokirani 5 minuta \nza ovog radnika";
textBox1.Text = "";
}
else
{ //rest code of app....

26 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rafnas T P8y ago
Accepted answer
remove line no 25,26 then add this line there
int broj = Convert.ToInt32(command2.ExecuteScalar());
and remove line no 32,33 then add below lines there
int broj2 = Convert.ToInt32(command4.ExecuteScalar());
Rafnas T P8y ago
check this query howmany rows are returning

SELECT (DATEDIFF(SECOND, vrijemeprijave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime= (" + queryString + ")
Goran Bibic8y ago
string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";

When put in textbox1 bar code exp.106 string have just 1 value from sql Worker name. That worker have value 106...only one
Rafnas T P8y ago
check this query in line 10 in sql how many rows are coming

string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";

otherwise change this to

string queryString = "SELECT top 1 [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";
Goran Bibic8y ago
Column name solved...e
error is...

,,SUBQUERY returned more then 1 value. This is not permited when the subquery folows =,!=,<,<=,>,>=or when te subquery is used as an expression... ,,
Rafnas T P8y ago
check column name in your sql table , the column should match with that. check all the column names in that select query
Goran Bibic8y ago
Error is invalid column name ime_prezime
Rafnas T P8y ago
try these
remove line no 22 and add below code
string commanda = "select case when (SELECT (DATEDIFF(SECOND, vrijemeprijave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime= (" + queryString + ")) < 5 THEN 1 ELSE 0 END";
same way remove line 29 and add below code
string commanda2 = "select case when (SELECT (DATEDIFF(SECOND, vrijemeodjave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime= (" + queryString + ")) < 5 THEN 1 ELSE 0 END";
Goran Bibic8y ago
Code updated:

Error is:

An expresion of non-boolean type specified in a context where a contition expected.near ')'. Incorect sintax '<'
Rafnas T P8y ago
can you update your code and tell me what is the error you are getting
Goran Bibic8y ago
I am not solve the problem
I describe errrors 29 and 45 line
Rafnas T P8y ago
hi,
did you solve that
Goran Bibic8y ago
New line is from 19 to 45

Error is 19 or 22 or 29...
private void prijavaAction()  //04.04. final doradjena perfect
       {

           {
               SqlConnection con = new SqlConnection(cs);

               if (textBox1.Text.All(char.IsDigit))

               {
                   string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
                   using (SqlConnection connection = new SqlConnection(cs))
                   {
                       SqlCommand command = new SqlCommand(queryString, connection);
                       connection.Open();
                       SqlDataReader reader = command.ExecuteReader();

             ///////////////////////////////////////////dodatak za prijavu 5 minuta

                      // if (Int32.Parse(queryString) != 0)
                          // {

                           string commanda = "SELECT CASE WHEN (DATEDIFF(SECOND, vrijemeprijave, GETDATE()) / 60)) from dbo.prijava_radnika where ime_prezime=" + queryString + " < 5 THEN 1 ELSE 0 END;";
                           SqlConnection connection3 = new SqlConnection(cs);
                           SqlCommand command2 = new SqlCommand(commanda, connection3);
                           connection3.Open();
                          // SqlDataReader reader3 = command2.ExecuteReader();
                           int broj = Convert.ToInt32(command2.ExecuteScalar());

                           string commanda2 = "SELECT CASE WHEN (DATEDIFF(SECOND, vrijemeodjave, GETDATE()) / 60)) from dbo.prijava_radnika where ime_prezime=" + queryString + " < 5 THEN 1 ELSE 0 END;";
                           SqlConnection connection4 = new SqlConnection(cs);
                           SqlCommand command4 = new SqlCommand(queryString, connection4);
                           connection4.Open();
                          // SqlDataReader reader4 = command4.ExecuteReader();
                           int broj2 = Convert.ToInt32(command4.ExecuteScalar());

                           if (broj == 1 || broj2 == 1)
                           {
                               notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                               messageLabel.Text = "Prijava i odjava su blokirani 5 minuta \nza ovog radnika";
                               textBox1.Text = "";
                           }
                           else
                          {

            ///////////////////////////////////dodatak za prijavu 5 minuta


                        try
                       {
                           if (reader.Read())  //radi
                                               //while (reader.Read())  //radi
                           {

                               notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
                               messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                               readerresultTextbox.Text = (reader["ime_prezime"].ToString());


                               string prijava = "SELECT count(*) from dbo.prijava_radnika where vrijemeprijave is not null and vrijemeodjave is null and radnik = '" + readerresultTextbox.Text + "' and redni_broj is NULL";
                               SqlCommand provjeraprijaveradnika = new SqlCommand(prijava, con);
                               con.Open();
                               int result = Convert.ToInt32(provjeraprijaveradnika.ExecuteScalar());
                               //con.Open();
                               con.Close();
                               if ((result == 0)) //radi
                                                  //if ((prijava !))
                               {
                                   String saveStaff = "INSERT INTO dbo.prijava_radnika (vrijemeprijave, status, radnik,idfakture) VALUES(@vrijemeprijave,'prijavljen', '" + readerresultTextbox.Text + "',@idfakture)";
                                   using (SqlConnection openCon2 = new SqlConnection(cs))
                                   {
                                       using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
                                       {
                                           querySaveStaff.Connection = openCon2;
                                           querySaveStaff.Parameters.Add("@vrijemeprijave", SqlDbType.DateTime).Value = DateTime.Now;
                                           querySaveStaff.Parameters.Add("@idfakture", SqlDbType.Int).Value = id_fakture;
                                           openCon2.Open();
                                           querySaveStaff.ExecuteNonQuery();
                                           openCon2.Close();

                                           notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
                                           messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                           readerresultTextbox.Text = (reader["ime_prezime"].ToString());
                                       }
                                   }
                               }
                               else
                               {

                                   String updateStaff = " declare @maxNo integer = 0 select @maxNo = isnull(max(redni_broj), 0) from[dbo].[prijava_radnika] UPDATE[dbo].[prijava_radnika] SET"
                                       + " [vrijemeodjave] = @vrijemeodjave,[redni_broj] = (@maxNo+1),[status] = 'odjavljen' WHERE radnik = '" + readerresultTextbox.Text + "'and [status] = 'prijavljen'";



                                   //string queryString = "SELECT [ime_prezime] FROM dbo.radnici WHERE bar_kod = '" + textBox1.Text + "'";// pronaci radnika u bazi
                                   using (SqlConnection connection1 = new SqlConnection(cs))
                                   {
                                       SqlCommand command1 = new SqlCommand(queryString, connection1);
                                       connection1.Open();
                                       SqlDataReader reader1 = command1.ExecuteReader();
                                       connection.Close();
                                       try
                                       {
                                           if (reader1.Read())
                                           {


                                               using (SqlConnection openCon3 = new SqlConnection(cs))
                                               {
                                                   using (SqlCommand queryupdateStaff = new SqlCommand(updateStaff))
                                                   {
                                                       queryupdateStaff.Connection = openCon3;
                                                       queryupdateStaff.Parameters.Add("@vrijemeodjave", SqlDbType.DateTime).Value = DateTime.Now;
                                                       openCon3.Open();
                                                       queryupdateStaff.ExecuteNonQuery();
                                                       openCon3.Close();

                                                   }
                                               }

                                               notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FFE11A");
                                               messageLabel.Text = (reader1["ime_prezime"].ToString()) + " je odjavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                           }
                                       }
                                       catch { }


                                   }

                                   // Always call Close when done reading.

                                   reader.Close();
                               }

                           }
                           else
                           {
                               notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                               messageLabel.Text = "Radnik ne postoji u bazi! \nProverite vašu karticu ili probajte ponovo!";
                               textBox1.Text = "";


                           }
                       }
                       finally
                       {

                       }
                           }

                       //}dodatak za prijavu 5 minuta

                   }
               }

               else
               {
                   notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#FD7400");
                   messageLabel.Text = "Bar kod nije važeci ili nije bar kod";
                   textBox1.Text = "";
               }





           }
       }
Goran Bibic8y ago
Line 29 error
Incorect sintax near keyword 'SELECT'
Incorect sintax near ')'
string commanda2 = "select case when (select (DATEDIFF(SECOND, vrijemeodjave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime=" + queryString + ") < 5 THEN 1 ELSE 0 END";
Rafnas T P8y ago
same way remove line 29 and add below code
string commanda2 = "select case when (SELECT (DATEDIFF(SECOND, vrijemeodjave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime=" + queryString + ") < 5 THEN 1 ELSE 0 END";
Rafnas T P8y ago
remove line no 22 and add below code
string commanda = "select case when (SELECT (DATEDIFF(SECOND, vrijemeprijave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime=" + queryString + ") < 5 THEN 1 ELSE 0 END";
Goran Bibic8y ago
Error line 27

An expression of non-boolean type specified in a context where a condition is expected, near ')'.
int broj = Convert.ToInt32(command2.ExecuteScalar());
line 27
Rafnas T P8y ago
in line no 19 is that if condition required, if no then remove that
Rafnas T P8y ago
yes, that correct
Goran Bibic8y ago
Dattime insert into
String saveStaff = "INSERT INTO dbo.prijava_radnika (vrijemeprijave, status, radnik,idfakture) VALUES(@vrijemeprijave,'prijavljen', '" + readerresultTextbox.Text + "',@idfakture)";
                                    using (SqlConnection openCon2 = new SqlConnection(cs))
                                    {
                                        using (SqlCommand querySaveStaff = new SqlCommand(saveStaff))
                                        {
                                            querySaveStaff.Connection = openCon2;
                                            querySaveStaff.Parameters.Add("@vrijemeprijave", SqlDbType.DateTime).Value = DateTime.Now;
                                            querySaveStaff.Parameters.Add("@idfakture", SqlDbType.Int).Value = id_fakture;
                                            openCon2.Open();
                                            querySaveStaff.ExecuteNonQuery();
                                            openCon2.Close();

                                            notificationPanel.BackColor = System.Drawing.ColorTranslator.FromHtml("#2EFF36");
                                            messageLabel.Text = (reader["ime_prezime"].ToString()) + " je prijavljen u \n " + DateTime.Now.ToString("dd.MM.yyyy HH:mm:ss");
                                            readerresultTextbox.Text = (reader["ime_prezime"].ToString());
                                        }
                                    }
Goran Bibic8y ago
I am post code...chek line form 18 to 35
Rafnas T P8y ago
can you tell me how are you saving time in database, is it string or in time format or datetime
Goran Bibic8y ago
From 18 to 35 lines must put inside...is this right way?
Rafnas T P8y ago
ok. now what happening with this code,
is both select query working fine
Goran Bibic8y ago
I need to chek from sql column datetime and if is dattime < 5 minutes to write label messages...if is ok goint to reader resulttextbox and insert or update...Understand?
Rafnas T P8y ago
describe what you need exactly