Cannot Update MS Access Database

[mrnervios]

I am new to VB.NET 2010 and I am trying to update my Access database.
I have an application running in VB6.0 and converted to VB 2010, but now I want to use a Datagridview and can't populate with a recordset. So, I am trying to test a new small program that works for displaying the data but cannot update.
It works when using the Northwind database but not with mine.

The code is as follows:

Imports System.Data.OleDb

Public Class frmMSAccess
    'Create connection
    Dim conn As OleDbConnection

    'create data adapter
    Dim da As OleDbDataAdapter

    'create dataset
    Dim ds As DataSet = New DataSet

    'Set up connection string
    Dim cnString As String

    Dim sqlQRY As String

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'update customers table
        da.Update(ds, "Active")
    End Sub

    Private Sub frmMSAccess_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        cnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Listings.mdb"

        '     sqlQRY = "SELECT * FROM Customers WHERE City = 'London' "

        sqlQRY = "SELECT * FROM ActiveListings WHERE Status Is Null"
        conn = New OleDbConnection(cnString)

        Try
            ' Open connection
            conn.Open()

            da = New OleDbDataAdapter(sqlQRY, conn)

            'create command builder
            Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(da)

            'fill dataset
            da.Fill(ds, "Active")

            DataGridView1.DataSource = ds
            DataGridView1.DataMember = "Active"

        Catch ex As OleDbException
            MsgBox(ex.ToString)
        Finally
            ' Close connection
            conn.Close()
        End Try
    End Sub

   

  
End Class

This small program I downloaded from Planet Source and works like a charm with, as I said, Northwind.

It gives the following error:

Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND Quantity Available IS NULL) OR (Quantity Available = ?)) AND ((? = 1 AND Item Title IS NULL) OR (Item Title = ?)) AND ((? = 1 AND Item ID IS NULL) OR (Item ID = ?)) AND ((? = 1 AND Start Date IS NULL) OR (Start Date = ?)) AND ((?'.

The table has an ID (auto generated) as a primary key.

Thanks for your help

 

[jmcilhinney]

It is a very bad idea to include spaces in any identifiers and this is an example of why. It is legal but it can cause problems if you aren't constantly making allowances for it. You apparently have columns named 'Quantity Available', 'Item Title', 'Item ID' and 'Start Date'. Those columns should be named 'QuantityAvailable', 'ItemTitle', 'ItemID' and 'StartDate', all without spaces, and such issues won't arise. This should also be done for consistency because your table is named 'ActiveListings' rather than 'Active Listings', so if you don't use spaces in table names then you shouldn't be doing so in column names either.

That said, if you are unable to change the column names for whatever reason, you can still make it work. You can set the QuotePrefix and QuoteSuffix properties of the command builder to "[" and "]" respectively and it will then wrap your identifiers in those characters. Like I said, spaces are legal but cause problems if you don't specifically cater for them in your code. It's simpler to just not include them at all.

 

[jmcilhinney]

On an unrelated note, this connection string is not good:

Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=..\data\Listings.mdb

That is an unorthodox location for a database and one that is unlikely to be used once deployed. You should have just added the database to your project as a source file and then let it be copied to the output folder on build. If you don;t want to accept the default location for a database then it should be under documents or app data, not an odd location like that.

 

[mrnervios]

Thanks for your reply.

I have added cb.QuotePrefix = "["
cb.QuoteSuffix = "]"
to correct the problem and it works just fine now.

Thanks again

 

[mrnervios]

Thanks for the suggestion, obviously I would like to deploy the final product.

However I do not want to make it a source file but manage the database externally.

I have to figure it out how to do it and if I can't the I will ask for your help again if you don't mind.

Thanks