Problems with c# calling a stored procedure

[ace333]

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Xml.XPath;
using System.Xml.Xsl;
using System.Xml;
using System.Xml.Schema;
using System.Data.Common;
using System.Data.OleDb;
using System.Text;
using MCE.ConfigHandler;
using System.Configuration;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;


namespace Project1
{
/// <summary>
/// Summary description for WebForm1.
/// </summary>
public class xmlApp: System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label lblErrors;
protected System.Web.UI.WebControls.Label lblErrors2;
protected System.Web.UI.WebControls.Label lblErrors3;
protected System.Web.UI.WebControls.Button btnRead;
protected System.Web.UI.WebControls.Label lblfileLoc;
protected System.Web.UI.WebControls.Label lblErrors4;
protected System.Web.UI.WebControls.Label lblErrors5;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.HtmlControls.HtmlInputFile inFileLocation;


private void Page_Load(object sender, System.EventArgs e)
{

/**if(inFileLocation.Value == "")
{
btnRead.Enabled = false;
lblfileLoc.Visible = true;
}
if(inFileLocation.Value != "")
{
btnRead.Enabled = true;
lblfileLoc.Visible = false;
}
**/
lblErrors.Visible = false;
lblErrors2.Visible = false;
lblErrors3.Visible = false;
lblErrors4.Visible = false;
lblErrors5.Visible = false;

}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: This call is required by the ASP.NET Web Form Designer.
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
this.btnRead.Click += new System.EventHandler(this.btnRead_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion




public void ValidationHandler(object sender, ValidationEventArgs e)
{
// Dump any validation errors to the UI

lblErrors3.Visible = true;
lblErrors3.Text = e.Message;

Response.Write(e.Message);



}

private void btnRead_Click(object sender, System.EventArgs e)
{

try
{
//****************************************************************
String loc1 = inFileLocation.Value;
loc1 = loc1.Replace(@"\",@"\\");
//string m_strFileName = @"C:\temp\e.xml";

XmlTextReader fileReader = null;
fileReader = new XmlTextReader (loc1);
String procParamName = "";
String trim ="";


while (fileReader.Read())
{
if (fileReader.NodeType == XmlNodeType.Element)
{
if (fileReader.LocalName.Equals("nameOf"))
{
procParamName = fileReader.ReadString();
procParamName = procParamName.Trim();


Response.Write(fileReader.ReadString());
}

if (fileReader.LocalName.Equals("param"))
{
trim = fileReader.ReadString();
trim = trim.Trim();
procParamName = procParamName+"," + trim;
Response.Write(fileReader.ReadString());

}
}
}




//**
XmlValidatingReader xvr = new XmlValidatingReader(fileReader);
xvr.ValidationType = ValidationType.Schema;

// Tell the validator what to do with errors
xvr.ValidationEventHandler += new ValidationEventHandler(ValidationHandler);
// Load the document, thus validating

XmlDocument xd = new XmlDocument();
xd.Load(xvr);

xvr.Close();
//xtr.Close();
//**
fileReader.Close();

//****************************************************************
Response.Write("</br>"+"</br>"+"</br>");
//Response.Write("Ans: "+procParamName+"</br>");

string strParams = procParamName;
Response.Write("XML: "+strParams+"</br>");

string [] arguments = strParams.Split(',');

getData(arguments);

//Response.Write("</br>"+"length "+arguments.Length+"</br>"+"</br>");




//****************************************************************


}//End of try block

catch(XmlException eff)
{


lblErrors4.Visible = true;
lblErrors4.Text ="File not validated"+"</br>" + eff.ToString();
}

catch (Exception eg)
{
lblErrors5.Visible = true;
lblErrors5.Text ="Please enter a file path"+"</br>"+ eg.ToString();
}




}//End of read method

/// <summary>
/// Method that is called to execute a stored procedure against the database and return a DataSet.
/// </summary>
/// <param name="aParams">A set or parameters to populate the command object.</param>
/// <returns>A dataset containing the results of the stored procedure.</returns>
public DataSet getData(string[] aParams)
{

string sConn = GetConnString();

OracleConnection oracleConn = new OracleConnection(sConn);
OracleCommand objCmd;

DataSet dsRet = new DataSet();

try
{
PopulateCommand(aParams,out objCmd);

objCmd.Connection=oracleConn;

OracleDataAdapter objDA = new OracleDataAdapter(objCmd);

objDA.Fill(dsRet);

}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
finally
{
oracleConn.Close();
}

return dsRet;

}



//***********************************************************************************************
private void PopulateCommand(string[] aParams,out OracleCommand objCmd)
{
objCmd = new OracleCommand();

objCmd.CommandType=CommandType.StoredProcedure;

OracleParameter param;

for (int i=0;i< aParams.Length;i++)
{
String par = aParams;
if (i==0)
{
objCmd.CommandText= aParams[0];
Response.Write("<br>"+"<br>"+"<br>"+"<br>"+"<br>"+aParams[0]);
}//End if.

else
{


param = new OracleParameter(aParams,OracleDbType.Varchar2);

Response.Write("<br>"+"<br>"+"<br>"+"<br>"+"<br>"+param);

objCmd.Parameters.Add(param);

}//End else.

}//End for loop.

}//End PopulateCommand method.


//*************************************************************************************************



//*************************************************************************************************
static public string GetConnString()
{
string sDBServer = SecurityConfig.GetVersionSetting("DBServer");
string sDBUser = SecurityConfig.GetVersionSetting("DBUser");
string sDBPassword = SecurityConfig.GetVersionSetting("DBPassword");
string sDBProvider = SecurityConfig.GetVersionSetting("DBProvider");
StringBuilder sb = new StringBuilder();

//sb.Append("Provider=");
//sb.Append(sDBProvider);
sb.Append("User Id=");
sb.Append(sDBUser);
sb.Append(";Password=");
sb.Append(sDBPassword);
sb.Append(";Data Source=");
sb.Append(sDBServer);


return sb.ToString();
}//End of method

//***********************************************************************************************************


}

}

The following code is designed to read values from xml, validate them, call the stored prcedure and execute it with the parameters that are present. The problem is that while everything executes the values that i want written to the database are not being written but instead null values are being written to the database instead. I have not used the paramter builder in oracle before so I'm wondering have i made an error there. I checked the stored procedure in oracle on its own and it works fine.
Any ideas would be well received

 

[chiph]

Can you summarize your problem in one paragraph or less?

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first

 

[cappmgr]

In post thread855-1097679
you posted that private void PopulateCommand(string[] aParams,out OracleCommand objCmd)
worked.
In that method you have

String args = aParams[i];   
param = new OracleParameter(args,OracleDbType.Varchar2);
param.Direction = ParameterDirection.Input;
param.Value = args;

I do not see it in this post.
I am also unclear how it works. Don't you also have to pass the parameter name?
Marty

 

[ace333]

I'm taking the paramaters from an xml file, adding them to an array called aParams and then passing to the oracle paramter method. The reason the above code was not working is because the line
param.Direction = ParameterDirection.Input;
was missing. There was nothing to tell the application that the paramters were to be treated as input. Without that line three null values were being added instead