Adding Records

  • Thread starter Thread starter Jared
  • Start date Start date
J

Jared

Hello all,
I don't believe I forgot how to do this, I want to add a new record to a new
access database and I cannot remember how to do this.
I have a connection object which opens and closes correctly. What do I have
to do to get that pesky little record into the database? I have tried
several ways and nothing seems to be working for me.
If someone would be kind enough to include a small example to insert a
single item into a database I would be very grateful. I haven't worked with
databases since vb6, I've been using mostly XML, the problem with this is I
am parsing SUS (software update service) log files in excess of 25MB that is
several hundred thousand records, I really need to be able to search and
manipulate it quickly.

Thanks in advance,
Jared

Here is one of my tries that went nowhere.
'Dim dt As DataTable = Me.MyDataSet.Tables("tblClientDetails")

Dim dt As DataTable = MyTable

Dim MyRow As DataRow

MyRow = dt.NewRow

MyRow("PingID") = NewRecord.Details.PING_ID

MyRow("DateServiced") = NewRecord.DateServiced

MyRow("TimeServiced") = NewRecord.TimeServiced

MyRow("RequestingIP") = NewRecord.RequestingIP

MyRow("ServicingIP") = NewRecord.ServicingIP

MyRow("VERSION") = NewRecord.Details.VERSION

MyRow("CLIENT") = NewRecord.Details.CLIENT

MyRow("ACTIVITY") = NewRecord.Details.ACTIVITY

MyRow("ITEM") = NewRecord.Details.ITEM

MyRow("DEVICE") = NewRecord.Details.DEVICE

MyRow("MAJ_VERSION") = NewRecord.Details.PLATFORM.MAJ_OS_VER

MyRow("MIN_VERSION") = NewRecord.Details.PLATFORM.MIN_OS_VER

MyRow("BUILD_NUM") = NewRecord.Details.PLATFORM.BUILD_NUM

MyRow("PLAT_ID") = NewRecord.Details.PLATFORM.PLAT_ID

MyRow("SUITE_MASK") = NewRecord.Details.PLATFORM.SUITE_MASK

MyRow("PROD_TYPE") = NewRecord.Details.PLATFORM.PROD_TYPE

MyRow("PROCESSOR_ARCH") = NewRecord.Details.PLATFORM.PROCESSOR_ARCH

MyRow("LANGUAGE") = NewRecord.Details.LANGUAGE

MyRow("STATUS") = NewRecord.Details.STATUS

MyRow("ERRORCode") = NewRecord.Details.ERRORCode

MyRow("MESSAGE") = NewRecord.Details.MESSAGE

MyRow("PROXY") = NewRecord.Details.PROXY

dt.Rows.Add(MyRow)
 
I think you need to Update the data adapter with your new row...

da.Update(ds, "tblClientDetails")

good luck!

Drew
 
I'm sorry, it's in my code, right below the add row. I don't get any error
messages when the code executes I just don't get a record in my database.
Any more suggestions?
 
Hi Jared,

A couple of questions:
1. Where is your connection object and did you open it?
2. I see you commented out dim dt as datatable = me. etc - but what is
'mytable'?

If you could send me the full code of this sub (leave out all but one of the
myrow("client") = rows), including the update routine, I'm sure I could
figure it out.

Regards,

Bernie Yaeger
 
Hi Jared,

Another point, which may indeed be the issue: did you write an
updatecommand? Are you using the commandbuilder to update? Without one of
these, the back end will not update.

Bernie
 
Yea, try adding something like this...

Dim cmdBuilder As New OleDbCommandBuilder(da)

da.InsertCommand = cmdBuilder.GetInsertCommand
da.DeleteCommand = cmdBuilder.GetDeleteCommand
da.UpdateCommand = cmdBuilder.GetUpdateCommand

Drew
 
I have tried this several ways, so please be patient. The connection is
opened before the first record is to be saved, the connection opens fine and
no other object are throwing any exceptions either. Also, someone asked what
the MyTable was, I apologize for that, I am displaying a log file in an
..aspx page and I populated a stand alone datatable with the data and bound
the database to the datagrid, it works but it isn't permanent .I didn't
really look over the code very well before posting. One last thing, I used
the Access SQL view and I noticed that the Language field was surrounded by
square brackets, does access uses this field internally? That was the only
field like that. If anyone can provide a small example of inserting records
into a database I would be forever grateful. Thanks goes out to all that
have been trying to help.

