Solved

Trouble inserting rows into Access table

Posted on 2007-04-09
9
244 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

746 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

8 Experts available now in Live!

Get 1:1 Help Now