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

Question

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())";

I see no indication that you're populating the parameters... you're never using the fName or fDescription parameters. — Jon Skeet
– Jon Skeet, Commented Sep 17, 2012 at 15:11
Thanks for your reply. fDescription is passed to the function. fName is a superfluous variable which I should not even have had in my code. I found my answer and I added it to my question. please fee free to comment on it, since that is what I found to be working. Whether it is a preferred way or not, I am interested to know. Thanks again. — user1298925
– user1298925, Commented Sep 17, 2012 at 16:21
Well fDescription passed to the method - but it's not passed to the database at all. Your workaround invites SQL injection attacks, please don't use it. — Jon Skeet
– Jon Skeet, Commented Sep 17, 2012 at 16:25
Thanks. So how do I do it otherwise? I would appreciate the correct syntax. — user1298925
– user1298925, Commented Sep 17, 2012 at 17:26
See my answer. If that doesn't work, please give details rather than just the "it did not work" that you left on the other answer. — Jon Skeet
– Jon Skeet, Commented Sep 17, 2012 at 17:27

Josh · Accepted Answer · 2012-09-17 15:12:57Z

3

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.

answered Sep 17, 2012 at 15:12

Josh

2,9751 gold badge21 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1298925 Over a year ago

Thank you. I did add parameters and it did not work. My problem was with the syntax, I found out.. I will update my question with the solution I found and please fee free to comment on it, since that is what I found to be working. Whether it is a ppreferred way or not, I am interested to know. Thanks again.

Jon Skeet · Accepted Answer · 2012-09-17 16:29:41Z

2

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.

answered Sep 17, 2012 at 16:29

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

3 Comments

user1298925 Over a year ago

Thank you so much. Your suggestipon worked. Could you possibly tell me the correct syntax for a select statement. I could put it in a separet question if you would like. command.CommandText = "SELECT F_SCRIPT FROM XYZ WHERE ID='" + UserID + "' FOR UPDATE";

Jon Skeet Over a year ago

@user1298925: What have you tried, based on my existing answer? You need to be willing to do some work for yourself, and read around a topic. Do you not understand why you should not include values directly within the SQL?

user1298925 Over a year ago

Actually, I have been trying to come up with the answer myself but I have not tested it yet. I was thinking by the time I try my different solutions maybe the right answer would be here too. Here is my untested solution. command.CommandText = "SELECT F_SCRIPT FROM XYZ WHERE ID=:UserID AND " +" NAME =:fDescription" + " FOR UPDATE "; command.Parameters.Add("UserID", OracleType.Number).Value = UserID; command.Parameters.Add("fDescription", OracleType.NVarChar).Value = fDescription;

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related