locked
Verify the data RRS feed

  • Question

  • User-401818107 posted

    I have a time program, in which a line of code has errors and I need your professional help!

    In my database, I have five Columns, ID, IN, Lunch_OUT, Lunch_IN, End_Work.

    If Lunch_OUT and END_WOrk is blank, the next day when the user click in, the system will display a message tellin hime the that the field is empty.

    Below is the code and error message: 

    strSQL= " select * from clock  where IDEmployee= '" & currEmpNumber & "'  order by serverdate DESC LIMIT 1"

    call OpenDB()              
     Dim objDR1 as OdbcDataReader
    Dim Cmd1 as New odbcCommand(strSQL, connTemp)
    objDR1=Cmd1.ExecuteReader(system.data.CommandBehavior.CloseConnection)
        
              
            If (objDR1.Read()) Then


             If ((objDR1.GetString("End_work") + "") = "") then

                    labelMessage2.text = " Your last record is missing'"
                End If
                   end if
     Call CloseDB()


    Error: If ((objDR1.GetString("End_work") + "") = "") then
    Line 93:
    Line 94:                 labelMessage2.text = " Your last record is missing'"

     Appreciat it and thanks in advance! 

    Monday, February 15, 2010 1:25 PM

All replies

  • User-1161841047 posted

    Try this way:

        If String.IsNullOrEmpty(objDR1("Lunch_OUT")) And String.IsNullOrEmpty(objDR1("END_WOrk")) Then
    
         End If



    Monday, February 15, 2010 2:27 PM
  • User-401818107 posted

    Thank you for your help!

    I have the error below:

     

     'IsNullOrEmpty' is not a member of 'String'.

    If String.IsNullOrEmpty(objDR1("Lunch_OUT")) And String.IsNullOrEmpty(objDR1("END_WOrk")) Then  

     

    Thank you!


     

    Monday, February 15, 2010 2:56 PM
  • User-1161841047 posted

    IsNullOrEmpty Method it's new in the .NET Framework version 2.0, may be you are using previous release.


    of you can use :

     If objDR1("Lunch_OUT") = Nothing And objDR1("END_WOrk") = Nothing Then
    
      End If



    Monday, February 15, 2010 3:07 PM
  • User-401818107 posted

    Thank you so much for your quick response!  You are my hero!!  I have been stuck there for weeks! Yes, there is no error now. But the fields for Lunch_out and End_day are empty and the message " Your last record is missing" didn't show up at all. Could you please take a look at the code below and advise. Appreciate  for your great help!

     

    Sub Clock_IN(sender as Object, e as EventArgs)      
               


                     
                Dim currEmpNumber As String = employeeID.Text

                      currEmpNumber = checkUserInput(currEmpNumber.Trim())
                     

           strSQL="Select * from Employee WHERE  IDEmployee= '" & currEmpNumber & "'"


         
                                              call openDB()
                                              
                                              Dim objDR as OdbcDataReader
                                              Dim Cmd as New odbcCommand(strSQL, connTemp)
               
                                              objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)                                   
                                             
                                             
                                              call CloseDB()
                                             
         
               
                strSQL="Select IDEmployee from Employee WHERE  IDEmployee= '" & currEmpNumber & "' "           
                                     
               
                call openDB()
               
                Dim myCommand1 As New odbcCommand(strSQL, connTemp)
                      currEmpID = myCommand1.ExecuteScalar()
                      If currEmpID = 0 Then
                             labelMessage1.text = "Wrong ID! Please try again!"                            
                      
               

    else      

    strSQL= " select * from clock  where IDEmployee= '" & currEmpNumber & "'  order by serverdate DESC LIMIT 1"

    call OpenDB()              
     Dim objDR1 as OdbcDataReader
    Dim Cmd1 as New odbcCommand(strSQL, connTemp)
    objDR1=Cmd1.ExecuteReader(system.data.CommandBehavior.CloseConnection)
        
              
            If (objDR1.Read()) Then

    If objDR1("Lunch_OUT") = Nothing And objDR1("END_WOrk") = Nothing Then 


                    labelMessage2.text = " Your last record is missing'"
                End If
                   end if
     Call CloseDB()    
              


     strSQL="Select count(*) from Clock WHERE  IDEmployee= '" & currEmpNumber & "' and serverDate = '" & currServerDate & "'"      
    call OpenDB()                                                                                   
          Dim myCommand3 As New odbcCommand(strSQL, connTemp)
          myCountEmployee = myCommand3.ExecuteScalar()                                    
            Call CloseDB()        
                If myCountEmployee = 0
                  strSQL=      "INSERT INTO Clock '-----------------insert the data into the database ---------------------'
                     
     
                            call OpenDB()
                                  call InsertUpdate(strSQL)
                         call CloseDB()     

     

                                  labelMessage.text ="Good Morning! "
                                 
                                  else
                           
                           
         
                            If myCountEmployee > 0 Then                 
     
        labelMessage1.text ="Error! You have already clocked-in! "

        labelMessage.visible=false                       

                           
                           
                     
               
     
      End if
       
       End if
          End if      
      End Sub

     

     

    Monday, February 15, 2010 3:36 PM
  • User-401818107 posted

    Thank you! I have figuered it out! 

    Monday, February 15, 2010 3:49 PM
  • User-401818107 posted

    Hi contributor,

    I come back again for this. I modified only one line of code since only one of the them could be blank:

     If objDR1("Lunch_OUT") = Nothing or objDR1("END_WOrk") = Nothing

    After further testing, even though the user has no blank data, all are clock out, the system still shows the messte "Your last record is missing". How to fix it. Thank you!

    Tuesday, February 16, 2010 10:48 AM
  • User-1161841047 posted

    Hi contributor,

    I come back again for this. I modified only one line of code since only one of the them could be blank:

     If objDR1("Lunch_OUT") = Nothing or objDR1("END_WOrk") = Nothing

    After further testing, even though the user has no blank data, all are clock out, the system still shows the messte "Your last record is missing". How to fix it. Thank you!

    Sorry to be late.

    with the last code if one or both of them is null (Nothing in VB)  will show the message, so check the values by Tracing or assign the values to variables or label for testing the value :

    String Lunch_OUT=objDR1("Lunch_OUT")

    lblLunch_OUT.Text=Lunch_OUT


    Wednesday, February 17, 2010 2:38 AM
  • User-401818107 posted

    Thank you contributor! But after testing, the label shows 12:37:34 PM every time. I checked my database and found it is the time of Lunch_out for 12/31/2009. How to fix it? Appreciat it!!

    Wednesday, February 17, 2010 11:50 AM
  • User-1161841047 posted

    Is IDEmployee the primary key of clock table? if it's Primary key you don't need to use Order by in your query :

    strSQL= " select * from clock  where IDEmployee= '" & currEmpNumber & "'  order by serverdate DESC LIMIT 1"

    just use :

    strSQL= " select * from clock  where IDEmployee= '" & currEmpNumber & "'"

    Am not sure why you used that way !

    Wednesday, February 17, 2010 2:55 PM
  • User-401818107 posted

    Hi, Star! Thank you for your time! But it still does not work. It picks the data of 3/4/2009 and I don't know why this time it retrieved the record in the middle.

    Wednesday, February 17, 2010 3:24 PM
  • User-1161841047 posted

    May know I understand more about your issue.IDEmployee is a foreign key in table clock.

    You said before :

    But after testing, the label shows 12:37:34 PM every time. I checked my database and found it is the time of Lunch_out for 12/31/2009. How to fix it? Appreciat it!!

    is the date 12/31/2009  the last date?? what it should appear or return?

    Wednesday, February 17, 2010 4:11 PM
  • User-401818107 posted

    Thank you! 12/31/2009 is not the last date in my system. Actually yesterday 2/16/2010 is the last date.  I want to select the last date and then check to verify if there is data missing in OUT_LUNCH and END_WORK.  Also when I set up the database, the data type for server date is varchar, my bad. So do I have to convert the serverdate from varchar to date and then do the select statment? And how? Appreciate it!!

    Wednesday, February 17, 2010 5:06 PM