GRR - can't get .net oracle data provider parameter sql statement to work - please help?

From: sherifffruitfly <sherifffruitfly_at_gmail.com>
Date: 1 Nov 2006 17:52:24 -0800
Message-ID: <1162432344.753745.305130@e3g2000cwe.googlegroups.com>

Hi,

I the parameters in the commandstring never get substituted for. So I end up sending an invalid sql statement over to the oracle db. Apparently I don't understand how to parametrize sql statements. Sigh.

I've tried this both with and without the ":" in the ParameterNames - same not-substituting problem.

I'm certain I'm missing something stupid & simple - anyone want to point it out to me? I'd prefer not to go back to the commandString = "blahblahblah" + variable; bad way of doing things...

thanks for any assistance,

cdj

		public static void DeleteTestRates(DateTime start, DateTime end)
		{
			ParentFormStatusBar.Text = "Deleting rates...";



			//string oracleStart = start.ToString("dd-MMM-yyyy");

			//string oracleEnd = end.ToString("dd-MMM-yyyy");


			string strConn = "user id=cmr_app;password=cmr#app01;data
source=cmr_uat";
			OracleConnection dc_UAT = new OracleConnection(strConn);
			string oracleTable = "cmr_admin.tbl_market_intrates_test";

			OracleCommand oc = new OracleCommand();

			OracleParameter tableParam = new OracleParameter();
			tableParam.ParameterName = ":oracleTable";
			tableParam.Value = oracleTable;
			tableParam.OracleDbType = OracleDbType.Varchar2;
			tableParam.Direction = ParameterDirection.Input;
			oc.Parameters.Add(tableParam);

			OracleParameter startParam = new OracleParameter();
			startParam.ParameterName = ":oracleStart";
			startParam.Value = start;
			startParam.OracleDbType = OracleDbType.Date;
			startParam.Direction = ParameterDirection.Input;
			oc.Parameters.Add(startParam);

			OracleParameter endParam = new OracleParameter();
			endParam.ParameterName = ":oracleEnd";
			endParam.Value = end;
			endParam.OracleDbType = OracleDbType.Date;
			endParam.Direction = ParameterDirection.Input;
			oc.Parameters.Add(endParam);

			oc.CommandText = "delete from :oracleTable where as_of_date between
:oracleStart and :oracleEnd";
			oc.Connection = dc_UAT;



			//MessageBox.Show(tableParam.Value.ToString()+ "\n" +

oc.CommandText+"\n" + "Params: " + oc.Parameters.Count);

                        int rowsAffected;

			try
			{
				dc_UAT.Open();
				rowsAffected = oc.ExecuteNonQuery();
				dc_UAT.Close();
				ParentFormStatusBar.Text = rowsAffected.ToString() + " records
deleted.";
			}
			catch(Exception ex)
			{
				ParentFormStatusBar.Text = "Delete error: " + ex.Message;
			}
		}

Received on Wed Nov 01 2006 - 19:52:24 CST