0

I have written a procedure-

create or replace procedure cv(x in int,y in int,z in varchar)
as
cursor c
is
select email,collegename,cgpa,compname
from student_cv
where (cgpa>=x and yearsofexp>=y) and compname=z;
tem c%rowtype;
begin
open c;
loop
fetch c into tem;
exit when c%notfound;
insert into temp values(tem.email,tem.collegename,tem.cgpa,tem.compname);
end loop;
end;
/

and now based on two combo box values(which the user inputs) and 1 local string constitute the parameters passed to my procedure.

This is the code I am using-

 //DB_connect();
                string oracleb = "Data source=HPPRO58; user ID=system; password=deb;";
                conn = new OracleConnection(oracleb);
                //conn.Open();
                conn.Open();
                String x3 = "google";
                String x1 ;
                String x2;
                String s1 = "delete from temp";
                OracleCommand comm = new OracleCommand(s1, conn);
                comm.ExecuteNonQuery();

               // System.Data.OracleClient.OracleCommand comm2 = new System.Data.OracleClient.OracleCommand();
                OracleCommand comm2 = new OracleCommand();
                String s2 = "cv";
                comm2.Connection = conn;
                comm2.CommandText = s2;
                comm2.CommandType = CommandType.StoredProcedure;
                comm2.Parameters.Add("x", System.Data.OracleClient.OracleType.Number).Value = comboBox1.Text;
                comm2.Parameters.Add("y", System.Data.OracleClient.OracleType.Number).Value = comboBox2.Text;
                comm2.Parameters.Add("z", System.Data.OracleClient.OracleType.VarChar).Value = x3;
                //comm.Parameters.Add("x", OracleType.Number).Value = comboBox1.Text;
                comm2.ExecuteNonQuery();

                String s3 = "select * from temp";
                OracleCommand comm3 = new OracleCommand(s3, conn);
                OracleDataAdapter MyAdapter3 = new OracleDataAdapter();//adapter acts as interface btw database and dataset(which is collectio of tables)
                MyAdapter3.SelectCommand = comm;
                DataTable dTable3 = new DataTable();//datatable represents a single table in database 
                MyAdapter3.Fill(dTable3);
                dataGridView1.DataSource = dTable3;
                conn.Close();

but on running this code the error displayed is -

enter image description here

and line 59 is -

 comm2.Parameters.Add("x", System.Data.OracleClient.OracleType.Number).Value = comboBox1.Text;

therefore I think there is some problem in declaring the parameters in my code

ubuntu_noob
  • 2,305
  • 6
  • 23
  • 64
  • https://stackoverflow.com/a/11048965/34092 shows you another approach to try. – mjwills Nov 09 '18 at 22:57
  • 1
    Your stack trace indicates that you're using Oracle.DataAccess (by Oracle) in your unqualified references to `OracleConnection` and `OracleCommand` but then you have fully qualified `OracleType` references that indicate that you also have a reference to System.Data.OracleClient (by Microsoft). The call to `cmd.Parameters.Add` is not using the overload you expect: it's taking `(string name, object value)`, not `(string name, OracleDbType type)`. Try fixing that, first. – madreflection Nov 09 '18 at 23:11
  • In fact, looking at the stack trace again, that's exactly what's happening. So internally, something like a type lookup is probably returning null without a check and that's causing the exception. – madreflection Nov 09 '18 at 23:19
  • @madreflection could you tell me what changes I need to do?..I am a beginner in this and not able to undertand what changes need to be done – ubuntu_noob Nov 09 '18 at 23:29
  • 1
    Choose one library to use and remove the reference to the other. Then resolve any errors you get. – madreflection Nov 10 '18 at 00:05
  • @madreflection what is the equivalent of System.Data.OracleClient.OracleType.Number in oracle? – ubuntu_noob Nov 10 '18 at 00:14
  • See https://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDbTypeEnumerationType.htm – madreflection Nov 10 '18 at 00:21

0 Answers0