CODEWITHSUNDEEP
c#sqloracle-database
Magic Mick
Magic Mick

Reputation: 1523

SQL command not working through C#

Ok so I am having a mind bender moment here and cannot for the life of me work out why the following code only works intermittently.

When I run the following code I usually get a result from the database, but I recently found an item which returns an error ORA-01858 a non-numeric character was found where a numeric was expected. I have tried debugging the code and copy pasting the exact runtime SQL command into Toad for Oracle and it returns results without problems, just not in my app?

Code:

            using (var conn = OracleConnect(username, password))
            {
                conn.Open();
                string cmd = "SELECT DISTINCT(ENTITYID) FROM MY.DATABASE WHERE ITEM_ID = '" + itemid + "'";
                using (var oleCmd = new OleDbCommand(cmd, conn)) //ORA-01858 exception here
                {
                    using (var oleRead = oleCmd.ExecuteReader())
                    {
                        while (oleRead.Read())
                        {
                            string entity = oleRead["ENTITYID"].ToString();
                            if (!string.IsNullOrEmpty(entity)) _entityid.Add(entity);
                        }
                    }
                }
            }

Any ideas?

RESOLVED thanks to Hassan

I tried using Oracle.DataAccess.Client; OracleConnection with OracleCommand and it seems to work, not sure why OleDb didn't.

Upvotes: 0

Views: 231

Answers (3)

emmet
emmet

Reputation: 67

ITEM_ID is alphanumeric (int) and not a varchar. so the value of "itemid" you are passing is rejected because it's not alphanumeric.

Upvotes: -2

mybirthname
mybirthname

Reputation: 18127

using (var conn = OracleConnect(username, password))
            {
                conn.Open();
                string cmd = "SELECT DISTINCT(ENTITYID) FROM MY.DATABASE WHERE ITEM_ID =?";
                using (var oleCmd = new OleDbCommand(cmd, conn))
                {
                    oleCmd.Parameters.AddWithValue("@p1", itemid);
                    using (var oleRead = oleCmd.ExecuteReader())
                    {
                        while (oleRead.Read())
                        {
                            string entity = oleRead["ENTITYID"].ToString();
                            if (!string.IsNullOrEmpty(entity)) _entityid.Add(entity);
                        }
                    }
                }
            }

Like this should work and use parameters to protect your application from sql injection ! Also if this is serious application please make Database connection on different layer.

Upvotes: 3

bl2b
bl2b

Reputation: 121

remove the double single quote in the WHERE condition. May be your ITEM_ID is integer but you passes a string value. Here's the revised code

string cmd = "SELECT DISTINCT(ENTITYID) FROM MY.DATABASE WHERE ITEM_ID = " + itemid + "";

in database it will generate this script:

SELECT DISTINCT(ENTITYID) FROM MY.DATABASE WHERE ITEM_ID = 1

Upvotes: 0

Related Questions