c# stored proc issue...

[ace333]

CREATE OR REPLACE PACKAGE WES_UAT.MAILSERVICE

AS

PROCEDURE InsertEMail

(

i_SendTo IN EMAIL.SendTo%TYPE,

i_SendFrom IN EMAIL.SendFrom%TYPE,

i_Subject IN EMAIL.Subject%TYPE,

i_EMailBody IN EMAIL.EMailBody%TYPE,

i_BodyFormat IN EMAIL.BodyFormat%TYPE,

i_Priority IN EMAIL.Priority%TYPE,

i_LastUpdated IN EMAIL.LastUpdated%TYPE DEFAULT SYSDATE,

i_errorEmailAddress IN EMAIL.errorEmailAddress%TYPE,

i_ccEmailAddress IN EMAIL.ccEmailAddress%TYPE,

i_systemName IN EMAIL.systemName%TYPE,

i_inputDate IN EMAIL.inputDate%TYPE DEFAULT SYSDATE

 

);

 

PROCEDURE InsertEMail

(

i_SendTo IN EMAIL.SendTo%TYPE,

i_SendFrom IN EMAIL.SendFrom%TYPE,

i_Subject IN EMAIL.Subject%TYPE,

i_EMailBody IN EMAIL.EMailBody%TYPE,

i_BodyFormat IN EMAIL.BodyFormat%TYPE,

i_Priority IN EMAIL.Priority%TYPE,

i_LastUpdated IN EMAIL.LastUpdated%TYPE DEFAULT SYSDATE,

i_errorEmailAddress IN EMAIL.errorEmailAddress%TYPE,

i_ccEmailAddress IN EMAIL.ccEmailAddress%TYPE,

i_systemName IN EMAIL.systemName%TYPE,

i_inputDate IN EMAIL.inputDate%TYPE DEFAULT SYSDATE

 

 

 

 

)

IS

BEGIN

 

INSERT INTO

EMail

(SendTo,

SendFrom,

Subject,

EMailBody,

BodyFormat,

Priority,

LastUpdated,

errorEmailAddress,

ccEmailAddress,

systemName,

inputDate

)

VALUES

(i_SendTo,

i_SendFrom,

i_Subject,

i_EMailBody,

i_BodyFormat,

i_Priority,

i_LastUpdated,

i_errorEmailAddress,

i_ccEmailAddress,

i_systemName,

i_inputDate

);

 

COMMIT;

 

END INsertEMail;

 

The procedure is part of a package...

 

This is the c# that calls it...

 

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 Oracle.DataAccess.Client;

using Oracle.DataAccess.Types;

 

using System.Web.Mail;

using log4net;

using MCE.ConfigHandler;

 

namespace WebApplication4

{

/// <summary>

/// Summary description for WebForm1.

/// </summary>

public class WebForm1 : System.Web.UI.Page

{

 

public OracleConnection _oraConnection = null;

public void Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

 

OracleCommand _oraCommand = new OracleCommand();

_oraCommand.CommandText = "MAILSERVICE.InsertEMail";

_oraCommand.CommandType = CommandType.StoredProcedure;

_oraCommand.Connection = GetConnection();

_oraCommand.Parameters.Add("i_SendTo", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";

_oraCommand.Parameters.Add("i_SendFrom", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";

_oraCommand.Parameters.Add("i_Subject", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";

_oraCommand.Parameters.Add("i_EMailBody", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";

_oraCommand.Parameters.Add("i_BodyFormat", OracleDbType.Double, ParameterDirection.Input).Value = 1;

_oraCommand.Parameters.Add("i_Priority", OracleDbType.Double, ParameterDirection.Input).Value = 1;

 

_oraCommand.Parameters.Add(" i_errorEmailAddress", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";

_oraCommand.Parameters.Add(" i_ccEmailAddress", OracleDbType.Varchar2, ParameterDirection.Input).Value = "noel.nicholson@csfb.com";

_oraCommand.Parameters.Add(" i_systemName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "CC";

 

 

try

{

_oraCommand.ExecuteNonQuery();

}

catch (OracleException oex)

{

Response.Write("Failed to execute stored procedure MAILSERVICE.InsertEMail"+"<br/>"+"<br/>"+ oex);

}

}

 

public OracleConnection GetConnection()

{

if ( _oraConnection == null )

{

_oraConnection = new OracleConnection(SecurityConfig.GetVersionSetting("WebSecurityDB") + SecurityConfig.GetVersionSetting("WebSecurityDBIdentity"));

_oraConnection.Open();

}

return _oraConnection;

}

 

#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.Load += new System.EventHandler(this.Page_Load);

 

}

#endregion

}

}

 

 

This is the error I am getting...

 

Failed to execute stored procedure MAILSERVICE.InsertEMail

 

Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 103: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null others avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe 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.ExecuteNonQuery() at WebApplication4.WebForm1.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\webapplication4\webform1.aspx.cs:line 50

 

 

The error revolves around the three extra columns i have added and trying to added data to them

 

i_errorEmailAddress,

i_ccEmailAddress,

i_systemName,

 

I have no idea what the problem is as all the other values work and it seems the exact same.....

 

Can anyone spot the problem,,,

I dont think the column values are wrong as two are taken from sysdate so are not there as parameters but i'm not sure :(

[Diesel]

Did you write the code? You seemed suprised the error catching worked...

 

Do you have to declare then define the procedure instead of just defining it as you do?

 

Anyway, I noticed that the 3 params you are talking of have spaces before their names, as such:

 

_oraCommand.Parameters.Add(" i_errorEmailAddress", OracleDbType

 

That might be the problem.

[ace333]

i spotted that too

 

Did you write the code? You seemed suprised the error catching worked...

 

Do you have to declare then define the procedure instead of just defining it as you do?

 

Anyway, I noticed that the 3 params you are talking of have spaces before their names, as such:

 

_oraCommand.Parameters.Add(" i_errorEmailAddress", OracleDbType

 

That might be the problem.

 

ya to my absolute horror i noticed that as well, i spend a day staring at this problem,,,, i nearly threw the computer across the room in frustration