如何将texbox值传递给Oracle存储过程 [英] How to pass texbox values to Oracle stored procedure
问题描述
大家好,
任何人都可以帮我解决问题。我通过Oracle存储过程从C#登录表单验证用户名和密码。但是当点击Login按钮时,会出现这样的错误
2014年3月12日22:12:17 ERROR- Oracle.DataAccess.Client.OracleException ORA-06502:PL / SQL:数字或值错误:字符串缓冲区太小
ORA-06512:在Oracle.DataAccess.Client.OracleException.HandleErrorHelper的第1行(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: 246行
我的存储过程是
创建或替换程序RSHP10.PROC_LOGIN_AUTHENTICATION(
VRESULT OUT NUMBER,
VLOGIN_ID在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
来自RSHP10.USER_MASTER
WHERE LOGIN_ID = VLOGINID;
IF(VLOGIN_ID = VLOGINID和VLOGIN_PASSWORD = VLOGINPASSWORD和VSUPERUSER ='Y')
那么
VRESULT:= 0;
ELSIF(VLOGIN_ID = VLOGINID和VLOGIN_PASSWORD = VLOGINPASSWORD和VUSERSTATUS ='有效')
那么
VRESULT:= 1;
ELSE
VRESULT:= 2;
结束如果;
EXCEPTION
当NO_DATA_FOUND那么
VRESULT:= -1;
SYS.DBMS_SYSTEM.KSDWRT (2,'PROC_LOGIN_AUTHENTICATION'|| SQLERRM);
当其他的时候那么
VRESULT:= -2;
SYS.DBMS_SYSTEM.KSDWRT(2,'PROC_LOGIN_AUTHENTICATION'|| SQLERRM );
END PROC_LOGIN_AUTHENTICATION;
/
C#代码是
public void LoginAuthentication() // 用户登录验证功能
{
尝试
{
尝试
{
尝试
{
使用( 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();
}
其他 如果(txtPassword.Text == )
{
MessageBox.Show(Resources.Password_Required,Resources.HealthcareProfessional_MessageBox_Caption,MessageBoxButtons.OK ,MessageBoxIcon.Information);
txtPassword.Text = ;
txtPassword.Focus();
}
其他 开关(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();
隐藏();
Log.Info(txtLoginID + 已成功登录到应用程序);
break ;
case 1 :
CheckProductLicense();
break ;
默认:
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 (例外se)
{
Log.Error(se);
MessageBox.Show(se.ToString());
}
}
嗨
在程序中你提到了
VRESULT OUT NUMBER,
VLOGIN_ID在VARCHAR2,
VLOGIN_PASSWORD在VARCHAR2
并且在你的程序中你用作
cmd.Parameters。添加( VLOGIN_ID,OracleDbType.NVarchar2, 20 )。 Value = txtLoginID。 Text .Trim();
cmd.Parameters。添加( VLOGIN_PASSWORD ,OracleDbType.NVarchar2, 20 )。Value = txtLoginID。 Text .Trim() ;
cmd.Parameters。添加(新的OracleParameter( VRESULT,OracleDbType。十进制))。Direction = ParameterDirection。输出;
提供与您的程序相同的日期类型。
并检查Null和Not Null值。
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());
}
}
Hi In procedure you mention as
VRESULT OUT NUMBER,
VLOGIN_ID IN VARCHAR2,
VLOGIN_PASSWORD IN VARCHAR2
and in your program you have used as
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;
provide the same date type as in your procedure .
and also check with Null and Not Null value.
这篇关于如何将texbox值传递给Oracle存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!