Adding parameters to PL/SQL in visual studio
up vote
0
down vote
favorite
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 -

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
c# sql oracle visual-studio oracle11g
|
show 2 more comments
up vote
0
down vote
favorite
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 -

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
c# sql oracle visual-studio oracle11g
stackoverflow.com/a/11048965/34092 shows you another approach to try.
– mjwills
Nov 9 at 22:57
1
Your stack trace indicates that you're using Oracle.DataAccess (by Oracle) in your unqualified references toOracleConnectionandOracleCommandbut then you have fully qualifiedOracleTypereferences that indicate that you also have a reference to System.Data.OracleClient (by Microsoft). The call tocmd.Parameters.Addis not using the overload you expect: it's taking(string name, object value), not(string name, OracleDbType type). Try fixing that, first.
– madreflection
Nov 9 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 9 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 9 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 at 0:05
|
show 2 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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 -

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
c# sql oracle visual-studio oracle11g
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 -

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
c# sql oracle visual-studio oracle11g
c# sql oracle visual-studio oracle11g
asked Nov 9 at 22:42
ubuntu_noob
6631530
6631530
stackoverflow.com/a/11048965/34092 shows you another approach to try.
– mjwills
Nov 9 at 22:57
1
Your stack trace indicates that you're using Oracle.DataAccess (by Oracle) in your unqualified references toOracleConnectionandOracleCommandbut then you have fully qualifiedOracleTypereferences that indicate that you also have a reference to System.Data.OracleClient (by Microsoft). The call tocmd.Parameters.Addis not using the overload you expect: it's taking(string name, object value), not(string name, OracleDbType type). Try fixing that, first.
– madreflection
Nov 9 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 9 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 9 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 at 0:05
|
show 2 more comments
stackoverflow.com/a/11048965/34092 shows you another approach to try.
– mjwills
Nov 9 at 22:57
1
Your stack trace indicates that you're using Oracle.DataAccess (by Oracle) in your unqualified references toOracleConnectionandOracleCommandbut then you have fully qualifiedOracleTypereferences that indicate that you also have a reference to System.Data.OracleClient (by Microsoft). The call tocmd.Parameters.Addis not using the overload you expect: it's taking(string name, object value), not(string name, OracleDbType type). Try fixing that, first.
– madreflection
Nov 9 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 9 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 9 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 at 0:05
stackoverflow.com/a/11048965/34092 shows you another approach to try.
– mjwills
Nov 9 at 22:57
stackoverflow.com/a/11048965/34092 shows you another approach to try.
– mjwills
Nov 9 at 22:57
1
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 9 at 23:11
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 9 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 9 at 23:19
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 9 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 9 at 23:29
@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 9 at 23:29
1
1
Choose one library to use and remove the reference to the other. Then resolve any errors you get.
– madreflection
Nov 10 at 0:05
Choose one library to use and remove the reference to the other. Then resolve any errors you get.
– madreflection
Nov 10 at 0:05
|
show 2 more comments
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53234201%2fadding-parameters-to-pl-sql-in-visual-studio%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
stackoverflow.com/a/11048965/34092 shows you another approach to try.
– mjwills
Nov 9 at 22:57
1
Your stack trace indicates that you're using Oracle.DataAccess (by Oracle) in your unqualified references to
OracleConnectionandOracleCommandbut then you have fully qualifiedOracleTypereferences that indicate that you also have a reference to System.Data.OracleClient (by Microsoft). The call tocmd.Parameters.Addis not using the overload you expect: it's taking(string name, object value), not(string name, OracleDbType type). Try fixing that, first.– madreflection
Nov 9 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 9 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 9 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 at 0:05