Member Avatar for hemavl

I have got problem in updating the access database.
THIS IS MY CODE
//

Dim con As New OleDb.OleDbConnection
    Dim spl As String
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    
 
con.ConnectionString = "PROVIDER= Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Malai\Documents\database31.mdb"
        con.Open()
        spl = "select * from REMAINDERDETAILS"
        da = New OleDb.OleDbDataAdapter(spl, con)
        da.Fill(ds, "remind")
        con.Close()
        
        ds.Tables("remind").Rows(0).Item("PERSON NAME") = nametxt.Text
        ds.Tables("remind").Rows(0).Item("DESCRIPTION") = descriptiontxt.Text
        ds.Tables("remind").Rows(0).Item("REMDATE") = datetxt.Text
        da.Update(ds, "remind")
//
this gives me the following error

//da.Update(ds, "remind")//

Syntax error (missing operator) in query expression '((NUMBER = ?) AND ((? = 1 AND PERSON NAME IS NULL) OR (PERSON NAME = ?)) AND ((? = 1 AND DESCRIPTION IS NULL) OR (DESCRIPTION = ?)) AND ((? = 1 AND REMDATE IS NULL) OR (REMDATE = ?)))'.

Can anybody help me to proceed.

Edited by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).
  • 2 Contributors
  • 1 Reply
  • 122 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by kvprajapati

Dani AI

Generated

Quick diagnosis: the error comes from the SQL the adapter/command-builder is generating — the WHERE clause in the message shows column names with spaces (for example PERSON NAME) but Access requires identifiers with spaces or special characters to be delimited. The command builder also needs a proper primary key in the DataTable so it can build an UPDATE WHERE clause that targets rows correctly; without that the generated SQL can be malformed or ambiguous.

Practical fixes and checks (apply in this order):

  • Make the SELECT return the exact columns with Access delimiters so the provider sees the real names:

    da.SelectCommand.CommandText = "SELECT [NUMBER], [PERSON NAME], [DESCRIPTION], [REMDATE] FROM REMAINDERDETAILS"

  • Make sure the DataTable has a primary key column set (the command builder uses it when generating WHERE). For example, after Fill set the table primary key to the NUMBER column:

    Dim dt As DataTable = ds.Tables("remind")
dt.PrimaryKey = New DataColumn() { dt.Columns("NUMBER") }

  • If using an OleDbCommandBuilder, force identifier quoting so Access gets brackets around names with spaces; then inspect the generated command text before calling Update:

    Using cb As New System.Data.OleDb.OleDbCommandBuilder(da)
    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"
    Debug.WriteLine(cb.GetUpdateCommand().CommandText)
End Using

Longer-term: avoid spaces or reserved words in column names (rename to PersonName or Person_Name) so SQL generation is simpler and less error-prone.

Related reference material: OleDbCommandBuilder behavior and QuotePrefix/QuoteSuffix, DataTable.PrimaryKey usage, and Access rules for reserved words/special characters are documented by Microsoft:

This expands on ’s note about providing commands for the DataAdapter and points out the concrete problems that produce the "missing operator" message seen by .

Member Avatar for kvprajapati

In order to update dataset/datatable you have to configure Insert,Update,Delete, and Select command of DataAdapter.


Use OleDbCommandBuilder class to populate insert,delete, and update commands.

Dim con As New OleDb.OleDbConnection
    Dim spl As String
    Dim ds As New DataSet
    Dim da As New OleDb.OleDbDataAdapter
    
con.ConnectionString = "PROVIDER= Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Malai\Documents\database31.mdb"
        con.Open()
        spl = "select * from REMAINDERDETAILS"
        da = New OleDb.OleDbDataAdapter(spl, con)

        Dim cb as new OleDb.OleDbCommandBuilder(da)
 
        da.Fill(ds, "remind")
        con.Close()
        
        ds.Tables("remind").Rows(0).Item("PERSON NAME") = nametxt.Text
        ds.Tables("remind").Rows(0).Item("DESCRIPTION") = descriptiontxt.Text
        ds.Tables("remind").Rows(0).Item("REMDATE") = datetxt.Text
        da.Update(ds, "remind")
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.