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

Question

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 + ') ; //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 + ') ; 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 
za ovog radnika' ; textBox1.Text = '' ; } else { //rest code of app....

Rafnas T P · 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 P · Answer

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

Goran Bibic · Answer

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 
 " + DateTime.Now.ToString( "dd.MM.yyyy HH:mm:ss" ); readerresultTextbox.Text = (reader[ "ime_prezime" ].ToString()); } }

Goran Bibic · Answer

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 P · Answer

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 Bibic · Answer

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 P · Answer

check column name in your sql table , the column should match with that. check all the column names in that select query

Goran Bibic · Answer

Error is invalid column name ime_prezime

Rafnas T P · Answer

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

Goran Bibic · Answer

Code updated: Error is: An expresion of non-boolean type specified in a context where a contition expected.near ')'. Incorect sintax '

Rafnas T P · Answer

can you update your code and tell me what is the error you are getting

Goran Bibic · Answer

I am not solve the problem I describe errrors 29 and 45 line

Rafnas T P · Answer

hi, did you solve that

Goran Bibic · Answer

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 + " ; 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 + " ; 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 
za 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 
 " + 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 
 " + 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 
 " + 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! 
Proverite 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 Bibic · Answer

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

Rafnas T P · Answer

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

Goran Bibic · Answer

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 P · Answer

in line no 19 is that if condition required, if no then remove that

Rafnas T P · Answer

yes, that correct

Goran Bibic · Answer

I am post code...chek line form 18 to 35

Rafnas T P · Answer

can you tell me how are you saving time in database, is it string or in time format or datetime

Goran Bibic · Answer

From 18 to 35 lines must put inside...is this right way?

Rafnas T P · Answer

ok. now what happening with this code, is both select query working fine

Goran Bibic · Answer

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 P · Answer

check this query howmany rows are returning SELECT (DATEDIFF(SECOND, vrijemeprijave, GETDATE()) / 60) from dbo.prijava_radnika where ime_prezime= (" + queryString + ")

Rafnas T P · Answer

describe what you need exactly

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

Answers (26)