Need Help adding database records

[Guest]

I'm trying to copy records from the Event Logs to a databse. I can read the
database & the event logs but hit a problem when trying to create a NewRow.
Follows is the code - scroll down I've indicated the line in question.

Thanks in advance for assistance.

BigSam

Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient

Public Class Form1
Protected objDataAdapter As New SqlDataAdapter
Protected conn As New SqlConnection
Protected objCmd As New SqlCommand
Protected objDataSet As New DataSet
Protected objDataTable As New DataTable
Protected moReturn As Management.ManagementObjectCollection
Protected moSearch As New Management.ManagementObjectSearcher
Protected mo As New Management.ManagementObject
Protected strComputer As String = "Samsbox"
Protected strSelect As String
Protected strReturn As String
Protected strSearch As String

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
'LogSystemEvents()
'LogSecurityEvents()
LogApplicationEvents()

End Sub
Sub LogApplicationEvents()

Dim dtmDate = CreateObject("WbemScripting.SWbemDateTime")
Dim DateToCheck = CreateObject("WbemScripting.SWbemDateTime")

conn.ConnectionString = "Server=LocalHost; Integrated Security=true;
Database = SysMgt;"
'Get Oldest date time from table for server name
strSelect = "select * from tblApp Where ComputerName = '" &
strComputer & "' Order by TimeWritten DESC"

Try
objDataAdapter.SelectCommand = New SqlCommand(strSelect, conn)
objDataAdapter.Fill(objDataSet)
objDataTable = objDataSet.Tables(0)
If objDataTable.Rows.Count = 0 Then
DateToCheck = DateAdd(DateInterval.Day, -1, Now)
Else
DateToCheck = objDataTable.Rows(0).Item("TimeWritten")
End If
'dtmDate.SetVarDate(DateToCheck, True) '

