none
Syntax Error in Update Statement

    Question

  • I am frantic.. I have tried everything I can think of..  No matter what I do when I try to update a Access 2002 table using the OleDbDataAdapter object in C#, I recieve the following error: "Syntax Error in Update Statement" . 

    WHat am I doing wrong??  I tried both using the wizard and doing it all manually in code.   I simply did an SQL statement of "SELECT * FROM Sked_Doctors".  Well I tried using command builder, manually typing in the update statement, adding all the paramters manually, all to no avail.  I am convinced it is the version of .NET 2003 enterprise that I own to be the problem.  IT is an OEM type version, but I would expect it to be fully functional.  Can anyone tell me if they have had any similiar problems with this???  I, have tore this thing apart to the bare bones and have read a lot of documentation on the subject .. To humor you, let me post you my final piece of code:

    private bool InitializeDataObjects()

    {

    bool blSuccess = true;

    try

    {

    string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\ThreeStar32\Data\StarData.mdb;Persist Security Info=False";

    string strSelect = "SELECT DoctorID, First, Last FROM Sked_Doctor";

    m_connStarData = new OleDbConnection(strConn);

    this.m_daSkedDoctor = new OleDbDataAdapter(strSelect, this.m_connStarData);

    OleDbCommandBuilder cmdbldr = new OleDbCommandBuilder(this.m_daSkedDoctor);

    cmdbldr.RefreshSchema();

    this.m_daSkedDoctor.UpdateCommand = new OleDbCommand( @"UPDATE Sked_Doctor SET DoctorID = ?, First = ?, Last = ? WHERE DoctorID = ?"); //cmdbldr.GetUpdateCommand();

    this.m_daSkedDoctor.UpdateCommand.CommandType = CommandType.Text;

    this.m_daSkedDoctor.UpdateCommand.Parameters.Add( new OleDbParameter("@DoctorID", OleDbType.Integer, 0,"DoctorID"));

    this.m_daSkedDoctor.UpdateCommand.Parameters.Add("@First", OleDbType.Char, 13,"First");

    this.m_daSkedDoctor.UpdateCommand.Parameters.Add("@Last", OleDbType.Char, 16, "Last");

    this.m_daSkedDoctor.UpdateCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

    this.m_daSkedDoctor.UpdateCommand.Connection = this.m_connStarData;

    this.m_daSkedDoctor.InsertCommand = cmdbldr.GetInsertCommand();

    this.m_daSkedDoctor.DeleteCommand = cmdbldr.GetDeleteCommand();

    this.m_daSkedDoctor.MissingSchemaAction = MissingSchemaAction.AddWithKey;

    this.m_daSkedDoctor.Fill(this.m_dsSkedDoctor, "Sked_Doctor");

    this.txtFirstName.DataBindings.Add("Text", this.m_dsSkedDoctor.Tables[0], "First");

    this.txtLastName.DataBindings.Add("Text", this.m_dsSkedDoctor.Tables[0], "Last");

    }

    catch(Exception err)

    {

    MessageBox.Show("Error initializing Data objects. InitializeDataObjects function.\n Error: " + err.Message, "Error");

    blSuccess = false;

    }

    finally

    {

    if(m_connStarData.State == ConnectionState.Open)

    m_connStarData.Close();

    }

    return blSuccess;

    }




    AND, This is where the error occurs, on the update statement:

    /// <summary>

    /// Updates all data in the bound Sked_Doctor datatable in the Sked_Doctor dataset to the Sked_Doctor table.

    /// </summary>

    /// <returns>True On Success, False On Failure</returns>

    private bool UpdateSkedDoctorTable()

    {

    bool blSuccess = true;

    try

    {

    m_daSkedDoctor.Update(this.m_dsSkedDoctor.Tables[0]);

    }

    catch(Exception err)

    {

    MessageBox.Show("Exception Type: " + err.GetType().ToString());

    System.Data.OleDb.OleDbException oleerr = (OleDbException) err;

    for(int i = 0; i < oleerr.Errors.Count; i++)

    Console.WriteLine(oleerr.ErrorsIdea.Message);

    MessageBox.Show("Error updating Sked Doctor dataset to StarData databaase. UpdateSkedDoctorTable function.\n"+ "Error Message: " + err.Message, "Error");

    blSuccess = false;

    }

    finally

    {

    }

    return blSuccess;

    }




    Well, I have tried it a million different ways, even have used the query builder with almost no coding done on my part.. Anyhow, I am very frustrated, and I know this should work.  I would appreciate any input that can be given to me.. THank you.


    Friday, October 28, 2005 9:30 PM

Answers

  • Put the field names in square brackets.

    You have 2 field names that are Microsoft Jet reserved words, namely First and Last.  To make a long story short, try to avoid using Jet reserved words as field names.

    See this KB for list of reserved words:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;321266

    So try this, it should work:

    this.m_daSkedDoctor.UpdateCommand = new OleDbCommand( @"UPDATE Sked_Doctor SET [DoctorID] = ?, [First] = ?, [Last] = ? WHERE [DoctorID] = ?"); //cmdbldr.GetUpdateCommand();
    Sunday, October 30, 2005 1:13 AM

All replies

  • Put the field names in square brackets.

    You have 2 field names that are Microsoft Jet reserved words, namely First and Last.  To make a long story short, try to avoid using Jet reserved words as field names.

    See this KB for list of reserved words:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;321266

    So try this, it should work:

    this.m_daSkedDoctor.UpdateCommand = new OleDbCommand( @"UPDATE Sked_Doctor SET [DoctorID] = ?, [First] = ?, [Last] = ? WHERE [DoctorID] = ?"); //cmdbldr.GetUpdateCommand();
    Sunday, October 30, 2005 1:13 AM
  • Thanks, I got it working now.. I see first and last are reserved words in ado.net or access so i changed the field names and it works... I never had any of these problems in DAO or ADO.. Funny that this happen to me in .NET..

    Sunday, October 30, 2005 2:28 AM