ORA-00984: column not allowed here

Question

0.00/5 (No votes)

See more:

Hi,
I am trying to insert data to oracle db using following code

connection.Open();
string sid = txtSID.Text.ToString();
string strSQL = "INSERT INTO DBSERVERS(SID) VALUES(" + txtSID.Text + ")";
OracleCommand cmd =new OracleCommand(strSQL,connection);
cmd.ExecuteNonQuery();

This gives "ORA-00984: column not allowed here" error. Please help me.

Posted 14-Nov-10 1:26am

Kasunmit

Updated 14-Nov-10 1:35am

JF2015

v2

Add a Solution

Comments

OriginalGriff 14-Nov-10 8:56am

"i thanx for help then "ORA-01036: illegal variable name/number oracle base" error y is that ??? pls help - Kasunmit 2 mins ago"
It's possible that Oracle is objecting to the '@' character, try replacing it in both places with ':' instead, which most Oracle examples I can see are using. ('@' works fine for SQL, SqlCE, MySql, etc., but I haven't tried it on Oracle - it could be different)

OriginalGriff 14-Nov-10 9:16am

"and need to another thing if i need to inert more than one as a example i need to enter eSID and NAME how can i use ur code for it pls write this as a answer thanx - Kasunmit 6 mins ago"
How many guesses would you like? :laugh:

It's not exactly rocket science, is it?

string strSQL = "INSERT INTO DBSERVERS(SID, NAME) VALUES(:ID, :NAME)";
OracleCommand cmd =new OracleCommand(strSQL,connection);
cmd.Parameters.AddWithValue(":ID", sid);
cmd.Parameters.AddWithValue(":NAME", name);
...

1 solution

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)

OriginalGriff · Accepted Answer · 2010-11-14T02:03:00

A couple of things:

string sid = txtSID.Text.ToString();

What is this going to do? The Text property is a string already - using ToString is going to do nothing except waste time and memory!

string strSQL = "INSERT INTO DBSERVERS(SID) VALUES(" + txtSID.Text + ")";

Why did you not use the "sid" you prepared in the line above? It makes no difference, but still...

string strSQL = "INSERT INTO DBSERVERS(SID) VALUES(" + txtSID.Text + ")";

Never do this!
Two reasons:
1) You lay yourself wide open to an SQL Injection attack - Google "Bobby Tables" to find out what I mean.
2) It is probably causing your problem, because the content of =your text box is being passed as part of the SQL command.

Instead, use:

string strSQL = "INSERT INTO DBSERVERS(SID) VALUES(@ID)";
OracleCommand cmd =new OracleCommand(strSQL,connection);
cmd.Parameters.AddWithValue("@ID", sid);
cmd.ExecuteNonQuery();

This way, the content of the textbox is passed in verbatim and cannot be treated as a command.