Parameter passing to database ?

[schwankieog]

I am attempting to connect to a Oracle database through c# code. for speed reason i want to use parameters. the following works fine for select statements but when i try to pass in parameters in an alter statement I receive the error "ORA-01036 illegal variable name/number" oracle states this problem occurs because the parser cannot match my parameters to parameters in the SQL. well its not hard to see the names are the same. any ways any help would be greatly appreciated. please pay attention to the parameters. the connection is an oracle connection as are the parameters, the non-standard method calls are helpers i have created since the actual program must run on any dbms type. result is a dataset. again this code work perfectly for a select statement. its just bombs on the alter statement. thanks in advance for any help. here is my code:

DataSet result = SelectQuery(tableName, sql);

command.CommandText = "alter trigger :TRIGGER " + enableDisable;

command.Parameters.Add(inst.GetNewParameter("TRIGGER", DbType.String, 255));
command.Prepare();

for (int i = 0; i < result.Tables[tableName].Rows.Count; i++) {
((IDbDataParameter) command.Parameters[&quot;TRIGGER&quot;]).Value =
result.Tables[tableName].Rows[&quot;TRIGGER_NAME&quot;].ToString();

command.ExecuteNonQuery();
}

 

[schwankieog]

bumping it back to the top, in hopes that someone will have an answer

 

[chiph]

I don't think the Oracle provider likes putting the name of the trigger to modify as a parameter. I ran into a similar situation with MS SQLServer .. you couldn't specify the name of a table to update as a parameter.

Chip H.

 

[schwankieog]

thanks for the help chip,
after further research i learned that you can only pass parameters for literal values. hopefully this will save someone a lot of time in the future if the hit this post in a search.