Hi All,
Can anybody help me to solve the issue. I am validating username and password from a C# login form through Oracle stored procedure. But when clicking Login button, an error is showing like this
12 Mar 2014 22:12:17 ERROR- Oracle.DataAccess.Client.OracleException ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1 at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck)
at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck)
at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
at HealthcareProfessional.FrmLogin.LoginAuthentication() in D:\R_S_Software_Services_L_L_C\Projects\VisualStudio2010\Healthcare\HealthcareProfessional1.0\HealthcareProfessional\HealthcareProfessional\FrmLogin.cs:line 246
My Stored Procedure is
CREATE OR REPLACE PROCEDURE RSHP10.PROC_LOGIN_AUTHENTICATION (
VRESULT OUT NUMBER,
VLOGIN_ID IN VARCHAR2,
VLOGIN_PASSWORD IN VARCHAR2)
IS
VLOGINID VARCHAR2(50);
VLOGINPASSWORD VARCHAR2(50);
VUSERSTATUS VARCHAR2(50);
VSUPERUSER VARCHAR2(50);
BEGIN
SELECT LOGIN_ID, LOGIN_PASSWORD, USER_STATUS, SUPER_USER
INTO VLOGINID, VLOGINPASSWORD, VUSERSTATUS, VSUPERUSER
FROM RSHP10.USER_MASTER
WHERE LOGIN_ID = VLOGINID;
IF (VLOGIN_ID = VLOGINID AND VLOGIN_PASSWORD = VLOGINPASSWORD AND VSUPERUSER = 'Y')
THEN
VRESULT := 0;
ELSIF (VLOGIN_ID = VLOGINID AND VLOGIN_PASSWORD = VLOGINPASSWORD AND VUSERSTATUS = 'Active')
THEN
VRESULT := 1;
ELSE
VRESULT := 2;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
VRESULT := -1;
SYS.DBMS_SYSTEM.KSDWRT (2, 'PROC_LOGIN_AUTHENTICATION ' || SQLERRM);
WHEN OTHERS THEN
VRESULT := -2;
SYS.DBMS_SYSTEM.KSDWRT (2, 'PROC_LOGIN_AUTHENTICATION ' || SQLERRM);
END PROC_LOGIN_AUTHENTICATION;
/
C# Code is
public void LoginAuthentication()
{
try
{
try
{
try
{
using (var con = new OracleConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
var cmd = new OracleCommand("PROC_LOGIN_AUTHENTICATION", con) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.Add("VLOGIN_ID", OracleDbType.NVarchar2, 20).Value = txtLoginID.Text.Trim();
cmd.Parameters.Add("VLOGIN_PASSWORD", OracleDbType.NVarchar2, 20).Value = txtLoginID.Text.Trim();
cmd.Parameters.Add(new OracleParameter("VRESULT", OracleDbType.Decimal)).Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
if (txtLoginID.Text == "")
{
MessageBox.Show(Resources.Login_ID_Required, Resources.HealthcareProfessional_MessageBox_Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
txtLoginID.Text = "";
txtLoginID.Focus();
}
else if (txtPassword.Text == "")
{
MessageBox.Show(Resources.Password_Required, Resources.HealthcareProfessional_MessageBox_Caption, MessageBoxButtons.OK, MessageBoxIcon.Information);
txtPassword.Text = "";
txtPassword.Focus();
}
else switch (cmd.Parameters["VRESULT"].Value.ToString())
{
case "0":
var fh = new FrmHome();
var login = new DelPassData(fh.GetLoginId);
var pwd = new DelPassData(fh.GetPassword);
login(txtLoginID);
pwd(txtPassword);
fh.Show();
Hide();
Log.Info(txtLoginID + "successfully logged into application");
break;
case "1":
CheckProductLicense();
break;
default:
MessageBox.Show(Resources.Invalid_Login_ID_Password, Resources.HealthcareProfessional_MessageBox_Caption, MessageBoxButtons.OK, MessageBoxIcon.Error);
txtLoginID.Text = "";
txtPassword.Text = "";
txtLoginID.Focus();
break;
}
cmd.Dispose();
con.Close();
con.Dispose();
}
}
catch (ArgumentException a)
{
Log.Error(a);
MessageBox.Show(a.ToString());
}
}
catch (OracleException oe)
{
Log.Error(oe);
MessageBox.Show(oe.ToString());
}
}
catch (Exception se)
{
Log.Error(se);
MessageBox.Show(se.ToString());
}
}