How to pass texbox values to Oracle stored procedure

See more:

C#

Oracle

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() // Function for User Login Authentication
        {
            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());
            }
        }