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 -



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










share|improve this question





















  • 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 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











  • @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














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 -



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










share|improve this question





















  • 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 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











  • @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












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 -



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










share|improve this question













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







c# sql oracle visual-studio oracle11g






share|improve this question













share|improve this question











share|improve this question




share|improve this question










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 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











  • @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






  • 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











  • 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

















active

oldest

votes











Your Answer






StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);













 

draft saved


draft discarded


















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






























active

oldest

votes













active

oldest

votes









active

oldest

votes






active

oldest

votes















 

draft saved


draft discarded















































 


draft saved


draft discarded














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





















































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







Popular posts from this blog

Kleinkühnau

Makov (Slowakei)

Deutsches Schauspielhaus