none
Syntax Error In Update RRS feed

  • Question

  • I am trying to get get the example below to work but I get a Syntax Error on Update
    on the line near the bottom m_dataAdapter.Update(mUsers);


    namespace DemoApp4
    {
        public partial class Form1 : Form
        {
            private string strUserid,strpaswd;
           
            OleDbConnection m_cnADONetConnection = new OleDbConnection(); //(1)
            OleDbDataAdapter  m_dataAdapter;
            DataTable mUsers = new DataTable();
           
            // int below is to keep track of the users current position(row) within the DataTable.
            int m_rowPosition = 0;
    
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                txtUserId.Text = "";
                txtPassword.Text = "";
               
                m_cnADONetConnection.ConnectionString =
                  @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DeveloperInfo\FilesInfoFromWork\GamesPgmingOtherCds.mdb"; //(2)
    
                m_cnADONetConnection.Open(); //(3)
                
                m_dataAdapter =
                  new OleDbDataAdapter("Select * from Address_Sec", m_cnADONetConnection);
    
                OleDbCommandBuilder m_cbCommandBuilder =
                   new OleDbCommandBuilder(m_dataAdapter);
                
                m_dataAdapter.Fill(mUsers);
    
                //had to add this code below because the flow was never going to the ShowCurrentRecord procedure(method)
                //The code in the book example did not have the line below.So that was a bug in the example from the book
                this.ShowCurrentRecord();
    
            }
            private void Form1_FormClosing(object sender, FormClosingEventArgs e)
            {
                //close connection
                m_cnADONetConnection.Close();
                m_cnADONetConnection.Dispose(); //releases all resources that it owns
            }
               private void ShowCurrentRecord()
               { 
                 if (mUsers.Rows.Count == 0)
                 {
                     txtUserId.Text = "";
                     txtPassword.Text = "";
                 }
                
                        txtUserId.Text = mUsers.Rows[m_rowPosition]["UserId"].ToString();
                        txtPassword.Text = mUsers.Rows[m_rowPosition]["Password"].ToString();
    
               }
               private void btnClose_Click(object sender, EventArgs e)
               {
                   this.Close();
               }
               private void btnMoveFirst_Click(object sender, EventArgs e)
               {
                   m_rowPosition = 0;
                   this.ShowCurrentRecord();
               }
               private void butnMovePrevious_Click(object sender, EventArgs e)
               {
                  
                   if(m_rowPosition !=0)
                   {
                       m_rowPosition -- ;
                       this.ShowCurrentRecord();
                   } 
               }
               private void btnMoveNext_Click(object sender, EventArgs e)
               {
                   
                   if(m_rowPosition < mUsers.Rows.Count - 1)
                   {
                       m_rowPosition++;
                       this.ShowCurrentRecord();
                   }
               }
    
               private void btnMoveLast_Click(object sender, EventArgs e)
               {
                   
                   if(mUsers.Rows.Count !=0)
                   {
                       m_rowPosition = mUsers.Rows.Count - 1;
                       this.ShowCurrentRecord();
    
                   }
               }
    
               private void btnSave_Click(object sender, EventArgs e)
               {
                   
                   if(mUsers.Rows.Count !=0)
                   {
                       mUsers.Rows[m_rowPosition]["UserId"] = txtUserId.Text;
                       mUsers.Rows[m_rowPosition]["Password"] = txtPassword.Text;
                       
                       
                      // m_dataAdapter.UpdateCommand = m_cbCommandBuilder.GetUpdateCommand();
                       m_dataAdapter.Update(mUsers);
                   }
               }
    
               private void btnAddNew_Click(object sender, EventArgs e)
               {
                   
                   DataRow drNewRow = mUsers.NewRow();
                   drNewRow["UserId"] = txtNewUserId.Text;
                   drNewRow["Password"] = txtNewPassword.Text;
                   mUsers.Rows.Add(drNewRow);
                   m_dataAdapter.Update(mUsers);
                   m_rowPosition = mUsers.Rows.Count - 1;
                   this.ShowCurrentRecord();
               }
        }
    }



    Tuesday, February 6, 2018 6:54 AM

