Solved

Trouble inserting rows into Access table

Posted on 2007-04-09
9
260 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 62

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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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 62

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 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