PSQL can't add value to parameters

Question

I'm trying to wirte a PSQL query that somewhat easily readable, so I want to use the PsqlCommand.Parameters.AddWithValue() function, but I have some problem with that...

Here is my code:

PsqlConnection connection = new PsqlConnection(connectionString);
PsqlCommand cmd = new PsqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = connection;
connection.Open();

cmd.CommandText = @"
    INSERT INTO Table (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field111, field12)
    VALUES (@Field1, @Field2, @Field3, @Field4, @Field5, @Field6, @Field7, @Field8, @Field9, @Field10, @Field111, @Field12)";

cmd.Parameters.AddWithValue("@Field1", someValue1)
cmd.Parameters.AddWithValue("@Field2", someValue2);
cmd.Parameters.AddWithValue("@Field3", someValue3);
...
...
...
cmd.Parameters.AddWithValue("@Field12", someValue4);

cmd.ExecuteNonQuery();
connection.Close();

But I get an error that says: Pervasive.Data.SqlClient.PsqlException: 'Pervasive.Data.SqlClient.Lna.k: [LNA][Pervasive][ODBC Engine Interface]Error in expression: @Field1 '

I tried it on some other way, which was this one:

PsqlConnection connection = new PsqlConnection(connectionString);
PsqlCommand cmd = new PsqlCommand();
cmd.CommandTimeout = 0;
cmd.Connection = connection;
connection.Open();

cmd.CommandText = @"
    INSERT INTO Table (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field111, field12)
    VALUES (@Field1, @Field2, @Field3, @Field4, @Field5, @Field6, @Field7, @Field8, @Field9, @Field10, @Field111, @Field12)";

PsqlParameter field1 = new PsqlParameter("@Field1", PsqlDbType.VarChar, 12);
PsqlParameter field2 = new PsqlParameter("@Field2", PsqlDbType.VarChar, 30);
PsqlParameter field3 = new PsqlParameter("@Field3", PsqlDbType.VarChar, 1);
...
...
...
PsqlParameter field12 = new PsqlParameter("@Field12", PsqlDbType.VarChar, 1);


cmd.Parameters.Add(field1).Value = someValue1;
cmd.Parameters.Add(field2).Value = someValue2;
cmd.Parameters.Add(field3).Value = someValue3;
...
...
...
cmd.Parameters.Add(field12).Value = someValue12;

cmd.ExecuteNonQuery();
connection.Close();

But I get the same error :( Any help would be greatly appreciated!

try using `:` instead of `@` in parameter names. Like `:Field1`, `:Field2`, `:Field3` etc. — Chetan, Feb 24 '21 at 09:13
@ChetanRanpariya I didn't use `?` but I tried it, no success, with that and neither with `:`... I tried it with `$` and now it says something else, it says: `[LNA][Pervasive][ODBC Engine Interface]Error in expression: INSERT INTO Table (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field111, field12) VALUES (<< ??? >>$Field1'` Any ideas? — Martin Serdült, Feb 24 '21 at 09:21
Other thing you can try is to use just `?` instead of parameter names and also add parameters with name `?` in command with values in the same order as they are expected in the query.. As suggested in https://stackoverflow.com/questions/18082840/how-to-bind-parameters-via-odbc-c — Chetan, Feb 24 '21 at 09:39
It seems like that's the way to do it! :D If I change the query to this `cmd.CommandText = @"INSERT INTO Table (field1, field2, field3, field4, field5, field6, field7, field8, field9, field10, field111, field12) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";` I can add values to the parameters like this: `cmd.Parameters.AddWithValue("Field1", someValue1);` Thank you! :D — Martin Serdült, Feb 24 '21 at 10:11
Pervasive only uses the `?` for parameters. You can't use the `@` or `:`. — mirtheil, Feb 24 '21 at 14:19

score 0 · Accepted Answer · answered May 21 '21 at 15:52

Pervasive databases use ? as a parameter, nothing else. For example:

command.CommandText = "INSERT INTO Table (name, number, date) VALUES (?, ?, ?)";
command.Parameters.AddWithValue("name", stringName);
command.Parameters.AddWithValue("number", integerNumber);
command.Parameters.AddWithValue(null, dateTimeDate);

IMPORTANT!

As you can see at the 3rd example, you can use null, as an identifier for the variable. That's because the order is important and the identifier is arbitary.

PSQL can't add value to parameters

1 Answers1

IMPORTANT!