vb 2010 express Syntax error in Update Statement

I am learning vb express.  I started with 2008 a week ago and have just imported a test project into 2010 which I have just downloaded. The import produced no errors.

I am using an access database with the OleDB provider to read a single table into a DataAdapter using example tutorial code.

Under 2008 everything was fine in debug mode, I can display and edit and save the data back to the database.

After importing into 2010 all seemed the same until I tried to save.  My save button click event code is similar to:
 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If m_dtCustomers.Rows.Count <> 0 Then
            m_dtCustomers.Rows(m_rowPosition)("ID") = txtID.Text
            m_dtCustomers.Rows(m_rowPosition)("Company") = txtCompany.Text
            m_dtCustomers.Rows(m_rowPosition)("Address1") = txtAddress1.Text
et.c  et.c    
      m_dtCustomers.Rows(m_rowPosition)("Contact") = txtContact.Text
        End If
    End Sub

This worked perfectly in 2008 but not in 2010 as I get an exception on the update line saying there is a syntax error.

The form class declarations are:

 Private m_cnADONetConnection As New OleDb.OleDbConnection()
    Private m_daDataAdapter As OleDb.OleDbDataAdapter
    Private m_cbCommandBuilder As OleDb.OleDbCommandBuilder
    Private m_dtCustomers As New DataTable

and the form load event:

 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        m_cnADONetConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\visual_basic_tests\test1.mdb"
        m_daDataAdapter = New OleDb.OleDbDataAdapter("Select * From Customers", m_cnADONetConnection)
        m_cbCommandBuilder = New OleDb.OleDbCommandBuilder(m_daDataAdapter)

    End Sub

I haven't shown the code for displaying the record as it is just basic stuff.

Can anyone advise, as I don't know where to go from here. Has 2010 changed anything?



Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

This should not have stopped working just by upgrading to 2010. Do two things
1) Make sure you dont use single quote (') in the values
2) Check by stepping through what the update query looks like.
chrisatworkAuthor Commented:

Said I was a beginner!  Have checked 1) all ok.  How do I do 2) ?

I did some further checking and the routine gives the same problem now in 2008 express, so I must conclude that I have changed something since the very basic first test I did.  All I have done from the point that it worked in 2008 was to introduce a method of selecting a record in the dataset by setting the m_rowPosition value for the ID value with an integer derived from a text box input.

The save event fills the fields from the display text boxes with the relevant values then calls the data table update.  I assume this internally opens the database and updates it then closes the database again.

The only thing I can think of is that the ID field in the access database is Long integer where all the rest are text.  Do I have to use a different format for the line: ' m_dtCustomers.Rows(m_rowPosition)("ID_Number") = txtIDNumber.Text '  in the save event, or does the adapter know what to do with the data?
>m_dtCustomers.Rows(m_rowPosition)("ID_Number") = txtIDNumber.Text

This should be fine as long as there is an integer in txtIDNumber.

For 2, set a breakpoint on .Update line (click on left side area to it). Then when the breakpoint hits during runtime, in the "Immediate" window, type:


and see what SQL query is returned.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

chrisatworkAuthor Commented:
Ok I did this in the save click event, as only the ID Number field in the database is Long integer, the rest are Text:

 Dim IDVal As Integer
        IDVal = CInt(txtIDNumber.Text)

If m_dtCustomers.Rows.Count <> 0 Then
   m_dtCustomers.Rows(m_rowPosition)("ID_Number") = IDVal
   m_dtCustomers.Rows(m_rowPosition)("Company") = txtCompany.Text


Putting a breakpoint as suggested returned the following in the immediate window for both queries:

Referenced object has a value of 'Nothing'

There are blank fields in the database (and therefore presumably in the dataset) so I am obviously missing something here
As a test, use this in immediate window


if it returns a valid sql then it means the command builder is not generating the update and insert commands. Is the ID_Number a primary key in DB?
chrisatworkAuthor Commented:
that returns:

"Select * From Customers"

Yes the ID_Number field is a primary key, Long Integer, non Autonumber field.  Integers start at 1000 and run sequentially thereafter so I do a translation from the returned ID number to the actual dataset row number needed in the code, keeping the result as an integer.

(Will be out for 3 or 4 hours now)

chrisatworkAuthor Commented:
Could really do with input on this asap.

Retested the project in 2008 and it produced the same error so it's obviously something I have done/not done.  Have trawled the web looking for answers with little success, Should my data table have a primary key on the ID_Number field as the access db does? If so how do I code it and how do I test the table for it?

If I comment out the m_daDataAdapter.Update(m_dtCustomers) line then the data table updates ok for the active session, I just can't write it back to the db

Follow this example for manually configuring the commands

chrisatworkAuthor Commented:
Read through this but didn't answer my particular question which was why is the update command failing with a syntax error.

As I don't fully understand the mechanics of the dataadapter I need guidance as to how to configure it.

The data adapter was invoked through code, not by dragging a control, and I cannot see how to configure it except through code, and I don't know how to do that.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chrisatworkAuthor Commented:
Ok, sorry for the long delay in closing this.

The question was not actually answered but the information given allowed me to direct my reading into several areas which eventually led me to the solution, which was to add ORDERED BY ID to the sql.

This solved the update problem as I was unaware that the adapter apparently did not necessarily read the records as laid out in the database, and needed to be instructed specifically.  This also meant that the commandbuilder apparently did not have the correct row information to update.

Learned a lot, took far longer than I wanted, but have solved this problem, so thanks.

chrisatworkAuthor Commented:
Information accurate as given, just did not unfortunately directly address the question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.