Please help me to solve oracle database error

Question

0.00/5 (No votes)

See more:

"One or more errors occurred during processing of command.
ORA-00936: missing expression "

error name

table is test3 with one attribute name varchar2(20)

coding is

C#

public string show(string country)
{
string var;

            OleDbConnection cn = new OleDbConnection("Provider=MSDAORA.1;User ID=system;password=tiger;Data Source=XE;Persist Security Infor=False"); 
            
                       
            try
            {
                cn.Open();
                string s = "insert into test3(NAME) values(@nam)";


                OleDbCommand cmd = new OleDbCommand(s, cn);
                cmd.Parameters.AddWithValue("@nam", country);
 
                cmd.ExecuteNonQuery(); 
                var="success";
            } 
            catch (OleDbException e1) 
            { 
                Console.Write(e1.Message);
                Console.Write(e1.StackTrace);
                var = e1.Message;
            }
            cn.Close();
            return var;
}

Posted 23-Jul-13 6:59am

Member 8780842

Updated 23-Jul-13 7:49am

CHill60

v3

Add a Solution

Comments

ZurdoDev 23-Jul-13 13:11pm

What's the value of country?

Member 8780842 23-Jul-13 13:37pm

country is passed as parameter

ZurdoDev 23-Jul-13 13:48pm

I know, what is the value though. The error indicates you have a syntax issue.

ZurdoDev 23-Jul-13 13:49pm

You may need to put single quotes in your statement.

insert into test3(NAME) values('@nam')

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

woopsydoozy · Answer 1 · 2013-07-23T07:55:00

Solution 1

2 issues:
1) I don't know that "@" is a valid character for identifying parameters in Oracle
2) When you add the parameter and specify the name, do not use the escape character

So more like this:

C#

string s = "insert into test3(NAME) values(:nam)";

and

C#

cmd.Parameters.AddWithValue("nam", country);

Posted 23-Jul-13 7:55am

woopsydoozy

Comments

CHill60 23-Jul-13 14:00pm

Sorry for the overlap on solutions - you can type faster than me :-)

CHill60 · Answer 2 · 2013-07-23T07:58:00

Solution 2

I don't think that OLEDb supports named parameters in the command ... try the following instead (warning - my syntax might not be perfect)

string s = "insert into test3(NAME) values(?)";
OleDbParameter pNamVal = New OleDbParameter("@nam", OleDbType.Varchar2, 20)
 pNamVal.Value = country

Posted 23-Jul-13 7:58am

CHill60

Comments

Member 8780842 24-Jul-13 3:39am

I tried this sir

public string show(decimal busid,decimal totalseats,string bustype,string journeydate,string arrtime,string depttime,string source,string destination,decimal ticketprice,string bpoint,string dpoint)
{
string var;

OleDbConnection cn = new OleDbConnection("Provider=MSDAORA.1;User ID=system;password=tiger;Data Source=XE;Persist Security Infor=False");

try
{
cn.Open();
string s = "Insert into busdetails(BUS_ID,TOTAL_SEATS,BUS_TYPE,JOURNEY_DATE,ARR_TIME,DEPT_TIME,SOURCE,DESTINATION,TICKET_PRICE,B_POINT,D_POINT) values(:busid,:totseats,:bustype,:jdate,:arrtime,:dtime,:source,:dest,:tickprice,:bpoint,:dpoint)";

OleDbCommand cmd = new OleDbCommand(s, cn);
//@busid,@totseats,@bustype,@jdate,@arrtime,@dtime,@source,@dest,@tickprice,@bpoint,@dpoint
cmd.Parameters.AddWithValue("busid", Convert.ToDecimal(busid));
cmd.Parameters.AddWithValue("totseats", Convert.ToDecimal(totalseats));
cmd.Parameters.AddWithValue("bustype", bustype);
cmd.Parameters.AddWithValue("jdate", journeydate);
cmd.Parameters.AddWithValue("arrtime", arrtime);
cmd.Parameters.AddWithValue("dtime", depttime);
cmd.Parameters.AddWithValue("source", source);
cmd.Parameters.AddWithValue("dest", destination);
cmd.Parameters.AddWithValue("tickprice", Convert.ToDecimal(ticketprice));
cmd.Parameters.AddWithValue("bpoint", bpoint);
cmd.Parameters.AddWithValue("dpoint", dpoint);

cmd.ExecuteNonQuery();
var="success";
}
catch (OleDbException e1)
{
Console.Write(e1.Message);
Console.Write(e1.StackTrace);
var = e1.Message;
}
cn.Close();
return var;
}
}

table format is
bus_id not null nummber(6,2)
total_seats number
bus_type varchar2<4000>
journey_date varchar2<4000>
arr_time varchar2<4000>
dept_time varchar2<4000>
source varchar2<4000>
destination varchar2<4000>
ticket_price number(5,2)
b_point varchar2<4000>
d_point varchar2<4000>
I am getting this error

ORA 0:01008: Not all variables bound

CHill60 24-Jul-13 7:20am

I think you meant this comment for woopsydoozy as you've used the syntax he suggested not mine. I personally haven't used bound variables like this so I won't be much help going forward - you could try cmd.BindByName = true; first though