System data oledb oledbexception ошибка синтаксиса в инструкции update - Большая энциклопедия инструкций и руководств

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();

Источник

Есть приложение с БД Access. Нужно чтоб по запросу SQL UPDATE SET изменял все боксы, не могу додумать как сделать обновление всех , могу только одного (код вставлю ниже)

int kod = Convert.ToInt32(textBox2.Text);// переменную код сохранится то значение которое ввели в текст бокс
string query = "UPDATE Воспитанники SET ФИО ='" + textBox3.Text + "' WHERE Код = " + kod;
OleDbCommand command = new OleDbCommand(query, myConnection);// в созданную переменную команда вставляем запрос переменную кверти и после указываем переменную по БД
command.ExecuteNonQuery(); // удаление
MessageBox.Show("Данные о воспитаннике успешно изменены "); 
this.воспитанникиTableAdapter.Fill(this.database1DataSet.Воспитанники);// чтоб просмотр обновлялся

Pekor

4492 серебряных знака14 бронзовых знаков

задан 9 мая 2022 в 18:24

У тебя в Update только ФИО, надо в запрос добавить также другие поля:

string query = "UPDATE Воспитанники SET ФИО = @FIO, пол = @pol, датарождения = @dr ... where Код = " + kod;
OleDbCommand cmd = new OleDbCommand(sql, con);
cmd.Parameters.Add("@FIO", textBox4.Text);
cmd.Parameters.Add("@pol", textBox5.Text);
cmd.Parameters.Add("@dr ", textBox6.Text);
    ...
cmd.ExecuteNonQuery();
con.Close(); //con это твое подключение к БД

ответ дан 9 мая 2022 в 18:51

PekorPekor

4492 серебряных знака14 бронзовых знаков

Источник

I have been getting a syntax error in my UPDATE datagridview code which happens to work in another .cs file. My group has been looking at different solutions online but everything won’t work.

My group has been looking at different solutions online but everything won’t seem to work.

        {
            connection.Open();
            OleDbCommand cmd = connection.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "Update Table1 set treatment = '" + treat.Text + "', remarks = '" + appRemarks.Text + "', cost = '" + treatCost.Text + "', Time = '" + textBox2.Text + "' where lastName = '" + Lastname.Text + "' ";


            cmd.ExecuteNonQuery();
            connection.Close();
            MessageBox.Show("Updated Successfully!");
        }

The expected output should be Updated Successfully! and it should reflect in the database file after clicking the update button. Sometimes the output is «Microsoft Engine database» which does not save the changes.

The error says «System.Data.OleDb.OleDbException: ‘Syntax error in UPDATE statement.'» pointing to cmd.ExecuteNonQuery();

asked May 28, 2019 at 16:58

First, never use string concatenation to build a query. You’re asking for a SQL Injection attack. The biggest thing I could see here is make sure that only columns that are string columns (varchar, char, text, etc..) have single-quoted values. Is cost a number? If so then it should be:

, cost=" + treatCost.Text + ",

If cost is a number, also make sure that there isn’t a currency amount in the input field. If someone puts in 1,422.00 it’s not a number and will fail since , is for decoration.
If someone puts in $1422.00 it’s not a number as $ is for decoration.
Either of these would fail the query.

answered May 28, 2019 at 17:07

Mark FitzpatrickMark Fitzpatrick

1,6141 gold badge11 silver badges8 bronze badges

This would happen if someone types an apostrophe into the remarks field, which SQL server will interpret as the ending quote of the string. But much worse things can happen if the user knows a bit of sql and wants to cause trouble. For example, putting '-- in the remarks will result in

Update Table1 set treatment = 'blah', remarks = ''-- where lastName = 'foobar'

which will overwrite every row in the table, not only the one containing foobar.

Use query parameters so that user-provided values can’t be interpreted as query keywords and structure.

Instead of remarks = '" + appRemarks.Text + "' you will have remarks = @Remarks as well as

cmd.Parameters.Add("@Remarks", SqlDbType.NText).Value = appRemarks.Text;

and all the other user inputs likewise.

answered May 28, 2019 at 17:21

Ben VoigtBen Voigt

278k43 gold badges420 silver badges721 bronze badges

Источник

Remove From My Forums

Question
I have the following code block and its giving me more problems than it should. It is a simple update command to an access database. For whatever reason it keeps throwing an exception on my DataAccessLayer. Any thoughts would be greatly appreciated:

Code:
```
public static Boolean SubmitLocalDB(long TestCounter, double YLoad, double YStrength, double Elongation, int BadData, int Ignore, string Direction, int Attempt)
        {
            string SqlQuery = ("UPDATE Results SET [Yield Load] = ?, [Yield Strength] = ?, [ElongationPercent] = ?, BadData = ?, Ignore = ?, Direction = ?, Complete = 1 WHERE TestCounter = ?");
            Boolean results = false;
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = ConfigurationManager.AppSettings.Get("LocalDBConnectionString");
            try
            {
                conn.Open();
                using (OleDbCommand cmd = new OleDbCommand(SqlQuery, conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("[Yield Load]", YLoad);
                    cmd.Parameters.AddWithValue("[Yield Strength]", YStrength);
                    cmd.Parameters.AddWithValue("ElongationPercent", Elongation);
                    cmd.Parameters.AddWithValue("BadData", BadData);
                    cmd.Parameters.AddWithValue("Ignore", Ignore);
                    cmd.Parameters.AddWithValue("Direction", Direction);
                    cmd.Parameters.AddWithValue("TestCounter", TestCounter);
                    
                    cmd.ExecuteNonQuery();
                    results = true;
                }
            }
            catch (Exception ex) { if (Attempt == 1) { log = new WriteToLog("DAL: " + ex.ToString()); } results = false; }
            finally { conn.Dispose(); conn.Close(); }
            return results;
        }
```
Exception:
System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement.
   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.ExecuteNonQuery()
   at DataAccess.DAL.SubmitLocalDB(Int64 TestCounter, Double YLoad, Double YStrength, Double Elongation, Int32 BadData, Int32 Ignore, String Direction, Int32 Attempt) in C:\Documents and Settings\ctz2165\Desktop\Local Projects\TensileTesterDataSync\v2.0.0\uss.mto.operations.TensileTester\DataAccess\DAL.cs:line
183

Answers

It seems the issue was with my Ignore field… I changed it to [Ignore] and it worked… Thanks for your help anyway.
- Marked as answer by
  
  Monday, March 5, 2012 10:01 PM

Источник

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();
        }
    }

Источник

Question

Answers