Add Record to MS Access Database

T

thomasp

Is there anyone that knows how to add a record to a MS Access database with
VB.NET. I have search for 3 days for some code that works. I have gathered
bits and pieces of code and put it together, but I have not found a solution
that works. If anyone has some code that works please reply.

Thanks

Thomas

The following gives an Invalid SQL Statement at: oAdapter.Update(ds,
"LCMR")

Dim oAdapter As OleDb.OleDbDataAdapter
Dim cb As OleDb.OleDbCommandBuilder
Dim dr As DataRow
Dim ds As DataSet
Dim strSQL As String = "Select * from LCMR"
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strAppPath & "TAM.mdb;Persist Security
Info=False"

ds = New DataSet()

oAdapter = New OleDb.OleDbDataAdapter(strSQL, strConn)

oAdapter.Fill(ds, "LCMR")

Try


dr = ds.Tables("LCMR").NewRow()
dr.BeginEdit()

dr("ID") = 1000
dr("ADate") = "6-JUN-5"
dr("ATime") = "01:00"
dr("POO") = strPOO
dr("POO_Alt") = 22
dr("POI") = strPOI
dr("POI_Alt") = 22
dr("Distance") = 1000
dr("Direction") = 100
dr("Target_NO") = "KT1001"
dr("Weapon Type") = "sasdf"
dr("Confirmed") = 1

dr.EndEdit()

ds.Tables("LCMR").Rows.Add(dr)

cb = New OleDb.OleDbCommandBuilder(oAdapter)

oAdapter.InsertCommand = cb.GetInsertCommand

oAdapter.Update(ds, "LCMR")
ds.AcceptChanges()

Catch oException As Exception
MessageBox.Show(oException.Message)


End Try
 
S

Scott M.

You've got a lot of unnecessary code and objects here. I don't see any
information about your connection object. You have a connection string, but
no connection object. Instead of making a copy of your entire LCMR table
and passing it into your application with the DataAdapter's Fill method, use
the FillSchema method to create an empty table that is based on the
original. You've also got the wrong things in your Try section (actually,
it's that you don't have the right things in there). Lastly, it's a good
idea to not use the "o" prefix on your variables to identify them as
objects, since everything is an object anyway.

Here's what you need:


Dim strSQL As String = "SELECT * FROM LCMR"
Dim strCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
strAppPath & "TAM.mdb;Persist
Security Info=False"
Dim con As New OleDb.OleDbConnection(strCON)
Dim da As New OleDb.OleDbDataAdapter(strSQL, con)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim ds As New DataSet
Dim newRow As New DataRow

Try
da.FillSchema(ds, "LCMR")
newRow = ds.Tables("LCMR").NewRow()

newRow("ID") = 1000
newRow("ADate") = "6-JUN-5"
 
K

Ken Tucker [MVP]

Hi,

To use access with vb.net

Add IMPORTS System.Data.OleDb at the top of your app.

dim connAccess as OleDbConnection
Dim da as OleDbDataAdapter
dim cmd as OleDbCommandBuilder
dim ds as DataSet

private sub main
Dim strConn As String

strConn = "Provider = Microsoft.Jet.OLEDB.4.0;"

strConn += "Data Source = db.mdb"

connAccess = New OleDbConnection(strConn)

da = new OleDbDataAdapter("Select * from TableName", connAccess)
cmd = new OleDbCommandBuilder(da)
ds = new dataSet("TableName")

da.fill(ds, "TableName")

' To add a new record

dim dr as datarow = ds.tables(0).newrow

with dr
.item(..) = "..."
end with

ds.tables(0).rows.add(dr)
da.update(ds, "TableName")
ds.acceptchanges()
end sub

Ken
-------------------
Is there anyone that knows how to add a record to a MS Access database with
VB.NET. I have search for 3 days for some code that works. I have gathered
bits and pieces of code and put it together, but I have not found a solution
that works. If anyone has some code that works please reply.

Thanks

Thomas

The following gives an Invalid SQL Statement at: oAdapter.Update(ds,
"LCMR")

Dim oAdapter As OleDb.OleDbDataAdapter
Dim cb As OleDb.OleDbCommandBuilder
Dim dr As DataRow
Dim ds As DataSet
Dim strSQL As String = "Select * from LCMR"
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strAppPath & "TAM.mdb;Persist Security
Info=False"

