[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Trouble inserting rows into Access table

Posted on 2007-04-09
9
Medium Priority
?
282 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 64

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 2000 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 64

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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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