CODEWITHSUNDEEP
c#sqlinsertoracle11g
user1298925
user1298925

Reputation: 2424

Why this SQL insert into table statement does not work in C#?

I get a ORAC 0936 when ExcuteNonQuery executes. I guess my problem is the VALUES section of my SQL. Thanks in advance for your help

public int NewFunction(int UserID, string fName, string fDescription, string connectionStr)
        {
            OracleConnection connection = new OracleConnection(connectionStr);
            try
            {
                //PLease Note that User ID and  fDescription are passed into the function as  
//parameters
                connection.Open();

                OracleCommand command = connection.CreateCommand();

                OracleTransaction transaction = connection.BeginTransaction();
                command.Transaction = transaction;
                        string LoadedFileName = @WfName;//Fd.FileName;
               //read the xaml content from file 
                command.CommandText = "INSERT INTO XYZ(ID, NAME , F_SCRIPT) VALUES (@UserID, @fDescription, EMPTY_CLOB())";

                command.ExecuteNonQuery();
                transaction.Commit();
               }

////////////////////////////////////////////////////////////////////// //***************************** //OK, thanks for all the replies. This is how I got it to work, using other posts from stackckoverflow for other questions. Please note that fDescription and USerID are being passed to the function.

command.CommandText = "INSERT INTO IMS_Workflow (ID, NAME , F_SCRIPT) VALUES ('" + UserID + "', '" + WfDescription + "', EMPTY_CLOB())";

Upvotes: 0

Views: 1150

Answers (2)

Jon Skeet
Jon Skeet

Reputation: 1502036

Your original code looks like it's trying to use parameterized SQL:

command.CommandText = "INSERT INTO XYZ(ID, NAME , F_SCRIPT) VALUES (@UserID, @fDescription, EMPTY_CLOB())";

... but you never set the values for the parameters, and it's using SQL Server style names (@ prefix instead of :). You need to declare the parameters properly, and giving them values, e.g.

command.CommandText = "INSERT INTO XYZ(ID, NAME , F_SCRIPT) VALUES (:UserID, :fDescription, EMPTY_CLOB())";
command.Parameters.Add("UserId", OracleType.Number).Value = userId;
command.Parameters.Add("fDescription", OracleType.NVarChar).Value = fDescription;

(It's possible that the prefix of @ would work as well; I'm only going by examples in documentation.)

Your workaround is to embed the values directly into your SQL. Don't do this. You're inviting SQL injection attacks.

Upvotes: 2

Josh
Josh

Reputation: 2975

I may be missing something but I don't see where you're adding the parameters @UserID or @fDescription to your command object's parameters collection. Add those and I suspect you should be fine.

Upvotes: 3

Related Questions