CREATEPARAMETER class??

[bnathvbdotnet]

I have a SQL statements stored in the config.xml file.

There is a WHERE clause in the sql statements. The values in the Where clause will be known only at runtime. How can I do this? Some one suggested me to use OLEDBParameter object.
The sql statement is read from config.xml file


//The value of sTerm will changed based on the resultset of previous query.

string sTerm = "Overnight";

ssql = "SELECT Term, BenchMark FROM RATESLIVE WHERE Pricing_Tier = 1 AND Term = '" + sTerm + "'";

OleDbConnection oMSAccessConnection = new OleDbConnection(sConnectionstring);

OleDbCommand oMSAccessCommand = new OleDbCommand(ssql, oMSAccessConnection);

oMSAccessConnection.Open();

OleDbDataReader oDataReader = oMSAccessCommand.ExecuteReader();

[jmcilhinney]

Your SQL code should be:

SELECT Term, BenchMark FROM RATESLIVE WHERE Pricing_Tier = 1 AND Term = @Term

then at run time you simply add a corresponding parameter to your OleDbCommand:

oMSAccessCommand.Parameters.AddWithValue("@Term", "Overnight"); // or whatever value you want.

This code is for .NET 2.0. In .NET 1.x you will have to use Add instead of AdWithValue. I really think that after 310 posts you should know to specify your version by now. Just because you don't think it's important for a particular question doesn't mean that it isn't. If you can't remember to check a radio button when you create a thread then put your version in your sugnature.

[bnathvbdotnet]

My apolozies Jm. I always check the radio button to indicate the version. But some how I forgot when my 2 year old was distracting me sitting on my lap.

Thanks for your response.

[bnathvbdotnet]

jm,

I get "Illegal variable or number" error.

string ssql = "SELECT Term, Adv_Rate,FROM RATESLIVE WHERE Term = @Term ";


OracleConnection _oOracleConnection = new OracleConnection(_sConnectionString);
OracleCommand _oOracleCommand = new OracleCommand(ssql, _oOracleConnection);
_oOracleCommand.Parameters.AddWithValue("@Term", "Overnight");

_oOracleConnection.Open();
//when executing next statement, the exception is thrown..
return _oDataReader = _oOracleCommand.ExecuteReader();

}

System.Data.OracleClient.OracleException: ORA-01036: illegal variable name/numbe
r

[jmcilhinney]

Sounds like an Oracle thing. Using "@ParameterName" is the convention for SQL Server via SqlClient. Maybe OracleClient requires a different method of naming parameters.

I checked out the help topic for the OracleParameter class and the code example they provide prefixes parameter names with a lower case "p", so try "pTerm" as the parameter name instead of "@Term".

[bnathvbdotnet]

Its actually : instead of @