Oracle数据库，SQL Update语句将不起作用(OLEDB) [英] Oracle Database, SQL Update statement will not work (OLEDB)

问题描述

我设置了一个数字主键和一个存储卡车FINS的字母数字字段，该字段只是数字和字母的随机组合.我不生成鳍，这些鳍将始终与卡车车队的标识号相同.

I set a numeric primary key, and an alphanumeric field that stores truck FINS, which is just a random mix of numbers and letters. I do not generate the fins, these fins will always be the same as it's the truck fleet identification number.

这是代码视图:

 storeTruckSplit = truckSplit[1];//Stores truck FIN


        //Update truck value
        try
        {
            conn.Open();
            OleDbCommand command;
            command = new OleDbCommand(
                "Update Trucks" +
                " SET Trucks.TruckInUse = ? WHERE TFIN = " + storeTruckSplit.ToString(), conn);
            command.Parameters.Add(new OleDbParameter("@use", "T"));
            command.ExecuteNonQuery();//Commit   
            conn.Close();
        }
        catch (OleDbException exception)
        {
            MessageBox.Show(exception.Message, "OleDb Exception");
        }

这是表格视图:

CREATE TABLE Trucks
(

TruckID number(9)  CONSTRAINT TRUCK_PK PRIMARY KEY,
TFIN char(9) NOT NULL,
TruckCategory varchar(80) NOT NULL,
TruckCodeName varchar(50) NOT NULL,
MaxWeight number(10) NOT NULL,
TruckSize number(10) NOT NULL,
TruckInUse varchar(1) NULL

);

在此表上插入 TRUCKID 之前，还有一个序列和触发器.

There is also a sequence and trigger for before inserting on this table for the TRUCKID.

我得到的是 ORA-00904 ，请注意，这显示的是 C6977734D ，它是在更新的where子句中使用的卡车鳍.

I am getting ORA-00904 and note this is showing C6977734D which is a truck fin that is used on the where clause of the update.

确切消息:

在处理命令期间发生了一个或多个错误.ORA-00904:"C6977734D":无效的缩进符.

"One or more errors occurred during processing of command. ORA-00904: "C6977734D": invalid indentifer.

推荐答案

也将 TFIN 值也设置为参数:

    command = new OleDbCommand(
            "Update Trucks" +
            " SET Trucks.TruckInUse = ? WHERE TFIN = ?", conn);
        command.Parameters.Add(new OleDbParameter("@use", "T"));
        command.Parameters.Add(new OleDbParameter("@tfin", storeTruckSplit));
        command.ExecuteNonQuery();//Commit   

就目前而言，您并没有在要过滤的值周围加上引号，因此查询将其视为标识符(字段，变量等)，而不是常量.由于您已经在使用参数作为使用中"的值了(由于您要提供一个恒定值，所以这不是必需的)，最好的解决方法是也将参数用于过滤器.

As it stands you are not putting quotes around the value you're filtering on, so the query is treating it as an identifier (field, variable, etc) rather than a constant value. Since you're already using a parameter for the "in use" value (which is not necessary since you're providing a constant value) the best fix is to use a parameter for the filter as well.

这篇关于Oracle数据库，SQL Update语句将不起作用(OLEDB)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！