0
string sql = @"SELECT sum(subtotal) FROM table1 WHERE clientid=:v1;" +
"SELECT * FROM table2  WHERE clientid = :v2 "
cmd = DBConnection.GetCommand(); 
cmd.CommandText = sql;
cmd.Parameters.Clear();
cmd.Parameters.Add(":v1", "Name1");
cmd.Parameters.Add(":v2", "Name2");
OracleDataReader reader = cmd.ExecuteReader(); //Error: Invalid Character
while (reader.Read())
{
  ..results from first query
}
reader.NextResult();
while (reader.Read())
{
  ..results from second query
}

.NET is not allowing reader.NextResults using binding variables? Why i am getting Invalid Character??

user2369009
  • 555
  • 2
  • 5
  • 11

1 Answers1

4

Remove the : when adding parameters to the command. 

cmd.Parameters.Add("v1", "Name1");
cmd.Parameters.Add("v2", "Name2");

OracleCommand.Parameters Property

When using named parameters in an SQL statement called by an OracleCommand of CommandType.Text, you must precede the parameter name with a colon (:). However, in a stored procedure, or when referring to a named parameter elsewhere in your code (for example, when adding OracleParameter objects to the Parameters property), do not precede the named parameter with a colon (:). The .NET Framework Data Provider for Oracle supplies the colon automatically.

Habib
  • 219,104
  • 29
  • 407
  • 436
  • Habib, you suggest that my query should look like: string sql = @"SELECT sum(subtotal) FROM table1 WHERE clientid=v1;" + "SELECT * FROM table2 WHERE clientid =v2 " ? – user2369009 Sep 16 '13 at 13:10
  • @user2369009, not the query text, it should be the command parameters. Refresh the page. You should **remove `:` while adding parameters** – Habib Sep 16 '13 at 13:11
  • It didn't solved my issue. The queries are returning valid results in oracle. I changed to cmd.Parameters.AddWithValue(v1, "Name1"); – user2369009 Sep 16 '13 at 15:20
  • No. your parameter should be added like `cmd.Parameters.AddWithValue("v1", "Name1");` – Habib Sep 16 '13 at 15:55
  • It looks that it was not about that. cmd.Parameters.AddWithValue("v1", "Name1"); – user2369009 Sep 17 '13 at 06:52