Add record to Access Databases

[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

 

[Ron Allen]

Thomas,
What error are you getting? I would guess that it would be a syntax
error in the insert command as you have fields that have spaces in the names
or are reserved words. To get around that error set the QuotePrefix and
QuoteSuffix properites of the CommandBuilder to "[" and "]" respectively or
both to "`". Other errors may be caused by the table not having a
PrimaryKey. You can try FillSchema first if the table has a primary key
defined in Access. If not you will have to specify which field is the
PrimaryKey.
You may get faster answers in microsoft.public.dotnet.framework.adonet.

Ron Allen

 

[Armin Zingler]

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")

Is the SQL statement invalid? What does oAdapter.InsertCommand.CommandText
return?


BTW, there is a newsgroup for ADO.Net related questions (as this one is not
language dependent):
microsoft.public.dotnet.framework.adonet


Maybe setting the commandbuilder's quoteprefix and quotesuffix properties
(probably to "[" and "]") helps. You don't have to set the DataAdapter's
Insertcommand property on your own because that's done automatically by the
CommandBuilder (2nd paragraph in the documentation on the CommandBuilder
class).



Armin

 

[thomasp]

Thanks for all the help.
The space in the line:
dr("Weapon Type") = "sasdf"
between Weapon and Type was the problem. It seems that everything that
hangs me up is something simple.

Thanks

Thomas