Error : Parameter name not found, in Postgresql

Question

I am working in Asp.net with postgresql with devart dotconnect connection driver. When i am executing a parmeterized query it is showing error,

Parameter name not found

My code is below

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);
DAF.Command.CommandText = @"Do $$
DECLARE newTestID INT;
BEGIN
"+"Insert into \"TestMaster\"(\"TestName\", \"TestShortName\", \"RCUID\", \"RCDate\", \"RCTime\") " + @"
    "+"values (:TestName, :TestShortName, 0, CURRENT_DATE, CURRENT_TIME(0)) " + @"
    "+"RETURNING \"TestID\" INTO newTestID;"+@"
END$$;";

// Rest is execution code

What i am making a mistake here ?

Edit : I got on which code this message is occurring, it is not on insert command it is on returning command.

Answer 1

Parameter name not found must be an error from the database driver. It's hard to be sure without the full exception trace, but at a guess I think that DevArt must require you to set the SQL text before attempting to set parameters.

e.g.

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.CommandText = "SELECT ...."; /* or whatever your SQL is */
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);

(I haven't used it, I'm just going by the examples in some of the docs, which while they refer to Oracle parameters seem much the same for the basics.)

---

However, it's also possible that DevArt can't parse the DO block and $$ quoting. To confirm that, try:

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);
Command.CommandText = "Insert into \"TestMaster\"(\"TestName\", \"TestShortName\", \"RCUID\", \"RCDate\", \"RCTime\") values (:TestName, :TestShortName, 0, CURRENT_DATE, CURRENT_TIME(0)) RETURNING \"TestID\" INTO newTestID;";

If this works, but the version in the DO block doesn't, then it's probably because DevArt can't parse the DO block or its $$ quoting to find parameters.

You might need to use an SQL function instead (assuming you're trying to do more after the first statement, otherwise there'd be no point using a DO block at all). Then call the function in a separate statement. e.g. first execute this SQL without any parameters:

CREATE OR REPLACE FUNCTION myfunc(testname text, testshortname text) returns void AS $$
DECLARE
    newTestID INT;
BEGIN
    INSERT into "TestMaster"("TestName", "TestShortName", "RCUID", "RCDate", "RCTime")
    VALUES (testname, testshortname, 0, CURRENT_DATE, CURRENT_TIME(0))
    RETURNING TestID INTO newTestID;

    -- whatever you want to do with newTestID here
END$$;

then run another statement to call the function, like:

Command = new Devart.Data.PostgreSql.PgSqlCommand();
Command.CommandText = "SELECT myfunc(:TestName, :TestShortName)";
Command.Parameters.AddWithValue("@TestName", testmaster.TestName);
Command.Parameters.AddWithValue("@TestShortName", testmaster.TestShortName);

to call it.

There's no CREATE TEMPORARY FUNCTION so you'll have to DROP FUNCTION afterwards if you don't want to keep and re-use it.