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
– Dietz, Commented Oct 5, 2015 at 17:31
I have not worked with OleDb for quite some time, but see if this helps you: 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.
– Eric J., Commented Oct 5, 2015 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.
– Eric J., Commented Oct 5, 2015 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
– juharr, Commented Oct 5, 2015 at 17:36
The main point here is you have to use distinct names no matter how you create and add the parameters. — juharr
– juharr, Commented Oct 5, 2015 at 19:59

Community · Accepted Answer · 2017-05-23 12:29:34Z

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, 2017 at 12:29

CommunityBot

11 silver badge

answered Oct 5, 2015 at 18:04

Caleb Mauer

6726 silver badges11 bronze badges

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

3 Comments

Vítor Martins Over a year ago

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...

Caleb Mauer Over a year ago

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.

Vítor Martins Over a year ago

You were right! Thanks very much this worked like a charm, you're the man! ;)

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related