All messed up with OLEDB, Access2000 & Update 
Author Message
 All messed up with OLEDB, Access2000 & Update

I keep getting a message "Syntax error in INSERT INTO statement". This is my
first attempt at VB.Net and MSAccess2000.
I have the following code attached to a command button:

Dim strConnection As String

Dim cn As OleDbConnection

Dim cmdCapture As New OleDbCommand()

Dim cmdInsert As New OleDbCommand()

Dim da As New OleDbDataAdapter()

Dim ds As New DataSet()

Dim dt As New DataTable()

Dim dr As DataRow

Dim mlngCurRow As Long

'

Try

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=a:\CBOTDataBase.mdb"

cn = New OleDbConnection(strConnection)

cn.Open()

cmdCapture = cn.CreateCommand

cmdCapture.CommandText = "SELECT * FROM CBOTDownLoad"

da.SelectCommand = cmdCapture

'

cmdInsert = cn.CreateCommand

cmdInsert.CommandText = "Insert Into CBOTDownLoad " & _

                                              "(CommodityMonth, Date, Open,
High, Low, Close)" & _

                                               "Values(?, ?, ?, ?, ?, ?)"







cmdInsert.Connection = cn

da.InsertCommand = cmdInsert

'

da.Fill(ds, "dtCBOTDownLoad")

dt = ds.Tables("dtCBOTDownLoad")

mlngCurRow = 0

dr = dt.NewRow()

    dr("CommodityMonth") = "SX"

    dr("Date") = CDate("08/12/02")

    dr("Open") = 600.0

    dr("High") = 605.0

    dr("Low") = 590.0

    dr("Close") = 595.0

dt.Rows.Add(dr)

da.Update(ds, "dtCBOTDownLoad")

dgCBOT.DataSource = ds

dgCBOT.DataMember = "dtCBOTDownLoad"

cn.Close()

Catch ex As Exception

    MsgBox("Error: " & ex.Source & ": " & ex.Message & " (" & ex.ToString &
")", MsgBoxStyle.OKOnly, "ButtonReadDataBase")

End Try



Sun, 30 Jan 2005 07:05:53 GMT  
 All messed up with OLEDB, Access2000 & Update
B&J,
    This can often be do to using 'reserved words' as field names.  They
will generally work for SELECT statements but not for Insert, etc.  You have
Date as a field name which is a reserved word so you need to quote it (
[Date] would be the normal way).  Open may be reserved as well.

Ron Allen

Quote:
> I keep getting a message "Syntax error in INSERT INTO statement". This is
my
> first attempt at VB.Net and MSAccess2000.
> I have the following code attached to a command button:

> Dim strConnection As String

> Dim cn As OleDbConnection

> Dim cmdCapture As New OleDbCommand()

> Dim cmdInsert As New OleDbCommand()

> Dim da As New OleDbDataAdapter()

> Dim ds As New DataSet()

> Dim dt As New DataTable()

> Dim dr As DataRow

> Dim mlngCurRow As Long

> '

> Try

> strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data
> Source=a:\CBOTDataBase.mdb"

> cn = New OleDbConnection(strConnection)

> cn.Open()

> cmdCapture = cn.CreateCommand

> cmdCapture.CommandText = "SELECT * FROM CBOTDownLoad"

> da.SelectCommand = cmdCapture

> '

> cmdInsert = cn.CreateCommand

> cmdInsert.CommandText = "Insert Into CBOTDownLoad " & _

>                                               "(CommodityMonth, Date,
Open,
> High, Low, Close)" & _

>                                                "Values(?, ?, ?, ?, ?, ?)"







> cmdInsert.Connection = cn

> da.InsertCommand = cmdInsert

> '

> da.Fill(ds, "dtCBOTDownLoad")

> dt = ds.Tables("dtCBOTDownLoad")

> mlngCurRow = 0

> dr = dt.NewRow()

>     dr("CommodityMonth") = "SX"

>     dr("Date") = CDate("08/12/02")

>     dr("Open") = 600.0

>     dr("High") = 605.0

>     dr("Low") = 590.0

>     dr("Close") = 595.0

> dt.Rows.Add(dr)

> da.Update(ds, "dtCBOTDownLoad")

> dgCBOT.DataSource = ds

> dgCBOT.DataMember = "dtCBOTDownLoad"

> cn.Close()

> Catch ex As Exception

>     MsgBox("Error: " & ex.Source & ": " & ex.Message & " (" & ex.ToString
&
> ")", MsgBoxStyle.OKOnly, "ButtonReadDataBase")

> End Try



Mon, 31 Jan 2005 00:00:13 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. oledb: foxpro index file not updating after inserts and updates

2. VBA ups & downs

3. vba ups & downs

4. Updating Tables That Are Linked to Access2000

5. Help with Inconsistent/buggy UPDATE in VB5/Access2000

6. Update/Insert into An Access2000 Memo field

7. Access2000 not updating

8. VB update to Access2000 table using ADO ?

9. OleDB - DataRow update and Transaction

10. oledb update command not inserting recently added row...

11. Problem updating a database from a dataset using OLEDB command builder

12. Updating child recordsets using the Microsoft.Jet.OLEDB.3.51 provider

 

 
Powered by phpBB® Forum Software