Public Sub OpenConnection()
Dim ConnectString As String
Dim ServerPath As String = Me.MapPath("database/SUSReport.mdb")
MyConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=Admin;" _
& "Data Source=" _
& ServerPath & ";" _
& "Mode=Share Deny None;" _
& "Extended Properties="""";" _
& "Jet OLEDB:System database="""";" _
& "Jet OLEDB:Registry Path="""";" _
& "Jet OLEDB:Engine Type=5;" _
& "Jet OLEDB:Database Locking Mode=1;" _
& "Jet OLEDB:Global Partial Bulk Ops=2;" _
& "Jet OLEDB:Global Bulk Transactions=1;" _
& "Jet OLEDB:Create System Database=False;" _
& "Jet OLEDB:Encrypt Database=False;" _
& "Jet OLEDB:Don't Copy Locale on Compact=False;" _
& "Jet OLEDB:Compact Without Replica Repair=False;" _
& "Jet OLEDB:SFP=False"
MyConnection.Open()
myCommand = New OleDb.OleDbCommand
End Sub

Public Sub CloseConnection()
MyConnection.Close()
End Sub

Public Sub AddRecord(ByVal NewRecord As SUSRecord)
Dim SQLString As String
With myCommand
.CommandType = CommandType.Text
.CommandText = "INSERT INTO tblClientDetails(" _
& "PingID, DateServiced, TimeServiced, RequestingIP, " _
& "ServicingIP, VERSION, CLIENT, ACTIVITY, ITEM, " _
& "DEVICE, MAJ_VERSION, MIN_VERSION, BUILD_NUM, " _
& "PLAT_ID, SUITE_MASK, PROD_TYPE, PROCESSOR_ARCH, " _
& "[LANGUAGE], STATUS, ERRORCode, MESSAGE, PROXY) " _
& "VALUES (,'" & NewRecord.Details.PING_ID & "','" _
& NewRecord.DateServiced & "','" & NewRecord.TimeServiced & "','" _
& NewRecord.RequestingIP & "','" & NewRecord.ServicingIP & "','" _
& NewRecord.Details.VERSION & "','" & NewRecord.Details.CLIENT &
"','" _
& NewRecord.Details.ACTIVITY & "','" & NewRecord.Details.ITEM &
"','" _
& NewRecord.Details.DEVICE & "','" &
NewRecord.Details.PLATFORM.MAJ_OS_VER & "','" _
& NewRecord.Details.PLATFORM.MIN_OS_VER & "','" &
NewRecord.Details.PLATFORM.BUILD_NUM & "','" _
& NewRecord.Details.PLATFORM.PLAT_ID & "','" &
NewRecord.Details.PLATFORM.SUITE_MASK & "','" _
& NewRecord.Details.PLATFORM.PROD_TYPE & "','" &
NewRecord.Details.PLATFORM.PROCESSOR_ARCH & "','" _
& NewRecord.Details.LANGUAGE & "','" & NewRecord.Details.STATUS &
"','" _
& NewRecord.Details.ERRORCode & "','" & NewRecord.Details.MESSAGE &
"','" _
& NewRecord.Details.PROXY & "') SELECT tblClientDetails.PingID,
tblClientDetails.DateServiced, " _
& "tblClientDetails.TimeServiced, tblClientDetails.RequestingIP,
tblClientDetails.ServicingIP, " _
& "tblClientDetails.VERSION, tblClientDetails.CLIENT,
tblClientDetails.ACTIVITY, tblClientDetails.ITEM, " _
& "tblClientDetails.DEVICE, tblClientDetails.MAJ_VERSION,
tblClientDetails.MIN_VERSION, " _
& "tblClientDetails.BUILD_NUM, tblClientDetails.PLAT_ID,
tblClientDetails.SUITE_MASK, " _
& "tblClientDetails.PROD_TYPE, tblClientDetails.PROCESSOR_ARCH,
tblClientDetails.LANGUAGE, " _
& "tblClientDetails.STATUS, tblClientDetails.ERRORCode,
tblClientDetails.MESSAGE, " _
& "tblClientDetails.PROXY, tblClients.PingID,
tblClients.RequestingIP " _
& "FROM tblClients INNER JOIN tblClientDetails ON tblClients.PingID
= tblClientDetails.PingID"
SQLString = .CommandText
.Connection = MyConnection
myCommand.ExecuteNonQuery()

End With

End Sub

Sub test(ByVal NewRecord As SUSRecord)
''Dim MyAdapter As New OleDbDataAdapter("SELECT * FROM tblClientDetails
ORDER BY PingID", MyConnection.ConnectionString)
''MyDataSet = New DataSet
'Dim MyAdapter = New OleDbDataAdapter("SELECT * FROM tblClientDetails",
Me.MyConnection)
'MyAdapter.Fill(Me.MyDataSet, "tblClientDetails")

'Dim dt As DataTable = Me.MyDataSet.Tables("tblClientDetails")
'Dim MyRow As DataRow
'MyRow = dt.NewRow
'Start of the '
MyRow("PingID") = NewRecord.Details.PING_ID
'End
dt.Rows.Add(MyRow)
MyAdapter.Update(Me.MyDataSet, "tblClientDetails")
End Sub
 
Back
Top