ds = New DataSet()

oAdapter = New OleDb.OleDbDataAdapter(strSQL, strConn)

oAdapter.Fill(ds, "LCMR")

Try


dr = ds.Tables("LCMR").NewRow()
dr.BeginEdit()

dr("ID") = 1000
dr("ADate") = "6-JUN-5"
dr("ATime") = "01:00"
dr("POO") = strPOO
dr("POO_Alt") = 22
dr("POI") = strPOI
dr("POI_Alt") = 22
dr("Distance") = 1000
dr("Direction") = 100
dr("Target_NO") = "KT1001"
dr("Weapon Type") = "sasdf"
dr("Confirmed") = 1

dr.EndEdit()

ds.Tables("LCMR").Rows.Add(dr)

cb = New OleDb.OleDbCommandBuilder(oAdapter)

oAdapter.InsertCommand = cb.GetInsertCommand

oAdapter.Update(ds, "LCMR")
ds.AcceptChanges()

Catch oException As Exception
MessageBox.Show(oException.Message)


End Try
 
S

Scott M.

Gee, that looks an awful lot like what I wrote. Except: If all you are
doing is adding a row, don't use DataAdapter.Fill, use
DataAdapter.FillSchema. There's no sense in bringing down 500 records just
to add one.
 
T

thomasp

Thank you very much for all the help. I will get my code in order. As you
can tell I am not much of a programer, but I love trying. This is my first
go with VB.NET. I like it so far.

Thomas
 
C

Cor Ligthert

Thomas,

If you really only want to add a row with data to a table in a database than
the most efficient method is just to create an SQL insert command and proces
that with OleDb.OleDbCommand.ExecuteNonQuery

I hope this helps,

Cor
 
T

thomasp

For those they might be interested, this is what I ended up with. Criticism
welcome.

Thomas

Public Sub subImportLCMR(ByVal intCounter As Integer, ByVal strImportDate As
String, _
ByVal intMaxID As Integer, ByRef bolSuccessful
As Boolean, _
ByRef strFirstDTG As String, ByRef strLastDTG
As String, _
ByRef strSummary As String)

Dim intStart As Integer = 0
Dim intNumRecords As Integer
Dim intDistance As Integer
Dim intDirection As Integer

Dim datImportDate As Date
Dim datRecordDate As Date

Dim diffEast As Double
Dim diffNorth As Double



intSuccessful = 0
bolSuccessful = True
strSummary = ""

'Heading for summary
strSummary = "Date" & vbTab & vbTab & "Time" & vbTab & "POO" & vbTab
& vbTab & "POO Alt" & vbTab & "POI" & vbTab & vbTab & _
"POI Alt" & vbTab & "Distance" & vbTab &
"Direction" & vbCr

'Save the original value of intCounter as the number of records in
aryLCMRData
intNumRecords = intCounter

'set the date variable for what record the import should start on
datImportDate = CDate(strImportDate)

'This If Then loop determines where the import should start based on
the above date
'it cycles through the aryLCMRData until it comes to the first
record with a date/time
'group newer than or equal to the given date/time group
If Len(strImportDate) > 0 Then
For intCounter = 0 To intCounter - 1
datRecordDate = CDate(aryLCMRData(intCounter, 1) & " " &
aryLCMRData(intCounter, 0))
If datRecordDate >= datImportDate Then
intStart = intCounter
Exit For
End If
Next
End If

'This For Next loop will start
For intCounter = intStart To intNumRecords - 1

'Get the distance and the direction
intDistance = Distance(aryLCMRData(intCounter, 2),
aryLCMRData(intCounter, 4), diffEast, diffNorth)
intDirection = Direction(diffNorth, diffEast)

'up the ID by one
intMaxID = intMaxID + 1

Dim strSQL As String = "SELECT * FROM LCMR"
Dim strCON As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & _
strAppPath & "TAM.mdb;Persist
Security Info = False"

Dim con As New OleDb.OleDbConnection(strCON)
Dim da As New OleDb.OleDbDataAdapter(strSQL, con)
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim ds As New DataSet
Dim newRow As DataRow

Try
da.FillSchema(ds, SchemaType.Source, "LCMR")
newRow = ds.Tables("LCMR").NewRow()

