How to insert image in oracle database in c# .net application

Question

When i tried to insert, the error 'not enough values' appears.

public int CreateAdmin( string product_name, string quality, string quantity, string price, string product_image)
{            
    string connectionString = "User Id=hr;Password=hr;Data Source=localhost:1521/xe";

    OracleConnection orc = new OracleConnection();
    orc.ConnectionString = connectionString; //assign connection
    //OracleDataAdapter da = new OracleDataAdapter();
    orc.Open();

    OracleCommand cmd = new OracleCommand();//object of command
    cmd.Connection = orc;
    cmd.CommandType = CommandType.Text; // declare command type

    cmd.CommandText = "insert into Product values( :b, :c, :d, :a, :p)";

    cmd.Parameters.Add("@b", product_name); //add paramenter
    cmd.Parameters.Add("@c", quality);
    cmd.Parameters.Add("@d", quantity);
    cmd.Parameters.Add("@a", price);
    cmd.Parameters.Add("@p", product_image);

    //da.InsertCommand = cmd;
    int i = cmd.ExecuteNonQuery();
    orc.Close();
    return i;
}

what happens if you change all the parameter adds to be Parameters.Add(new OracleParameter("paramname", paramvalue)); such as Parameters.Add(new OracleParameter("a", price)); — BugFinder
– BugFinder, Commented Feb 2, 2018 at 10:17
Possible duplicate of How to write parameterized oracle insert query? — Patrick Artner
– Patrick Artner, Commented Feb 2, 2018 at 13:08

Patrick Artner · Accepted Answer · 2018-02-02 15:15:44Z

1

Your paramnames are wrong, they need to be named as the one you set in params - you use :a in the command and @a when setting the parameter. Parameter start with @ (for SqlServer) or : (for OracleServer).

If your table has more columns than those 5 you have to provide them as well, else the db does not know where to put the given parameters. (an auto-inc ID additionally is fine, if thats your 6th column it will work).

public int CreateAdmin (string product_name, string quality, string quantity, string price, string product_image)
{
  string connectionString = "User Id=hr;Password=hr;Data Source=localhost:1521/xe";

  using (var orc = new OracleConnection (connectionString))
  {
    using (var cmd = orc.CreateCommand ())
    {
      cmd.CommandType = CommandType.Text; // declare command type

      // Product has exactly 5 or 5 + 1 auto-inc ID column, else provide the
      // column names as well: 
      // insert into Product ( name,qual,quant,price,img ) values( :b, :c, :d, :a, :p)";
      cmd.CommandText = "insert into Product values( :b, :c, :d, :a, :p)";

      cmd.Parameters.Add (":b", product_name); //add paramenter
      cmd.Parameters.Add (":c", quality);
      cmd.Parameters.Add (":d", quantity);
      cmd.Parameters.Add (":a", price);
      cmd.Parameters.Add (":p", product_image);

      //da.InsertCommand = cmd;
      int i = cmd.ExecuteNonQuery ();

      return i;
    }
  }
}

I changed your code to benefit from using(var orc = new OracleConnection()) { .... }-pattern with IDisposables - it auto-close/disposes your connection (same for command) on leaving the scope.

Edited due to comment by Wernfried-Domscheit:

Oracle needs : (@ is for SqlServer) - this answer how-to-write-parameterized-oracle-insert-query supports it - it even uses parameter names without : - so perhaps parameternames are just inserted by "order" instead of "by name"

edited Feb 2, 2018 at 15:15

answered Feb 2, 2018 at 11:09

Patrick Artner

51.9k10 gold badges50 silver badges79 bronze badges

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

2 Comments

Wernfried Domscheit Over a year ago

I think @ is for SQL-Server. For Oracle it must be : (in both, the SQL string and the parameter name)

Patrick Artner Over a year ago

@WernfriedDomscheit Thanks for pointing it out - the linked answer omits the : in the parametername though.

user5307594 · Accepted Answer · 2018-02-02 10:29:22Z

0

You have two options for saving an image to your DB:

Convert your image to Base64 and save the output string to your DB
Save your image as a file on your server and save the path of this image to your DB (Recommended)

answered Feb 2, 2018 at 10:29

user5307594

Comments

SaschaM78 · Accepted Answer · 2018-02-02 14:22:59Z

You will most likely have more columns in your Product table than you give in your values list. Explicitly give column names:

OracleCommand cmd = new OracleCommand();//object of command
cmd.Connection = orc;
cmd.CommandType = CommandType.Text; // declare command type

// explicitly add column names
cmd.CommandText = "insert into Product (product_name, quality, quantity, price, product_image) values( :b, :c, :d, :a, :p)";
cmd.Parameters.Add(":b", product_name); //add paramenter
cmd.Parameters.Add(":c", quality);
cmd.Parameters.Add(":d", quantity);
cmd.Parameters.Add(":a", price);
cmd.Parameters.Add(":p", product_image);
int i = cmd.ExecuteNonQuery();

Please change the column names in the above code to match your column names in your table. Let us know if that worked for you.

Edit

As stated by Patrick Artner in his well-written answer, the parameter name placeholders had to be the same in the CommandText and when adding the parameters to the command. When using OracleCommand, the placeholder needs to be : rather than @ as stated in the MSDN OracleCommand.Parameters page.

Collectives™ on Stack Overflow

How to insert image in oracle database in c# .net application

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related