Parameters.AddWithValue vs Parameters.Add?

Question

I am inserting data using below code first why i am not able to use Parameters.AddWithValue(); ?? and what the actual difference between Parameters.AddWithValue and Parameters.Add ???? I had made search but each one said the the other is deprecated which one is really deprecated now ?! and what the Namespace for each one ?? as I know the using Parameters.AddWithValue is Oracle.DataAccess; and for the Parameters.Add is using System.data.oracle;

second why I am getting below error when at this line I don't find anything wrong with it

using (OracleCommand cmd =new OracleCommand(cmdstr,connstr))

The best overloaded method match for 'Oracle.DataAccess.Client.OracleCommand.OracleCommand(string, Oracle.DataAccess.Client.OracleConnection)' has some invalid arguments

Argument 2: cannot convert from 'string' to 'Oracle.DataAccess.Client.OracleConnection'

Oracle.DataAccess.Client.OracleParameterCollection does not contain a definition for 'AddWithValue' and no extension method 'AddWithValue' accepting a first argument of type 'Oracle.DataAccess.Client.OracleParameterCollection' could be found (are you missing a using directive or an assembly reference?)

public void connect_to_db()
{
    string connstr = "Data Source=orcl;User Id=user;Password=pwd;";
    string cmdstr = @"insert into customers 
                      set    (CUST_NAME) 
                      values (:TB_CUST_NAME);";
    using (OracleConnection conn = new OracleConnection(connstr))
    using (OracleCommand cmd =new OracleCommand(cmdstr,connstr))
    {
        conn.Open();
        cmd.Parameters.AddWithValue(":TB_CUST_NAME", TB_CUST_NAME);
        cmd.ExecuteNonQuery();
    }
}

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

The first error is caused because you pass your connstr (string) instead of your conn (OracleConnection). Change this:

using (OracleCommand cmd =new OracleCommand(cmdstr,connstr))

To this

using (OracleCommand cmd =new OracleCommand(cmdstr,conn))

As for the second one, the OracleParameter does not seem to need :. Example use:

OracleCommand oraCommand = new OracleCommand("SELECT fullname FROM sup_sys.user_profile
                           WHERE domain_user_name = :userName", db);
oraCommand.Parameters.Add(new OracleParameter("userName", domainUser));

It seems like there is a difference between Oracle.DataAccess.Client and System.Data.OracleClient. OracleParameterCollection.AddWithValue seems to exist in System.Data.OracleClient. But you use Oracle.DataAccess.Client.

edited May 23 '17 at 11:45

Community

1
1

answered Mar 18 '16 at 13:14

Ian

30,182
19
69
107

the :TB_CUST_NAME is the name of the textbox i want to retrieve its data and insert it into a table so I am not using select i am using insert into and I had point to the textbox name i want to get the value from – samer Mar 18 '16 at 14:24
@samer note that you do that (having `:`) in your `cmdstr` (`OracleCommand`), you don't do that in your `OracleParameter` – Ian Mar 18 '16 at 14:28
according to that post http://stackoverflow.com/questions/4105042/inserting-textbox-values-into-database – samer Mar 18 '16 at 15:38
i used that way now but i got error ORA-00911: invalid character cmd.Parameters.Add(new OracleParameter(":userName", textboxuserName.Text)); – samer Mar 18 '16 at 15:39
Again, the : is not needed for OracleParameter. It is only for OracleCommand. Try to remove : in the OracleParameter – Ian Mar 18 '16 at 16:02
i did that but nothing happened just nothing string cmdstr = @"insert into customers set (CUST_NAME) values (:TB_CUST_NAME);"; – samer Mar 18 '16 at 16:19
the add parameter will be like this cmd.Parameters.Add(new OracleParameter("TB_CUST_NAME", TB_CUST_NAME.Text)); – samer Mar 18 '16 at 16:19
@samer any error now? If there isn't, your code has at least been syntatically correct now. So, the original question is solved. As for the reason why your database is not updated, that is likely belong to another question. Maybe it is caused by wrong column name or wrong value. But you are syntatically correct now. – Ian Mar 18 '16 at 16:20
no error nothing happen while when I re add the : i got the ORA-00911: invalid character I tried to get the insert string and pasted into toad and run it and data inserted – samer Mar 18 '16 at 16:26
Thanks my friends and @Ian your right a f*** ; at the end of insert statment was the problem -_-"silly enough – samer Mar 18 '16 at 16:36

Parameters.AddWithValue vs Parameters.Add?

1 Answers1