System data oledb oledbexception ошибка синтаксиса в инструкции update

I’m writing an application which stores user information. Currently the user is supposed to update their Name, Height, Weight and Birthday.

string height = TB_ClientHeight.Text;
    string weight = TB_ClientWeight.Text;
    string name = TB_ClientName.Text;
    string bday = dateTimePicker1.Value.ToString("dd-MM-yyyy");
    int heightint = Convert.ToInt32(height);
    int weightint = Convert.ToInt32(weight);

It’s updated by calling the public static string username variable from another form and using that as the WHERE UserName = @username.

usernamestringo = Login.usernameFromLogin;

I’ve followed other SO answers in this context and corrected some issues (like preventing SQL Injection). However I’m still getting a syntax error while updating these fields as claimed by OleDbException. 

using (OleDbConnection myCon = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=O:ReposDatabaseDatabase.accdb;Persist Security Info=False"))
using (OleDbCommand cmd = new OleDbCommand())
{
    cmd.CommandType = CommandType.Text;
    string query = "UPDATE TPersons SET Name=@Name, SET Height=@Height, SET Weight=@Weight, SET Bday=@Bday " + " WHERE FirstName= @username";
    cmd.CommandText = query;
    cmd.Parameters.AddWithValue("@Name", name.ToString());
    cmd.Parameters.AddWithValue("@Height", heightint.ToString());
    cmd.Parameters.AddWithValue("@Weight", weightint.ToString());
    cmd.Parameters.AddWithValue("@Bday", bday.ToString());
    cmd.Parameters.AddWithValue("@username", usernamestringo);

    cmd.Connection = myCon;
    myCon.Open();
    cmd.ExecuteNonQuery();
    MessageBox.Show("Updated!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
    cmd.Parameters.Clear();
}

The OleDbException is:

Index #0
NativeError: -526847407
Source: Microsoft Access Database Engine
SQLState: 3000
Description (message): Syntax error in UPDATE statement.

Could anyone guide me where my syntax is wrong? Thank you!

Источник
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

if(analyse.data.Length == 1) { return; }
            string path = listBox1.SelectedItem.ToString().Substring(1); //Имя файла
            int id = GetId(path); //Получаем его id по имени
 
            string query = "SELECT COUNT(*) AS num FROM Gist_info WHERE ID_photo=" + id;
            dbCommand = new OleDbCommand(query, connection);
            OleDbDataReader reader = dbCommand.ExecuteReader();
 
            string imgData = "";
            string posData = "";
            for (int i = 0; i < analyse.data.Length-1; ++i) //заполняем массив позиций картинки и цвета в формате X:Y:X:Y и R:G:B:R:G:B
            {
                imgData += analyse.data[i].getColor().R + ":" + analyse.data[i].getColor().G + ":" + analyse.data[i].getColor().B + ":";
                posData += analyse.data[i].getPoint().X + ":" + analyse.data[i].getPoint().Y + ":";
            }
            imgData = imgData.Substring(0, imgData.Length - 1); //Удалить : в конце
            posData = posData.Substring(0, posData.Length - 1); //Удалить : в конце
 
            dbCommand = new OleDbCommand(query, connection);
            dbCommand.ExecuteNonQuery();
 
            string avR = analyse.getAverageGistogramm("R").ToString(); //Получаем данные для записи в бд
            string avG = analyse.getAverageGistogramm("G").ToString();
            string avB = analyse.getAverageGistogramm("B").ToString();
            string MedR = analyse.getMed()[0].ToString();
            string MedG = analyse.getMed()[1].ToString();
            string MedB = analyse.getMed()[2].ToString();
            string SgR = analyse.getSg()[0].ToString();
            string SgG = analyse.getSg()[1].ToString();
            string SgB = analyse.getSg()[2].ToString();
            string AllBright = analyse.getAvgBrightness().ToString();
 
 
            reader.Read();
            if ((int)reader["num"] != 0) //Если есть запись
            {
                query = "UPDATE Gist_info SET AllBright='" + AllBright + "', avR = '"+ avR + "', avG='" + avG + "', avB='" + avB + "', " +
                     "Img='" + imgData + "', Pos='" + posData +"', MedR='" + MedR + "', SgR='"+ SgR +
                     "', MedG='" + MedG +"', MedB='"+ MedB + "', SgG='"+ SgG + "', SgB='" + SgB + "' WHERE ID_photo=" + id;
            }
            else
            {
                /*query = "INSERT INTO Gist_info (ID_photo, AllBright, avR, avG, avB, Img, Pos, MedR, MedG, MedB, SgR, SgG, SgB) "
               + "VALUES ("+id+", '"+analyse.getAvgBrightness()+"', '"+avR+"', '"+avG+"', '"+avB+"', '"+imgData+"', '"+posData+"', " +
               "'"+MedR+"', '"+MedG+"', '"+MedB+"', '"+SgR+"', '" +SgG+"', '"+SgB+"')";*/
                query = "INSERT INTO Gist_info (ID_photo, AllBright, avR, avG, avB, Img, Pos, MedR, MedG, MedB, SgR, SgG, SgB) VALUES " +
                    "('"+id+"', '"+AllBright+"', '"+avR+"', '"+avG+"', '"+avB+"', '" + imgData+ "', '"+posData+"', '"+MedR+"', '"+MedG+"', '"+MedB+"', '"+SgR+"', '"+SgG+"', '"+SgB+"');";
            }
            reader.Close();
 
            /*try
            {*/
                dbCommand = new OleDbCommand(query, connection); //Здесь возникает ошибка
                dbCommand.ExecuteNonQuery();
Источник

the code for update is for storedprocedures in SQL server and may not work with access DB:

        string _Update_Emp = "UPDATE AlbahraniNetwork SET FirstName=@FirstName,SecondName=@SecondName,LastName=@LastName,Phone=@Phone,Cell=@Cell,Email=@Email,Address=@Address where FirstName=@FirstName";

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);
        if (!appPath.EndsWith("\"))
            appPath += "\";
        _Conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");

        //_Conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersAbdullahdocumentsvisual studio 2010ProjectsAlbahraniNetwork2AlbahraniNetwork2Database31.accdb";
        _Conn.Open();
        OleDbCommand _Update_Command = new OleDbCommand(_Update_Emp, _Conn);

        _Update_Command.Parameters.AddWithValue("@FirstName", FirstName.Text);
        _Update_Command.Parameters.AddWithValue("SecondName", SecondName.Text);
        _Update_Command.Parameters.AddWithValue("@LastName", LastName.Text);
        _Update_Command.Parameters.AddWithValue("@Phone", Phone.Text);
        _Update_Command.Parameters.AddWithValue("@Cell", Cell.Text);
        _Update_Command.Parameters.AddWithValue("@Email", Email.Text);
        _Update_Command.Parameters.AddWithValue("@Address", Address.Text);
        _Update_Command.ExecuteNonQuery();

Make it simple and instead use:

  string _Update_Emp = "UPDATE AlbahraniNetwork SET " +
       FirstName="" + FirstName.Text + """ +
       ",SecondName="" + SecondName.Text  + """ +
       ",LastName=""+ LastName.Text  + """ +
       ",Phone=""+ Phone.Text + """ +
       ",Cell="" + Cell.Text + """ +
       ",Email="" + Email.Text + """ +
       ",Address="" + Address.Text + """ +
       " where FirstName="" + FirstName.Text +";";

        string appPath = Path.GetDirectoryName(Application.ExecutablePath);
        if (!appPath.EndsWith("\"))
            appPath += "\";
        _Conn.ConnectionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");

        //_Conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:UsersAbdullahdocumentsvisual studio 2010ProjectsAlbahraniNetwork2AlbahraniNetwork2Database31.accdb";
        _Conn.Open();
        OleDbCommand _Update_Command = new OleDbCommand(_Update_Emp, _Conn);

Источник
  • Remove From My Forums

  • Question

  • I have one code for updating the password, however the code is giving the error that «Syntax error in UPDATE statement.», before this I was getting error «Syntax
    error in Insert into statement    » which was solved by «Noam B«, the link to that page is

    here. Now i am getting these error. I tried to solve it but no success, can you guys help me once again. 

    The code that I have written is:

    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace Cyber_Application
{
    public partial class ChangePassword : Form
    {
        string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\Cyber Application\param.accdb; Jet OLEDB:Database Password=123456";

        public ChangePassword()
        {
            InitializeComponent();
        }

        private void changePassword()
        {
            OleDbConnection cn = new OleDbConnection(connectionString);
            cn.Open();
            string scmd1 = "UPDATE EnterSoftware set Password = '" + txtNewPassword.Text + "' where UserName = 'admin'";
            OleDbCommand cmd1 = new OleDbCommand(scmd1, cn);
            OleDbDataReader sdr1 = cmd1.ExecuteReader();
            cn.Close();
            MessageBox.Show("Password has been changed successfully", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information);
            this.Close();
        }

        private void btnOk_Click(object sender, EventArgs e)
        {
            OleDbConnection cn = new OleDbConnection(connectionString);
            cn.Open();
            string scmd = "select Password from EnterSoftware";
            OleDbCommand cmd = new OleDbCommand(scmd, cn);
            OleDbDataReader sdr = cmd.ExecuteReader();

            while (sdr.Read())
            {
                if (txtCurrentPassword.Text == sdr["Password"].ToString())
                {
                    if (txtNewPassword.Text == "" || txtConfirmPassword.Text == "")
                    {
                        MessageBox.Show("Password cannot be blank", "Change Password Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }

                    if (txtNewPassword.Text == txtConfirmPassword.Text && txtNewPassword.Text != "" || txtConfirmPassword.Text != "")
                    {
                        changePassword();
                    }
                    else
                    {
                        MessageBox.Show("Password do not match", "Change Password Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        txtCurrentPassword.Text = "";
                        txtNewPassword.Text = "";
                        txtConfirmPassword.Text = "";
                        txtCurrentPassword.Focus();
                    }
                }

                else
                {
                    MessageBox.Show("Invalid Current Password", "Change Password Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    txtCurrentPassword.Text = "";
                    txtNewPassword.Text = "";
                    txtConfirmPassword.Text = "";
                    txtCurrentPassword.Focus();
                }
            }
            cn.Close();
        }
    }
}

    The error code is as below:

    System.Data.OleDb.OleDbException was unhandled
  Message=Syntax error in UPDATE statement.
  Source=Microsoft Office Access Database Engine
  ErrorCode=-2147217900
  StackTrace:
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
       at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
       at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.OleDb.OleDbCommand.ExecuteReader()
       at Cyber_Application.ChangePassword.changePassword() in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationChangePassword.cs:line 27
       at Cyber_Application.ChangePassword.btnOk_Click(Object sender, EventArgs e) in E:Cyber ApplicationChangePassword.cs:line 52
       at System.Windows.Forms.Control.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnClick(EventArgs e)
       at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ButtonBase.WndProc(Message& m)
       at System.Windows.Forms.Button.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.RunDialog(Form form)
       at System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       at System.Windows.Forms.Form.ShowDialog()
       at Cyber_Application.MainForm.changePasswordToolStripMenuItem_Click(Object sender, EventArgs e) in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationMainForm.cs:line 79
       at System.Windows.Forms.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ToolStrip.WndProc(Message& m)
       at System.Windows.Forms.ToolStripDropDown.WndProc(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
       at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
       at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       at System.Windows.Forms.Application.Run(Form mainForm)
       at Cyber_Application.Program.Main() in E:ClientsS V Cyber CafeCyber ApplicationCyber ApplicationProgram.cs:line 17
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:

    • Edited by

      Monday, September 26, 2011 9:59 AM

Answers

  • The EnterSoftware 
    table contains a column named Password which is one reserved word.

    Use curly braces [Password]

    string scmd1 = "UPDATE EnterSoftware set [Password] = '" + txtNewPassword.Text + "' where UserName = 'admin'";

    • Edited by
      Zain_Ali
      Monday, September 26, 2011 10:04 AM
    • Marked as answer by
      tapan.desai
      Monday, September 26, 2011 10:09 AM

    • Marked as answer by
      tapan.desai
      Monday, September 26, 2011 10:12 AM
Источник

How to fix “System.Data.OleDb.OleDbException: 'Syntax error in UPDATE statement.'”?

What I have tried:

public void btnUpdtLicens_Click_1(object sender, EventArgs e)
        {
            string conString = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source=" + DBPath + ";";
            using (OleDbConnection con = new OleDbConnection(conString))
            {
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    foreach (DataGridViewRow row in LicenseAllctnGridView.Rows)
                    {

                        if (row.Cells[0].Value != null && row.Cells[1].Value != null)
                        {
                            OleDbDataAdapter _oda = new OleDbDataAdapter();
                            //string query = "update AllottedLicense set Department = " + row.Cells[0].Value.ToString() + ", AllottedLicense = " + row.Cells[1].Value.ToString() + ", where Department = " + row.Cells[0].Value.ToString() + "; ";
                            OleDbCommand cmd = new OleDbCommand("update AllottedLicense set Department = " + row.Cells[0].Value.ToString() + ", AllottedLicense = " + row.Cells[1].Value.ToString() + ", where Department = " + row.Cells[0].Value.ToString() + "", con);
                            _oda.SelectCommand = cmd;
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }

                }
                con.Close();
        }
    }

Источник

Понравилась статья? Поделить с друзьями:
  • System componentmodel win32exception ошибка при создании дескриптора окна
  • System cmos checksum bad default configuration used ошибка
  • System ck ошибка карриер 1300
  • System check in progress ошибка на ленд
  • System aggregateexception произошла одна или несколько ошибок