newRow("ID") = intMaxID
newRow("ATime") = "#" & aryLCMRData(intCounter, 0) & "#"
newRow("ADate") = "#" & aryLCMRData(intCounter, 1) & "#"
newRow("POO") = aryLCMRData(intCounter, 2)
newRow("POO_Alt") = Val(aryLCMRData(intCounter, 3))
newRow("POI") = aryLCMRData(intCounter, 4)
newRow("POI_Alt") = Val(aryLCMRData(intCounter, 5))
newRow("Distance") = intDistance
newRow("Direction") = intDirection
newRow("Target_NO") = ""
newRow("Weapon_Type") = ""
newRow("Confirmed") = 0

ds.Tables("LCMR").Rows.Add(newRow)

If da.Update(ds, "LCMR") Then

'Get the first successful DTG imported
If Len(strFirstDTG) < 1 Then
strFirstDTG = Left(aryLCMRData(intCounter, 1) & " "
& aryLCMRData(intCounter, 0), 15)
End If

'Get the last DTG imported
strLastDTG = Left(aryLCMRData(intCounter, 1) & " " &
aryLCMRData(intCounter, 0), 15)

'Add one to the successful import record counter
intSuccessful = intSuccessful + 1

'Create import summary
strSummary = strSummary & aryLCMRData(intCounter, 1) &
vbTab & vbTab & aryLCMRData(intCounter, 0) & vbTab & _
aryLCMRData(intCounter, 2) &
vbTab & aryLCMRData(intCounter, 3) & vbTab & _
aryLCMRData(intCounter, 4) &
vbTab & aryLCMRData(intCounter, 5) & vbTab & _
intDistance & vbTab &
intDirection & vbCr

End If

da.Dispose()
ds.Dispose()

If gfrmImport.prgProgress.Value + 1 <
gfrmImport.prgProgress.Maximum Then
gfrmImport.prgProgress.Value =
gfrmImport.prgProgress.Value + 1
Else
gfrmImport.prgProgress.Value =
gfrmImport.prgProgress.Maximum
End If


Catch oException As OleDbException
MessageBox.Show(oException.Message)
bolSuccessful = False
Catch oException As Exception
MessageBox.Show(oException.Message)
bolSuccessful = False

End Try

Next

End Sub
 
M

Michael Cole

Is there anyone that knows how to add a record to a MS Access
database with VB.NET.
Click to expand...

Just one minor point here, you have crossposted this to many unnecessary
groups. Of the six groups that it has been crossposted to, two of them,
microsoft.public.vb.database and microsoft.public.vb.database.ado refer
specifically to VB Classic, not VB.Net. Perhaps in future you could check
what groups you are posting to.

For reference,

--
<response type="generic" language="VB.Net">
This newsgroup is for users of Visual Basic version 6.0
and earlier and not the misleadingly named VB.Net
or VB 200x. Solutions, and often even the questions,
for one platform will be meaningless in the other.
When VB.Net was released Microsoft created new newsgroups
devoted to the new platform so that neither group of
developers need wade through the clutter of unrelated
topics. Look for newsgroups with the words "dotnet" or
"vsnet" in their name. For the msnews.microsoft.com news
server try these:

microsoft.public.dotnet.general
microsoft.public.dotnet.languages.vb

</response>
 
S

Scott M.

No need to pass your string arguments ByRef. Strings are classes and
therefore are Reference Types. If you pass a reference type ByRef, it is
actually slightly LESS efficient than passing a reference type ByVal. Both
will have the same result.
 
G

Guest

Thomas,

I have a small vb.net project that I was using to test a problem with
ADO.net being so much slower than ADO (ADODB recordset). It inserts 30,000
records into a database using one ADO method and 3 ADO.net methods. It
tells you the time in seconds it took to complete the write. I will
demonstate how to connect and write to an Access database.

If you are interested, the project is available at this URL:

http://www.kelbli.net/pub/transfer/DBtest.zip


The database is in the bin directory (make sure you extract with relative
paths enabled).

Enjoy

Brian
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add record to Access Databases 3
Add record to Access Database 3
getting error on record duplication 18
How do I fill a Datagridview? 4
Please help..... 8
Edit DataGridView columns 1
deleted row information cannot be accessed... 5
Delete record from SQL DB 5

Top