Answers

  • Hello,

    In the future when asking a question provide the exact error message thrown. Also place your code into a code block via this button.

    Okay, without knowing the error message but know the error is with the update where the update command was generated by the objects used my guess would be there is no auto-incrementing primary key in the table or one or more field names is a reserve word or has spaces in them.

    When you allow the DataAdapter to set the Update command then if something is wrong it's a black box. I would highly suggest setting the update statement yourself as shown here.

    If you read the remarks on this page it gives you insight to one issue.

    During Update, if this property is not set and primary key information is present in the DataSet, the UpdateCommand can be generated automatically if you set the SelectCommand property and use the OleDbCommandBuilder. Then, any additional commands that you do not set are generated by the OleDbCommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information, see Generating Commands with CommandBuilders.

    So we need to know more about the database table is the bottom line along with the error messages.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by x5789vbn Wednesday, February 7, 2018 12:30 PM
    Tuesday, February 6, 2018 10:47 AM
    Moderator
  • Hello x5789vbn,

    Wizend is correct, you could try to adopt Wizend's advice. I tested the code and it works well now.

            private void btnSave_Click(object sender, EventArgs e)
            {
                var  commandBuilder = new OleDbCommandBuilder(m_dataAdapter);
                commandBuilder.QuotePrefix = "[";
                commandBuilder.QuoteSuffix = "]";
    
                if (mUsers.Rows.Count != 0)
                {
                    mUsers.Rows[m_rowPosition]["UserId"] = txtUserId.Text;
                    mUsers.Rows[m_rowPosition]["Password"] = txtPassword.Text;     
    
                    m_dataAdapter.Update(mUsers);
                }
            }

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by x5789vbn Wednesday, February 7, 2018 12:26 PM
    Wednesday, February 7, 2018 8:18 AM
    Moderator

All replies

  • Hello,

    In the future when asking a question provide the exact error message thrown. Also place your code into a code block via this button.

    Okay, without knowing the error message but know the error is with the update where the update command was generated by the objects used my guess would be there is no auto-incrementing primary key in the table or one or more field names is a reserve word or has spaces in them.

    When you allow the DataAdapter to set the Update command then if something is wrong it's a black box. I would highly suggest setting the update statement yourself as shown here.

    If you read the remarks on this page it gives you insight to one issue.

    During Update, if this property is not set and primary key information is present in the DataSet, the UpdateCommand can be generated automatically if you set the SelectCommand property and use the OleDbCommandBuilder. Then, any additional commands that you do not set are generated by the OleDbCommandBuilder. This generation logic requires key column information to be present in the DataSet. For more information, see Generating Commands with CommandBuilders.

    So we need to know more about the database table is the bottom line along with the error messages.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by x5789vbn Wednesday, February 7, 2018 12:30 PM
    Tuesday, February 6, 2018 10:47 AM
    Moderator
  • Additionally, sometimes you run into a Syntax Error when there exist any reserved words in your database that are not wrapped in square brackets. 

    In such cases it may help to add the following lines to your code:

    OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_dataAdapter);
    m_cbCommandBuilder.QuotePrefix = "[";
    m_cbCommandBuilder.QuoteSuffix = "]";
    wizend

    • Proposed as answer by Fei HuModerator Wednesday, February 7, 2018 8:18 AM
    Tuesday, February 6, 2018 2:02 PM
  • Password is a reserved word in Jet SQL. I would recommend changing the column name to see if it makes any difference.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Tuesday, February 6, 2018 3:03 PM
  • Hello x5789vbn,

    Wizend is correct, you could try to adopt Wizend's advice. I tested the code and it works well now.

            private void btnSave_Click(object sender, EventArgs e)
            {
                var  commandBuilder = new OleDbCommandBuilder(m_dataAdapter);
                commandBuilder.QuotePrefix = "[";
                commandBuilder.QuoteSuffix = "]";
    
                if (mUsers.Rows.Count != 0)
                {
                    mUsers.Rows[m_rowPosition]["UserId"] = txtUserId.Text;
                    mUsers.Rows[m_rowPosition]["Password"] = txtPassword.Text;     
    
                    m_dataAdapter.Update(mUsers);
                }
            }

    Best regards,

    Neil Hu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by x5789vbn Wednesday, February 7, 2018 12:26 PM
    Wednesday, February 7, 2018 8:18 AM
    Moderator