ORA-01036 error with asp net 1

[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(@"\",@"\\");


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(',');
string bb="";



/**for(int i=0;i<arguments.Length;i++)
{
if (i>0)
{
arguments="'"+arguments+"'";
bb = arguments;


string g = bb;

}

}**/

/** for(int i=0;i<arguments.Length;i++)
{

{

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

}

}
**/





getData(arguments);

//GetInt(arguments);





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


}//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 void /**DataSet**/ getData(string[] aParams)
{

string sConn = GetConnString();

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

DataSet dsRet = new DataSet();

try
{
PopulateCommand(aParams,out objCmd); //Add the parameters to the stored procedure.

objCmd.Connection=oracleConn;

OracleDataAdapter objDA = new OracleDataAdapter(objCmd);

objDA.Fill(dsRet);



///DataTable dataTable = dsRet.Tables[0];
///DataGrid1.DataSource = dataTable;
///DataGrid1.DataBind();


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


//return dsRet;

}


/**public int GetInt(string[] aParams)
{
string sConn = GetConnString();

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

int iResult=0;

try
{

PopulateCommand(aParams,out objCmd);

objCmd.Connection=objConn;
objConn.Open();

iResult = Convert.ToInt32(objCmd.ExecuteScalar().ToString());

}
catch (Exception ex)
{

Response.Write("</br>"+"</br>"+"</br>"+"</br>"+"</br>"+"</br>"+"</br>"+"</br>"+"NOT CONNECTING TO ORACLE..........................."+ex.ToString());
//throw
//Response.Write(ex.ToString());

}
finally
{
objConn.Close();
}

return iResult;

}


**/

//***********************************************************************************************
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
{
line 351 String args = "'"+aParams+"'"; //this line is causing an error

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

param.Direction = ParameterDirection.Input;

param.Value = args;



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

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


}

}
Line 351 is giving me the following error

'Cruz'Oracle.DataAccess.Client.OracleException ORA-01036: illegal variable name/number at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String procedure, String[] args) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, IntPtr opsSqlCtx, Object src, String[] args) at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior) at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at Project1.xmlApp.getData(String[] aParams) in c:\inetpub\

http://wwwroot\project1\xmlapp.aspx.cs:line

259


Basically what i want to do is wrap all parmaters that are inputed in a single quote so that they will be treated as strings and run correctly in the stored procedure or function they are being sent to.
This is done in the following line
String args = "'"+aParams+"'"; //this line is causing an error

I'm appending a single quote to the start and end of each parameter.
I just dont understand where the error is coming from. When i take away the single quotes it all works fine.

but i need the sinlgle quotes around each variable for other parts of the app later on... Strings wont execute in oracle with out them,
any help would be appreciated

 

[ace333]

IS THERE ANYONE OUT THERE THAT CAN HELP ME WITH THIS PROBLEM, I HAVE TRIED TO SOLVE THIS ISSUE ON THE SQL SIDE WHERE I WOULD JUST ADD A SINGLE QUOTE BEFORE AND AFTER EACH VARIABLE... BUT NO ONE SEEMS TO BE ABLE TO DO THAT EITHER

I CANT BELIEVE THAT YOU CANT INSERT A VALUE INTO TABLE THAT CONTAINS SINGLE QUOTES
:-(

 

[ca8msm]

I CANT BELIEVE THAT YOU CANT INSERT A VALUE INTO TABLE THAT CONTAINS SINGLE QUOTES

You can. When you use parameters, single quotes are taken into account. If you are not using parameters you can simply use the Replace "double up" the single quote e.g.

Replace("O'Neill","'","''")

Also, please try not to use Caps Lock when writing posts as it is deemed as SHOUTING!


____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.

 

[ace333]

I know that but that is not exactly what i want to do

if you look at the code above this line in particular
String args = "'"+aParams+"'"; //this line is causing an error

I'm taking parameters in from an xml file, adding them to a string array and then passing them to a procedure or function. the problem is that i have to construct and excute the function differently from the procedure and errors are occuring if single quotes are not around the variable names that go to the function.

 

[ca8msm]

There are two things that I'm not sure I understand:

1) This line:

String args = "'"+aParams[i]+"'"; //this line is causing an error

You say it is causing an error. What error is it throwing?

2) Why do you need to add single quotes here:

    line 351        String args = "'"+aParams[i]+"'"; //this line is causing an error
    
                    param = new OracleParameter(args,OracleDbType.Varchar2);

                    param.Direction = ParameterDirection.Input;

                    param.Value = args;

As you are using parameters, if the value that is being inserted contains single quotes, that shouldn't be a problem as parameters can cope fine with these.



____________________________________________________________

Need help finding an answer?

Try the Search Facility or read FAQ222-2244 on how to get better results.