Link to home
Start Free TrialLog in
Avatar of chrisatwork
chrisatwork

asked on

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
            m_daDataAdapter.Update(m_dtCustomers)
        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_cnADONetConnection.Open()
        m_daDataAdapter = New OleDb.OleDbDataAdapter("Select * From Customers", m_cnADONetConnection)
        m_cbCommandBuilder = New OleDb.OleDbCommandBuilder(m_daDataAdapter)
        m_daDataAdapter.Fill(m_dtCustomers)
        Me.ShowCurrentRecord()

    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?

Thanks

Christopher

Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of chrisatwork
chrisatwork

ASKER

CodeCruiser

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:
?m_daDataAdapter.UpdateCommand.CommandText

then
?m_daDataAdapter.InsertCommand.CommandText

and see what SQL query is returned.
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

etc

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

?m_daDataAdapter.SelectCommand.CommandText


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?
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)

Thanks
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

http://www.asp.net/learn/data-access/tutorial-69-vb.aspx
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.

ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.

Christopher
Information accurate as given, just did not unfortunately directly address the question.