C# Programming
12-04-2009, 07:40 PM
Hello all, i had no problem at adding parameters to a stored procedure and puttng the result in a dataset until i stated trying this with a Stored Procedure with select * from table where column like something+'% that takes parameters..
The problem is that it says `invalid number` at runtime not when executing procdure at my editor, so the problem is passing the parameters but i couldn`t figure it out, btw i use Oracle;
Let me give you the codes of stored procedure and how i call it from c# :
CREATE OR REPLACE PROCEDURE OGR_USER.studentfind(std_FName nvarchar2, std_LName nvarchar2, pRc out sys_refcursor)
as
begin
open pRc for select * from ogr_user.STUDENT WHERE FNAME LIKE std_FName+'%' AND LNAME LIKE std_LName+'%';
end;
OracleParameter std_FName = new OracleParameter("std_FName", OracleDbType.NVarchar2);
std_FName.Value = name;
std_FName.Size = 100;
comm.Parameters.Add(std_FName);
OracleParameter std_LName = new OracleParameter("std_LName", OracleDbType.NVarchar2);
std_LName.Value = surname;
std_LName.Size = 100;
comm.Parameters.Add(std_LName);
comm.Parameters.Add("pRc", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
i tried parameters type varchar, nvarchar thinking that it would make a difference but no luck, i would really appreciate if anyone could help me with this, something goes wrong when i use like and % because i used the same code without a like query and there was no problem
The problem is that it says `invalid number` at runtime not when executing procdure at my editor, so the problem is passing the parameters but i couldn`t figure it out, btw i use Oracle;
Let me give you the codes of stored procedure and how i call it from c# :
CREATE OR REPLACE PROCEDURE OGR_USER.studentfind(std_FName nvarchar2, std_LName nvarchar2, pRc out sys_refcursor)
as
begin
open pRc for select * from ogr_user.STUDENT WHERE FNAME LIKE std_FName+'%' AND LNAME LIKE std_LName+'%';
end;
OracleParameter std_FName = new OracleParameter("std_FName", OracleDbType.NVarchar2);
std_FName.Value = name;
std_FName.Size = 100;
comm.Parameters.Add(std_FName);
OracleParameter std_LName = new OracleParameter("std_LName", OracleDbType.NVarchar2);
std_LName.Value = surname;
std_LName.Size = 100;
comm.Parameters.Add(std_LName);
comm.Parameters.Add("pRc", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
i tried parameters type varchar, nvarchar thinking that it would make a difference but no luck, i would really appreciate if anyone could help me with this, something goes wrong when i use like and % because i used the same code without a like query and there was no problem