1

I want to insert multiple rows into my database, I tried to use parameters but I did something wrong.

In debug mode, the parameters do have values, but once the insert is done, I still have @compte, @mo, @userimp in my database instead of their values.

string INSERTDELAMORTPart2 = "'123'," + "'@compte'" + "," + "'@mo'" + "," + "'ubs'" + "," + "'2'" + "," + "'@userimp'" + "," + "'22.10.17'" + ",'";

OdbcCommand Query = new OdbcCommand(INSERTDELAMORTPart2, InfoConnexion); 

Query.Parameters.AddWithValue("@compte", _cb_Compte.SelectedItem);
Query.Parameters.AddWithValue("@mo", MondantResultat);
Query.Parameters.AddWithValue("@userimp", UserImp);

Query.ExecuteNonQuery();

I have tried Parameters.Add, and also Parameters["@name"].Value, but the result stays the same.

What am I doing wrong ?

EDIT :

I have an Oracle database and I need the single quote to insert my values. I have tried to pass my whole INSERT string into a parameter but it doesn't work.

I'm about to give up and just replace the ' by '' even if it's a bad idea.

Improve this question
4
  • Don't use string concatenation, use parameters for everything. Commented Nov 18, 2017 at 14:38
  • Query in the post does not use parameters but simply quoted strings... Are you sure you pasted correct sample? Commented Nov 18, 2017 at 14:38
  • Additionally, you've specified single quotes around your parameters, which means you'll try to insert the parameter names as strings, not their values. Commented Nov 18, 2017 at 14:39
  • I have change my string to INSERTDELAMORTPart2 = "'123'," + "@compte" + "," + "@mo" + "," + "'ubs'" + "," + "'2'" + "," + "@userimp" + "," + "'22.10.17'" + ",'"; But if i delete the double-quote i get the error compte, mo and userimp don't exist. Commented Nov 18, 2017 at 16:20

3 Answers 3

Reset to default
2

A few things.

  1. If you are using Oracle, I highly recommend you use ODP.net or managed ODP.net, not ODBC. ODBC should work, but the native drivers will work more efficiently and will avoid any oddities you might with with the abstraction layer ODBC provides. Managed ODP.net has the additional advantage of not requiring an Oracle client be installed on the target machine

  2. SQL Server (and other databases) use the "@" as a parameter, but Oracle uses the colon ":" instead. Some databases (like PostgreSQL) will take either. While the parameter in your SQL requires the colon, when you use assign the parameter in .NET you need to omit it. Again, this is an Oracle thing you won't find in other databases.

  3. Your SQL doesn't appear to have any command in it... only values. Where is your "insert into?"

  4. Especially with Oracle, you want to be explicit about your datatypes... so use Add instead of AddWithValue (which is actually an overload of Add in ODP.net). Alternatively, you can set the datatype later.

The converted code would look something like this:

string insert = "insert into my_table values (:COMPTE, :MO, :USERIMP)";

using (OracleCommand cmd = new OracleCommand(insert, Connection))
{
    // no ":" in the parameter declarations
    cmd.Parameters.Add(new OracleParameter("COMPTE", OracleDbType.Int32));
    cmd.Parameters.Add(new OracleParameter("MO", OracleDbType.Varchar2));
    cmd.Parameters.Add(new OracleParameter("USERIMP", OracleDbType.Varchar2));
    cmd.Parameters[0].Value = 123;
    cmd.Parameters[1].Value = "ubs";
    cmd.Parameters[2].Value = "22.10.17";

    cmd.ExecuteNonQuery();
}

I guessed at your datatypes -- make sure they match your DDL when you actually implement this, and if these aren't the only three columns, update your insert command appropriately.

If for some reason I misunderstood, and this is not Oracle, I still recommend use of the native driver rather than ODBC. When you use ODBC, you assume a lot about the target machine -- assumptions that may not turn out to be true.

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

2 Comments

I changed my "@" by ":" and used the .Add then .Value and it worked. I spent my whole weekend for this. BIG THANK YOU. PS: The use of the ODBC driver is in the specification.
I'm so glad. I added the Oracle tag to your question, just in case anyone else ever runs into a similar issue. Thanks for letting me know it worked. If you ever have to do thousands of inserts, do check out ODP.net. It has bulk insert/update that will make it worth it.
2

The command should look something like this:

insert into t(compte, mo, userimp) -- of course the table and columns should be the right names
    values (@compte, @mo, @userimp);

No single quotes are needed.

I would recommend against using AddWithValue(). Instead, be explicit about the types.

Improve this answer

2 Comments

I can't delete the double-quote or I get the error that compte, mo and userimp don't exist
@FistiPaul . . . This answer says nothing about double quotes. It refers to single quotes in the query.
1

You have this:

string INSERTDELAMORTPart2 = "'123'," + "'@compte'" ...

Note the single quotes around the parameter names. They should not be there. You want this instead:

string INSERTDELAMORTPart2 = "'123'," + "@compte"
Improve this answer

1 Comment

If I delete the single quotes, I get a missing expression when I insert. Even if I try to insert by myself in SQL without the single quotes I get an invalid column error. PS:@compte has a value of "CH1800767000Z52461335" Is there a way to load the value into @compte before .ExecuteNonQuery() ? So I can add to my string those single quotes that I need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.