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

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"

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
-------------------
<(E-Mail Removed)> wrote in message
news:42bd82f9$0$11766$(E-Mail Removed)...
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

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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
> -------------------
> <(E-Mail Removed)> wrote in message
> news:42bd82f9$0$11766$(E-Mail Removed)...
> 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
>
>

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

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

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

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

http://www.eggheadcafe.com/articles/..._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



<(E-Mail Removed)> wrote in message
news:42bd82f9$0$11766$(E-Mail Removed)...
> 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

(E-Mail Removed) 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> (E-Mail Removed) 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
>
>