How to add several OldDbParameters with values to a single OleDbCommand?

Question

Guys I'm trying to insert data into a db and I'm using the following code:

String connectionString = "Provider=vfpoledb;Data Source=C:\\TESTE;Collating Sequence=machine;";
                OleDbConnection FPDBConn = new OleDbConnection(connectionString);
                FPDBConn.Open();

                String strSQL = "INSERT INTO tercli ([id], [nome], [morada], [localidade], [codpostal], [telefone], [fax], [telemovel], [idcontr], [contribuin], [idzona], [zona], [idvendedor], [vendedor], [idpagament], [pagamento], [descontoge], [preco], [mapa], [dias], [plafond], [aviso], [email], [contacto], [rt_irs], [alim], [nru0], [cds0], [dtn0], [nru1], [nome1], [cds1], [dtn1], [nru2], [nome2], [cds2], [dtn2], [nru3], [nome3], [cds3], [dtn3], [nru4], [nome4], [cds4], [dtn4], [nru5], [nome5], [cds5], [dtn5], [dtini], [dtult], [dtprox], [obs], [agrup], [nbenef], [descontog1], [dtnasc], [activo], [cae], [resp], [ncontrato], [hst], [numexa], [numads], [numfunc], [cae1], [riva], [site], [datafac1], [datafac2], [bi], [carta], [sms], [mail], [prefer], [novo], [prof], [foto], [tel], [vlbi], [obs1], [prefer1], [distrito], [adesao], [sexo], [cpessoais], [nib], [balcao], [cproced], [dtfim], [pontostot], [pontosusa]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

                OleDbCommand FPDBCmd = new OleDbCommand(strSQL, FPDBConn);
                FPDBCmd.CommandType = CommandType.Text;
                FPDBCmd.Parameters.AddWithValue("?", "5     ");
                FPDBCmd.Parameters.AddWithValue("?", "Vitor");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "919042228");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "  ");
                FPDBCmd.Parameters.AddWithValue("?", "   ");
                FPDBCmd.Parameters.AddWithValue("?", "      ");
                FPDBCmd.Parameters.AddWithValue("?", "     ");
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", "            ");
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", "");
                FPDBCmd.Parameters.AddWithValue("?", 0.000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", "");
                FPDBCmd.Parameters.AddWithValue("?", "             ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", "      ");
                FPDBCmd.Parameters.AddWithValue("?", "        ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "       ");
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", "              ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "        ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "        ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "       ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "        ");
                FPDBCmd.Parameters.AddWithValue("?", "       ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "           ");
                FPDBCmd.Parameters.AddWithValue("?", "    ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", "            ");
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", "           ");
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", "        ");
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", "            ");
                FPDBCmd.Parameters.AddWithValue("?", "       ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", " ");
                FPDBCmd.Parameters.AddWithValue("?", "       ");
                FPDBCmd.Parameters.AddWithValue("?", "         ");
                FPDBCmd.Parameters.AddWithValue("?", "          ");
                FPDBCmd.Parameters.AddWithValue("?", "           ");
                FPDBCmd.Parameters.AddWithValue("?", now);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.Parameters.AddWithValue("?", 0.0000);
                FPDBCmd.ExecuteNonQuery();

                FPDBCmd.Dispose();
                FPDBConn.Close();

The thing is it seems to only be adding 1 parameter each time I run it instead of inputting all the parameters at the same time.

I've been looking at OleDbParameterCollection Class and doing OleDbParameter arrays but since the types of my parameters vary (OleDbType.Char, OleDbType.Numeric, OleDbType.DBDate and OleDbType.LongVarChar) I'm not exactly sure how to go about it...

Can you guys help, please?

Looks to me like you have LOTS of lines of code to specify the value of a single parameter named "?". Would replacing the ? with more appropriate names work? (im not that into oledb, hence the comment) — Dietz, Oct 05 '15 at 17:31
I have not worked with OleDb for quite some time, but see if this helps you: http://stackoverflow.com/a/28920335/141172 Note that you can include DECLARE statements in your inline SQL by separating each statement with a semicolon. — Eric J., Oct 05 '15 at 17:31
@Dietz: There are a bunch of parameters. OleDb does not support named parameters (as far as I know). Every parameter is named ? and OleDb is supposed to replace them with values based on their position in the statement. — Eric J., Oct 05 '15 at 17:33
@EricJ. True, but `Parameters` acts like a dictionary where the names are keys. For OleDb the names don't matter for the mapping to the SQL, but do for keeping them separate. — juharr, Oct 05 '15 at 17:36
Yeah @EricJ. from what I read it seems OldDb does not support named parameters and that is why I've been using "?" under the assumption it will insert them in order. — Vítor Martins, Oct 05 '15 at 17:37
Check out the example in the documentation for [OleDbParameterCollection.Add](https://msdn.microsoft.com/en-us/library/k2xhb8d5(v=vs.110).aspx). Even though ? is used in the SQL they still give the parameter a meaningful name. — juharr, Oct 05 '15 at 17:38
1) What you need to look at is [OleDbParameter Constructor](https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbparameter.oledbparameter%28v=vs.110%29.aspx), in particular the [OleDbParameter Constructor (String, OleDbType)](https://msdn.microsoft.com/en-us/library/46kb8y7x%28v=vs.110%29.aspx) overload. Just don't use `.AddWithValue`. 2) For your convenience, you can name the parameters in the add statement - the name will be ignored. — Andrew Morton, Oct 05 '15 at 17:39
@juharr the OldDbParameterCollection seemed like the way to go for me, but the constructor doesn't allow for me to define a value per parameter. How would I go about it? — Vítor Martins, Oct 05 '15 at 17:42
You can do `FPDBCmd.Parameters.Add("MeaningfulName", Type).Value = value;` This ensures the correct type is used instead of relying on `AddWithValue` which can have unexpected results. The main thing is just to give each parameter a different name, but you still have to add them in the correct order. — juharr, Oct 05 '15 at 17:43
On another note if all your parameters are hard coded, you could just put them directly into the SQL. — juharr, Oct 05 '15 at 17:46
@juharr Yes it is hardcoded right now while I'm figuring out how it works but it won't be in the future ;) also "FPDBCmd.Parameters.Add("MeaningfulName", Type).Value = value;" is not allowed since the types don't match. — Vítor Martins, Oct 05 '15 at 17:50
@VítorMartins Does it work if you use the form `FPDBCmd.Parameters.Add(new OleDbParameter { ParameterName = "id", OleDbType = OleDbType.VarChar, Size=24, Value = "5 " });`? (The Size is only relevant for some types.) — Andrew Morton, Oct 05 '15 at 18:12
@VítorMartins What do you mean by the types not matching? `OleDbParameter.Value` is an `object` so anything will work there and you have to replace `Type` in my example with the correct `OleDbType` — juharr, Oct 05 '15 at 18:13
@juharr Yes you are right, that works, but as a single parameter, I was trying to build a parameter array with that and it wasn't working. Still it works as it used to, a single parameter, it still does not insert all of them at once. — Vítor Martins, Oct 05 '15 at 19:57
@AndrewMorton Yes it works, but again as a single parameter it is still not inserting all of them... — Vítor Martins, Oct 05 '15 at 19:58
The main point here is you have to use distinct names no matter how you create and add the parameters. — juharr, Oct 05 '15 at 19:59
Thanks very much everyone, I used some of the comments and mostly Calebs answer and got it working ;) — Vítor Martins, Oct 05 '15 at 20:57

score 1 · Accepted Answer · edited May 23 '17 at 12:29

1

In place of "?" for your parameters, try using "@id", "@nome", "@morada", and so on for all the parameters.

Then add this line before .ExecuteNonQuery():

FPDBCmd.ConvertNamedParametersToPositionalParameters();
FPDBCmd.ExecuteNonQuery();

ConvertNamedParametersToPositionalParameters() allows you to use named parameters but it will convert them into question marks for you.

See this related question for more explanation

edited May 23 '17 at 12:29

Community

1
1

answered Oct 05 '15 at 18:04

Caleb Mauer

662
6
11

This only allows me to name my paremeters it doesn't solve the problem at hand which is inserting several parameters into a single command... – Vítor Martins Oct 05 '15 at 19:39
Yes it does, because each subsequent call to set the parameter is overwriting the previously set value. If you use named parameters then convert them it should properly set each parameter. As a bonus, your code will become easier to maintain and read. Try it and see if it works. I admit, there might be a different setting that would do the same thing, but this will work and look nice. – Caleb Mauer Oct 05 '15 at 19:49
You were right! Thanks very much this worked like a charm, you're the man! ;) – Vítor Martins Oct 05 '15 at 20:56

How to add several OldDbParameters with values to a single OleDbCommand?

1 Answers1