asp.net实现oracle更改出错，提示ORA-00911: 无效字符

Question

string sql = string.Format(@"update UDS_DEVICEINFO SET DEVICENAME=:DEVICENAME,
                                                                   DEVICETYPE=:DEVICETYPE,
                                                                   DEVICEVENDOR=:DEVICEVENDOR,
                                                                   SNNUMBER=:SNNUMBER,
                                                                   DEVICEPROVIDER=:DEVICEPROVIDER,
                                                                   PRODUCTDATE =to_date(:PRODUCTDATE,'yyyy-MM-dd'),
                                                                   DEVICENOTE=:DEVICENOTE;
                                                                   DEVICEPRICE=:DEVICEPRICE,
                                                                   BUYDATE=to_date(:BUYDATE,'yyyy-MM-dd'),
                                                                   BUYPEOPLE=:BUYPEOPLE,
                                                                   BUYTELEPHONE=:BUYTELEPHONE,
                                                                   DEVICESTATUS=:DEVICESTATUS 
                                                                   WHERE ID=:ID");

Answer 1

trunc(PRODUCTDATE) =to_date(:PRODUCTDATE,'yyyy-MM-dd'),

Answer 2

DEVICENOTE=:DEVICENOTE;
这地儿是逗号

Answer 3

 string sql = string.Format(@"update UDS_DEVICEINFO SET DEVICENAME=:DEVICENAME,
                                                                   DEVICETYPE=:DEVICETYPE,
                                                                   DEVICEVENDOR=:DEVICEVENDOR,
                                                                   SNNUMBER=:SNNUMBER,
                                                                   DEVICEPROVIDER=:DEVICEPROVIDER,
                                                                   trunc(PRODUCTDATE)=to_date(:PRODUCTDATE,'yyyy-MM-dd'),
                                                                   DEVICENOTE=:DEVICENOTE;
                                                                   DEVICEPRICE=:DEVICEPRICE,
                                                                   trunc(BUYDATE)=to_date(:BUYDATE,'yyyy-MM-dd'),
                                                                   BUYPEOPLE=:BUYPEOPLE,
                                                                   BUYTELEPHONE=:BUYTELEPHONE,
                                                                   DEVICESTATUS=:DEVICESTATUS 
                                                                   WHERE ID=:ID");
加上这个后提示ORA-00927: 缺失等号

Answer 4

参照：C#调用Oracle存储过程利用参数化SQL语句，向Oracle数据库插入DateTime类型数据时该如何设置？？？

Answer 5

    prams = new OracleParameter[] { 
                                    new OracleParameter(":userName", userName), 
                                    new OracleParameter(":pwd", pwd), 
                                    new OracleParameter(":sex", sex), 
                                    new OracleParameter(":mobile", mobileNo), 
                                    new OracleParameter(":cretType", GetLicType(cretType)), 
                                    new OracleParameter(":cretNo", cretNo),                                            
                                    new OracleParameter(":fixedPhone", fixedPhone), 
                                    new OracleParameter(":mail", email), 
                                    new OracleParameter(":postCode", postCode), 
                                    new OracleParameter(":address", contactAdd), 
                                    new OracleParameter(":dvrLicNo", drivLicense), 
                                    new OracleParameter(":dvrLicDate", getDrivLiceDay), 
                                    new OracleParameter(":recomName", referee), 
                                    new OracleParameter(":knowDesc", couponCode), 
                                    new OracleParameter(":reg_mode", "网站注册"),
                                    new OracleParameter(":birthday", birthday)
                                    };

Answer 6

new OracleParameter(":birthday", birthday)注意将datetime类型的birthday参数改为Convert.ToDateTime(birthday);new OracleParameter(":birthday", Convert.ToDateTime(birthday))

Answer 7

哦，那别加trunc,DEVICENOTE=:DEVICENOTE;
这地儿改成逗号试试

Answer 8

 string sql = string.Format(@"update UDS_DEVICEINFO SET DEVICENAME=:DEVICENAME,
                                                                   DEVICETYPE=:DEVICETYPE,
                                                                   DEVICEVENDOR=:DEVICEVENDOR,
                                                                   SNNUMBER=:SNNUMBER,
                                                                   DEVICEPROVIDER=:DEVICEPROVIDER,
                                                                   PRODUCTDAT:PRODUCTDATE,
                                                                   DEVICENOTE=:DEVICENOTE,
                                                                   DEVICEPRICE=:DEVICEPRICE,
                                                                   BUYDATE=:BUYDATE,
                                                                   BUYPEOPLE=:BUYPEOPLE,
                                                                   BUYTELEPHONE=:BUYTELEPHONE,
                                                                   DEVICESTATUS=:DEVICESTATUS 
                                                                   WHERE ID=:ID");
后边改成逗号了   还是提示确实等号

Answer 9

string product = de.ProductDate;
            DateTime dt1 = Convert.ToDateTime(product);
            string buy = de.BuyDate;
            DateTime dt2 = Convert.ToDateTime(buy);            string sql = string.Format(@"update UDS_DEVICEINFO SET DEVICENAME=:DEVICENAME,
                                                                   DEVICETYPE=:DEVICETYPE,
                                                                   DEVICEVENDOR=:DEVICEVENDOR,
                                                                   SNNUMBER=:SNNUMBER,
                                                                   DEVICEPROVIDER=:DEVICEPROVIDER,
                                                                   PRODUCTDAT:PRODUCTDATE,
                                                                   DEVICENOTE=:DEVICENOTE,
                                                                   DEVICEPRICE=:DEVICEPRICE,
                                                                   BUYDATE=:BUYDATE,
                                                                   BUYPEOPLE=:BUYPEOPLE,
                                                                   BUYTELEPHONE=:BUYTELEPHONE,
                                                                   DEVICESTATUS=:DEVICESTATUS 
                                                                   WHERE ID=:ID");
            OracleCommand cmd = new OracleCommand(sql, OracleHelper.Conn);
            cmd.Parameters.AddWithValue(":ID",de.Id );
            cmd.Parameters.AddWithValue(":DEVICENAME", de.DeviceName);
            cmd.Parameters.AddWithValue(":DEVICETYPE", de.DeviceType);
            cmd.Parameters.AddWithValue(":DEVICEVENDOR", de.DeviceVendor);
            cmd.Parameters.AddWithValue(":SNNUMBER", de.SnNumber);
            cmd.Parameters.AddWithValue(":DEVICEPROVIDER", de.DeviceProvider);
            cmd.Parameters.AddWithValue(":PRODUCTDATE", dt1);
            cmd.Parameters.AddWithValue(":DEVICENOTE", de.DeviceNote);
            cmd.Parameters.AddWithValue(":DEVICEPRICE", de.DevicePrice);
            cmd.Parameters.AddWithValue(":BUYDATE", dt2);
            cmd.Parameters.AddWithValue(":BUYPEOPLE", de.BuyPeople);
            cmd.Parameters.AddWithValue(":BUYTELEPHONE", de.BuyTelephone);
            cmd.Parameters.AddWithValue(":DEVICESTATUS", de.DeviceStatus);
            cmd.ExecuteNonQuery();
            OracleHelper.Conn.Close();

Answer 10

DEVICENOTE=:DEVICENOTE;

Answer 11

PRODUCTDAT:PRODUCTDATE,
 这忘了一个逗号 不好意思