2005/11/09 18:32:11, Event:Access, Door:entry, Card No.:2727, Description:266:55748
Date time event door card no. description
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
2005/11/09 18:32:11, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2005/11/09 18:32:13, Event:Access, Door:entry, Card No.:2345, Description:266:55732
2006/01/11 14:07:02, Event:Cover Removed Return to Normal, Door:5
2006/01/11 14:07:02, Event:Cover Removed Alarm, Door:5
2005/11/09 18:33:58, Event:Card No. Error, Door:entry, Description:266:51792
2006/01/11 14:07:02, Event:Cover Removed Return to Normal, Door:5
2006/01/24 17:31:27, Eventownload Success!
2006/01/11 14:07:02, Event:Cover Removed Alarm, Door:5
2005/11/09 18:32:21, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2006/01/24 17:31:27, Eventownload Success!
2005/11/09 18:33:58, Event:Card No. Error, Door:entry, Description:266:51792
2006/01/11 14:07:02, Event:Cover Removed Return to Normal, Door:5
2006/01/11 14:07:02, Event:Cover Removed Alarm, Door:5
2005/11/09 18:34:00, Event:Card No. Error, Door:entry, Description:266:51792
2006/01/24 17:31:27, Eventownload Success!
2005/11/09 18:33:20, Event:Card No. Error, Door:entry, Description:266:51792
2005/11/09 18:32:36, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2005/11/09 18:32:46, Event:Access, Door:entry, Card No.:2727, Description:266:55748
2005/11/09 18:32:54, Event:Card No. Error, Door:entry, Description:266:51792
2005/11/09 18:33:42, Event:Card No. Error, Door:entry, Description:266:51792
2005/11/09 18:34:36, Event:Card No. Error, Door:entry, Description:266:51792
hello everyone... can you please help me with my problem, i have a project time and attendance system using a reader which the reader creates a textlog. want i want to do is to save only the row that have a format above, and those row that mismatch the format discard it.. i already know how to save it in database, i just want to validate the saving..hope you can help me
Dim inFile As Integer
Dim InLine As String
Dim data() As String
Dim fields As Integer
Dim sFile As String
sFile = txtFile.Text
inFile = FreeFile
Open sFile For Input As #inFile
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & "SERVER=localhost;" & " DATABASE=time_and_attendance;" & "UID=root;PWD=admin; OPTION=3"
While Not EOF(inFile)
Line Input #inFile, InLine
data() = Split(InLine)
fields = UBound(data())
On Error GoTo badfile
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber, Description) " & _
"VALUES ('" & data(0) & "', " & _
"' " & Left(data(1), 8) & "', " & _
"' " & Mid(data(2), 7) & "', " & _
"' " & Card(data(5)) & "', " & _
"' " & data(6) & "')"
Wend
MsgBox "Record successfully saved ", vbInformation + vbOKOnly, "Record Saved"
Unload Me
Me.Show
Close #inFile
Exit Sub
badfile:
MsgBox "Error type of file ", vbCritical + vbOKOnly, "Error"
or if somebody help me on how to load the textfile in listbox, same validation ingnore the data that not match the format above....
========================code to open the textfile in list box, just help to validate the format above=======================
Dim sFile As String
Dim inFile As Integer
Dim sTemp As String
Open sFile For Input As inFile
While Not EOF(inFile)
Line Input #inFile, sTemp
olist.AddItem sTemp
Wend
Close inFile
royaloba 0 Junior Poster in Training
Yomet 1 Junior Poster
royaloba,
In order to parse out the data you do not want I suggest validating the input line before splitting it into the data() array.
Assuming that you only want the "Access" events in your database I would change your code to the following:
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
Data() = Split(InLine)
Fields = UBound(Data())
On Error GoTo badfile
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber, Description) " & _
"VALUES ('" & Data(0) & "', " & _
"' " & Left(Data(1), 8) & "', " & _
"' " & Mid(Data(2), 7) & "', " & _
"' " & Card(Data(5)) & "', " & _
"' " & Data(6) & "')"
End If
WendYou should now be able to change the condition to include whatever events you want, i.e. "Card No. Error" or others, by simply adding conditions to the "If ... Then" statement.
Similarly you can screen out certain doors by using the same technique,
i.e. If InStr(InLine, "Door:entry") > 0 Then Hope this helps
Yomet
royaloba 0 Junior Poster in Training
sir jomet,
hello sir, i try it already... and it is working...thanks.
Yomet 1 Junior Poster
hello sir it's me again.... i finish the validation on saving the textlog... i'm adding a new
field in the Table Its TimeOut here's the senario sir i want to put the first time in fields
TIMEIN, Then last record will be for TIMEOUT..base on their card number! did you get me sir??
i already try it sir, but the problem when i save it, the output is like this...Date TimeIn Event Door CardNumber TimeOut
2005/11/24 09:32:42, Access, Door:entry, 2727 9:33:30
2005/11/24 09:33:30, Access, Door:entry, 2727
2005/11/24 09:33:42, Access, Door:entry, 2727
2005/11/24 09:34:04, Access, Door:entry, 2727
2005/11/24 09:42:05, Access, Door:entry, 2727
2005/11/24 09:51:13, Access, Door:entry, 2345
2005/11/24 09:51:19, Access, Door:entry, 2345
2005/11/24 09:51:26, Access, Door:entry, 2345
2005/11/24 09:51:37, Access, Door:entry, 2345
2005/11/24 09:51:47, Access, Door:entry, 2345
2005/11/24 09:55:25, Access, Door:entry, 2727
2005/11/24 16:30:50, Access, Door:entry, 2345
this is the output i want sir
Date TimeIn Event Door CardNumber TimeOut
2005/11/24 09:33:42 Access, Door:entry, 2727 9:55:25
2005/11/24 09:51:19 Acesss, Door:entry, 2345 16:30:50
i want to save only the first swipe of the card(first us timein) and the last swipe of the
card(last us TimetOut). i keep on asking if it is possible, cause i'm in stage of getting
familiar with vb not in the stage of a senior or junior programmer... i hope you can help me
sir..
or if you have a other way to do it sir...by the way again thanks to the previous code you gave
me..it's working well sir..;)
royaloba,
As I said in my answer to your PM I am posting your question and my answer here for everybody to profit from the information exchanged.
Without giving you the code I will give you the steps I would take to do this.
1) Every time you read a log line you need to see if that card has already been used during that day
HINT use a SELECT statement with date and card number in the WHERE clause
2) If the card has not been used then you need to add an entry into your table with the card number, date and time as Time In - this is the code you already have.
3) If the card has already been used you need to update the table with the current time as Time Out - this is the code you need to create
HINT use an UPDATE statement
Hope this helps and please tell me how it works out
Yomet
Comatose 290 Taboo Programmer Team Colleague
I want to give a thumbs up to Yomet here.... Keep up the good work.
royaloba 0 Junior Poster in Training
sir jomet,
hello sir, sorry but i cannot do it, without your sample code sir.. would you please provide sir... because i didn't know where to put the select case..thanks sir
Yomet 1 Junior Poster
Hi again royaloba,
When I said a SELECT statement I did not mean a SELECT CASE statement but rather a SQL SELECT statement.
Here is the gist of what I would do, this might not work correctly in your program but with minor tweaks it should.
'You will need to add the following declaration
Dim rs As RecordsetWhile Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
Data() = Split(InLine)
Fields = UBound(Data())
On Error GoTo badfile
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblattendance WHERE [Date] = '" & Data(0) & "' AND CardNumber = '" & Data(5) & "'")
If rs.EOF Then 'No data returned, i.e. first time this card is used
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber, Description) " & _
"VALUES ('" & Data(0) & "', " & _
"' " & Left(Data(1), 8) & "', " & _
"' " & Mid(Data(2), 7) & "', " & _
"' " & Card(Data(5)) & "', " & _
"' " & Data(6) & "')"
Else 'Data has been returned, i.e. the card has already been used today
conn.Execute "UPDATE tblattendance SET TimeOut = '" & Left(Data(1), 8 & "'")
End If
rs.Close
Set rs = Nothing
End If
WendHope this code sheds some light on the solution to your problem.
Happy coding
Yomet
royaloba 0 Junior Poster in Training
hello sir jomet its me again.. still i can't solve my problem before.. please help. this is regarding with the timein timeout issue that i tell you before i include the program so you could tell where the problem is..please help me..i will also include the mysql database
username=admin
pasword=admin
Yomet 1 Junior Poster
Hi royaloba,
Sorry to hear that things are not working out. I would like to know what is going wrong. Just saying "It's not working" does not help me a lot since there are a million things that could be not working. For instance, I found a syntax error in your UPDATE statement, you coded "UPDATE INTO" whereas the correct syntax is
UPDATE <tablename>
SET <field> = <value>[, <field> = <value>[,...]]
[WHERE <where clause>]so if your error is a syntax error that might be your problem.
I made an error, that you corrected nicely, by assuming you were using an Access database, thanks for not getting me into trouble... ;)
I do not have MySQL installed on my computer so I cannot run your program, it gives me an error about the ODBC data source not being defined etc.
If you could give me a better description of what is going wrong it would be very helpful.
Thanks
Yomet
P.S. My nick is spelled with "Y" not "J"... ;)
royaloba 0 Junior Poster in Training
Code:
---------
'You will need to add the following declaration
Dim rs As Recordset
---------
Code:
---------
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
Data() = Split(InLine)
Fields = UBound(Data())
On Error GoTo badfile
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblattendance
WHERE [Date] = '" & Data(0) & "' AND CardNumber = '" & Data(5) & "'")
If rs.EOF Then 'No data returned, i.e. first time this card is
used
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event,
CardNumber, Description) " & _
"VALUES ('" & Data(0) & "', " & _
"' " & Left(Data(1), 8) & "', " & _
"' " & Mid(Data(2), 7) & "', " & _
"' " & Card(Data(5)) & "', " & _
"' " & Data(6) & "')"
Else 'Data has been returned, i.e. the card has already been
used today
conn.Execute "UPDATE tblattendance SET TimeOut = '" &
Left(Data(1), 8 & "'")
End If
rs.Close
Set rs = Nothing
End If
Wend
---------
sir YOMET,
hello sir that is the sample code that you given to me, before...my problem is the set rs=currentdb.OpenRecordset is not working with mysql database. i just modify your code into
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())
'On Error GoTo badfile
Set rs = New ADODB.Recordset
squery = "": squery = ("Select * from tblattendance where Date='" & data(0) & "' AND CardNumber = '" & data(5) & "'")
Call ExecuteCommand
If rs.EOF Then ' NO DATA RETURNED
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
"VALUES ('" & data(0) & "', " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Mid(data(2), 7) & "', " & _
"'" & Card(data(5)) & "')"
Else ' DATA HAS BEEN USED
conn.Execute "UPDATE tblattendance set TimeOut='" & Left(data(1), 8 & "'")
End If
rs.Close
Set rs = Nothing
End If
but still the output is the same... see the sample pic that i attach...
royaloba 0 Junior Poster in Training
sir yomet
hello sir i have some good news for you, i was able to set the TIMEOUT, but there is few error i want to fix..
SAMPLE OUTPUT mysql
--------------------------------------------------------------------------------
Date TimeIn Event CardNumber TimeOut
--------------------------------------------------------------------------------
2002-05-02 | 21:30:12 | Access | 451 | 21:32:09 |
2002-05-02 | 21:30:12 | Access | 451 | 21:32:09 |
2002-05-02 | 21:30:22 | Access | 451 | 21:32:09 |
2002-05-02 | 21:30:48 | Access | 546 | 21:32:09 |
2002-05-02 | 21:30:55 | Access | 546 | 21:32:09 |
2002-05-02 | 21:31:19 | Access | 546 | 21:32:09 |
2002-05-02 | 21:31:59 | Access | 234 | 21:32:09 |
2002-05-02 | 21:32:06 | Access | 234 | 21:32:09 |
2002-05-02 | 21:32:08 | Access | 234 | 21:32:09 |
2002-05-02 | 21:32:09 | Access | 234 | 21:32:09 |
---------------------------------------------------------------------------------
i just revise some of your sample code sir... as you see in the output i
the code read only the last TimeIn... the right output shoul be like this
--------------------------------------------------------------------------------
Date TimeIn Event CardNumber TimeOut
--------------------------------------------------------------------------------
2002-05-02 | 21:30:12 | Access | 451 | 21:32:22 |
2002-05-02 | 21:30:48 | Access | 546 | 21:31:19 |
2002-05-02 | 21:31:59 | Access | 234 | 21:32:09 |
--------------------------------------------------------------------------------
read only the first TimeIn and the LAST record and put this as TimeOut Base on their CARDNUMBER and the DATE...i need your suggestions sir...thanks..and if you can provide me a bit of code...thanks
THE CODE I USE SIR
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & "SERVER=localhost;" & " DATABASE=time_and_attendance;" & "UID=root;PWD=admin; OPTION=3"
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
"VALUES ('" & data(0) & "', " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Mid(data(2), 7) & "', " & _
"'" & Card(data(5)) & "')"
Set rs = New ADODB.Recordset
squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
Call ExecuteCommand
If rs.EOF Then ' DATA HAS BEEN USED
conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'"
End If
rs.Close
Set rs = Nothing
End If
Yomet 1 Junior Poster
royaloba,
I can see where you code is going wrong and there are two quite simple errors.
1) What you do is adding a record every time you read a line wihtout first verifying if that card has already been used today.
2) The UPDATE statement later on will update all the records - not only the ones for that specific card and day. This error comes from my own code so I take full responsibility for it, I forgot to add the WHERE clause in the code I gave you.
Using your own code I will show you what I am talking about and how to correct it.
BTW, please use the code tags like this
[ code ]
Here goes all your code.
Please remove the spaces between the square brackets "[" and "]" and the "code" and "/code" parts
[ /code ]
OK, here goes
Set conn = New ADODB.Connection
conn.Open "DRIVER={MySQL ODBC 3.51 Driver};" & "SERVER=localhost;" & " DATABASE=time_and_attendance;" & "UID=root;PWD=admin; OPTION=3"
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())
'**********************
'At this point you have fresh data in your array and what you
'WANT to do is verify if the current card, in Card(data(5)), has
'already been used today.
'What you need here is a condition to see if the card has already
'been used. Actually the IF statement you use later is almost there, i.e.
Set rs = New ADODB.Recordset
squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
Call ExecuteCommand
'If rs.EOF Then ' DATA HAS BEEN USED
If NOT rs.EOF Then 'Data has NOT been used so insert it.
'**********************
conn.Execute "INSERT INTO tblattendance (Date, TimeIn, Event, CardNumber) " & _
"VALUES ('" & data(0) & "', " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Mid(data(2), 7) & "', " & _
"'" & Card(data(5)) & "')"
'**********************
'Now you have inserted the data into your database since it was
'not there to begin with.
'However, if the data was already in the table you need to update
'the TimeOut with the current time. So we scrap the If statement
'(since we used it above) and use the else clause.
Else
'**********************
'Set rs = New ADODB.Recordset
'squery = "": squery = "Select * from tblattendance where Date='" & 'data(0) & "' and CardNumber='" & data(5) & "'"
'Call ExecuteCommand
'If rs.EOF Then ' DATA HAS BEEN USED
'conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'"
'**********************
'The new UPDATE statement
conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "'" & _
WHERE Date='" & 'data(0) & "' and CardNumber='" & data(5) & "'"
'**********************
End If
rs.Close
Set rs = Nothing
End IfSeeing my last answer I would like to say sorry for misleading you, I was tired and didn't read the code correctly, there was no syntax error, just my eyes seeing double... :o
As far as I can see this should do the trick. Try it out and tell me how it works.
Happy coding
Yomet
royaloba 0 Junior Poster in Training
hello sir Yomet
first thanks for helping me with this project
the problem is when i put the "if not rs.eof then" the return record in the data base in empty... and when i replace it with "if rs.eof" the ouput for all the timeout is the same..sir i attach a sample program, i convert it to access so you could determine where is the problem occurs..thanks sir..
Yomet 1 Junior Poster
royaloba,
Thanks for taking the time to convert your database into Access, it helped.
Now for what I found:
1) There seems to be some problem with the "ExecuteCommand" sub since it never gives back a recordset with records in it. However when I replaced that line with rs.Open squery, conn it gave the expected results.
2) I had to revert to your original code for inserting and updating records by using conn.Execute instead of db.Execute because when using the mix between DAO and ADODB the record was not saved by DAO before the ADODB went to find it. Weird I know but by using the following code everything went well.
Open sFile For Input As #inFile
' Set db = DBEngine.OpenDatabase(App.Path & "\db1.mdb")
While Not EOF(inFile)
Line Input #inFile, InLine
If InStr(InLine, "Event:Access") > 0 Then
data() = Split(InLine)
fields = UBound(data())
Set rs = New ADODB.Recordset
squery = "": squery = "Select * from tblattendance where Date=#" & data(0) & "# AND CardNumber='" & Card(data(5)) & "'"
' Call ExecuteCommand
rs.Open squery, conn
If rs.EOF Then
conn.Execute "INSERT INTO tblattendance ([Date], [TimeIn], CardNumber) " & _
"VALUES (#" & data(0) & "#, " & _
"'" & Left(data(1), 8) & "', " & _
"'" & Card(data(5)) & "')"
'Set rs = New ADODB.Recordset
'squery = "": squery = "Select * from tblattendance where Date='" & data(0) & "' and CardNumber='" & data(5) & "'"
'Call ExecuteCommand
'If rs.EOF Then ' DATA HAS BEEN USED
Else
conn.Execute "UPDATE tblattendance set TimeOut = '" & Left(data(1), 8) & "' where Date=#" & data(0) & "# AND CardNumber='" & Card(data(5)) & "'"
End If
rs.Close
Set rs = Nothing
End If
Wend
MsgBox "Record successfully saved ", vbInformation + vbOKOnly, "Record Saved"
Unload Me
Me.Show
Close #inFile
Exit SubYou were correct in using If rs.EOF instead of If Not rs.EOF , typical brain-fart on my behalf.
Hope this code works and keep up the good work.
Yomet
royaloba 0 Junior Poster in Training
Sir Yomet,
thank you very much it's working very well i just replace the # because it's not valid in mysql... i can now move on to my project...
again THANKS;)
Yomet 1 Junior Poster
royaloba,
I am happy that things worked out well.
Good luck with the rest of the project
Happy coding
Yomet
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.