VS2005往Oracle数据库中插入数据失败。

bineran 2011-02-25 03:51:27

declare
segment_id NUMBER DEFAULT 0;
sx NUMBER DEFAULT 0;
ResponsiblePerson varchar(500);
begin
select NVL(max(segment_id),0)+1 into segment_id from sa.segment;
insert into sa.segment(name,ResponsiblePerson,segment_site,segment_id,segment_group_yn,user_tag_ident,long_description,Address,gmt_last_updated,rstat_type_code,depth,segmentcode)
values(sname,'暂无',segment_site,segment_id,segment_group_yn,user_tag_ident,long_description,Address,gmt_last_updated,rstat_type_code,depth,segmentcode);

end;
以上是拼接出来的字符串
下面是参数信息
OracleParameter[] par = new OracleParameter[]{
new OracleParameter("segment_site","0000000000000000"),
new OracleParameter("segment_group_yn",obj_T_Segment.segment_group_yn),
new OracleParameter("user_tag_ident",obj_T_Segment.UserTagIdent),
new OracleParameter("sname",obj_T_Segment.Name),
new OracleParameter("long_description",obj_T_Segment.long_description),
new OracleParameter("Address",obj_T_Segment.Address),
new OracleParameter("gmt_last_updated",DateTime.Now),
new OracleParameter("rstat_type_code",1),
new OracleParameter("depth",obj_T_Segment.depth ),
new OracleParameter("segmentcode",obj_T_Segment.segmentcode)};

执行失败　　错误信息是
ORA-01036: 非法的变量名/编号

...全文

148 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

bineran 2011-02-28

打赏
举报

StringBuilder strSql = new StringBuilder();
strSql.Append("declare \r\n");
strSql.Append("sid NUMBER DEFAULT 0; \r\n");
strSql.Append("begin \r\n");
strSql.Append("select NVL(max(segment_id),0)+1 into sid from sa.segment; \r\n");
strSql.Append(" insert into sa.segment(segment_site,segment_id) \r\n");
strSql.Append(" values('0000000000000000',sid);\r\n");
strSql.Append("end;\r\n");

string connectionString = @"User ID=sa;Password=sa;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.1.123)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=onlinedt)))";
int rows = 0;
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand(strSql.ToString(), connection))
{
try
{
connection.Open();
rows = cmd.ExecuteNonQuery();
}
catch (System.Data.OracleClient.OracleException E)
{
throw new Exception(E.Message);
}
finally
{ connection.Close(); }
}
}

以上这些代码都不行了。郁闷
把拼出来的字符串放到　SQLPLUS里面执行都是成功的。用C#执行就出错。搞不懂了。

一切为了你 2011-02-25

打赏
举报

看看是不是参数写少了，名称写对了没有

bineran 2011-02-25

打赏
举报

[Quote=引用 5 楼 liuzhc 的回复:]
new OracleParameter(":segment_site","0000000000000000"),
[/Quote]
问题尚未解决。依旧报错

拿凤姐抵债 2011-02-25

打赏
举报

new OracleParameter(":segment_site","0000000000000000"),

bineran 2011-02-25

打赏
举报

我用的是　OracleCommand
using (OracleConnection connection = new OracleConnection(connectionString))
{
using (OracleCommand cmd = new OracleCommand())
{
try
{

PrepareCommand(cmd, connection, null, SQLString, cmdParms);
rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
catch (System.Data.OracleClient.OracleException E)
{
return 0;
// throw new Exception(E.Message);
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}

bineran 2011-02-25