By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,441 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,441 IT Pros & Developers. It's quick & easy.

table is not getting updated, even though insert statement is successful

P: 7
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.Connection
Dim rs As New ADODB.Recordset
Dim rs1 As New ADODB.Recordset

Public Sub Form_Load()
Set cn = New ADODB.Connection
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).Value = "-" Then
rs.MoveNext
Else
If rs.Fields(0).Value <> "administrator" And rs.Fields(0).Value <> "narendra" Then
cn.Execute "insert into result values('" & rs.Fields(1) & "','" & rs.Fields(0) & "','" & rs.Fields(0).Value & "@cisco.com'," & rs.Fields(2).Value & ")"
End If
rs.MoveNext
End If
Wend
Unload Me
End
End Function
Function readfile()
Dim obj As New Scripting.FileSystemObject
Dim arr(11)
c = 0
strfilename = "c:\winnt\system32\logfiles\w3svc1\" & strfile & ".log"
obj.GetFile strfilename
Set objtextstream = obj.OpenTextFile(strfilename, ForReading)
Do While Not objtextstream.AtEndOfStream
strreadingline = objtextstream.ReadLine
'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(strreadingline) - From + 1
arr(cnt) = Mid(strreadingline, From, posn)
Else
arr(cnt) = Mid(strreadingline, 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) <> "administrator" 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
Aug 13 '07 #1
Share this Question
Share on Google+
7 Replies


P: 7
juat a reminder for my previous query
Aug 27 '07 #2

hariharanmca
100+
P: 1,977
juat a reminder for my previous query
What type of connection you are using

1. jet 4.0
2. DSN

chect the database file path in the connection.
Aug 27 '07 #3

P: 7
What type of connection you are using

1. jet 4.0
2. DSN

chect the database file path in the connection.

Its DSN path is correct if I give some othere table name its updating that.. only the tables which I am updating from log files are not getting updated
Aug 27 '07 #4

hariharanmca
100+
P: 1,977
Its DSN path is correct if I give some othere table name its updating that.. only the tables which I am updating from log files are not getting updated

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) & "')"

Remove that On Error Resume Next then it'll throw error.

then you'll be clear.
Aug 27 '07 #5

P: 7
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) & "')"

Remove that On Error Resume Next then it'll throw error.

then you'll be clear.
ok let me try that.. thank you
Aug 27 '07 #6

P: 7
ok let me try that.. thank you
Hey I recreated the table.. now its working fine..
Aug 27 '07 #7

hariharanmca
100+
P: 1,977
Hey I recreated the table.. now its working fine..
Can you give, what is the error you got? so that'll help others to avoid posting new query.
Aug 27 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.