?
Solved

Oracle ExecuteNonQuery Error invalid character, but for other sql database system is good

Posted on 2008-11-15
11
Medium Priority
?
895 Views
Last Modified: 2012-06-21
Oracle SQL ExecuteNonQuery Error invalid character, but for other sql database system is good.

The code is working fine in MySql, MsSql, FirebirdSql.
And also work in Oracle SQL Plus when inserting a command like:
INSERT INTO TEST(number1,number2,string1) VALUES(1,1,'a');

Whats in oracle? Why this code errors an invalid character in ExecuteNonQuery()?
Any suggestion of the code for oracle?
Or perhaps edit the code for sure.

Honestly I hate oracle system database, but the company requires to use it.
Hmp!


Private lConStr As String = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=FINGER)));User Id=myuser;Password=mypassword;"       ''''''glova private variable
 
Protected lmyconn As OracleConnection   '''''gloval variable
Protected lmyComIUD As New OracleCommand ''''global variable
 
Private OpenDBs...................      ''''called during form opened.
lmyconn = New OracleConnection()
lmyconn.ConnectionString = lConStr
lmyconn.Open()
lmyComIUD.Connection = lmyconn
End Sub
 
Private sub Saving_Click...................................  ''''called when clicked
 
     Dim sqlIUD As String ''''private variable
     sqlIUD = "INSERT into test(number1,number2,string1) VALUES(?a?b?c)"
 
     lmyComIUD.Parameters.Add("?a", 2)
     lmyComIUD.Parameters.Add("?b", 2)
     myComIUD.Parameters.Add("?c", "b")
 
     lmyComIUD.CommandType = CommandType.Text
     lmyComIUD.CommandText = sqlIUD
 
     lmyComIUD.ExecuteNonQuery() '''''error invalid character. works in mysql,mssql
 
 
End Sub

Open in new window

0
Comment
Question by:rionroc
  • 5
  • 4
  • 2
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22967066
remove the ";" at the end.
0
 
LVL 12

Author Comment

by:rionroc
ID: 22967132
Hello

Thanks for the quick reply.

I did not put any ;

Except this: but works fine.
And also work in Oracle SQL Plus when inserting a command like:
INSERT INTO TEST(number1,number2,string1) VALUES(1,1,'a');

But in .Net it didn't.
Please see the code.


Great is our GOD.
:)
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22967248
Shouldn't this statement
>> sqlIUD = "INSERT into test(number1,number2,string1) VALUES(?a?b?c)"

be like this?

sqlIUD = "INSERT into test(number1,number2,string1) VALUES(?a, ?b, ?c)"
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 12

Author Comment

by:rionroc
ID: 22967553
Hello


Thanks for that.


But its not working too.



Great is our GOD.
:)
0
 
LVL 27

Accepted Solution

by:
sujith80 earned 2000 total points
ID: 22967873
Generally bind variables are prefixed with a colon, I am not sure about the usage in .net.

Try to use colons instead of the question marks for the bind variables. Something like

sqlIUD = "INSERT into test(number1,number2,string1) VALUES(:a, :b, :c)"
     lmyComIUD.Parameters.Add(":a", 2)
     lmyComIUD.Parameters.Add(":b", 2)
     myComIUD.Parameters.Add(":c", "b")
 
0
 
LVL 12

Author Comment

by:rionroc
ID: 22967955
Hello


Thanks for that.


But its not working too.



Great is our GOD.
:)
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22967991
Well, the add parameter portion didn't have to use the colons by the way.
Try this

sqlIUD = "INSERT into test(number1,number2,string1) VALUES(:a, :b, :c)"
     lmyComIUD.Parameters.Add("a", 2)
     lmyComIUD.Parameters.Add("b", 2)
     myComIUD.Parameters.Add("c", "b")
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22968284
rionroc,

> But its not working too.
is a bit few feedback. some error message should be helpful.

try to help us help you!

but I think that sujith80 has got it, now...
0
 
LVL 12

Author Comment

by:rionroc
ID: 22970307
Ops!

>>but I think that sujith80 has got it, now...
Yes, it's working fine now.

Sorry, I did not recognize it, because some error comes out after that command:

Public Function Savedid() as integer
 
     Dim sqlIUD As String ''''private variable
     sqlIUD = "INSERT into test(number1,number2,string1) VALUES(:a,:b,:c)"
 
     lmyComIUD.Parameters.Add(":a", 2)
     lmyComIUD.Parameters.Add(":b", 2)
     myComIUD.Parameters.Add(":c", "b")
 
     lmyComIUD.CommandType = CommandType.Text
     lmyComIUD.CommandText = sqlIUD
 
     lmyComIUD.ExecuteNonQuery() '''works fine now because of : only :)


Dim did As Integer
Dim idCMD As OracleCommand = New OracleCommand("SELECT @@IDENTITY", lmyconn)
did = CInt(idCMD.ExecuteScalar()) ''''error was ORA-00936: missing expression '''
 
Return did

End Sub



Great is our GOD.
:)
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22970318
i dont understand.
what is not working now?

if you are referring to this statement
>>Dim idCMD As OracleCommand = New OracleCommand("SELECT @@IDENTITY", lmyconn)
It is not a valid SQL statement. An sql statement should have a SELECT and FROM part for the minimum.
0
 
LVL 12

Author Closing Comment

by:rionroc
ID: 31517077
Thanks :)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question