Add Record to MS Access Database

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

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Scott
Sat Jun 25 14:14:55 CDT 2005

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"
.
.
.
newRow("Weapon Type") = "sasdf"
newRow("Confirmed") = 1

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

da.Update(ds, "LCMR")
Catch ex As OleDbException
'DataBase exceptions handled here
Catch ex As Exception
'All other exceptions handled here
End Try

Ken
Sat Jun 25 20:08:31 CDT 2005

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
-------------------
<thomasp@msala.net> wrote in message
news:42bd82f9$0$11766$b9f67a60@news.newsdemon.com...
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

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Scott
Sat Jun 25 20:16:07 CDT 2005

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.


"Ken Tucker [MVP]" <vb2ae@bellsouth.net> wrote in message
news:ucYtUueeFHA.1040@TK2MSFTNGP10.phx.gbl...
> 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
> -------------------
> <thomasp@msala.net> wrote in message
> news:42bd82f9$0$11766$b9f67a60@news.newsdemon.com...
> 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
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access
>
>

thomasp
Sat Jun 25 21:26:42 CDT 2005

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

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Cor
Sun Jun 26 01:22:40 CDT 2005

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

thomasp
Sun Jun 26 12:14:19 CDT 2005

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

--
Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
------->>>>>>http://www.NewsDemon.com<<<<<<------
Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Robbe
Sun Jun 26 20:19:41 CDT 2005

Let this write the ADO.NET code for you in an object oriented fashion.

http://www.eggheadcafe.com/articles/microsoftaccess_source_code_generator.asp

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp



<thomasp@msala.net> wrote in message
news:42bd82f9$0$11766$b9f67a60@news.newsdemon.com...
> 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
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

Michael
Sun Jun 26 21:33:14 CDT 2005

thomasp@msala.net wrote:
> Is there anyone that knows how to add a record to a MS Access
> database with VB.NET.

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>

--
Regards,

Michael Cole

Robbe
Mon Jun 27 17:54:42 CDT 2005

I just replied to "Reply Group". The original poster sent this to all
the groups. I wouldn't purposely post to newsgroups that aren't
relevant.

--
2004 and 2005 Microsoft MVP C#
Robbe Morris
http://www.masterado.net

Earn $$$ money answering .NET Framework
messageboard posts at EggHeadCafe.com.
http://www.eggheadcafe.com/forums/merit.asp



"Michael Cole" <noone@hansen.com> wrote in message
news:OqtyRCseFHA.1404@TK2MSFTNGP09.phx.gbl...
> thomasp@msala.net wrote:
>> Is there anyone that knows how to add a record to a MS Access
>> database with VB.NET.
>
> 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>
>
> --
> Regards,
>
> Michael Cole
>
>

Scott
Tue Jun 28 23:16:05 CDT 2005

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.


<thomasp@msala.net> wrote in message
news:42bee27d$0$11816$b9f67a60@news.newsdemon.com...
> 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
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access

bdwest
Fri Jul 01 01:05:02 CDT 2005

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



"thomasp@msala.net" wrote:

> 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
>
> --
> Posted via NewsDemon.com - Premium Uncensored Newsgroup Service
> ------->>>>>>http://www.NewsDemon.com<<<<<<------
> Unlimited Access, Anonymous Accounts, Uncensored Broadband Access
>