Hi all,
I am using VB 5.0 and msaccess as a database, I am reading the data from logfiles and inserting it in db, its not throwing any error, but after excecution I not find any records in table Below is my code:
Public strfile As String
Dim newdate, predate, olddate As Date
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Public Sub Form_Load()
Set cn = New ADODB.Connectio n
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
cn.Open "DSN=MS Access Database12"
cn.Execute "delete from hitfile"
cn.Execute "delete from hitresult"
cn.Execute "delete from logfile"
cn.Execute "delete from result"
'checking date for validity
newdate = Date - 1
predate = DateAdd("m", -4, newdate)
olddate = predate
'generating the log file name
'------------------------------------------------------------
'when the from & to date of the period is entered by the user
While olddate <= newdate + 1
If olddate > newdate Then
Call resultfun
Else
strfile = "ex" & Format(olddate, "yy") & Format(olddate, "mm") & Format(olddate, "dd")
Call readfile
End If
olddate = olddate + 1
Wend
Unload Me
End Sub
Function resultfun()
qry = "select username,date1, count(username) from logfile group by username,date1"
rs.Open qry, cn
While Not rs.EOF
If rs.Fields(0).Va lue = "-" Then
rs.MoveNext
Else
If rs.Fields(0).Va lue <> "administra tor" And rs.Fields(0).Va lue <> "narendra" Then
cn.Execute "insert into result values('" & rs.Fields(1) & "','" & rs.Fields(0) & "','" & rs.Fields(0).Va lue & "@cisco.com '," & rs.Fields(2).Va lue & ")"
End If
rs.MoveNext
End If
Wend
Unload Me
End
End Function
Function readfile()
Dim obj As New Scripting.FileS ystemObject
Dim arr(11)
c = 0
strfilename = "c:\winnt\syste m32\logfiles\w3 svc1\" & strfile & ".log"
obj.GetFile strfilename
Set objtextstream = obj.OpenTextFil e(strfilename, ForReading)
Do While Not objtextstream.A tEndOfStream
strreadingline = objtextstream.R eadLine
'Excluding all the headings and field names from the log file
If strreadingline <> "" And InStr(1, strreadingline, "#") = 0 Then
'The login entry starts from line no 4
If (c > 3) Then
i = 1
From = 1
cnt = 0
temp1 = 1
posn = 0
'Each entry has got 11 fields
While (cnt < 11)
'Splitting each line of text into several fields based on the blank space
From = posn + 1
posn = InStr(From, strreadingline, " ")
If posn = 0 Then
posn = Len(strreadingl ine) - From + 1
arr(cnt) = Mid(strreadingl ine, From, posn)
Else
arr(cnt) = Mid(strreadingl ine, From, posn - From)
End If
cnt = cnt + 1
temp1 = From + 1
Wend
'Discard all the administrator entries
If IsDate(arr(0)) Then
On Error Resume Next
If arr(9) = 302 Then
'MsgBox arr(7)
On Error Resume Next
cn.Execute "insert into logfile values('" & arr(0) & "','" & arr(1) & "','" & arr(2) & "','" & arr(3) & "','" & arr(4) & "'," & arr(5) & ",'" & arr(6) & "','" & arr(7) & "'," & arr(9) & ",'" & arr(10) & "')"
End If
End If
If IsDate(arr(0)) And arr(3) <> "-" And arr(3) <> "administra tor" And arr(3) <> "narendra" And arr(3) <> "narenda" Then
a = arr(1)
'Extract the log entry for accessing some document
If (InStr(1, arr(7), ".") <> 0) And InStr(1, arr(7), "resources" ) = 0 And (InStr(1, arr(7), "Documents" ) <> 0 Or InStr(1, arr(7), "documents" ) <> 0) And InStr(1, arr(7), "gif") = 0 Then
On Error Resume Next
cn.Execute "insert into hitfile values('" & arr(0) & "','" & arr(2) & "','" & arr(3) & "','" & arr(4) & "','" & arr(6) & "','" & arr(7) & "'," & arr(9) & ",'" & arr(10) & "')" slash = InStr(26, arr(7), "/")
Docname = Mid(arr(7), slash + 1)
On Error Resume Next
cn.Execute "insert into hitresult values('" & arr(0) & "','" & arr(3) & "','" & Docname & "')"
End If
End If
End If
End If
c = c + 1
Loop
End Function
I am using VB 5.0 and msaccess as a database, I am reading the data from logfiles and inserting it in db, its not throwing any error, but after excecution I not find any records in table Below is my code:
Public strfile As String
Dim newdate, predate, olddate As Date
Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset
Public Sub Form_Load()
Set cn = New ADODB.Connectio n
Set rs = New ADODB.Recordset
Set rs1 = New ADODB.Recordset
cn.Open "DSN=MS Access Database12"
cn.Execute "delete from hitfile"
cn.Execute "delete from hitresult"
cn.Execute "delete from logfile"
cn.Execute "delete from result"
'checking date for validity
newdate = Date - 1
predate = DateAdd("m", -4, newdate)
olddate = predate
'generating the log file name
'------------------------------------------------------------
'when the from & to date of the period is entered by the user
While olddate <= newdate + 1
If olddate > newdate Then
Call resultfun
Else
strfile = "ex" & Format(olddate, "yy") & Format(olddate, "mm") & Format(olddate, "dd")
Call readfile
End If
olddate = olddate + 1
Wend
Unload Me
End Sub
Function resultfun()
qry = "select username,date1, count(username) from logfile group by username,date1"
rs.Open qry, cn
While Not rs.EOF
If rs.Fields(0).Va lue = "-" Then
rs.MoveNext
Else
If rs.Fields(0).Va lue <> "administra tor" And rs.Fields(0).Va lue <> "narendra" Then
cn.Execute "insert into result values('" & rs.Fields(1) & "','" & rs.Fields(0) & "','" & rs.Fields(0).Va lue & "@cisco.com '," & rs.Fields(2).Va lue & ")"
End If
rs.MoveNext
End If
Wend
Unload Me
End
End Function
Function readfile()
Dim obj As New Scripting.FileS ystemObject
Dim arr(11)
c = 0
strfilename = "c:\winnt\syste m32\logfiles\w3 svc1\" & strfile & ".log"
obj.GetFile strfilename
Set objtextstream = obj.OpenTextFil e(strfilename, ForReading)
Do While Not objtextstream.A tEndOfStream
strreadingline = objtextstream.R eadLine
'Excluding all the headings and field names from the log file
If strreadingline <> "" And InStr(1, strreadingline, "#") = 0 Then
'The login entry starts from line no 4
If (c > 3) Then
i = 1
From = 1
cnt = 0
temp1 = 1
posn = 0
'Each entry has got 11 fields
While (cnt < 11)
'Splitting each line of text into several fields based on the blank space
From = posn + 1
posn = InStr(From, strreadingline, " ")
If posn = 0 Then
posn = Len(strreadingl ine) - From + 1
arr(cnt) = Mid(strreadingl ine, From, posn)
Else
arr(cnt) = Mid(strreadingl ine, From, posn - From)
End If
cnt = cnt + 1
temp1 = From + 1
Wend
'Discard all the administrator entries
If IsDate(arr(0)) Then
On Error Resume Next
If arr(9) = 302 Then
'MsgBox arr(7)
On Error Resume Next
cn.Execute "insert into logfile values('" & arr(0) & "','" & arr(1) & "','" & arr(2) & "','" & arr(3) & "','" & arr(4) & "'," & arr(5) & ",'" & arr(6) & "','" & arr(7) & "'," & arr(9) & ",'" & arr(10) & "')"
End If
End If
If IsDate(arr(0)) And arr(3) <> "-" And arr(3) <> "administra tor" And arr(3) <> "narendra" And arr(3) <> "narenda" Then
a = arr(1)
'Extract the log entry for accessing some document
If (InStr(1, arr(7), ".") <> 0) And InStr(1, arr(7), "resources" ) = 0 And (InStr(1, arr(7), "Documents" ) <> 0 Or InStr(1, arr(7), "documents" ) <> 0) And InStr(1, arr(7), "gif") = 0 Then
On Error Resume Next
cn.Execute "insert into hitfile values('" & arr(0) & "','" & arr(2) & "','" & arr(3) & "','" & arr(4) & "','" & arr(6) & "','" & arr(7) & "'," & arr(9) & ",'" & arr(10) & "')" slash = InStr(26, arr(7), "/")
Docname = Mid(arr(7), slash + 1)
On Error Resume Next
cn.Execute "insert into hitresult values('" & arr(0) & "','" & arr(3) & "','" & Docname & "')"
End If
End If
End If
End If
c = c + 1
Loop
End Function
Comment