strSearch = "Select * from Win32_NTLogEvent " _
& "Where LogFile = 'Application' and TimeWritten >= '" &
DateToCheck & "'"
moSearch = New Management.ManagementObjectSearcher(strSearch)
moReturn = moSearch.Get
Dim NewRow As DataRow
'The next line is causing me trouble.
NewRow = objDataSet.Tables("tblApp").NewRow()
For Each mo In moReturn
NewRow.Item("LogID") = "APP"
NewRow.Item("Category") = mo("CategoryString")
NewRow.Item("ComputerName") = mo("ComputerName")
NewRow.Item("EventCode") = mo("EventCode")
NewRow.Item("Message") = mo("Message")
NewRow.Item("RecordNumber") = CInt(mo("RecordNumber"))
NewRow.Item("SourceName") = mo("SourceName")
dtmDate = mo("TimeWritten")
strReturn = WMIDateStringToDate(dtmDate)
NewRow.Item("TimeWritten") = strReturn
NewRow.Item("EventType") = CInt(mo("EventType"))
NewRow.Item("User") = mo("User")
objDataSet.Tables("tblApp").Rows.Add(NewRow)
Next
objDataAdapter.Dispose()
objDataSet.Dispose()
objDataTable.Dispose()
conn.Close()
moReturn.Dispose()
moSearch.Dispose()
mo.Dispose()
Catch e As Exception
Console.WriteLine(e.ToString(), MsgBoxStyle.Exclamation, "Error
opening database")
End Try
objDataAdapter.Dispose()
objDataSet.Dispose()
objDataTable.Dispose()
conn.Close()
moReturn.Dispose()
moSearch.Dispose()
mo.Dispose()
End Sub
Function WMIDateStringToDate(ByVal dtmDate)
WMIDateStringToDate = CDate(Mid(dtmDate, 5, 2) & "/" & _
Mid(dtmDate, 7, 2) & "/" & Mid(dtmDate, 1, 4) _
& " " & Mid(dtmDate, 9, 2) & ":" & _
Mid(dtmDate, 11, 2) & ":" & Mid(dtmDate, 13, 2))
End Function

End Class

 

[Cor Ligthert[MVP]]

Sam,

I see a lot of code but in the short time most of us take mostly to evaluate
problems from others I do not see an update command as well not the commands
to fill the properties of that in the right way or that you use a
commandbuilder.

If you have a problem, then please make a short sample in this kind of
problems, test it first yourself and use than that and give your problems
with that.

Now we probably are looking to a lot of not needed commands, but that cannot
be the problem.

Cor

 

[Bill McCarthy]

Hi Sam,

So you are referring to the table by name "tblApp" yet I don't see where
that name is speciified or used elsewhere. What happens if you refer to the
table by index, 0 instead ?

e.g:
Dim newRow as DataRow = NewRow = objDataSet.Tables(0).NewRow()

Regards,

Bill.

 

[Guest]

Thanks, that fix the problem.
Sorry to have included all of the code, but I thought it could be helpful.

Now that I'm trying to add new records to my database table, I've
encountered another problem that doesn't make sense. The 1st loop through
moReturn works fine, but the 2nd time through I get a message saying 'This
row already belongs to this table.' The table has an Identity field as the
primary key (not referenced in my code), but why isn't it being incremented
with the subsequent adds?

Thanks again,

BigSam

 

[Bill McCarthy]

Hi Sam,

If this is the same code, could it be because the NewRow is called outside
the For loop ? It should be inside it.

 

[Guest]

Yes, I believe that was covered in programming 101 (back in Fortran & PL/1
days).
I moved the NewRow inside the loop which eliminated the error, then
determined that the database isn't being updated. I thought the changes to
the dataset or table had not been applied; after a little searching I
determined that I needed to objDataTable.AcceptChanges() after my loop.
This didn't solve the matter, so I updated the statements to reference my
database table (thinking that the application didn't know what table to
update & helping to document the code):
objDataAdapter.Fill(objDataSet, "tblApp")
objDataTable = objDataSet.Tables("tblApp")
and inside the loop:
NewRow = objDataSet.Tables("tblApp").NewRow()
objDataSet.Tables("tblApp").Rows.Add(NewRow)

Still no database updates. What am I missing now?

Thanks,

BigSam

 

[Bill McCarthy]

Hi Sam,

Perhaps you need to say exactly what it is you are trying to do. I'm
beginning to think it's get the records form the NTLog and add them to a
database ? If that's the case then there's a lot of things you probably
should be changing in your code....

A Dataset is a disconnected data store. It will not and cannot automatically
update a database. Retrieving and Updating records is done via a
DataAdapter. So just as you used one for the Fill, you use one for Update.
When you use a DataAdapter to Update a database from a Dataset, it looks in
the DataSet's tables rows to see if any rows are marked as modified or
changed. As such when you Add a NewRow internally it has flag indicating
it is new. If however you call AccpetChanges, that mark is cleared so the
DataAdapter cannot tell that row needs to be inserted into the database. So
don't call AccpetChanges until after you've update the database.

You probably also do not need to be getting all records form the database to
start with as that has no impact on the Update if you don't modify those
rows. If you aren't displaying them don't get them. IF you need the last
date, you could try modifying the Select command to get just the TOP 1.

I highly recommend you use Strongly Typed Datasets for this., and hence get
the DataAdpater's Select, Insert and Delete commands generated for you, plus
remove any of the issues with referring to columns or tables with string
identifiers which can't be checked until runtime.

Regards,

Bill.

 

[Guest]

Yes, I am trying to copy records from the Event Logs to the database. Once I
get this program running I want to move the code to a service, so that
multiple servers can copy events to a single database on a timed interval.
The reason I'm retrieving database table entries, is to get the last
DateTime for the system the program is running on, so that I can get EventLog
records after that point in time.
Thanks for helping me better understand about using the adapter and dataset
- especially the disconnect aspect & using Update. However, based on your
other comments I guess I have a more basic question - should I be using a
dataadapter & dataset to process my updates? Should I be using using a series
of Insert commands?

Thanks,

BigSam

 

[Bill McCarthy]

Hi,

If you're not using the dataset for any other purpose then I probably
wouldn't use it. That being said, it's very quick and easy to drag and drop
create a strongly typed dataset from the database and get the appropriate
commands created. So to get it up and running, I would possibly consider
using the Strongly Typed DataSet approach.
Later if memory usage or performance was a concern then I'd probably look at
alternatives, such as using SQLCommand directly. You could use a typed
object to represent each log entry, but that's going to have similar memory
requirements to the strongly typed dataset, less, but still a lot of objects
created and then destroyed.
Probably best performance would be inline SQLCommands.