Solved

Trouble inserting rows into Access table

Posted on 2007-04-09
9
268 Views
Last Modified: 2010-04-23
Hi,
I am having a problem inserting a new row into a MS Access table

At the form level I have this code:
 ' constr is the connection string for the system database
    ' constr is defined in ProjConst.vb code
    Dim conn As New OleDb.OleDbConnection(constr)
    Dim qryJunk = "SELECT * FROM JunkTable"
    Dim daJunk As New OleDb.OleDbDataAdapter(qryJunk, conn)
    Dim cbJunk As New OleDb.OleDbCommandBuilder(daJunk)
    Dim dtJunk As New DataTable
    Dim drJunk As DataRow

Then in a sub that is called every 5 seconds by a timer i have this code:
 Sub fillMeasurementTbl()
       
        Dim junk
        Static counter
   
        daJunk.Fill(dtJunk)
        counter = counter + 1

        drJunk = dtJunk.NewRow
        drJunk.Item("counter") = counter
        drJunk.Item("junk1") = "sampleString1"
        drJunk.Item("junk2") = "sampleString1"
        dtJunk.Rows.Add(drJunk)
        Try
            daJunk.Update(dtJunk)
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
        End Try
    End Sub

When I run the Exception code is:
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at WindowsApplication1.frmTestDisp.fillMeasurementTbl() in C:\tescor XX-XXXX-1\frmTestDisp.vb:line 306

What am I doing wrong?

Thanks,
Charlie
0
Comment
Question by:charlieb01
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 18876119
Hi Charlie;

I am not a database programmer but if I am not mistaken in order for the following command:

    Dim cbJunk As New OleDb.OleDbCommandBuilder(daJunk)

to build the queries correctly the table must have a primary key defined. So if you have not done so already add a primary key to the table in the database.

Fernando
0
 
LVL 5

Expert Comment

by:mytonytiger
ID: 18876314
I built a small application to try and duplicate your error. I am using OLDB, but I am using an SQL database. Here is my code:

    Dim dajunk As OleDb.OleDbDataAdapter
    Dim drjunk As DataRow
    Dim dtjunk As DataTable


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim conn As New OleDb.OleDbConnection("commented out")
        Dim qryJunk = "SELECT * FROM table3"
        dajunk = New OleDb.OleDbDataAdapter(qryJunk, conn)
        Dim cbJunk As New OleDb.OleDbCommandBuilder(daJunk)
        dtjunk = New DataTable

        fillMeasurementTbl()
        fillMeasurementTbl()
        fillMeasurementTbl()

    End Sub

    Sub fillMeasurementTbl()

        Static counter As Integer

        daJunk.Fill(dtJunk)
        counter = counter + 1

        drJunk = dtJunk.NewRow
        drjunk.Item("col1") = counter
        drjunk.Item("col2") = "sample"
        drjunk.Item("col3") = "sample"
        dtJunk.Rows.Add(drJunk)
        Try
            daJunk.Update(dtJunk)
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub



I was unable to reproduce your error. Not sure if it helps, but thought I'd post it just in case.
0
 
LVL 5

Expert Comment

by:mytonytiger
ID: 18876325
BTW, my table looks like this:

CREATE TABLE [Table3] (
      [col1] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [col2] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [col3] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:charlieb01
ID: 18876711
Hi,

I modified my code from the original to the following using your example. BTW, my "JunkTable" has the following fields:
Counter - this is PK type is Integer
junk1 - type is text
junk2 - type is text

When I run this I get this error:

System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at WindowsApplication1.frmTestDisp.fillMeasurementTbl() in C:\tescor XX-XXXX-1\frmTestDisp.vb:line 333


Charlie

Below is my modified code per your comments:

At the Form Level:
 Dim daJunk As OleDb.OleDbDataAdapter
    Dim drJunk As DataRow
    Dim dtJunk As DataTable

At Form_Load:
        Dim conn As New OleDb.OleDbConnection(constr)
        Dim qryJunk = "SELECT * FROM JunkTable"
        daJunk = New OleDb.OleDbDataAdapter(qryJunk, conn)
        Dim cbJunk As New OleDb.OleDbCommandBuilder(daJunk)
        dtJunk = New DataTable

In the sub that is called every 5 seconds:
Sub fillMeasurementTbl()
       
       Static counter As Integer

        daJunk.Fill(dtJunk)
        counter = counter + 1

        drJunk = dtJunk.NewRow
        drJunk.Item("Counter") = counter
        drJunk.Item("junk1") = "sampleString1"
        drJunk.Item("junk1") = "sampleString2"
        dtJunk.Rows.Add(drJunk)
        Try
            daJunk.Update(dtJunk)
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
        End Try
       End Sub
0
 

Author Comment

by:charlieb01
ID: 18877722
Hi,

Does anyone have any ideas here? I am really stuck on this problem.

Thanks,
Charlie
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18879061
'Counter' is a reserved word in Access.  It needs to be surrounded by [] in any commandtext.  The CommandBuilder will do that for you automatically if you add these two lines

    cbJunk.QuotePrefix = "["
    cbJunk.QuoteSuffix = "]"

in your Form_Load event (or somewhere it will be called before your updating code fires).

That IS a problem.  Whether it's the ONLY problem, I'm not sure.  Is you PK an Autonumber in Access?

Roger
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 18879621
Hi charlieb01;

Thanks to Roger you can change your form load to this and that should now be able to inset the new row into the database.

        Dim conn As New OleDb.OleDbConnection(constr)
        Dim qryJunk = "SELECT * FROM JunkTable"
        daJunk = New OleDb.OleDbDataAdapter(qryJunk, conn)
        Dim cbJunk As New OleDb.OleDbCommandBuilder(daJunk)
        cbJunk.QuotePrefix = "["
        cbJunk.QuoteSuffix = "]"

        dtJunk = New DataTable

In the subroutine fillMeasurementTbl you should add a line to accept changes to the db, something like this.

        Try
            daJunk.Update(dtJunk)
            dtJunk.AcceptChanges()
        Catch ex As Exception

You have one more problem that I can see but not being a db programmer I do not know how to get it done and that is getting the last record added and getting the value from Counter so that you can set the value of counter so that you do not try to add a record with the same value.

Fernando
0
 

Author Comment

by:charlieb01
ID: 18880247
Thanks Roger.

I guess I should get myself a list of Access reserved words  :)

Maybe you will be stumped by my next question about using Date & Time as PK in an Access table!

Charlie
0
 
LVL 34

Expert Comment

by:Sancler
ID: 18880725
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question