A Oracle update trigger time field type problems

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

When I do a ASP.NET, use the Oracle database to update the data. A int type field list, string type, and date type, and asp.NET updates in the controls always appear problem, I check the Internet a lot of things, always look ready to accept either course, after all, is to first contact operation Oracle. I originally wrote updates posted (now the problem has been solved), you have a look:

The update statement as follows: 
sql = string.Format("update xxt_registration r set r.name='{0}',r.ip='{1}',r.port='{2}',r.time='{3}',r.location='{4}',r.installtime='{5}',r.type=(select d.id from xxt_devicetype d where d.name='{6}') where r.id='{7}'",

this.txtname.Text.Trim(), this.txtip.Text.Trim(), this.txtport.Text.Trim(), this.txttime.Text.Trim(), this.txtlocation.Text.Trim(), this.txtinstalltime.Text.Trim(), this.DropDownList_tname.Text.Trim(), this.txtid.Text.Trim());

First of all, there is the question of type “ ” “ single quotes ” problem, error, as shown below:

This error is, ORA-01861: does not match the text and format string.

May be because of your table inside the time column, the data type is date type.

After you transfer a character format in the database contents, don't know how to format the character information to date.

The following is a netizen to provide examples, mistakes and solutions. Here are very grateful for his help (link address is http://zhidao.baidu.com/question/551220541?quesup2&oldq=1)

SQL> create table test_time( time   date );
The table has been created. 
SQL> insert into test_time values('2013-05-17 16:55');
insert into test_time values('2013-05-17 16:55')
                             *
ERROR is located in No. 1 That's ok:
ORA-01861: The text does not match with the format string
SQL> insert into test_time values('2013-05-17 16:55:30');
insert into test_time values('2013-05-17 16:55:30')
                             *
ERROR is located in No. 1 That's ok:
ORA-01861: The text does not match with the format string
SQL> insert into test_time values(  TO_DATE('2013-05-17 16:55:30', 'YYYY-MM-DD HH24:MI:SS') );
Has been created 1 That's ok. 

For the update statement above, I summarize as follows:

The string type can be added to the single quotes, such as r.name='{0}', r.ip='{1}', r.port='{2}'and so on are possible;

The int type is not single quotes in the Oracle database update statement, such as id='{7}'is not enough, should remove the single quotes to ID={7};

The date type can be combined with a single quotation mark, but not like r.time='{3}', should use TO_DATE () to convert formats, such as: r.time=to_date ('{3}','yyyy-mm-dd'). The control input asp.net is a string type data, update of course should be converted into the time format, update the modified as follows:

sql = string.Format("update xxt_registration r set r.name='{0}',r.ip='{1}',r.port='{2}',r.location='{3}',r.installtime=to_date('{4}','yyyy-mm-dd'),r.type=(select d.id from xxt_devicetype d where d.name='{5}') where r.id={6}",
                    this.txtname.Text.Trim(), this.txtip.Text.Trim(), this.txtport.Text.Trim(), this.txtlocation.Text.Trim(), this.Calendarinstalltime.SelectedDate.ToString("yyyy-MM-dd"), this.DropDownList_tname.Text.Trim(), this.txtid.Text.Trim());

Finally, if the other answers you need to TO_DATE format in Oracle, the following link has a detailed explanation, I believe it can help you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Winfred at December 01, 2013 - 5:12 PM