Solved

Trouble inserting rows into Access table

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

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change data in datatable 8 31
Make a border less form movable 2 23
Definitions and default visual studio colors 5 58
Get size of each directory on each mapped drive 5 22
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now